SAPUI5 excel export - configure a column with type Text

[saurabh_vakil]

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

[junwu]

you are not able to read the date column for the new row or all the row? how you read the excel?

[saurabh_vakil]

Hello Jun,

We are unable to read the date value in correct format for all the rows - existing rows as well as rows added by user (except if the user explicitly sets the type of the date column to Text, or prefixes every date value with a leading apostrophe '). We are reading the excel values using the below code, written in the press event of the upload button of FileUploader. The value of the date cell (read in item.Valid_From inside the forEach) is where it is reading incorrectly, if user does not explicitly convert the cell type to Text.

                                var reader = new FileReader();
                                reader.onload = function (cFile) {
                                    var data = cFile.target.result;
                                    var workbook = XLSX.read(data, { type: 'binary' });
                                    // validate if user is uploading the correct template
                                    
                                        // reading excel data
                                        var aResult = [], aLoadData = [];
                                        workbook.SheetNames.forEach(function (sheetName) {
                                            var rObjArr = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);
                                            if (rObjArr.length > 0) {
                                                aResult = rObjArr;
                                            }
                                        });
                                        // filling the sheet data into local json model and updating table
                                        aResult.forEach((item) => {
					    // item.Valid_From value is read incorrectly if cell is not set to type Text
                                            if (item.Valid_From === undefined)
                                                item.Valid_From = "";
                                            else
						// format the date to yyyy-MM-ddTHH:mm:ss to send to OData
                                                item.Valid_From = oDateTimeFormat.format(new Date(item.Valid_From));
                                            let oLoadFactorData = {
                                                OriginPlant: item.Origin_Plant,
                                                ModeOfTransportation: item.Mode_of_Transportation,
                                                ModeOfTransportationDescript: "",
                                                ValidFrom: item.Valid_From,
                                                FreightMultiplier: item.Freight_Multiplier.toString()
                                            }
                                            aLoadData.push(oLoadFactorData);
                                        });
                                }

[junwu]

https://docs.sheetjs.com/docs/csf/features/dates/

45253 is not unexpected format. this is how excel handle date.