Shera Shera - 5 months ago 34
Javascript Question

Bootstrap TableExport decimal issue

I have a bootstrap table displaying employee data, including the payroll ID which have the given format : 1606.xxxx

Here's what my table looks like :

<table id="table_search"
data-toggle="table"
data-search="true"
data-show-refresh="true"
data-show-toggle="true"
data-show-columns="true"
data-show-export="true"
data-minimum-count-columns="2"
data-show-pagination-switch="true"
data-pagination="true"
data-page-list="[10, 25, 50, 100, ALL]"
data-show-footer="false"
data-export-data-type="all"
data-export-types="['excel']">
<thead>
<tr>
<th data-field="id">ID</th>
<th data-field="payroll_id" >Payroll ID</th>
<th data-field="nama_karyawan">Employee Name</th>
<th data-field="level">Level</th>
<th data-field="grade">Grade</th>
<th data-field="title">Title</th>
<th data-field="lokasi">Location</th>
<th data-field="cost_sales">Cost Sales</th>
<th data-field="dept">Department</th>
<th data-field="div">Division</th>
<th data-field="dir">Directorat</th>
<th data-field="active_period">Active Period</th>
</tr>
</thead>




The table displays it correctly, however when I export it into excel using TableExport plugin it goes like this
exported results

As you can see, somehow the plugin treats it as a number with decimal, which is exactly what I am avoiding. I've tried commenting the parseNumber function which might be the cause in the tableExport js file, however the results always comes out the same

What am I doing wrong ?

PS: I don't want formatting after the file is exported, I want it to export the data as is.

Answer

You could use

...
<td data-tableexport-msonumberformat="\@">123.450</td>
...

With this the tableExport.js will use mso-number-format: "\@" as TDstyle while creating the HTMLExcel export. This results in formatting the cell as Text.

Example: http://jsfiddle.net/uqtubq5c/1/

You could also use

...
<td data-tableexport-msonumberformat="0.000">123.450</td>
...

This leads to the number format 0.000 in Excel. So the cell content remains a number and will be useable in calculations further. The Text will possible lead to issues while used in calculations.


If you cannot set own data-tableexport-msonumberformat attributes to TD elements, then you could extend the tableExport.jquery.plugin.

In tableExport.js have:

...
      var defaults = {
        onMsoNumberFormat: onMsoNumberFormat,
...

onMsoNumberFormat must be a function.

If the function onMsoNumberFormat is like:

onMsoNumberFormat = function(cell, row, col) {
 if (row > 0 && col == 2) {
  return "#\\,##0\\.00";
 }
 if (row > 0 && col == 3) {
  return "\\@";
 }
};

then the third column (col==2) from row 2 (row>0) upwards will get style="mso-number-format:#\,##0\.00" and the fourth column (col==3) from row 2 (row>0) upwards will get style="mso-number-format:\@". @ is Textformat.

Example: http://jsfiddle.net/uqtubq5c/3/

Comments