user2571510 user2571510 - 2 years ago 198
Javascript Question

Datatables / TableTools: format data as text when exporting to Excel

I am using the Datatables TableTools plugin in order to provide an Export to Excel option for a table on my page.

In general everything works as intended.
My only issue is that I would need all the data resp. the columns in the resulting Excel table being formatted as text as otherwise I am losing data in some columns.

- I have a column that has leading zeros (e.g.

) which only appears with the leading zeros cut off (e.g.
) in the Excel file if this is not formatted as text.
- Another column contains 19-digit account numbers (e.g.
) which appears with the last four digits being changed to zeros (e.g.
) in the Excel file if this is not formatted as text.

Is there any way I can set this in my Datatables / TableTools initialisation so that it always exports all data as text into the Excel file ?

Many thanks for any help with this, Tim.

Answer Source

TableTools does not create a real excel file, it creates a csv file instead. Those contain only raw data, no formatting. Although the leading zeros are there, Excel usually will not show them. You have several options here:

  • change the formatting from within Excel
  • open the csv file from Excel's open dialog, from which you should be able to mark columns as text (you might need to change the file type to txt)
  • add quotes around the data
  • create a real excel file via some external library
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download