Question

15

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

rated 0 times [ 21] [ 6] / answers: 1 / hits: 34981 / 10 Years ago, thu, june 5, 2014, 12:00:00

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.

Answers

Only authorized users can answer the question. Please sign in first, or register a free account.

gageherberth

Add To Favorites

Follow

Total Points: 249

Total Questions: 115

Total Answers: 119

Location: Liechtenstein

Member since Sun, Sep 12, 2021

3 Years ago

answered 10 Years ago neildrews · Accepted Answer

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