Excel export force data as text

Excel export force data as text

edwardiv1edwardiv1 Posts: 13Questions: 3Answers: 0

I'm using the Excel export button and have a cell that contains the following id: "8805354340000001"
It shows correctly in datatables, but when I export to excel, it exports as "8805354340000000" - i.e. it loses the last digit, converting it to zero.

I'm assuming something during the export process is deciding that the last 1 is not significant enough to bother exporting (?). How can I force it to export the full ID (which is actually text, not a number).

I've tried:

customize: function ( xlsx ) {
  var sheet = xlsx.xl.worksheets['sheet1.xml'];
  $('row c', sheet).attr( 's', '50' );
}

and also:

"columnDefs": [
  {type:'string', targets: [...] }
]

but they don't make a difference. The customize works in that it formats it as text instead of number, but that just displays "8.80535E+15", which is even worse.

Answers

  • edwardiv1edwardiv1 Posts: 13Questions: 3Answers: 0

    Update: I've worked around this by using the CSV export instead of Excel export, but still curious why it happens with the Excel export.

  • colincolin Posts: 15,240Questions: 1Answers: 2,599

    It's an Excel issue, this thread here has some info (and other links) that help describe what's happening and what can be done,

    Colin

Sign In or Register to comment.