Datatables excel export – Format output data

Datatables data can be exported using excel plugin from datatable.  Refer to the following link for details on export options with datatables:

https://datatables.net/extensions/buttons/examples/html5/simple.html

It is possible to set custom formats on the data inside cells, so that numbers, decimals etc. are not messed up in excel output.

The following example shows, if a column E of a sheet contains a version number like 1.5, 5.2 etc. and column G contains a numeric data without decimals, then the following code is used to set the output format:

{extend: 'excelHtml5', 

text: '$excel_title',
customize: function(xlsx) {
var styles = xlsx.xl['styles.xml'];
var nFmt = '<numFmt numFmtId=\"777\" formatCode=\"######################.0####\"/>';
\$('numFmts', styles).append(nFmt);
var style = '<xf numFmtId=\"777\" fontId=\"0\" fillId=\"0\" borderId=\"0\" applyFont=\"1\" applyFill=\"1\" applyBorder=\"1\" xfId=\"0\" applyNumberFormat=\"1\"/>';
var el = \$('cellXfs', styles);
el.append(style);
var styleIdx = \$('xf', el).length - 1;
var sheet = xlsx.xl.worksheets['sheet1.xml'];
\$('col:eq(4)', sheet).attr('style', styleIdx);
\$('row:gt(0) c[r^=\"E\"]', sheet).attr('s', styleIdx);

var styles2 = xlsx.xl['styles.xml'];
var nFmt2 = '<numFmt numFmtId=\"778\" formatCode=\"######################0\"/>';
\$('numFmts', styles2).append(nFmt2);
var style2 = '<xf numFmtId=\"778\" fontId=\"0\" fillId=\"0\" borderId=\"0\" applyFont=\"1\" applyFill=\"1\" applyBorder=\"1\" xfId=\"0\" applyNumberFormat=\"1\"/>';
var e2 = \$('cellXfs', styles2);
e2.append(style2);
var styleIdx2 = \$('xf', e2).length - 1;
var sheet2 = xlsx.xl.worksheets['sheet1.xml'];
\$('col:eq(6)', sheet).attr('style2', styleIdx2);
\$('row:gt(0) c[r^=\"G\"]', sheet).attr('s', styleIdx2);

},
Posts created 7

Begin typing your search term above and press enter to search. Press ESC to cancel.

Back To Top