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:
We tried setting the DataFormatString of the grid column, no change...
Regards
Andreas
7 Answers, 1 is accepted
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.

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
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.

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
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.

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
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