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

RadGrid Export to Excel Date Format

5 Answers 816 Views
Grid
This is a migrated thread and some comments may be shown as answers.
AratiS
Top achievements
Rank 1
AratiS asked on 18 Jul 2012, 07:15 PM
Hi,

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

Sort by
0
Shinu
Top achievements
Rank 2
answered on 19 Jul 2012, 06:47 AM
Hello,

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.
0
raja
Top achievements
Rank 1
answered on 01 Mar 2016, 11:17 AM

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";
        }
    }
    




   
}

0
Don
Top achievements
Rank 1
answered on 01 Mar 2016, 02:38 PM

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

0
Don
Top achievements
Rank 1
answered on 01 Mar 2016, 02:40 PM
combination of Parent data in the first few columns and Child data on the following COLUMNS.. sorry i missed type..
0
Kostadin
Telerik team
answered on 04 Mar 2016, 08:06 AM
Hello Don,

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
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
AratiS
Top achievements
Rank 1
Answers by
Shinu
Top achievements
Rank 2
raja
Top achievements
Rank 1
Don
Top achievements
Rank 1
Kostadin
Telerik team
Share this question
or