I have a report, using DataTables with JQuery, using the export functionality of HTML5. I have the following columns

Code | Initial date | End Date | Collection Status | Amount to be paid

To export from my excel, I use following code:

buttons : [
    {
        extend : 'excelHtml5',
        exportOptions: {
            columns: ':visible',
            format: {
                body: function(data, row, column, node) {
                    return data.replace(',', '.');
                }
            }
        },
        className : 'btn btn-primary pull-right',
        text : 'Descargar <i class="glyphicon glyphicon-export"></i>',
        customize : function(xlsx) {
            var sheet = xlsx.xl.worksheets['sheet1.xml'];
            $('row c', sheet).attr('s', '25');
            $('row:first c', sheet).attr('s', '27');
        }
    },
    ....
]

I would like to get the sum of all of the values at the end, how can I achieve this?

I have a report, using DataTables with JQuery, using the export functionality of HTML5. I have the following columns

Code | Initial date | End Date | Collection Status | Amount to be paid

To export from my excel, I use following code:

buttons : [
    {
        extend : 'excelHtml5',
        exportOptions: {
            columns: ':visible',
            format: {
                body: function(data, row, column, node) {
                    return data.replace(',', '.');
                }
            }
        },
        className : 'btn btn-primary pull-right',
        text : 'Descargar <i class="glyphicon glyphicon-export"></i>',
        customize : function(xlsx) {
            var sheet = xlsx.xl.worksheets['sheet1.xml'];
            $('row c', sheet).attr('s', '25');
            $('row:first c', sheet).attr('s', '27');
        }
    },
    ....
]

I would like to get the sum of all of the values at the end, how can I achieve this?

• javascript
• jquery
• datatables

• Do you want to get total amount to be paid? – Prashant Pokhriyal Commented Apr 13, 2018 at 16:29
• @PrashantPokhriyal That's right. I want to calculate the sum of all the values at the end of the column – Oscar Moncayo Commented Apr 13, 2018 at 18:28

2 Answers 2

Looks like you want to implement Footer callback.

And make sure to include the footer to export to excel by adding footer: true to your buttons configuration.

You can use customize method of the excelHtml5 button type to change the style and data before exporting to the file.

demo

$(document).ready(function() {
  $('#table').dataTable({
    data: [{
        name: "Tiger Nixon",
        salary: "$3,120",
      },
      {
        name: "Garrett Winters",
        salary: "$5,300",
      },
    ],

    columns: [{
        data: "name"
      },
      {
        data: "salary"
      }
    ],

    dom: 'Bfrtip',

    buttons: [{
      extend: 'excelHtml5',
      filename: 'datatable',
      customize: function(xlsx) {
        var sheet = xlsx.xl.worksheets['sheet1.xml'],
          sum = 0;

        // read each row
        // Loop over the cells in column `B`
        $('row c[r^="B"]', sheet).each(function() {

          // Get the value and strip the non numeric characters
          var value = $(this).text();
          sum += Number(value.replace(/[^0-9\.-]+/g, ""));
        });

        // Create our number formatter.
        var formatter = new Intl.NumberFormat('en-US', {
          style: 'currency',
          currency: 'USD',

          // the default value for minimumFractionDigits depends on the currency
          // and is usually already 2
          minimumFractionDigits: 2,
        });

        function addTotal(index, data) {
          msg = '<row r="' + index + '">';
          for (i = 0; i < data.length; i++) {
            var key = data[i].k;
            var value = data[i].v;
            msg += '<c t="inlineStr" r="' + 'B' + '4' + '" s="42">';
            msg += '<is>';
            msg += '<t>' + formatter.format(sum) + '</t>';
            msg += '</is>';
            msg += '</c>';
          }
          msg += '</row>';
          return msg;
        }

        //insert
        var r1 = addTotal(1, [{
          k: 'A',
          v: 'ColA'
        }]);

        sheet.childNodes[0].childNodes[1].innerHTML = r1 + sheet.childNodes[0].childNodes[1].innerHTML;
      }
    }]
  });
});

body {
  font: 90%/1.45em "Helvetica Neue", HelveticaNeue, Verdana, Arial, Helvetica, sans-serif;
  margin: 0;
  padding: 0;
  color: #333;
  background-color: #fff;
}

<script src="https://ajax.googleapis./ajax/libs/jquery/1.11.1/jquery.min.js"></script>
<link href="https://cdn.datatables/1.10.7/css/jquery.dataTables.css" rel="stylesheet" type="text/css" />
<script src="https://cdn.datatables/1.10.7/js/jquery.dataTables.js"></script>

<link href="https://cdn.datatables/buttons/1.2.2/css/buttons.dataTables.css" rel="stylesheet" type="text/css" />
<script src="https://cdn.datatables/buttons/1.2.2/js/dataTables.buttons.js"></script>

<script src="https://cdnjs.cloudflare./ajax/libs/jszip/3.1.3/jszip.min.js"></script>
<script src="https://cdn.datatables/buttons/1.2.2/js/buttons.html5.min.js"></script>

<body>
  <div class="container">
    <table id="table">

    </table>
  </div>
</body>