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!

The default Excel styles provided with DataTables expect there to be exactly 2 decimal places (or none). So, numbers (which have a thousands separator), with 1 decimal place, will not be given a number format in Excel - they will get the "General" format. You can add a custom style during the export, but that is more work. — andrewJames
– andrewJames, Commented Apr 30, 2021 at 13:39
Side notes: (1) You should use https://cdn... in your URL, not just //.... (2) The logic to replace thousands separators will only replace one thousands separator. If you have numbers in the millions, you will end up with a mis-formatted number - so try replaceAll() instead. — andrewJames
– andrewJames, Commented Apr 30, 2021 at 13:39

andrewJames · Accepted Answer · 2021-04-30 21:22:17Z

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##.

Working like a charm, thank you very much! Only needed to raise the decimal number count

Juan Silvestre · Accepted Answer · 2025-06-10 18:45:22Z

I had the same problem, so here I write what worked for me after search a lot. If someone is looking another way to reach this in the future.

Excel needs US number format in order to work (at least is what I read about the Excel generated from datatable), so before create the excel, the datatable needs to format it the proper way. Then the excel itself (in my case Libre office) shows that number in the format of the excel instalation. If is an US instalation it should show the US format, if it's Hungarian it should show Hungarian, if it's Spanish, Spanish, and so on.

First you create a formatter. Inside the function that is in "body" you format your data. Here I looked for the column 7 that was the one that have the numbers that were exported in an incorrect format.

I show in the datatable the format for example 1.000.676,50, so on the function, first I deleted the point, so numero now should be 1000676,50. Then I change the comma by a point, and the number now is 1000676.50 . With that number now excel can work with that cell.

let exportFormatter = {
        format: {
            body: function (data, row, column, node) {
                var numero = '';
                if(column === 7){
                    numero = data.replace(/\./g, '');
                    numero = numero.replace(/,/g , '.');
                    return numero;
                }
                return data;
            }
        }
    };

Then you add that formatter, is the line that says exportOptions. I also used the style s66 for the problematic cell ($('row c[r^="H'+ num +'"]', sheet).attr('s', '66');), you can use the one that serves you most. Here is the datatables page that shows the list of them: https://datatables.net/reference/button/excelHtml5

        "language": {
            "url": "js/dtSpanish.json"
        },
        "pagingType": "full_numbers",
        "mData": null,
        "iDisplayLength": 25,
        "aaSorting": [],
        "columnDefs": [
            {"orderable": false}
        ],
        dom: 'Blfrtip',
        buttons: [
            {
                extend: 'pdfHtml5',
                title: 'Consulta recargo clientes',
                orientation: 'landscape',
                pageSize: 'LEGAL',
                customize: function (doc) {
                      var rowCount = doc.content[1].table.body.length;
                      for (i = 1; i < rowCount; i++) {
                          doc.content[1].table.body[i][0].alignment = 'center';
                          doc.content[1].table.body[i][1].alignment = 'center';
                          doc.content[1].table.body[i][2].alignment = 'center';
                          doc.content[1].table.body[i][3].alignment = 'center';
                          doc.content[1].table.body[i][4].alignment = 'center';
                          doc.content[1].table.body[i][5].alignment = 'right';
                          doc.content[1].table.body[i][6].alignment = 'right';
                          doc.content[1].table.body[i][7].alignment = 'right';
                          doc.content[1].table.body[i][8].alignment = 'center';
                      }
                }
            },
            {
                extend: 'excelHtml5',
                title: 'Consulta recargo clientes',
                orientation: 'landscape',
                exportOptions: exportFormatter,
                customize:function (xlsx) { 
                    var sheet = xlsx.xl.worksheets['sheet1.xml'];
                    
                    $('row c[r^="A2"]', sheet).attr('s', '32');
                    $('row c[r^="B2"]', sheet).attr('s', '32');
                    $('row c[r^="C2"]', sheet).attr('s', '32');
                    $('row c[r^="D2"]', sheet).attr('s', '32');
                    $('row c[r^="E2"]', sheet).attr('s', '32');
                    $('row c[r^="F2"]', sheet).attr('s', '32');
                    $('row c[r^="G2"]', sheet).attr('s', '32');
                    $('row c[r^="H2"]', sheet).attr('s', '32');
                    $('row c[r^="I2"]', sheet).attr('s', '32');
                    $('row c[r^="J2"]', sheet).attr('s', '32');
                    
                    var table = $('#tablaContratosFacturados').DataTable();
                    var numero = table.rows().count();
                    
                    for(i = 1 ; i <= numero; i++){
                        var num = i + 2;
                        $('row c[r^="A'+ num +'"]', sheet).attr('s', '51');
                        $('row c[r^="B'+ num +'"]', sheet).attr('s', '51');
                        $('row c[r^="C'+ num +'"]', sheet).attr('s', '51');
                        $('row c[r^="D'+ num +'"]', sheet).attr('s', '51');
                        $('row c[r^="E'+ num +'"]', sheet).attr('s', '51');
                        $('row c[r^="F'+ num +'"]', sheet).attr('s', '52');
                        $('row c[r^="G'+ num +'"]', sheet).attr('s', '52');
                        $('row c[r^="H'+ num +'"]', sheet).attr('s', '66');
                        $('row c[r^="I'+ num +'"]', sheet).attr('s', '51');
                    }
                }
            }
        ]
    });

I'm document it here before i forget about it. Hope someone can find it useful in a future and can adapt it to their needs :D

Collectives™ on Stack Overflow

JQuery Datatable Excel export number format

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related