on 11-29-2023 10:43 AM
Hi Experts,
We are using sap.ui.export.Spreadsheet class to export data from a sap.ui.table.Table control to an excel file. The data contains a date field (data type Edm.DateTime), for this we have defined the below column configuration:
aCols.push({
label: 'Valid_From',
property: 'ValidFrom'
});
This date value is returned by the OData service in the /Date(1451606400000)/ format, so we are formatting it in the ""MM/dd/yyyy" format before populating it in the excel export. So far so good, we are seeing the date in the excel in the expected format. The type of the cells holding this date value in the exported excel is General.
Now, users can add more rows to the excel and upload the same file back into the UI5 app, thereby adding the new rows in the Table control, to be updated to the backend. When users add the date in the same "MM/dd/yyyy" format while adding new rows, while reading these on uploading the excel we are seeing it in an unexpected format, like 45253, instead of 11/23/2023, which is what user has entered in the excel before uploading; and upon upload this date gets interpreted as 1 Jan, 1970.
But if we prefix the date value with an apostrophe (') ('11/23/2023), then the cells type in Excel becomes Text (instead of General as mentioned above) and this date is correctly interpreted and uploaded in the Table.
The question is, when we export the table data to excel file, how can we enforce the type of the column to be Text instead of Date/General?
Thanks
Saurabh
https://docs.sheetjs.com/docs/csf/features/dates/
45253 is not unexpected format. this is how excel handle date.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
73 | |
9 | |
7 | |
7 | |
6 | |
6 | |
6 | |
6 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.