jQuery Datatable Excel Exporting: Numeric Columns

Question

I am working on a page that originally exported a HTML table to Excel using the undesirable ActiveXObject. I have converted the table to a jQuery DataTable and am using the Excel export functionality described here: https://datatables.net/extensions/buttons/examples/initialisation/export.html

The export is working fine except for an issue I am having with a dollar amount column. When ActiveXObject was used to export, this column was recognized as a numeric column. With the DataTable export, however, the column is viewed as text which prevents numeric operations to be performed on them. Each cell in the column contains a little green triangle in the upper left corner indicating an issue with the data. When hovering over the warning icon the warning states "Number Stored as Text." The column can be converted to a number in Excel, but the users are used to this column automatically being numeric. Any ideas why this is happening and how to solve it?

Vargola Vargola · Accepted Answer · 2016-12-02T16:09:02

I was able to solve this problem as follows:

var buttonCommon = {
    exportOptions: {
        format: {
            body: function (data, column, row, node) {
                return (column >= 5 && column <= 7) ? data.replace(/[.]/g, '').replace(',', '.') : data;
            }
        }
    }
};

$('#tableHist').DataTable({
    "info": false,
    "ordering": false,
    "scrollX": true,
    "searching": false,
    dom: 'Bfrtip',
    buttons: [
        $.extend(true, {}, buttonCommon, {
            extend: 'excelHtml5',
            title: 'CIR_RESSEGURO_HISTORICO_AJUSTE_PMD'
        })
    ],
    "language": {
        "url": "//cdn.datatables.net/plug-ins/1.10.12/i18n/Portuguese-Brasil.json"
    }
});

jQuery Datatable Excel Exporting: Numeric Columns

1 Answers