Setting a Grid column date format prevents Excel and CSV export working

[Mike]

Hi,

I’m using a Syncfusion ej2 grid in a .Net Core 2.0 application.

The gird contains a datetime column. The date format of that column is set in the grid load function so that the date format displayed matches a user’s saved preference (For example dd/MM/YYYY or dd.MM.yy). I have followed the Syncfusion response given in this forum thread to set the datetime format on grid load: https://www.syncfusion.com/forums/140089/display-date-format

The grid also has a tool bar which provides: PDF export; Excel export and CSV Export.

If the grid load function is disabled, so that the date format is not set, then the three export tools all work correctly. However, if the load function is enabled, the date format is correctly set, but two of the three export tools then stop working. Only the PDF export tool continues to work.

I need to be able to both set the grid date format to correspond to the user’s choice, and also provide the user with three working export tools. What needs to be changed to do that?

The grid declaration and load function are included below.

Regards,

Mike

Grid:

<ejs-grid id="DataGrid" dataSource="@Model.Notifications"

  allowPaging="true"

  allowSorting="true"

  allowResizing="true"

  allowSelection="true"

  rowSelected="rowSelected"

  allowFiltering="true"

  allowExcelExport="true" 

  allowPdfExport="true"  

  toolbarClick="toolbarClick" 

  toolbar="@(new List<string>() { "ExcelExport", "PdfExport", "CsvExport", "Search" })" 

  locale="@Model.UICulture"

  load="load"

 >

<e-grid-pagesettings pageCount="25"></e-grid-pagesettings>

<e-grid-selectionsettings type="Single"></e-grid-selectionsettings>

<e-grid-filterSettings type="Menu"></e-grid-filterSettings>

   <e-grid-columns>

       <e-grid-column field="MessageID" validationRules="@(new { required=true})" headerText="ID" minWidth="20" width="50" maxWidth="100" textAlign="Left"></e-grid-column>

       <e-grid-column field="MessageDate" headerText="Date" minWidth="20" width="80"></e-grid-column>

       <e-grid-column field="From" headerText="From" editType="datepickeredit" textAlign="Left" format="yMd" minWidth="20" width="100"></e-grid-column>

       <e-grid-column field="Title" headerText="Title" allowResizing="true" editType="numericedit" textAlign="Left"  width="150" minWidth="50"></e-grid-column>

       <e-grid-column field="Message" headerText="Message" minWidth="50" width="300"></e-grid-column>

    </e-grid-columns>

</ejs-grid>

Grid load function:

<script>

    function load(args) {

        var gridObj = document.getElementById("DataGrid").ej2_instances[0];

        for (i = 0; i < gridObj.columnModel.length; i++) {

            if (gridObj.columnModel[i].field == "MessageDate") {

                gridObj.columnModel[i].format = { type: 'dateTime', format: '@Model.DateTimePattern' };  

            }

        }

    }

</script>

[Pavithra Subramaniyam]

Hi Mike, 

 

Greetings from Syncfusion. 

 

We have validated the reported problem and You can achieve your requirement by using custom formatting in ‘excelQueryCellInfo’ event of Grid. By default excel export supports the ‘columns.format’ as string but when we use the custom date format, it is in object type. In the below code 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.  

 

[index.cshtml] 

<ejs-grid id="Grid"  allowPaging="true"  allowPdfExport="true" allowExcelExport="true" toolbarClick="click" toolbar="@(new List<string>() { "PdfExport", "CsvExport", "ExcelExport" })" load = "load"  excelQueryCellInfo= "excelQueryCellInfo"    > 

       .  . . 

    <e-grid-columns> 

        .  .  . 

        <e-grid-column field="OrderDate" headerText="Order Date" width="150"></e-grid-column> 

    </e-grid-columns> 

</ejs-grid> 

 

<script> 

    function excelQueryCellInfo(args) { 

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

            var intl = new ej.base.Internationalization(); 

            var dFormatter = intl.getDateFormat({ format: "dd MMM yyyy", type: 'date', skeleton: "dd MMM yyyy" }); 

            var formattedString = dFormatter(new Date(args.value)); 

            args.value = formattedString; 

        }  

 

    } 

    function load(e) { 

        this.getColumnByField('OrderDate').format = { format: "dd MMM yyyy", type: 'date', skeleton: "dd MMM yyyy" }; 

    } 

     

     

</script> 

 

 

Documentation: https://ej2.syncfusion.com/javascript/documentation/api/grid/#load  

                              https://ej2.syncfusion.com/javascript/documentation/api/grid/#excelquerycellinfo  

              https://ej2.syncfusion.com/javascript/documentation/common/internationalization/#manipulating-datetime  

 

Please get back to us if you need any further assistance on this. 

 

Regards, 

Pavithra S. 

[Mike]

Hi Pavithra,

Perfect! All working now.

Thanks very much.

Regards,

Mike

[Pavithra Subramaniyam]

Hi Mike,  

Thanks for your update. 

We are happy to hear that the provided information helped you. 

Please contact us if you need any further assistance. As always, we will be happy to assist you.  

Regards,  

Pavithra S.