CODEWITHSUNDEEP
htmljquerydatatables
Cody Maxie
Cody Maxie

Reputation: 113

jQuery Datatable - enable decimal precision prior to excel output

I have a datatable with the following data:

<table id="mainTable">
  <thead> . . . </thead>
  <tbody>
    <td uv="3,212,497.32">3,212,497</td>
    . . .
  </tbody>
</table>

As you can see I'm using the custom "uv" attribute to show the non-rounded value of a given td. In my data table I have a "Decimal Precision" button that a user can press that does the following:

// for each td, if td has uv attr, convert to decimal value
// if new value only has one decimal, add trailing 0
$('tbody td').each(function() {
  if (typeof $(this).attr('uv') !== "undefined") {
    $(this).text( parseFloat($(this).attr('uv')).toLocaleString() );
    if (/^[-]?[\d,]+\.\d$/.test($(this).text())) {
      $(this).text($(this).text()+'0')
    }
  }
});

So from there my users can have decimal precision. Afterwards many will want to export to excel, so I have an excelHtml5 button to handle this:

}
  extend: 'excelHtml5',
  title: 'Summary Report', 
  action: function (e,dt,node,config) {
    $.fn.dataTable.ext.buttons.excelHtml5.action.call(this, e, dt, node, config);
  }
}

But on excel output I'm getting the same rounded values, despite the html in the datatable reflecting the new decimal accurate data. Is there a way to have the action function read the new values when outputting to excel?

Upvotes: 0

Views: 325

Answers (2)

Cody Maxie
Cody Maxie

Reputation: 113

Ok so the fix for this issue was pretty simple. What you can do is render the table with the decimal precision first, then after that block of code you'd round the number like so:

$('#mainTable').DataTable({
  // Code here to create the table
});

// after table creation, round the values in td elements
$('tbody td').each(function() {
  // matches -X,XXX.XX and X,XXX.XX formatted numbers
  if (/^[-]?[\d,]+\.\d{2}$/.test($(this).text())) { 
    $(this).text(Math.round($(this).text().replace(/,/g,'')).toLocaleString())
  }
});

Using this method your dt object that is used as an argument to the excel output function will use the decimal number (pre-rounding), but your html DataTable will show the rounded number.

Upvotes: 1

mplungjan
mplungjan

Reputation: 178011

I would use

<td uv="3212497.32">3,212,497</td>

and then use

+(uv.toFixed(2)).toLocaleString()

for decimals and

+(uv.toFixed(0)).toLocaleString().split(".")[0]

for none

Upvotes: 1

Related Questions