SmartTable Excel Download Date format issue

[bhavya_sri3]

I'm using sap.ui.comp.smarttable.SmartTable for displaying data in table. I was able to display Dates in Date format by using CustomData.

But when I'm downloading data in Excel, it is showing data in json date format

/Date(1451606400000)/

I was able to show date format by using the onBeforeExport event, by passing type as Edm.Date type. This way, I was able to display json date as Date in MM/dd/yyyy format. But my requirement is to show date as dd/MM/yyyy. I understand that I need to use inputFormat property, but I could not figureout how to pass the property as my required data. Can anyone please suggest on the format I need to use..

[maheshpalavalli]

Hi Bhavya,

Is the column property type that is coming from backend is of type date or string?

BR

Mahesh

[former_member631239]

HI,

I have also faced the same issue.

I have and extended column with date, but after giving type property as date, it was working.

<core:CustomData key="p13nData" value='\{"columnKey": "Callback", "sortProperty": "Callback","filterProperty":"Callback","columnIndex":"7", "leadingProperty": "Callback", "type":"date"}'/>

Thanks Manish!

[maheshpalavalli]

Hi Bhavya,

In the custom data that you are using, try passing the type as well:

...."leadingProperty":"", "type":"date" }'/>

in the custom data along with the other properties you are passing,

BR,
Mahesh

[bhavya_sri3]

Hi Mahesh,

I tried using the date type in custom data.. but it has no affect on the excel.. still getting the date in MM/dd/yyyy format.

Thanks,

Bhavya

[maheshpalavalli]

Hi Bhavya, What is the type of that property you are receiving from the backend, it is string or date?

BR,Mahesh

[maheshpalavalli]

You can see the example here as well, which showcases two scenarios

1. If the date is coming as a string

2. If the date is coming as a date type

https://ui5.sap.com/#/sample/sap.ui.export.sample.formatting/code/Spreadsheet.controller.js

I am still wondering why you are using the custom column? to do any formatting?(I dont see any formating there).

BR,
Mahesh

[bhavya_sri3]

Hi Mahesh,

Sorry for the delayed response..

I'm receiving date in the 'date' type from backend. It is coming in json format something like /Date(1490918400000)/

I have tried changing the settings similar to that of in the example but it is still not showing in correct format..

aCols.push({

label: 'DateString',

type: 'date',

property: 'SampleDateString',

inputFormat: 'yyyymmdd',

width: 25 });

Thanks,

Bhavyasri M

[AdrDen]

Hi,

Did someone find a solution for this? I've also tried onBeforeExport event, but without any success.

BR,

Adrian

[junwu]

change inputFormat to format to see if it helps

[bhavya_sri3]

The SmartTable is using Spreadsheet api internally and it seems it doesn't have "format" property for workbook.columns..

https://sapui5.hana.ondemand.com/#/api/sap.ui.export.Spreadsheet/constructor

Thanks,

Bhavyasri M

[junwu]

can you just try? it is no harm.....

[bhavya_sri3]

I tried.. it is not throwing any error but it is still showing the date in MM/dd/yyyy format..

Tried these two statements..

mExcelSettings.workbook.columns[33].format= "dd/MM/yyyy";

mExcelSettings.workbook.columns[33].format= "dd-MM-yyyy";

Do you think the date formats I'm trying to use are wrong?

Thanks,

Bhavya

[dipender002]

Hi,

Please refer to this - https://sapui5.hana.ondemand.com/#/topic/283217d8f187401c8045723abc27e5e0

When using Date EdmType, you should use 'format' property. You could write like this - 

{ property: "Duedate", label: "Due date (islamic)", type: sap.ui.export.EdmType.Date, format: "dd/mm/yyyy" } 

It is working for me. Hope it might solve your issue too.