Shera Shera - 8 months ago 56
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-page-list="[10, 25, 50, 100, ALL]"
<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>

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.


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.


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.