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

4 posts, 0 answers
  1. Eric
    Eric avatar
    7 posts
    Member since:
    Dec 2013

    Posted 22 Jul Link to this post

    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!

  2. Eyup
    Admin
    Eyup avatar
    3867 posts

    Posted 25 Jul Link to this post

    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
    Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
  3. Ronan
    Ronan avatar
    1 posts
    Member since:
    Jul 2019

    Posted 25 Jul in reply to Eyup Link to this post

    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
  4. Eyup
    Admin
    Eyup avatar
    3867 posts

    Posted 30 Jul Link to this post

    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
    Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
Back to Top