After doing export to Excel the date format of Date field in Excel is "11/29/2010 0:00".
Where as in the grid it is displaying as "11/29/2010 12:00:00 AM".
I want the Date format to remain the same in Excel as it is in the Grid, Please let me know how can i do that.
Thanks,
ADod
5 Answers, 1 is accepted
Try setting the as shown below.
C#:
protected
void
RadGrid1_ExcelExportCellFormatting(
object
source, ExcelExportCellFormattingEventArgs e)
{
if
((e.FormattedColumn.UniqueName) ==
"UniqueName"
)
{
e.Cell.Style[
"mso-number-format"
] = @
"m\/d\/yy\ h\:mm\ AM\/PM"
;
}
}
Thanks,
Shinu.
Shinu,
I am following the same method but on exporting to excel the date format is not changing.
Find the attachment of my code.
Kindly suggest where I am wrong
ASPX CODE
<telerik:RadGrid RenderMode="Lightweight" ID="rgrdJobSheet" runat="server" AllowPaging="true"
PageSize="7" AutoGenerateColumns="false"
OnItemCommand="rgrdJobSheet_ItemCommand" OnItemDataBound="rgrdJobSheet_ItemDataBound" OnNeedDataSource="rgrdJobSheet_NeedDataSource" OnExcelExportCellFormatting="rgrdJobSheet_ExcelExportCellFormatting"
>
<MasterTableView CommandItemDisplay="Top">
<CommandItemSettings ShowExportToExcelButton="true" ShowAddNewRecordButton="false" ShowRefreshButton="false" />
<Columns>
<telerik:GridBoundColumn DataField="ReceivedDate" HeaderText="Received Date" UniqueName="ReceivedDate" DataFormatString="{0:MM/dd/yyyy}" DataType="System.DateTime"
HeaderStyle-Width="140px">
</telerik:GridBoundColumn>
</Columns>
</MasterTableView>
</telerik:RadGrid>
CODE FILE
public partial class Pages_Reports_JobSheetReport : System.Web.UI.Page
{
WorkOrderBAL objWorkOrder;
OMSDetailsProperties objProperties;
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
Session["MenuPage"] = "JobSheet";
BindJobSheet();
rgrdJobSheet.DataBind();
}
}
protected void btSearch_Click(object sender, EventArgs e)
{
BindJobSheet();
rgrdJobSheet.DataBind();
}
protected void btReset_Click(object sender, EventArgs e)
{
}
private void BindJobSheet()
{
try
{
objWorkOrder = new WorkOrderBAL();
objProperties = new OMSDetailsProperties();
objProperties.DateFrom = "01-01-2015";
objProperties.DateTo = "01-10-2015";
objProperties.DevisionID = "1";
DataTable dt = objWorkOrder.ReportJobSheet(objProperties);
rgrdJobSheet.DataSource = dt;
}
catch (Exception ex)
{
}
}
protected void rgrdJobSheet_ItemDataBound(object sender, Telerik.Web.UI.GridItemEventArgs e)
{
if (e.Item is GridPagerItem)
{
GridPagerItem pager = (GridPagerItem)e.Item;
Label lbl = (Label)pager.FindControl("ChangePageSizeLabel");
lbl.Visible = false;
RadComboBox combo = (RadComboBox)pager.FindControl("PageSizeComboBox");
combo.Visible = false;
}
}
protected void rgrdJobSheet_NeedDataSource(object sender, Telerik.Web.UI.GridNeedDataSourceEventArgs e)
{
BindJobSheet();
}
protected void rgrdJobSheet_ExportCellFormatting(object sender, Telerik.Web.UI.ExportCellFormattingEventArgs e)
{
switch (e.FormattedColumn.UniqueName)
{
case "ReceivedDate":
e.Cell.Style["mso-number-format"] = @"dd\-mmm\-yy";
break;
}
}
protected void rgrdJobSheet_ItemCommand(object sender, GridCommandEventArgs e)
{
if (e.CommandName == RadGrid.ExportToExcelCommandName)
{
rgrdJobSheet.ExportSettings.Excel.Format = GridExcelExportFormat.ExcelML;
rgrdJobSheet.ExportSettings.IgnorePaging = true;
rgrdJobSheet.ExportSettings.ExportOnlyData = true;
rgrdJobSheet.ExportSettings.OpenInNewWindow = true;
rgrdJobSheet.ExportSettings.FileName = "Jobsheet";
}
}
}
Hi I'm new here, please help me :) i have a radGrid with parent and child view, now, i've successfully exported it to excel file using ExcelML.. here's my code
rgdReceiving.ExportSettings.FileName = "ReceivingAdjustment";
rgdReceiving.ExportSettings.IgnorePaging = true;
rgdReceiving.ExportSettings.ExportOnlyData = true;
rgdReceiving.ExportSettings.OpenInNewWindow = true;
rgdReceiving.MasterTableView.UseAllDataFields = true;
rgdReceiving.ExportSettings.Excel.Format = GridExcelExportFormat.ExcelML;
rgdReceiving.MasterTableView.HierarchyDefaultExpanded = true;
rgdReceiving.MasterTableView.DetailTables[0].HierarchyDefaultExpanded = true;
rgdReceiving.MasterTableView.HierarchyLoadMode = GridChildLoadMode.Client;
rgdReceiving.MasterTableView.DetailTables[0].HierarchyLoadMode = GridChildLoadMode.Client;
rgdReceiving.MasterTableView.ExportToExcel();
what i want is to export it in excel file in 1 row only not in grouping, for excel i have 2 parent data, and 5 child data each. when I export it, i should have 10 row only (combination of Parent data in the first few columns and Child data on the following rows., not in grouping.. Please help me ..
I am afraid this could not be achieved by using ExcelML. Nevertheless, a possible solution is to use ExportInfrastructure and manually create the Excel file as you need. You can find more information in the following help article. Additionally you can examine this code library which demonstrates how to export hierarchical grid.
Regards,
Kostadin
Telerik