JQuery Datatable Excel export number format

Question

We have a JQuery datatable with excel export, but cant solve a problem with numbers. Numbers displays in the datatable in hungarian format: 5 588,9906 (whitespace is the thousand separator, comma is the decimal point). Now we need to display the datas as numbers in excel, but thats not working every time. In excel settings, the thousand separator is whitespace, the decimal point is comma.

Datatable: datatable format

Result in Excel (lower one is ok, upper one is a string): excel error

The code:

var buttonCommon = {
    exportOptions: {
        format: {
            body: function ( data, row, column, node ) {
                return column === 6 || column === 8 || column === 9 || column === 10 || column === 11 || column === 12 || column === 13
                ? data.replace(',', '.').replace(' ', ',') : data;
            }
        }
    }
};

var table = $('#talaltszamlak').DataTable({
    dom: 'Blfrtip',
    buttons: [
        $.extend( true, {}, buttonCommon, {
            extend: 'excelHtml5'
        } ),
    ],
    pageLength: 50,
    "order": [[ 3, "asc" ]],
    language: {
        url: '//cdn.datatables.net/plug-ins/1.10.22/i18n/Hungarian.json'
    },
});

Thank You!

Answer 1

Here is an example where you provide your own custom Excel number format.

In this case, the Excel format string is:

#,##0.0##

So, we will get up to 3 decimal places (and a minimum of 1 decimal place).

The test data:

<div style="margin: 20px;">

    <table id="example" class="display dataTable cell-border" style="width:100%">
        <thead>
            <tr>
                <th>Name</th>
                <th>Amount</th>
            </tr>
        </thead>
        <tbody>
            <tr><td>Tiger Nixon</td><td>123,45</td></tr>
            <tr><td>Garrett Winters</td><td>4 567,892</td></tr>
            <tr><td>Ashton Cox</td><td>1 233 445,1</td></tr>
        </tbody>
    </table>

</div>

The DataTable with custom code:

$(document).ready(function() {

  var table = $('#example').DataTable( {
    dom: 'Brftip',
    buttons: [
      {
        extend: 'excelHtml5',
        text: 'Excel',
        exportOptions: {
          format: {
            body: function ( data, row, column, node ) {
              return reformatNumber(data, row, column, node);
            }
          }
        },
        customize: function( xlsx ) {
          addCustomNumberFormat(xlsx, '#,##0.0##');
          formatTargetColumn(xlsx, 'B'); // Excel column B
        }
      }
    ]
  } );
    
} );

function reformatNumber(data, row, column, node) {
  // replace spaces with nothing; replace commas with points.
  if (column === 1 ) {
    var newData = data.replace(',', '.').replaceAll(' ', '');
    return newData;
  } else {
    return data;
  }
}             

function addCustomNumberFormat(xlsx, numberFormat) {
  // this adds a new custom number format to the Excel "styles" document:
  var numFmtsElement = xlsx.xl['styles.xml'].getElementsByTagName('numFmts')[0];
  // assume 6 custom number formats already exist, and next available ID is 176:
  var numFmtElement = '<numFmt numFmtId="176" formatCode="' + numberFormat + '"/>';
  $( numFmtsElement ).append( numFmtElement );
  $( numFmtsElement ).attr("count", "7"); // increment the count
    
  // now add a new "cellXfs" cell formatter, which uses our new number format (numFmt 176):
  var celXfsElement = xlsx.xl['styles.xml'].getElementsByTagName('cellXfs');
  var cellStyle = '<xf numFmtId="176" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"' 
      + ' applyFont="1" applyFill="1" applyBorder="1"/>';
  // this will be the 8th "xf" element - and will therefore have an index of "7", when we use it later:
  $( celXfsElement ).append( cellStyle );
  $( celXfsElement ).attr("count", "69"); // increment the count
}

function formatTargetColumn(xlsx, col) {
  var sheet = xlsx.xl.worksheets['sheet1.xml'];
  // select all the cells whose addresses start with the letter prvoided
  // in 'col', and add a style (s) attribute for style number 68:
  $( 'row c[r^="' + col + '"]', sheet ).attr( 's', '68' );  
}

The code adds a new number format record to the Excel styles XML sheet; it then uses that record to create a new cell format record. Finally, it locates every cell in column B of the Excel spreadsheet and applies the cell formatter.

The end result is that a value which is displayed in the DataTable like this:

1 233 445,1

Will be displayed in Excel like this:

1,233,445.1

You can use whatever Excel number format string you want, instead of #,##0.0##.