Display sum of a column(as total) on a datatable and export to excel

0
votes

I am exporting a datatable as excel file with 4 columns, out of that my column 3 contains product prices, now after the export, I should see an additional row at the end of the table with "Total" and contains the sum of column 3 values.

I thought of showing it on the datatable using footercallback and export the datatable directly, but as I am using multi filter in the footer section and can't create one more footer this idea didn't work.

Expectation: Show the sum of a column-3(filtered) on the html page near to datatable saying that when I export as excel it should contain the summation value at the end of the table rows.

2
javascriptjqueryhtmldatatables
Add total as hidden column to datatable. When you export datatable it will also have hidden column values – Sangita Kendre
My requirement is to add a *row" as total which would give the Sum of the product prices @sangita – user3928562
Please share more details with some code sample to understand better – Sangita Kendre

2 Answers

1
votes

You can do something like this:

    var len=$("tr").length;

    var sum=0;

    for(var i=1;i<len;i++){
      sum += parseFloat($("tr td").eq(2).html());
    }

    $("table").createElement("<tr><td></td><td></td><td>"+sum+"</td></tr>");
0
votes

As I understood you need total of all prices for each product. You can have total as hidden column in datatable. When you do excel export it will show total. Here is sample html and javascript code. Hope this might help you.

HTML

<table class="table display" id="table2" >
        <thead>
            <tr>
                <th>Product</th>
                <th>Price </th>
                <th>Tax</th>
                <th>Shipping Charges</th>
                <th>Total</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <th>Product1</th>
                <td>100</td>
                <td>20</td>
                <td>12</td>
                <td>132</td>
            </tr>
            <tr>
                <th>Product2</th>
                <td>234</td>
                <td>34</td>
                <td>32</td>
                <td>300</td>
            </tr>
            <tr>
                <th>Product3</th>
                <td>543</td>
                <td>23</td>
                <td>54</td>
                <td>620</td>
            </tr>

        </tbody>
    </table>

Javascript

  $('#table2').dataTable({


            dom: 'Bfrtip',
            "columnDefs": [

            {
                "targets": [ 4 ],
                "visible": false
            }
        ],
            buttons: [{
                extend: 'excel',
                filename : 'test',

            }]
        });