The date format is changed to "MM/dd/yyyy" after exporting grid data to Excel BIFF

[Eric]

I am experiencing an issue and I am not sure it is a bug or just is an expected behavior from MS Excel.

I have a Telerik RadGrid, and there is a date column on the grid layout. The date format I am using to display date values is "dd/MM/yyyy". Then, I export that grid data to Excel file with GridExcelExportFormat.Biff type. The problem here is when I open the newly exported Excel file, the date format is changed to "MM/dd/yyyy". I think MS Excel did re-apply the default date format for my date column (?)

 

I want to keep displaying my date format "dd/MM/yyyy" in the Excel file after exporting the grid data, is there a way to do that?

 

Here is my code.

Default.aspx

01.<asp:LinkButton ID="linkBtnExportToExcel" runat="server" OnClick="linkBtnExportToExcel_Click">Export to Excel</asp:LinkButton>
02.
03.  <telerik:RadGrid AutoGenerateColumns="false" ID="rgInvoices"
04.      AllowFilteringByColumn="false" AllowSorting="True"
05.      EnableLinqExpressions="false"
06.      ClientSettings-EnableAlternatingItems="false"
07.      GroupingSettings-CaseSensitive="false"
08.      AllowPaging="true" AllowCustomPaging="true" PageSize="20" runat="server"
09.
10.      OnNeedDataSource="rgInvoices_NeedDataSource"
11.      OnItemDataBound="rgInvoices_ItemDataBound"
12.      OnItemCommand="rgInvoices_ItemCommand"
13.      OnSortCommand="rgInvoices_SortCommand">
14.
15.      <GroupingSettings CaseSensitive="false"></GroupingSettings>
16.      <ClientSettings EnableRowHoverStyle="true"></ClientSettings>
17.
18.      <MasterTableView AutoGenerateColumns="false" AllowFilteringByColumn="false" ShowFooter="false" DataKeyNames="EmployeeID,HiredDate,Employee">
19.
20.          <Columns>
21.              <telerik:GridBoundColumn UniqueName="Employee" DataField="Employee" HeaderText="Employee" ShowFilterIcon="false" AllowFiltering="false" AutoPostBackOnFilter="true" DataFormatString="{0:@}">
22.
23.              </telerik:GridBoundColumn>
24.              <telerik:GridBoundColumn UniqueName="HiredDate" DataField="HiredDate" HeaderText="Hired Date"
25.                  DataFormatString="{0:dd/MM/yyyy}" ShowFilterIcon="false" AllowFiltering="false" AutoPostBackOnFilter="true">
26.              </telerik:GridBoundColumn>
27.          </Columns>
28.
29.          <HeaderStyle CssClass="RadGrid-HeaderStyle" Font-Bold="true" />
30.          <ItemStyle CssClass="RadGrid-ItemStyle" />
31.          <AlternatingItemStyle CssClass="RadGrid-AlternatingItemStyle" />
32.      </MasterTableView>
33.  </telerik:RadGrid>

 

And Default.aspx.vb

01.Public Class _Default
02.    Inherits System.Web.UI.Page
03.    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
04.
05.    End Sub
06.
07.    Protected Sub rgInvoices_NeedDataSource(sender As Object, e As Telerik.Web.UI.GridNeedDataSourceEventArgs)
08.        rgInvoices.DataSource = GetGridSource()
09.    End Sub
10.
11.    Protected Sub rgInvoices_ItemDataBound(sender As Object, e As Telerik.Web.UI.GridItemEventArgs)
12.
13.    End Sub
14.
15.    Protected Sub rgInvoices_ItemCommand(sender As Object, e As Telerik.Web.UI.GridCommandEventArgs)
16.
17.    End Sub
18.
19.    Protected Sub rgInvoices_SortCommand(sender As Object, e As Telerik.Web.UI.GridSortCommandEventArgs)
20.
21.    End Sub
22.
23.    Protected Sub linkBtnExportToExcel_Click(sender As Object, e As EventArgs)
24.        Me.rgInvoices.ExportSettings.Excel.Format = GridExcelExportFormat.Biff
25.        Me.rgInvoices.ExportSettings.IgnorePaging = True
26.        Me.rgInvoices.ExportSettings.ExportOnlyData = True
27.        Me.rgInvoices.ExportSettings.OpenInNewWindow = True
28.        Me.rgInvoices.ExportSettings.FileName = "ExportExcelDemo"
29.        Me.rgInvoices.MasterTableView.ExportToExcel()
30.    End Sub
31.
32.    Private Function GetGridSource() As DataTable
33.        Dim dataTable As DataTable = New DataTable()
34.
35.        Dim column As DataColumn = New DataColumn()
36.        column.DataType = Type.[GetType]("System.Int32")
37.        column.ColumnName = "EmployeeID"
38.        dataTable.Columns.Add(column)
39.
40.        column = New DataColumn()
41.        column.DataType = Type.[GetType]("System.DateTime")
42.        column.ColumnName = "HiredDate"
43.        dataTable.Columns.Add(column)
44.
45.        column = New DataColumn()
46.        column.DataType = Type.[GetType]("System.String")
47.        column.ColumnName = "Employee"
48.        dataTable.Columns.Add(column)
49.
50.        Dim PrimaryKeyColumns As DataColumn() = New DataColumn(0) {}
51.        PrimaryKeyColumns(0) = dataTable.Columns("EmployeeID")
52.        dataTable.PrimaryKey = PrimaryKeyColumns
53.
54.
55.        Dim row As DataRow = dataTable.NewRow()
56.        row("EmployeeID") = 2
57.        row("HiredDate") = New Date(2019, 7, 1)
58.        row("Employee") = "Tina Bush"
59.        dataTable.Rows.Add(row)
60.
61.        Return dataTable
62.    End Function
63.
64.End Class

 

Thanks for your support!

[Eyup]

Hello Eric,

You can try enabling the SuppressColumnDataFormatStrings property to resolve this issue:
https://docs.telerik.com/devtools/aspnet-ajax/controls/grid/functionality/exporting/export-formats/excel-biff-export

Alternatively, you can try changing the Culture of the page since this may be related to Culture related behavior. In some countries the Month and Day values are reversed.

I hope this will prove helpful.

Regards,
Eyup
Progress Telerik

[Ronan]

Hi Eyup, 

 

Thank you for your help on this question. :) 

I have modified my code to enable SuppressColumnDataFormatStrings, but the output Excel file does not display my date values as expected. It seems like Excel converts the date values to integer (?). Please take a look at the attached image to get the issue.

 

And here is my modified code to enable the attribute

01.Protected Sub linkBtnExportToExcel_Click(sender As Object, e As EventArgs)
02.        Me.rgInvoices.ExportSettings.Excel.Format = GridExcelExportFormat.Biff
03.        Me.rgInvoices.ExportSettings.IgnorePaging = True
04.        Me.rgInvoices.ExportSettings.ExportOnlyData = True
05.        Me.rgInvoices.ExportSettings.OpenInNewWindow = True
06.        Me.rgInvoices.ExportSettings.SuppressColumnDataFormatStrings = True
07.        Me.rgInvoices.ExportSettings.FileName = "ExportExcelDemo"
08.        Me.rgInvoices.MasterTableView.ExportToExcel()
09.    End Sub

[Eyup]

Hi Ronan,

In this case you can open a formal support ticket to send us a runnable RadGrid web site demonstrating the issue you are facing. This will allow us to replicate the problem locally and provide more accurate and precise solutions.

Regards,
Eyup
Progress Telerik