Datatables / TableTools: format data as text when exporting to Excel
datatables, excel, export-to-excel, javascript, tabletools
Solution
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
Problem
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. Examples: - I have a column that has leading zeros (e.g. `0022`) which only appears with the leading zeros cut off (e.g. `22`) in the Excel file if this is not formatted as text. - Another column contains 19-digit account numbers (e.g. `1234567890123456789`) which appears with the last four digits being changed to zeros (e.g. `1234567890123450000`) 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.