RadGrid Hierarchy Export to Excel showing exporting master table rows but detail rows are blank lines

26 posts, 0 answers
  1. Tom
    Tom avatar
    8 posts
    Member since:
    Nov 2011

    Posted 01 Nov 2013 Link to this post

    Hi there,

    When trying to export to Excel from a hierarchical radgrid I am unable to have the details tables shown on the exported Excel spreadsheet. The appropriate lines are there, however they are blank.

    Below is the code I am using to export:

    ASPX:
    <asp:LinkButton ID="ExportToExcel" ToolTip="Export to Excel"
    runat="server" Text="Export To Excel" CommandName="Export"
    OnClick="ExportToExcel_Click" />
    CS:
    protected void ExportToExcel_Click(object sender, EventArgs e)
           {
               RadGrid.ExportSettings.FileName = "filename";
               RadGrid.ExportSettings.IgnorePaging = true;
               RadGrid.ExportSettings.ExportOnlyData = true;
               RadGrid.ExportSettings.OpenInNewWindow = true;
               RadGrid.MasterTableView.UseAllDataFields = true;
               RadGrid.ExportSettings.Excel.Format = GridExcelExportFormat.ExcelML;
     
               RadGrid.MasterTableView.HierarchyDefaultExpanded = true; // first level
               RadGrid.MasterTableView.DetailTables[0].HierarchyDefaultExpanded = true; // second level 
                
               RadGrid.MasterTableView.ExportToExcel();
           }

    All three grids (master, two detail tables) are set to HierarchyMode = "Client". 

    The resulting output is shown in the image below, the fully expanded radgrid is shown in the other image below. Note that the grid is not expanded when the button is clicked, but expanding the rows on the client beforehand doesnt make a difference.

    Any idea why the rows would show up in the excel file but the data not be written?

    Thank you,

    Tom
  2. Kostadin
    Admin
    Kostadin avatar
    1709 posts

    Posted 06 Nov 2013 Link to this post

    Hello Tom,

    I noticed that you are using an ExcelML format in your project. Keep in mind that this format requires that the HierarchyLoadMode property be set to either Client or ServerBind. When you are using this export format, all items in the hierarchy will expand either if they are collapsed before the export.

    Regards,
    Kostadin
    Telerik
    If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to the blog feed now.
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. Tom
    Tom avatar
    8 posts
    Member since:
    Nov 2011

    Posted 16 Nov 2013 Link to this post

    Hi Kostadin,

    My aspx page already has the HierarchyLoadMode set to Client, but I've added it in to my server side code as well:

    protected void ExportToExcel_Click(object sender, EventArgs e)
          {
              RadGrid.ExportSettings.FileName = "filename";
              RadGrid.ExportSettings.IgnorePaging = true;
              RadGrid.ExportSettings.ExportOnlyData = true;
              RadGrid.ExportSettings.OpenInNewWindow = true;
              RadGrid.MasterTableView.UseAllDataFields = true;
              RadGrid.ExportSettings.Excel.Format = GridExcelExportFormat.ExcelML;
     
              RadGrid.MasterTableView.HierarchyDefaultExpanded = true;
              RadGrid.MasterTableView.DetailTables[0].HierarchyDefaultExpanded = true;
     
              RadGrid.MasterTableView.HierarchyLoadMode = GridChildLoadMode.Client;
              RadGrid.MasterTableView.DetailTables[0].HierarchyLoadMode =
                      GridChildLoadMode.Client;
               
              RadGrid.MasterTableView.ExportToExcel();
          }

    But am still getting the same results. 

    I do notice that both before and after this change that there is a header row for the 3rd level hierarchy on occassion, but again with blank rows where the data should be. Also the second level hierarchy row is new present, header or data.

    Any ideas?

    Thank you,

    Tom
  5. Kostadin
    Admin
    Kostadin avatar
    1709 posts

    Posted 20 Nov 2013 Link to this post

    Hello Tom,

    I prepared a small sample and attached it to this tread. As you could see in my sample the hierarchy is exporting properly. Please give it a try and let me know how it differs from your real setup.

    Regards,
    Kostadin
    Telerik
    If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to the blog feed now.
  6. Gambit
    Gambit avatar
    2 posts
    Member since:
    Mar 2014

    Posted 05 Mar 2014 Link to this post

    Hello Kosdatin,
    I have the same problem. I tried your attached code but its not working. Have you solved this problem? Do you have any idea about it?
    Thank you,
  7. Kostadin
    Admin
    Kostadin avatar
    1709 posts

    Posted 10 Mar 2014 Link to this post

    Hi Gambit,

    Could you please elaborate a little bit more what exactly is not working on your side. I prepared a video which demonstrates the exported output of the sample from my previous reply. As you can see all the data of the MasterTableView and DetailTableViews are exported properly. I would appreciate if you could replicate the issue in my sample in order to investigate it further.

    Regards,
    Kostadin
    Telerik

    DevCraft Q1'14 is here! Watch the online conference to see how this release solves your top-5 .NET challenges. Watch on demand now.

  8. Majeed
    Majeed  avatar
    4 posts
    Member since:
    Mar 2014

    Posted 13 May 2014 in reply to Kostadin Link to this post

    Dear Telerik Team ..!

    I am too much worried to export RadGrid into excel or any other formate by builtin export button .
    when I clicked on export button it call need Data Source and Disappeared the header of export button and link button as I entered in my rad grid for edit the grid.
    kindly reply me ASAP
    you can see the Print short attached. 
  9. Princy
    Princy avatar
    17421 posts
    Member since:
    Mar 2007

    Posted 13 May 2014 in reply to Majeed Link to this post

    Hi Majeed,

    The exporting feature works only with regular postbacks. This means, that the asynchronous postback should be canceled when performing an export. Make sure you have disabled ajax for the Export button. More information on this topic is available below:
    Export from Ajaxified Grid

    Thanks,
    Princy
  10. Majeed
    Majeed  avatar
    4 posts
    Member since:
    Mar 2014

    Posted 13 May 2014 in reply to Princy Link to this post

    Dear Princy Thanks for you quick response issue is resolved but now I am unable to export RadGrid to Excel or any other formate when I clicked on export button it Fire Page_Load where Condition is 
     if (!IsPostBack)
                {              
                   var Result = db.sp_Get_SOData();
                  GV_SO.DataSource = Result;
                  GV_SO.DataBind();
                }
    Postback is true so it move on directly need DataSource

     protected void GV_SO_NeedDataSource(object sender, GridNeedDataSourceEventArgs e)
            {
                   var Result = db.sp_Get_SOData();
                    GV_SO.DataSource = Result;
            }
    and back to the browser without any action regarding exporting kindly Help me Thanks in advance
  11. Gambit
    Gambit avatar
    2 posts
    Member since:
    Mar 2014

    Posted 13 May 2014 Link to this post

    I solved, GridExcelExportFormat.ExcelML is not working.

    My code: 
    protected void ExportToExcel_Click(object sender, EventArgs e)
           {

                   RadGrid1.ExportSettings.ExportOnlyData = true;
                   RadGrid1.ExportSettings.IgnorePaging = true;
                   RadGrid1.ExportSettings.OpenInNewWindow = true;
                   RadGrid1.ExportSettings.FileName = "fileName";
                   RadGrid1.ExportSettings.Excel.Format = GridExcelExportFormat.Html;
                   RadGrid1.ExportSettings.HideStructureColumns = true;

                  RadGridBakimSorgulaData.MasterTableView.HierarchyDefaultExpanded = true;
                  RadGridBakimSorgulaData.MasterTableView.DetailTables[0].HierarchyDefaultExpanded = true;
                   
                  RadGrid.MasterTableView.HierarchyLoadMode = GridChildLoadMode.Client;
                  RadGrid.MasterTableView.DetailTables[0].HierarchyLoadMode = GridChildLoadMode.Client;

                  RadGridBakimSorgulaData.MasterTableView.ExportToExcel();
              
           }

    Excel style:
    protected void RadGrid1_HTMLExporting(object sender, GridHTMLExportingEventArgs e)
           {
                   e.XmlOptions = @"<xml>
                        <x:ExcelWorkbook> 
                          <x:ExcelWorksheets>
                            <x:ExcelWorksheet>
                              <x:WorksheetOptions>
                                <x:Print>
                                  <x:ValidPrinterInfo/> 
                                  <x:BlackAndWhite/> 
                                  <x:DraftQuality/> 
                                  <x:Gridlines/>
                                  <x:RowColHeadings/> 
                                </x:Print>
                              </x:WorksheetOptions>
                            </x:ExcelWorksheet>
                          </x:ExcelWorksheets>
                        </x:ExcelWorkbook>
                     </xml>";   
           }

  12. Princy
    Princy avatar
    17421 posts
    Member since:
    Mar 2007

    Posted 14 May 2014 in reply to Majeed Link to this post

    Hi Majeed,

    When you are binding the Grid using NeedDataSource event, you donot have to bind it again on PageLoad.Please take a look at the sample code snippet that i tried which works fine at my end. Provide your full code if this doesn't help.

    ASPX:
    <asp:ScriptManager ID="ScriptManager1" runat="server">
    </asp:ScriptManager>
    <script type="text/javascript">
        function onRequestStart(sender, args) {
            if (args.get_eventTarget().indexOf("ExportTo") >= 0) {
                args.set_enableAjax(false);
            }
        }
    </script>
    <telerik:RadAjaxManager ID="RadAjaxManager1" runat="server">
        <ClientEvents OnRequestStart="onRequestStart" />
        <AjaxSettings>
            <telerik:AjaxSetting AjaxControlID="RadGrid1">
                <UpdatedControls>
                    <telerik:AjaxUpdatedControl ControlID="RadGrid1" LoadingPanelID="RadAjaxLoadingPanel1" />
                </UpdatedControls>
            </telerik:AjaxSetting>
        </AjaxSettings>
    </telerik:RadAjaxManager>
    <telerik:RadAjaxLoadingPanel ID="RadAjaxLoadingPanel1" runat="server" Skin="Silk">
    </telerik:RadAjaxLoadingPanel>
    <telerik:RadGrid ID="RadGrid1" runat="server" AutoGenerateColumns="false" AllowPaging="true"AutoGenerateEditColumn="true" AllowSorting="true" OnNeedDataSource="RadGrid1_NeedDataSource">
        <ExportSettings ExportOnlyData="true" IgnorePaging="true" OpenInNewWindow="true">
        </ExportSettings>
        <MasterTableView DataKeyNames="OrderID" CommandItemDisplay="Top">
            <CommandItemSettings ShowExportToExcelButton="true" ShowExportToCsvButton="true" />
            <Columns>
                <telerik:GridBoundColumn UniqueName="OrderID" DataField="OrderID" HeaderText="OrderID">
                </telerik:GridBoundColumn>
                <telerik:GridBoundColumn DataField="ShipCity" HeaderText="ShipCity" UniqueName="ShipCity">
                </telerik:GridBoundColumn>
                <telerik:GridBoundColumn UniqueName="CustomerID" DataField="CustomerID" HeaderText="CustomerID">
                </telerik:GridBoundColumn>
                <telerik:GridBoundColumn DataField="Freight" HeaderText="Freight" UniqueName="Freight">
                </telerik:GridBoundColumn>
            </Columns>
        </MasterTableView>
    </telerik:RadGrid>

    C#:
    protected void RadGrid1_NeedDataSource(object sender, GridNeedDataSourceEventArgs e)
    {
        RadGrid1.DataSource = GetDataTable("SELECT * FROM Orders");
    }
    public DataTable GetDataTable(string query)
    {
        String ConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        SqlConnection conn = new SqlConnection(ConnString);
        SqlDataAdapter adapter = new SqlDataAdapter();
        adapter.SelectCommand = new SqlCommand(query, conn);
        DataTable myDataTable = new DataTable();
        conn.Open();
        try
        {
            adapter.Fill(myDataTable);
        }
        finally
        {
            conn.Close();
        }
        return myDataTable;
    }

    Thanks,
    Princy
  13. Asutosh
    Asutosh avatar
    130 posts
    Member since:
    Sep 2013

    Posted 11 Jun 2014 in reply to Kostadin Link to this post

    hi
     i am using your zip file code
    its work nice
    but i dont want MasterTableView header and filter
    i want hierarchical table header only once not or all hierarchical data and also i want filter for hierarchical data
    how to achieve it
    i have attached 2 xls file
    one is generated output and another is required output
    thanks
  14. Asutosh
    Asutosh avatar
    130 posts
    Member since:
    Sep 2013

    Posted 11 Jun 2014 in reply to Kostadin Link to this post

    hi

     i am using your zip file code

    its work nice

    but i dont want MasterTableView header and filter

    i want hierarchical table header only once not or all hierarchical data and also i want filter for hierarchical data

    how to achieve it

    i have attached 2 xls file

    one is generated output and another is required output
    thanks
  15. Asutosh
    Asutosh avatar
    130 posts
    Member since:
    Sep 2013

    Posted 13 Jun 2014 in reply to Kostadin Link to this post

    hi i am using your demo
    i have problem that i am binding my grid in button click event and i dont use onneeddatasource event
    i used following code to fill my grid
    gridviewname.datasource = tablename;
    gridviewname.databind();
    no when i use your code to export it give me null reference error
    if i use onneeddatasource event its works fine 
    but i dont want to use onneeddatasource


  16. Kostadin
    Admin
    Kostadin avatar
    1709 posts

    Posted 18 Jun 2014 Link to this post

    Hello Asutosh,

    I already answered your question in the following forum thread, so I would suggest you to continue our conversation there and close this one.

    Regards,
    Kostadin
    Telerik
     

    Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

     
  17. Spiros
    Spiros avatar
    15 posts
    Member since:
    May 2010

    Posted 18 Sep 2014 Link to this post

    Hi Guys , I have a grid with is Master Details , The export works fine but the Grid Caption is now being shown.
    RadGrid1.MasterTableView.Caption = "xxxx-xxxx";

    Here is the following code.
      RadGrid1.MasterTableView.Caption = "xxxx-xxxx";
                RadGrid1.ExportSettings.FileName = "filename";
                //RadGrid1.ExportSettings.Excel.FileExtension = "xlsx";
                RadGrid1.ExportSettings.IgnorePaging = true;
                RadGrid1.ExportSettings.ExportOnlyData = true;
                RadGrid1.ExportSettings.OpenInNewWindow = true;
                RadGrid1.MasterTableView.UseAllDataFields = true;

                RadGrid1.ExportSettings.Excel.Format = GridExcelExportFormat.ExcelML;

                RadGrid1.MasterTableView.HierarchyDefaultExpanded = true;
                RadGrid1.MasterTableView.DetailTables[0].HierarchyDefaultExpanded = true;

                RadGrid1.MasterTableView.HierarchyLoadMode = GridChildLoadMode.Client;
                RadGrid1.MasterTableView.DetailTables[0].HierarchyLoadMode = GridChildLoadMode.Client;

                RadGrid1.MasterTableView.ExportToExcel();
    Any Idea ?
    Thanks
  18. Kostadin
    Admin
    Kostadin avatar
    1709 posts

    Posted 22 Sep 2014 Link to this post

    Hello Spiros,

    Keep in mind that ExcelML format builds the output directly from the datasource and not from the page. If you need to export the Caption without manually modifying the output I would recommend you to use Html based export format.

    Regards,
    Kostadin
    Telerik
     

    Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

     
  19. SABITHA
    SABITHA avatar
    13 posts
    Member since:
    Aug 2013

    Posted 03 Feb 2015 in reply to Kostadin Link to this post

    Hi ,

    In the sample attached source ExportHierarchyGrid.aspx.cs after executing the source it is not able to collapse or expand the detail datas. 
    How can we give a collapse expand column in the excel sheet. pls do reply as soon as possible.


    Regards,
    Sabitha
  20. SABITHA
    SABITHA avatar
    13 posts
    Member since:
    Aug 2013

    Posted 03 Feb 2015 in reply to Kostadin Link to this post

    Is it possible to get the aggergate column in the excel sheet. I'm populating th grid from the server code that is the C# file. 
  21. Kostadin
    Admin
    Kostadin avatar
    1709 posts

    Posted 05 Feb 2015 Link to this post

    Hi SABITHA,

    I am afraid that the detail tables could not be collapsed and there isn't an option to enable this feature. Regards your second question could you please elaborate a little bit more? Are you talking about the aggregates in the footer item? If that is the case then I am afraid you have to use Html based export format since ExcelML does not export the footer items.

    Regards,
    Kostadin
    Telerik
     

    Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

     
  22. KFS
    KFS avatar
    4 posts
    Member since:
    Feb 2014

    Posted 12 Feb 2015 Link to this post

    Hi Dears,
    After trying the suggested solution the export now get the detail table in exported excel file but only one detail row in appear in file for each mater row.
    find below the code used
    RGrid.MasterTableView.UseAllDataFields = true;
    RGrid.MasterTableView.HierarchyDefaultExpanded = true; // first level
    RGrid.MasterTableView.DetailTables[0].HierarchyDefaultExpanded = true; // second level
    RGrid.MasterTableView.HierarchyLoadMode = GridChildLoadMode.Client;
    RGrid.MasterTableView.DetailTables[0].HierarchyLoadMode =GridChildLoadMode.Client;
    RGrid.MasterTableView.ExportToExcel();
    <ExportSettings ExportOnlyData="True" FileName="file name" IgnorePaging="True" OpenInNewWindow="True" HideStructureColumns="True">
              <Excel Format="Html" />                         
     </ExportSettings>

    please advice.
  23. Kostadin
    Admin
    Kostadin avatar
    1709 posts

    Posted 16 Feb 2015 Link to this post

    Hello,

    In case you need to expand the detail tables on the second level you have to loop though all detail tables on the first level and set HierarchyDefaultExpanded to true. In your case you are expanding only the first detail table by using this code  RGrid.MasterTableView.DetailTables[0]. I would recommend you to examine the following help article which demonstrates a possible solution to loop though all detail tables.

    Regards,
    Kostadin
    Telerik
     

    Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

     
  24. SABITHA
    SABITHA avatar
    13 posts
    Member since:
    Aug 2013

    Posted 20 Feb 2015 in reply to Gambit Link to this post

    Hi all,

    I'm using the telerik hierarchical rad grid. As per the inputs from the form i 'm using the format html for populatign the master and the detail table details.  Currently i'm having a requirement to make the header color same as that of grid . header background color=navy and forecolor= white.
    Could you please let me know how these can be done the in the html formating event.
  25. Kostadin
    Admin
    Kostadin avatar
    1709 posts

    Posted 24 Feb 2015 Link to this post

    Hello SABITHA,

    In case you are using the Html based export format you can hook OnExportCellFormatting  and apply a style element to each header cell. Please check out Styling rows/cells section in the the following help article which elaborates more on this approach.

    Regards,
    Kostadin
    Telerik
     

    Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

     
  26. SABITHA
    SABITHA avatar
    13 posts
    Member since:
    Aug 2013

    Posted 25 Feb 2015 in reply to Kostadin Link to this post

    Hi all,
    I tried to use the several methos in the excel exporting event in that I'm still not able to locate the header .  Please see the below sample code.
    .
    protected void RADGRid1_ExcelExportCellFormatting(object sender, ExcelExportCellFormattingEventArgs e)
    {
    GridDataItem item = e.Cell.Parent as GridDataItem;

    if (item.ItemType == GridItemType.Item)
    item.Style["Horizontal-Align"] = "Left";
    //if (item.ItemType == GridItemType.Header)
    //{
    // e.Cell.Style["Horizontal-Align"] = "Right";
    // e.Cell.Style["background-color"] = "2D62FF";
    //}
    //e.FormattedColumn.HeaderStyle.BackColor = Color.Navy;
    //e.FormattedColumn.HeaderStyle.CssClass = "rgHeader";
    //if (e.FormattedColumn.UniqueName == "TermSheetId")
    //{
    // e.Cell.Style["mso-header-data"] = "Color.Navy";
    //}
    }
  27. Kostadin
    Admin
    Kostadin avatar
    1709 posts

    Posted 27 Feb 2015 Link to this post

    Hello SABITHA,

    It appears that ExcelExportCellFormatting is not fired for the header cells and you can not use it for your case. Nevertheless you change the header color by hooking OnItemCreated. Note that you have to use a boolean variable to distinguish whether the export command is fired. Please check out the following code snippet.
    protected void RadGrid1_ItemCommand(object sender, Telerik.Web.UI.GridCommandEventArgs e)
    {
        if (e.CommandName == RadGrid.ExportToExcelCommandName || e.CommandName == RadGrid.ExportToPdfCommandName)
        {
            RadGrid1.MasterTableView.HierarchyDefaultExpanded = true;
            isExport = true;
        }
    }
     
    bool isExport = false;
    protected void RadGrid1_ItemCreated(object sender, GridItemEventArgs e)
    {
        if (e.Item is GridHeaderItem && isExport)
        {
            GridHeaderItem header = e.Item as GridHeaderItem;
            foreach (TableCell cell in header.Cells)
            {
                cell.Style.Add("background-color", "red");
            }
        }
    }


    Regards,
    Kostadin
    Telerik
     

    Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

     
Back to Top
UI for ASP.NET Ajax is Ready for VS 2017