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

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

25 Answers 312 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Tom
Top achievements
Rank 1
Tom asked on 01 Nov 2013, 07:02 PM
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

25 Answers, 1 is accepted

Sort by
0
Kostadin
Telerik team
answered on 06 Nov 2013, 09:50 AM
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.
0
Tom
Top achievements
Rank 1
answered on 16 Nov 2013, 10:43 PM
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
0
Kostadin
Telerik team
answered on 20 Nov 2013, 01:26 PM
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.
0
Gambit
Top achievements
Rank 1
answered on 05 Mar 2014, 10:21 AM
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,
0
Kostadin
Telerik team
answered on 10 Mar 2014, 08:47 AM
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.

0
Majeed
Top achievements
Rank 1
answered on 13 May 2014, 07:31 AM
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. 
0
Princy
Top achievements
Rank 1
answered on 13 May 2014, 08:56 AM
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
0
Majeed
Top achievements
Rank 1
answered on 13 May 2014, 09:44 AM
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
0
Gambit
Top achievements
Rank 1
answered on 13 May 2014, 10:48 AM
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>";   
       }

0
Princy
Top achievements
Rank 1
answered on 14 May 2014, 05:37 AM
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
0
Asutosh
Top achievements
Rank 1
answered on 11 Jun 2014, 07:07 AM
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
0
Asutosh
Top achievements
Rank 1
answered on 11 Jun 2014, 07:11 AM
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
0
Asutosh
Top achievements
Rank 1
answered on 13 Jun 2014, 10:00 AM
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


0
Kostadin
Telerik team
answered on 18 Jun 2014, 07:05 AM
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.

 
0
Spiros
Top achievements
Rank 1
answered on 18 Sep 2014, 05:56 AM
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
0
Kostadin
Telerik team
answered on 22 Sep 2014, 02:32 PM
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.

 
0
SABITHA
Top achievements
Rank 1
answered on 03 Feb 2015, 07:00 AM
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
0
SABITHA
Top achievements
Rank 1
answered on 03 Feb 2015, 07:50 AM
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. 
0
Kostadin
Telerik team
answered on 05 Feb 2015, 10:07 AM
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.

 
0
KFS
Top achievements
Rank 1
answered on 12 Feb 2015, 10:21 AM
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.
0
Kostadin
Telerik team
answered on 16 Feb 2015, 01:55 PM
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.

 
0
SABITHA
Top achievements
Rank 1
answered on 20 Feb 2015, 10:32 AM
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.
0
Kostadin
Telerik team
answered on 24 Feb 2015, 02:55 PM
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.

 
0
SABITHA
Top achievements
Rank 1
answered on 25 Feb 2015, 07:31 AM
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";
//}
}
0
Kostadin
Telerik team
answered on 27 Feb 2015, 03:35 PM
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.

 
Tags
Grid
Asked by
Tom
Top achievements
Rank 1
Answers by
Kostadin
Telerik team
Tom
Top achievements
Rank 1
Gambit
Top achievements
Rank 1
Majeed
Top achievements
Rank 1
Princy
Top achievements
Rank 1
Asutosh
Top achievements
Rank 1
Spiros
Top achievements
Rank 1
SABITHA
Top achievements
Rank 1
KFS
Top achievements
Rank 1
Share this question
or