Excel exported date column

[Nick]

When I have a date column in the DataGrid, the excel export doesn't let the user format that date column after exporting.

I came across this post in the forums https://www.syncfusion.com/forums/173738/how-can-i-specifiy-column-to-be-date-when-exported-to-excel?reply=S2jBkR but that prevents the column from being sorted as the type is changed to number.

I have attached a sample.

Attachment: DataGridTest_3a18b6b1.zip

[Naveen Palanivel]

Hi Nick,

Based on the shared information, we checked the provided sample the sorting action works correctly, to validate further kindly share the video demo of the problem you are facing with detailed explanation

Please let us know if you have any concerns.

Regards,

Naveen Palanivel

[Nick]

Sorry, should have said filtering and not sorting. I am not allowed to upload the excel export but if you do an excel export, the date column cannot be formatted. The fix that was mentioned by the original poster in the forum post that I linked changes the filtering of date type into number and that does not work for filtering.

I want the excel export of a date type column to be formattable without changing the Column type in the Grid to a number type.

I even tried the following in the OnToolbarClick method to accommodate that column type change to a number(as mentioned in the forum post) before exporting but the date column in the excel still cannot be formatted.

private async Task ToolbarClickHandler(Syncfusion.Blazor.Navigations.ClickEventArgs args)
    {
        if (args.Item.Id == "TestGrid_excelexport") //Id is combination of Grid's ID and itemname
        {
            ExcelExportProperties excelExportProperties = new ExcelExportProperties();
            excelExportProperties.IncludeTemplateColumn = true;
            var columns = await OrderGrid.GetColumnsAsync();
            foreach (var col in columns)
            {
                if (col.Type == ColumnType.Date)
                {
                    col.Type = ColumnType.Number;
                }
            }
            excelExportProperties.Columns = columns;
            await this.OrderGrid.ExportToExcelAsync(excelExportProperties);
        }
    }

[Nick]

Here's an attachment of the video demo showing the exported excel from the DataGrid not formatting the date type column

Attachment: Excel_export_formatting_issue_video_8d290230.zip

[Naveen Palanivel]

Hi Nick,

From your query, When date column type as Number ,filter does not work so, we suggest you to render a SfDatePicker using FilterTemplate feature of grid to filter the date column. 

Reference : 

https://blazor.syncfusion.com/documentation/datagrid/filtering/#filter-bar-template-with-custom-component 

https://help.syncfusion.com/cr/blazor/Syncfusion.Blazor.Grids.SfGrid-1.html#Syncfusion_Blazor_Grids_SfGrid_1_FilterByColumn 

https://blazor.syncfusion.com/documentation/datepicker/getting-started/ 

You can handle the filtering action using FilterByColumn method in the ValueChange event of SfDatePicker. We have also prepared a sample based on this scenario for your convenience, please download the sample from the link below, 

Please get back to us if you need further assistance. 

Regards,

Naveen

Attachment: DataGridTest_921d1118.zip

[Nick]

Hi Naveen,

Is there another solution that uses a date column type in the Grid that allows for the exported date column to be formatted without using number column type?

The datetime column in the export comes out as number that's not legible without formatting. I want the column value in the export to be in a date format while also giving the end users the ability to format it if they want to.

Appreciate your help.

Thanks

[Prathap Senthil]

We understand that you would like to export the column value in a date format while also providing the end users with the flexibility to format it according to their preferences. We have a solution that can meet your requirements. We have implemented a solution that allows users to format the column value during export using a button click. Below, you will find the relevant code snippets and a sample for your reference.

 

 

<p>Allow users to format it as desired while Export</p>

<SfButton OnClick="Click">Excel Export: Change date format to number format</SfButton>  

 

<SfGrid ID="Grid" @ref="DefaultGrid" DataSource="@Orders" AllowFiltering="true" AllowSorting="true" Toolbar="@(new List<string>() { "ExcelExport" })" AllowExcelExport="true" AllowPaging="true">

    ----------

   <GridColumns>

        -------

       <GridColumn Field=@nameof(Order.OrderDate) HeaderText=" Order Date" Format="d" Type="columnType" TextAlign="TextAlign.Right" Width="130">

 

            <FilterTemplate>

                <SfDatePicker TValue="DateTime?" Format="dd/MM/yyyy" Placeholder='Select a date'>

                    <DatePickerEvents TValue="DateTime?" ValueChange="FilterDateColumn"></DatePickerEvents>

                </SfDatePicker>

            </FilterTemplate>

 

        </GridColumn>

    </GridColumns>

</SfGrid>

 

@code {

    private SfGrid<Order> DefaultGrid;

 

    public List<Order> Orders { get; set; }

 

    public Syncfusion.Blazor.Grids.ColumnType columnType { get; set; } = ColumnType.Date;

 

 

     public async Task ToolbarClickHandler(Syncfusion.Blazor.Navigations.ClickEventArgs args)

    {

        if (args.Item.Id == "Grid_excelexport") //Id is combination of Grid's ID and itemname

        {

            await this.DefaultGrid.ExcelExport();
            C

          

        }

    }

 

 

    public async Task Click()

    {

 

        columnType = ColumnType.Number;

        await this.DefaultGrid.ExcelExport();
        columnType = ColumnType.Date;

 

    }

 

Attachment: DataGridTest_Modified_sample._da9a3a89.zip