SmartTable Excel Download Date format issue

Question

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.

In View for displaying date in table, used below:

<Column width="100px">
	<customData>
		<core:CustomData key="p13nData" value='\{"columnKey": "ExemptEndDt", "maxLength": "5","columnIndex":"33", "leadingProperty": "ExemptEndDt"}' />
	</customData>				
	<Label text="{i18n>ExemptEndDt}" design="Bold" tooltip="{i18n>ExemptEndDt}" />
</Column>

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..

The SmartTable is using Spreadsheet for achieving this https://sapui5.hana.ondemand.com/#/api/sap.ui.export.Spreadsheet/constructor

In controller, I changed type of the date column to Edm.Date type as below:

		onBeforeExport: function (oEvt) {
			var mExcelSettings = oEvt.getParameter("exportSettings");
			mExcelSettings.workbook.columns[33].type = sap.ui.export.EdmType.Date; //ExemptToDate
			mExcelSettings.workbook.columns[33].width = 10;
			mExcelSettings.workbook.columns[32].inputFormat = "dd/MM/yyyy"; //Not Working
			
			// GW export
			if (mExcelSettings.url) {
				return;
			}
		},

Please edit your question and show your code as formatted code instead of (links to) images. — Theo

Nancy T Nancy T · Accepted Answer · 2020-11-09T14:13:43

You could've achieved this format ('03.09.2020 0:00:00') by adding edmType property to customData in a view like this:

<core:CustomData key="p13nData" value='\{"columnKey": "Period", "columnIndex":"4", 
"edmType":"Edm.DateTime", ..}'/>

It's from John Patterson's comment in post New Excel export functionality available

In my case following solution let export to excel Dates without time: "14.09.2020":

<table:AnalyticalColumn leadingProperty="someProp">
    <Label text="{some binding}"/>
    <table:customData>
        <core:CustomData key="p13nData" value='\{ "columnKey": "someProp", "columnIndex" : "18", "type":"date"}'/>
    </table:customData>
    <table:template>
    [some template]
    </table:template>
</table:AnalyticalColumn>

SmartTable Excel Download Date format issue

1 Answers