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

[Tom]

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

[Kostadin]

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

[Tom]

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

[Kostadin]

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

[Gambit]

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,

[Kostadin]

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

[Majeed]

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. 

[Princy]

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

[Majeed]

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

[Gambit]

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

[Princy]

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

[Asutosh]

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

[Asutosh]

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

[Asutosh]

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

[Kostadin]

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

 

 

[Spiros]

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

[Kostadin]

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

 

 

[SABITHA]

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
​

[SABITHA]

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

[Kostadin]

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

 

 

[KFS]

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.

[Kostadin]

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

 

 

[SABITHA]

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.

[Kostadin]

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

 

 

[SABITHA]

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

[Kostadin]

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