In Datatable excel export, decimal values with % symbol are getting rounded off excel

user_10175889 0
Viewed 360

I am trying to export Datatable to excel sheet. Datatable has a column with decimal values and % symbol. After exporting, the decimal values are getting rounded off. But I need decimal values with % symbol to be present in the excel sheet. Can anyone help me with it. Thanks. [JSFiddle][1]

Table:

9.09% 7.88% 9.99% 8.38% 8.13% 7.57% 9.30% 8.95% -0.37%

9.09% 7.88% 9.99% 8.38% 8.13% 7.57% 9.30% 8.95% -0.37%

33.41% 30.42% 29.14% 34.44% 31.85% 32.14% 34.12% 34.98% 1.48%

8.98% 8.09% 7.70% 9.87% 9.35% 6.93% 8.98% 6.08% -0.78%

10.30% 11.38% 13.32% 12.40% 11.80% 13.64% 16.15% 13.42% 1.92%

exported excel

9% 8% 10% 8% 8% 8% 9% 9% 0%

9% 8% 10% 8% 8% 8% 9% 9% 0%

33% 30% 29% 34% 32% 32% 34% 35% 1%

9% 8% 8% 10% 9% 7% 9% 6% -1%

10% 11% 13% 12% 12% 14% 16% 13% 2%

datatables excel jquery javascript
1 Answers

The data in the generated .xlsx file includes the full decimal precision from the DataTable. This can be seen by opening the generated .xlsx container as a .zip file, then going into the xl then worksheets directories and opening sheet1.xml. The data for row 3 starts as:

<row r="3">
  <c r="A3" s="56">
    <v>0.1223</v>
  </c>
  <c r="B3" s="56">
    <v>1.6022999999999998</v>
  </c>
  <c r="C3" s="56">
    <v>0.10263</v>
  </c>
  ...

In this data structure the attribute s represents the style that is being used, in this case set to "56". The styles are contained within the .xls container in the file named styles.xml within the xl directory. The number is the index of the style within cellXfs, a sample of the styles data from this file is:

<cellXfs count="68">
  <xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" />
  <xf numFmtId="0" fontId="1" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" />
  ...

The styles which the DataTables excelHtml5 button adds by default are listed out in the documentation here. This shows "56" is '56 - Percentage integer value (automatically detected and used by buttons - since 1.2.3)'. excelHtml5 adds a built in style for percentage with 1 decimal place as number "60", but it doesn't have one for percentage with two decimal places.

To get two decimal places a new style must be added. The OpenXML file format already has a number format ID for percentage with two decimal places, based on the documentation here it is numFmtId="10". This number format can be used in the new style (else a new number format would have to added also).

The new style using the numFmtId="10" must be added to the styles.xml document, then the index of the newly added style used on the cells. Sample code to achieve this is below:

$('#example').DataTable({
  dom: 'Bfrtip',
  filename: "abc",
  buttons: [{
    extend: 'excelHtml5',
    text: 'Excel',
    customize: function(xlsx) {
      //Get the styles sheet
      let styles = xlsx.xl['styles.xml'];

      // Create a style which uses numFmtId=10, this is the format for percentage with 2 decimal places
      let style = '<xf numFmtId="10" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>';
      
      // Get the current cellXfs
      let cellXfs = $('cellXfs', styles);

      // Append the style to cellXfs, and update the cellXfs count attribute
      cellXfs.append(style);
      cellXfs.attr('count', $('xf', cellXfs).length);
      
      // Get sheet 1 and apply the new style, skipping first two rows.
      // The last element in cellXfs is the ID which is used as the value of s
      let sheet = xlsx.xl.worksheets['sheet1.xml'];
      $('row:gt(1) c', sheet).attr('s', $('xf', cellXfs).length - 1);
    }
  }]
});

A working sample based off the one you provided in the question can be found at https://jsfiddle.net/sg94ec2u/.

user_16204490
Related