How can I specifiy column to be date when exported to excel?

[Wilson]

Hello,

I want to change my date column to be formatted as "date" in excel when exported.

The "format" in my GridColumn is for modifying the data in my DB. (Picture 1) 
However when I export my table, the date is formatted as a general text. (Picture 2)

I would like the column to appear formatted like this in excel. (Picture 3) 
I've already tried changing the format type in the GridColumn but it didn't change anything in excel.

Any ideas? 

Pictures

1. 
   
2. 
3. 

[Rahul Narayanasamy]

Hi Wilson, 

Greetings from Syncfusion. 

We have checked your query and we will let you know that we have added one improvement “Support to change the type of date values in exported excel file from Grid” in our release(19.4.0.47).  From this version we have changed the selected date column type as Custom in format cells option of the Excel file. Currently, we only have support for changing the styles for cells only.  

Since we have used Syncfusion Excel Library in DataGrid component to Export the DataGrid and they don’t have any support for changing the column styles/types. Please let us know if you have any concerns. 

Regards, 

Rahul 

1

[Wilson]

Rahul, 

Thank you for your response. I was able to upgrade syncFusion and receive the latest patch, however, the latests patch only forces the date field in excel to be "date" but after running a "=isnumber" on the cell, it returns false. (see image)

The first row is once I click the field and click outside of it again (in order to lose focus), then excel auto converts the field to date. But the second row, is not formatted as date. 

I've tried different iterations on the format in gridcolumn, to no avail.

The second row is how the file is exported as.

Question

Do you know how to format the cell as date in excel? 
Or how to explicitly have "/" instead of "-" as the seperator?

This is my source code:

[Rahul Narayanasamy]

Hi Wilson, 

Thanks for the update. 

Queyr: how to explicitly have "/" instead of "-" as the seperator? 

You want to change “/” instead of “-“ in date column cell of the exported excel file. You can achieve your requirement by using ExcelQueryCellInfoEvent event of the Grid. Find the below code snippets and sample for your reference. 

 

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

    <GridEvents ExcelQueryCellInfoEvent="ExcelQueryCellInfoHandler" OnToolbarClick="ToolbarClickHandler" TValue="Order"></GridEvents> 

    <GridColumns> 

        <GridColumn Field=@nameof(Order.OrderID) HeaderText="Order ID" TextAlign="TextAlign.Right" Width="120"></GridColumn> 

        <GridColumn Field=@nameof(Order.CustomerID) HeaderText="Customer Name" Width="150"></GridColumn> 

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

        <GridColumn Field=@nameof(Order.Freight) HeaderText="Freight" Format="C2" TextAlign="TextAlign.Right" Width="120"></GridColumn> 

    </GridColumns> 

</SfGrid> 

 

@code{ 

    private SfGrid<Order> DefaultGrid; 

 

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

 

    . ..  

    public void ExcelQueryCellInfoHandler(ExcelQueryCellInfoEventArgs<Order> args) 

    { 

        if(args.Column.Field == "OrderDate") 

        { 

            args.Cell.Value = args.Cell.Value.ToString().Replace("-", "/"); 

        } 

    } 

} 

Sample: https://www.syncfusion.com/downloads/support/forum/173738/ze/WebApplication1643284684 

Please let us know if you have any concerns. 

Regards, 

Rahul 

[Wilson]

Thank you Rahul, for your timely response. 

For anyone else with this similar problem. A separate solution I've found that works is 

to change:  

• Type="ColumnType.Date"  to Type="ColumnType.Number"  

This allows the excel export to be converted to Longdate, and also change the format ordering of dd/mm/yyyy.

[Rahul Narayanasamy]

Hi Wilson, 

Thanks for the update. 

We are happy to hear that you have achieved your requirement. Please get back to us if you need further assistance. 

Regards, 

Rahul 

[Ruru Riguer]

Hi, 

Encountered the same scenario, I need to have the entire column type to be in Date when exported to excel. Have tried the marked answer on my scenario but doesn't seem to work well on me. 


For me,
When the file was loaded and navigated to the target column, the data type is in General, but if you clicked on the cell and  click outside of it again (in order to lose focus), then excel auto converts the field to date. 

What we want is to upon the initial load we want the field to be converted as date already.

Any thoughts on this ?

[Prathap Senthil]

Hi Ruru,

Before proceeding with the reporting of the problem, we require some additional clarification from your end. Please share the following details to proceed further on our end:

• To analyze the reported issue, could you please share a simple and reproducible sample with duplicate data that demonstrates the problem? This will assist us in identifying the issue more efficiently and providing a resolution.

• Could you please share us the video demonstration of the issue with replication steps?

• Share with us a grid code snippet with the model class?

The details requested above will be very helpful in validating the reported query on our end and providing a solution as soon as possible. Thanks for your understanding.

Regards,

Prathap S