javascript - JQuery Datatable Excel export number format - Stack Overflow

admin管理员组

文章数量:1391968

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, ma 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 ma.

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/plug-ins/1.10.22/i18n/Hungarian.json'
    },
});

Thank You!

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, ma 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 ma.

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/plug-ins/1.10.22/i18n/Hungarian.json'
    },
});

Thank You!

• javascript
• jquery
• excel
• datatables
• number-formatting

Share Improve this question Follow asked Apr 30, 2021 at 9:40 warrenbartonwarrenbarton 2311 silver badge33 bronze badges 3

• 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 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 Commented Apr 30, 2021 at 13:39
• Thank you. Using replace was a mistake. – warrenbarton Commented May 4, 2021 at 11:47

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 4

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

本文标签： javascriptJQuery Datatable Excel export number formatStack Overflow