Problem with Export to Excel functionality when formating datetime column

[Jan Poljovka]

Hi,

I have implemented a solution for formating datetime column which is described in this thread:
https://www.syncfusion.com/forums/136334/date-column-format
and the data is presented correctly but Export to Excel doesn't work, simply nothing happens when I click,

while with the export to PDF there is no problem.

[Pavithra Subramaniyam]

Hi Jan, 

We have validated the reported problem and by default excel export supports the ‘columns.format’ as string but when we use the custom date format, it is in object type. So that the excel file is not exported in Grid. You can resolve the reported problem by using the below way.  

In the below sample we have add the skeleton in the ‘columns.format’ property and customized the excel cell value by using the excelQueryCellInfo event which will Trigger before exporting each cell to Excel file.  

Please refer to the below code example, Documentation link and sample link for more information.   

[component.ts] 

@Component({ 

    selector: 'app-container', 

    template: `<ejs-grid #grid id='Grid' (excelQueryCellInfo)='excelQueryCellInfo($event)' [dataSource]='data' [toolbar]='toolbarOptions' height='272px' [allowPdfExport]='true' [allowExcelExport]='true' (toolbarClick)='toolbarClick($event)'> 

                <e-columns> 

                     <e-column field='OrderDate' headerText='Order Date' width='130' [format]='formatOptions' textAlign='Right'></e-column> 

                 .     .    . 

                </e-columns> 

                </ejs-grid>` 

}) 

export class AppComponent implements OnInit { 

    ngOnInit(): void { 

        .   .   . 

        this.formatOptions = { type: 'date', format: "dd/MM/yyyy", skeleton: "dd/MM/yyyy" }; 

    } 

     

    excelQueryCellInfo (args: ExcelQueryCellInfoEventArgs) {  

    if (args.column.field === 'OrderDate') {  

      var intl = new Internationalization();  

      var dFormatter = intl.getDateFormat({ format: "dd/MM/yyyy" });  

      var formattedDate = dFormatter(args.value);  

      args.value = formattedDate;  

    }  

  }  

}  

Documentation  : https://ej2.syncfusion.com/16.1.37/angular/documentation/grid/api-gridComponent.html#excelquerycellinfo   

 

Sample                :  https://plnkr.co/edit/PHfgE8WhXDrRFQi6XwoH?p=preview 

Regards, 

Pavithra S. 

[Mownika Koneru]

But it cannot be a date type column in Excel anymore. In Excel if you try to apply filter to the respective column it filters as a string and not on dates

[Thavasianand Sankaranarayanan]

Hi Mownika, 

By default,  we using corresponding date column as dateObject type in Grid dataSource there is no need any customization for Date Filter option in excel. We can set format to required date column to display the value in that format in UI level. Also we no need to add excelQueryCellInfo event to format that date column in excel export. 

But if we use corresponding date column as string type then we have to modify it to dateObject otherwise its works as string Filter. 

 

Please refer to  the below code, screenshot and sample link. 

DataSource code: 

export let data: Object[] = [ 

    { 

        OrderID: 10248, CustomerID: 'VINET', EmployeeID: 5, OrderDate: new Date(8364186e5), 

        ShipName: 'Vins et alcools Chevalier', ShipCity: 'Reims', ShipAddress: '59 rue de l Abbaye', 

        ShipRegion: 'CJ', ShipPostalCode: '51100', ShipCountry: 'France', Freight: 32.38, Verified: !0 

    } 

. . . . 

] 

Screenshot:  

 

Sample link: https://stackblitz.com/edit/angular-v28lrp-rshyju?file=app.component.ts 

If still facing the issue, please share the below details. 

1. Share your required column type in dataSource.
2. Share your sample dataSource.

 

Regards, 

Thavasianand S.