Export a time column to excel : time is not recognized as time in excel
Export a time column to excel : time is not recognized as time in excel
trucmuche2005
Posts: 71Questions: 22Answers: 2
Hello,
I have a datatable with a time (duration) column and I use buttons to export to Excel.
I use orthogonal datas to export the time column as hh:mm:ss because that format should be interpreted as time in Excel and should be summed easily. Exporting works perfectly, but the time datas are not recognized as time by excel and does not sum automatically.
I made extensive searches to find out how to export time data such that excel recognize them as time but had no luck.
Could you help me ?
Thanks !
T.
Replies
You are right, time is not something that the Buttons export will specifically set the Excel XML for. I don't know what the XML used for times / durations in Excel is - we'd need to look up the open spreadsheet documentation, or perhaps easier is to just generate an Excel file with a time, unzip it and see what XML Excel put in place. Then use the
customizemethod for the Button to modify the XML that is generated.I don't immediately know the answer to this I'm afraid - if I have time later I'll experiment a bit, bit unzipping an Excel file to see its XML structure is probably the way I would suggest you take this.
Allan
Thanks ! I finally managed to do what I want using the following post :
https://datatables.net/forums/discussion/76370/tips-for-exporting-time-string-data-h-mm-ss-to-excel