B4J Tutorial [Server] Table report with Export feature

Erel

Administrator
Staff member
Licensed User


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

AscySoft

Active Member
Licensed User
1) Is there a way to align to right "Population" cell?
2) How about sorting by population descending by default?
3) And another question: how to display a sum value, ie like "all the people in the world:" and put a value for "sum(population)"?
Thanks in advance.
 

AscySoft

Active Member
Licensed User
Ok, I have the answer for the 2nd question.
B4X:
 <script>
    $(document).ready(function() {
    $('#table1').dataTable( {
      "bProcessing": true,
       "order": [[ 2, "desc" ]],
      "sAjaxSource": 'tableHelper?method=show' //this is the server handler
    } );
  } );
  </script>
 

billzhan

Active Member
Licensed User
The footer callback example is a bit complicated.

I have a simple one with datatables v1.10, I can't find the original link on datatables.net

HTML:
    //add a button
<button onclick='sumColumn()'  id="btntosum">sumofColumn</button>  

<script>
//function to get the sum
        function sumColumn() {
        var table = $('#table1').DataTable();

        var sum = 0;
        var datalists=table.rows().data();    //get all rows

        if    (datalists){
            $.each(datalists,function(name,value) {
            sum += +value[0];  //0 is the first column, 1 is the second
            });                  
        };
      

        $("#btntosum").html(sum);
};  
</script>
 

billzhan

Active Member
Licensed User
You need try to add a callback(like the footer call back).

B4X:
$('#table1').dataTable( {
        "paging":  false,
        "ordering": true,
        //add more......
       
        //add callback
        drawCallback: function () {
            sumColumn();  
        }
   
    } );
 

AscySoft

Active Member
Licensed User
Yes. This was so simple after all. Thank you.:) One question does remain from post#2, the 1st one.
I just assume i must alter index.css by adding for "th" element a "text-align: right" or "align:right" element. Tryout and error. I will post back.
 

AscySoft

Active Member
Licensed User
No. It is by altering "table1" propertis like this:
B4X:
<script>
$(document).ready(function() {
$('#table1').dataTable( {
             "bProcessing": true,
             "order": [[ 2, "desc" ]],
             "sAjaxSource": 'tableHelper?method=show' //this is the server handler
             "aoColumnDefs": [
                           {"sClass": "left", "aTargets": [ 0 ]},
                           {"sClass": "center", "aTargets": [ 1 ]},
                           {"sClass": "right", "aTargets": [ 2 ]},
                             //and so on...
                                          ]
          } );
} );
</script>
The only thing I don't like is that aligning to right brings table header automatically to the right(to much close to sort sign), and I would preferred it to the center also.
This conclude my 1st question and all 3 of them. Thanks for the help. Hope it helps.
 
Top