This is a migrated thread and some comments may be shown as answers.

New xlsx format and datetime format

7 Answers 430 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Andreas
Top achievements
Rank 1
Andreas asked on 14 Apr 2015, 07:47 AM

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

7 Answers, 1 is accepted

Sort by
0
Viktor Tachev
Telerik team
answered on 16 Apr 2015, 01:13 PM
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.

 
0
Andreas
Top achievements
Rank 1
answered on 21 Apr 2015, 08:53 AM

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

0
Viktor Tachev
Telerik team
answered on 23 Apr 2015, 11:28 AM
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.

 
0
Andreas
Top achievements
Rank 1
answered on 24 Apr 2015, 08:35 AM

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

0
Accepted
Viktor Tachev
Telerik team
answered on 28 Apr 2015, 02:13 PM
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.

 
0
Andreas
Top achievements
Rank 1
answered on 09 Jul 2015, 05:32 AM

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

0
Viktor Tachev
Telerik team
answered on 13 Jul 2015, 11:38 AM
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
Tags
Grid
Asked by
Andreas
Top achievements
Rank 1
Answers by
Viktor Tachev
Telerik team
Andreas
Top achievements
Rank 1
Share this question
or