New xlsx format and datetime format

8 posts, 1 answers
  1. Andreas
    Andreas avatar
    143 posts
    Member since:
    May 2008

    Posted 14 Apr 2015 Link to this post

    Hi,

    How do we set the date format when using the new xlsx format export from a grid?

    All we get in Excel is that all datetime columns gets the custom format: M-D-ÅÅÅÅ. No matter what we do...

    Even in the online sample, both date columns are formatted in this way:

    http://demos.telerik.com/aspnet-ajax/grid/examples/functionality/exporting/excel-export/defaultcs.aspx

    We tried setting the DataFormatString of the grid column, no change...

    Regards
    Andreas

  2. Viktor Tachev
    Admin
    Viktor Tachev avatar
    2251 posts

    Posted 16 Apr 2015 Link to this post

    Hi Andreas,

    You can specify the format of the exported date via the  DataFormatString property of the column. Check out the following code-snippets that illustrate the approach:

    RadGrid Markup:

    <telerik:RadGrid runat="server" ID="RadGrid1"
        OnNeedDataSource="RadGrid1_NeedDataSource"
        AutoGenerateColumns="false"
        AllowPaging="true" PageSize="10">
     
        <ExportSettings ExportOnlyData="true">
            <Excel  Format="Xlsx" />
        </ExportSettings>
     
        <MasterTableView CommandItemDisplay="Top" DataKeyNames="ID"  CommandItemSettings-ShowExportToExcelButton="true">
             
            <Columns>
                <telerik:GridEditCommandColumn></telerik:GridEditCommandColumn>
                <telerik:GridBoundColumn DataField="ID" HeaderText="ID" UniqueName="ID" ReadOnly="true" InsertVisiblityMode="AlwaysVisible">
                </telerik:GridBoundColumn>
                <telerik:GridBoundColumn DataField="Name" HeaderText="Name" UniqueName="Name">
                </telerik:GridBoundColumn>
                <telerik:GridDateTimeColumn DataField="Date" HeaderText="DateColumn" DataFormatString="{0:MM\/dd\/yyyy}" ></telerik:GridDateTimeColumn>
            </Columns>
        </MasterTableView>
    </telerik:RadGrid>

    Dummy data source:

    protected void RadGrid1_NeedDataSource(object sender, Telerik.Web.UI.GridNeedDataSourceEventArgs e)
    {
        DataTable data = new DataTable();
     
        data.Columns.Add("ID", typeof(int));
        data.Columns.Add("Name");
        data.Columns.Add("Description");
        data.Columns.Add("Date", typeof(DateTime));
     
        for (int i = 1; i < 31; i++)
        {
            data.Rows.Add(i, "Name" + (i % 5).ToString(), "Description" + i.ToString(), DateTime.Now.AddDays(i));
        }
     
        RadGrid1.DataSource = data;
    }

    Give the approach a try and you should be able to implement the behavior you are looking for.

    Regards,
    Viktor Tachev
    Telerik
     

    See What's Next in App Development. Register for TelerikNEXT.

     
  3. Andreas
    Andreas avatar
    143 posts
    Member since:
    May 2008

    Posted 21 Apr 2015 in reply to Viktor Tachev Link to this post

    Hi,

    Well, the DataFormatString doesn't seems to have any effect in Excel, the format you provided, {0:MM\/dd\/yyyy}, is displayed as 2015-04-22 (Other order of date-parts and no backslashes as in the format!!!).

    The most important thing for us is to also include the time in the format, but that also seems impossible (tried {0:MM\/dd\/yyyy HH:mm:ss}), still displayed as 2015-04-22 in excel.

    Regards
    Andreas

  4. Viktor Tachev
    Admin
    Viktor Tachev avatar
    2251 posts

    Posted 23 Apr 2015 Link to this post

    Hi Andreas,

    I have tested the behavior and in some scenarios Excel is changing the way dates are displayed. However, the actual value of the cell is as specified in the DataFormataString.

    One approach you can use to avoid this behavior is to use backslash in the DataFormatString. For your convenience I am attaching a sample project that is working as expected on my and. I also made a short video of the behavior I am observing. Check it out here.

    Regards,
    Viktor Tachev
    Telerik
     

    See What's Next in App Development. Register for TelerikNEXT.

     
  5. Andreas
    Andreas avatar
    143 posts
    Member since:
    May 2008

    Posted 24 Apr 2015 in reply to Viktor Tachev Link to this post

    Hi,

    This is not working at all.

    First it will be formatted with backslash in the Grid, which I don't want.

    Second we do get an error "String was not recognized as a valid DateTime.". This is because with Swedish regional settings, the string "04\\25\\2015 10:30:36" can not be converted to date!

    Regards
    Andreas

  6. Answer
    Viktor Tachev
    Admin
    Viktor Tachev avatar
    2251 posts

    Posted 28 Apr 2015 Link to this post

    Hello Andreas,

    The behavior seems to be caused by a bug. I have notified the developers and they will provide a fix as soon as possible. Until a permanent fix is available you could use the following workaround.

    Handle the OnInfrastructureExporting event for RadGrid and set the string for the cells containing date manually.

    protected void RadGrid1_InfrastructureExporting(object sender, GridInfrastructureExportingEventArgs e)
    {
        for (int i = 2; i <= e.ExportStructure.Tables[0].Rows.Count; i++)
        {
     
            var cell = e.ExportStructure.Tables[0].Cells[3, i];
            cell.Value = ((DateTime)cell.Value).ToString("MM/dd/yyyy");
        }
     
    }

    As a token of gratitude for reporting the issue to us Telerik points were added to your account.

    Regards,
    Viktor Tachev
    Telerik
     

    See What's Next in App Development. Register for TelerikNEXT.

     
  7. Andreas
    Andreas avatar
    143 posts
    Member since:
    May 2008

    Posted 09 Jul 2015 Link to this post

    Hi,

    In the fixlist for 2015 Q2 it seemed to be fixed, but it is not quite...

    When setting the DataFormatString to {0:yyyy-MM-dd HH:mm:ss}, it has the correct format, but the time always becomes 00:00:00!!!

    Regard
    Andreas

  8. Viktor Tachev
    Admin
    Viktor Tachev avatar
    2251 posts

    Posted 13 Jul 2015 Link to this post

    Hello Andreas,

    By default Excel is "guessing" the content in a cell and formats it. In order to prevent that you need to define the format of the text explicitly.

    This can be done via the InfrastructureExporting event available for RadGrid. For convenience I have prepared a sample project where the behavior is implemented. Give it a try and let me know how it works.


    Regards,
    Viktor Tachev
    Telerik
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
Back to Top