RadGrid Export to Excel Date Format

6 posts, 0 answers
  1. AratiS
    AratiS avatar
    18 posts
    Member since:
    Jun 2012

    Posted 18 Jul 2012 Link to this post

    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







  2. Shinu
    Shinu avatar
    17764 posts
    Member since:
    Mar 2007

    Posted 19 Jul 2012 Link to this post

    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.
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. raja
    raja avatar
    6 posts
    Member since:
    Jun 2014

    Posted 01 Mar in reply to Shinu Link to this post

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




       
    }

  5. Don
    Don avatar
    2 posts
    Member since:
    Mar 2016

    Posted 01 Mar Link to this post

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

  6. Don
    Don avatar
    2 posts
    Member since:
    Mar 2016

    Posted 01 Mar in reply to Don Link to this post

    combination of Parent data in the first few columns and Child data on the following COLUMNS.. sorry i missed type..
  7. Kostadin
    Admin
    Kostadin avatar
    1711 posts

    Posted 04 Mar Link to this post

    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
Back to Top
UI for ASP.NET Ajax is Ready for VS 2017