RadGrid Export Format Xlsx Format but with some formatting like combine header

1 posts, 0 answers
  1. Manish
    Manish avatar
    69 posts
    Member since:
    Jul 2011

    Posted 26 Oct 2017 Link to this post

    Hi I am using radgrid and I want to apply export excel functionality on that but when I am trying to add header using Prerender event it is not working for 

    rgEnrolmentExport.ExportSettings.Excel.Format = GridExcelExportFormat.Xlsx;

    I need this format to remove prompt which we normally get on opening of Xls file 

    So please see code 

    On aspx -

     <telerik:RadGrid ID="rgEnrolmentExport" OnPreRender="rgEnrolmentExport_PreRender" runat="server" AllowPaging="false" AutoGenerateColumns="true" ExportSettings-Excel-Format="Html">
                        <ExportSettings IgnorePaging="True" OpenInNewWindow="True" ExportOnlyData="True">
                        </ExportSettings>
                    </telerik:RadGrid>

    on C# 

      rgEnrolmentExport.DataSource = dtExportData;
                                        rgEnrolmentExport.DataBind();

                                        DateTime obj1 = (DateTime)rdpExportDate.SelectedDate;
                                        rgEnrolmentExport.ExportSettings.FileName = "HO_Enrollment_Source_Report_" + obj1.ToString("MM.yyyy");
                                        rgEnrolmentExport.ExportSettings.Excel.Format = GridExcelExportFormat.Xlsx;
                                      //  rgEnrolmentExport.ExportSettings.Excel.FileExtension = "Xlsx";
                                        rgEnrolmentExport.HeaderStyle.Font.Bold = true;
                                        rgEnrolmentExport.MasterTableView.ExportToExcel();

     

     

    And in Prerender 

     

     protected void rgEnrolmentExport_PreRender(object sender, EventArgs e)
            {
                //get the current header 
               
                    GridItem[] header = rgEnrolmentExport.MasterTableView.GetItems(GridItemType.Header);
                    if (header.Length > 0)
                    {
                        //get the current THead element 
                        DateTime obj1 = (DateTime)rdpExportDate.SelectedDate;
                        GridTHead head = ((GridTHead)header[0].Parent.Controls[0].Parent);
                        //Get the GridHeaderItem from the THead 
                        GridHeaderItem currentHeaderItem = (GridHeaderItem)head.Controls[0];
                        //Clear all GridHeaderItems 
                        head.Controls.Clear();
                        //create a new GridHeaderItem which will be the new row 
                        GridHeaderItem newHeaderItem = new GridHeaderItem(rgEnrolmentExport.MasterTableView, 0, 0);

                        // newHeaderItem.Cells.Add(new GridTableHeaderCell());


                        newHeaderItem.Cells.Add(new GridTableHeaderCell());
                        newHeaderItem.Cells.Add(new GridTableHeaderCell());
                        newHeaderItem.Cells.Add(new GridTableHeaderCell { Text = "Massachusetts", ColumnSpan = 3, HorizontalAlign = HorizontalAlign.Center });


                        newHeaderItem.Cells.Add(new GridTableHeaderCell { Text = "Rhode Island", ColumnSpan = 3, HorizontalAlign = HorizontalAlign.Center });

                        //  newHeaderItem.Cells.Add(new GridTableHeaderCell());
                        newHeaderItem.Cells.Add(new GridTableHeaderCell { Text = "New Hampshire", ColumnSpan = 3, HorizontalAlign = HorizontalAlign.Center });

                        // newHeaderItem.Cells.Add(new GridTableHeaderCell());
                        newHeaderItem.Cells.Add(new GridTableHeaderCell { Text = "All  EMembers", ColumnSpan = 3, HorizontalAlign = HorizontalAlign.Center });

                        // head.Controls.AddAt(0, newHeaderItem);
                        GridHeaderItem newHeaderItem1 = new GridHeaderItem(rgEnrolmentExport.MasterTableView, 0, 0);
                        newHeaderItem1.Cells.Add(new GridTableHeaderCell());
                        newHeaderItem1.Cells.Add(new GridTableHeaderCell());
                        newHeaderItem1.Cells.Add(new GridTableHeaderCell { Text = "Heating Oil Enrollment Sources Report for " + obj1.ToString("MMMMM yyyy"), ColumnSpan = 12, HorizontalAlign = HorizontalAlign.Center });


                        //Add back the GridHeaderItems in the order you want them to appear 

                        head.Controls.Add(newHeaderItem1);
                        head.Controls.Add(newHeaderItem);
                        head.Controls.Add(currentHeaderItem);
                    }
                
            }

    So basically if I am not applying Xlsx format it woking fine pelase see attachment it is exoporting what ever I need to do but with Xlsx format it throwing error.

    So conclusion is I need to export file as I have attached in Image but in Xlsx format 

     

Back to Top