I need to format a columns datetime Display, but keep it's value in both the RadGrid And Excel Export using Biff(or someway)
I want my column to Display 06/18/13 in my Grid.
And I want it to Display 06/18/13 in my Excel Export.
However, I want my DateTime field in Excel to Retain it's DateTime attribute and be sortable.
This is my export before and after. I modified the Excel Cell Format of column B2 after export. I want it to display Tuesday, June 18, 2013 8:00:00 AM instead of Tuesday, June 18, 2013 12:00:00 AM.
<
telerik:GridBoundColumn
DataField
=
"Recorded Date2"
<br> FilterControlAltText="Filter Reported Date column" HeaderText="Reported Date" <
br
> ReadOnly="True" SortExpression="Recorded Date" UniqueName="Recorded_Date" ItemStyle-HorizontalAlign="Right" DataType="System.DateTime" DataFormatString="{0:MM/dd/yy}"><
br
> </
telerik:GridBoundColumn
>
I want my column to Display 06/18/13 in my Grid.
And I want it to Display 06/18/13 in my Excel Export.
However, I want my DateTime field in Excel to Retain it's DateTime attribute and be sortable.
Tuesday, June 18, 2013 8:00:00 AM | 06/18/13 |
Tuesday, June 18, 2013 8:00:00 AM | Tuesday, June 18, 2013 12:00:00 AM |
This is my export before and after. I modified the Excel Cell Format of column B2 after export. I want it to display Tuesday, June 18, 2013 8:00:00 AM instead of Tuesday, June 18, 2013 12:00:00 AM.
5 Answers, 1 is accepted
0

Princy
Top achievements
Rank 2
answered on 20 Jun 2013, 09:10 AM
Hi Chris,
To obtain date as 06/18/13 in Grid as well as Excel.Please try the code snippet.
ASPX:
To obtain date time as Tuesday, June 18, 2013 8:00:00 AM. Please try this code snippet.
ASPX:
Thanks,
Princy
To obtain date as 06/18/13 in Grid as well as Excel.Please try the code snippet.
ASPX:
<
telerik:RadGrid
ID
=
"RadGrid1"
runat
=
"server"
AllowSorting
=
"true"
>
<
MasterTableView
AutoGenerateColumns
=
"False"
DataKeyNames
=
"OrderID"
CommandItemDisplay
=
"Top"
>
<
CommandItemSettings
ShowExportToExcelButton
=
"true"
/>
<
Columns
>
<
telerik:GridDateTimeColumn
HeaderText
=
"ShippedDate"
DataField
=
"ShippedDate"
UniqueName
=
"ShippedDate"
SortExpression
=
"ShippedDate"
DataFormatString
=
"{0:d}"
>
</
telerik:GridDateTimeColumn
>
</
Columns
>
</
MasterTableView
>
</
telerik:RadGrid
>
To obtain date time as Tuesday, June 18, 2013 8:00:00 AM. Please try this code snippet.
ASPX:
<
telerik:RadGrid
ID
=
"RadGrid1"
runat
=
"server"
AllowSorting
=
"true"
>
<
MasterTableView
AutoGenerateColumns
=
"False"
DataKeyNames
=
"OrderID"
CommandItemDisplay
=
"Top"
>
<
CommandItemSettings
ShowExportToExcelButton
=
"true"
/>
<
Columns
>
<
telerik:GridDateTimeColumn
HeaderText
=
"ShippedDate"
DataField
=
"ShippedDate"
UniqueName
=
"ShippedDate"
SortExpression
=
"ShippedDate"
DataFormatString
=
"{0:f}"
PickerType
=
"DateTimePicker"
>
</
telerik:GridDateTimeColumn
>
</
Columns
>
</
MasterTableView
>
</
telerik:RadGrid
>
Thanks,
Princy
0

Chris
Top achievements
Rank 1
answered on 20 Jun 2013, 10:47 AM
Princy,
Is it possible to Show date 06/13/2013, but retain the full Date/Time info in Excel?
Is it possible to Show date 06/13/2013, but retain the full Date/Time info in Excel?
0

Jayesh Goyani
Top achievements
Rank 2
answered on 20 Jun 2013, 12:54 PM
Hello,
Thanks, Jayesh Goyani.
protected
void
RadGrid1_NeedDataSource(
object
sender, GridNeedDataSourceEventArgs e)
{
dynamic data =
new
[] {
new
{ ShippedDate = DateTime.Now.AddDays(1)},
new
{ ShippedDate = DateTime.Now.AddHours(1)},
new
{ ShippedDate = DateTime.Now.AddMonths(1)},
new
{ ShippedDate = DateTime.Now},
new
{ ShippedDate = DateTime.Now}
};
RadGrid1.DataSource = data;
}
protected
void
RadGrid1_ItemCommand(
object
sender, GridCommandEventArgs e)
{
if
(e.CommandName == RadGrid.ExportToExcelCommandName)
{
foreach
(GridDataItem item
in
RadGrid1.MasterTableView.Items)
{
item[
"ShippedDate"
].Text = Convert.ToDateTime(item.GetDataKeyValue(
"ShippedDate"
)).ToString();
}
}
}
<
telerik:RadGrid
ID
=
"RadGrid1"
runat
=
"server"
AllowSorting
=
"true"
OnNeedDataSource
=
"RadGrid1_NeedDataSource"
onitemcommand
=
"RadGrid1_ItemCommand"
>
<
MasterTableView
AutoGenerateColumns
=
"False"
DataKeyNames
=
"ShippedDate"
CommandItemDisplay
=
"Top"
>
<
CommandItemSettings
ShowExportToExcelButton
=
"true"
/>
<
Columns
>
<
telerik:GridDateTimeColumn
HeaderText
=
"ShippedDate"
DataField
=
"ShippedDate"
UniqueName
=
"ShippedDate"
SortExpression
=
"ShippedDate"
DataFormatString
=
"{0:d}"
>
</
telerik:GridDateTimeColumn
>
</
Columns
>
</
MasterTableView
>
</
telerik:RadGrid
>
Thanks, Jayesh Goyani.
0

Chris
Top achievements
Rank 1
answered on 20 Jun 2013, 09:04 PM
<
asp:Button
OnClientClick
=
"return gridExport()"
ID
=
"Button1"
runat
=
"server"
Text
=
"Export To Excel"
UseSubmitBehavior
=
"False"
/>
How can I intercept this in the C#?
0

Princy
Top achievements
Rank 2
answered on 21 Jun 2013, 06:07 AM
Hi Chris,
PLease try the following code snippet.
ASPX:
C#:
Thanks,
Princy
PLease try the following code snippet.
ASPX:
<
telerik:RadButton
ID
=
"rbtIGTExportToExcel"
Text
=
"Export To Excel"
OnClick
=
"btnExcel_Click"
runat
=
"server"
/>
C#:
protected
void
btnExcel_Click(
object
sender, EventArgs e)
{
foreach
(GridDataItem item
in
RadGrid1.MasterTableView.Items)
{
item[
"ShippedDate"
].Text = Convert.ToDateTime(item[
"ShippedDate"
].Text).ToString(
"dd-MM-yy hh:mm:ss"
);
}
RadGrid1.MasterTableView.ExportToExcel();
}
Thanks,
Princy