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,107

AscySoft

Active Member
Licensed User
Longtime 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
Longtime 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
Longtime 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>
 

AscySoft

Active Member
Licensed User
Longtime User
I confirm it works with a button. Thanks. But how to calculate sum on page load, without clicking on a button?
In mine example always return 0 on after document ready. With a button it return proper value.
 

billzhan

Active Member
Licensed User
Longtime 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
Longtime 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
Longtime 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.
 

delozoya

Member
Licensed User
Longtime User
Dont work because I dont view nothing in the web and i have a error
 

Attachments

  • 1.jpg
    1.jpg
    172.7 KB · Views: 667

delozoya

Member
Licensed User
Longtime User
No, in the log no. I upload the proyect.
 

Attachments

  • test.zip
    201.9 KB · Views: 442

billzhan

Active Member
Licensed User
Longtime User
Pls find changed projected(see line commented as changed). It should work now
 

Attachments

  • testt changed v2.zip
    290.6 KB · Views: 552
Top