B4J Tutorial [Server] Table report with Export feature

SS-2014-03-23_12.54.09.png


Online example: http://basic4ppc.com:51042/reports/index.html

In this example we are using a JQuery open source plugin named DataTables: https://datatables.net/release-datatables/examples/data_sources/ajax.html

DataTables makes it very simple to add powerful tables with features such as: pagination, search, sorting and others.
The data is loaded from the server. The server response is a JSON object.

This is the server code:
B4X:
Dim sq As SQL = DB.pool.GetConnection 'get a DB connection (change DB to Main)
Dim rs As ResultSet = sq.ExecQuery("SELECT Name, ID, Population FROM countries ORDER BY name ASC")
Dim countries As List
countries.Initialize
Do While rs.NextRow
   Dim country As List
   country.Initialize
   country.Add(rs.GetString("Name"))
   country.Add(rs.GetString("ID"))
   country.Add(rs.GetString("Population"))
   countries.Add(country)
Loop
sq.Close
'create a JSON response
Dim m As Map
m.Initialize
m.Put("aaData", countries)
Dim jg As JSONGenerator
jg.Initialize(m)
resp.ContentType = "application/json"
resp.Write(jg.ToString)

The html code:
B4X:
  <div id="tableDiv">
     <table id="table1">
       <thead>
       <tr>
         <th>Name</th>
         <th>ID</th>
         <th>Population</th>
       </tr>
     </thead>
     </table>
   </div>
   
   <script>
    $(document).ready(function() {
     $('#table1').dataTable( {
       "bProcessing": true,
       "sAjaxSource": 'tableHelper?method=show' //this is the server handler
     } );
   } );
   </script>

The user can also export the data as a CSV file or XLS (Microsoft Excel) file. The CSV file is created with StringUtils.SaveCSV.
The XLS file is created with jExcel library.
Note that in both cases we need to create a temporary file. You should remember that the code can be executed by several threads at the same time (if there are several concurrent requests). So each thread must work with its own temporary file.
We use the current thread index for that:
B4X:
Dim fileName As String = "xls_" & Main.srvr.CurrentThreadIndex

The handler code and the static files are included in the zip file.
 

Attachments

  • TableExample.zip
    5.7 KB · Views: 1,108

rbghongade

Active Member
Licensed User
Longtime User
Dear friends,
How can I add table rows based on timer expiry event? To be more precise, I want to add the current data after some time interval on webpage. So finally, i get the log of all the data with time stamp on the webpage.
 
Top