[Solved] RadGrid ExportToExcel with DetailTable Hidden/Invisible

[Wai Wai Chan]

Hi there,

Below is my fine-working ExportToExcel function.

Protected Sub btnExportToExcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExportToExcel.Click

        RadGrid1.MasterTableView.DetailTables(0).Visible = False ' purposely make DetailTable invisible but it shows us BLANK row in Excel..
        RadGrid1.ExportSettings.ExportOnlyData = True
        RadGrid1.ExportSettings.IgnorePaging = True
        RadGrid1.ExportSettings.OpenInNewWindow = True
        RadGrid1.MasterTableView.ExportToExcel()

End Sub

I am using a RadGrid attached with a DetailTable.
The only problem I am facing is when it come to Excel exporting, and I was trying to make the DetailTable not visible in Excel report.. but it was like a BLANK row appears alternately in between every data row. (refer to attached screenshot excel.jpg)

It causing problems when we doing data analyzing and formating. Would it be any solution for this issue?

Thank you very much. :)
waiwai

[Sebastian]

Hello Wai,

I tried to reproduce the issue you depicted using the latest version 2010.1.519 of RadControls for ASP.NET AJAX but unfortunately to no avail. I attached my test case to this forum post for your reference. Note that the detail tables are not present in the exported Excel file and there are no empty lines in the sheet.

Can you please verify that on your end? Note that in this case you do not need to hide the detail tables explicitly in the code-behind. Let me know if I am leaving something out.

Best regards,
Sebastian
the Telerik team

[Wai Wai Chan]

Hi Sebastian,

Thanks for your prompt response! :D

In fact I am using Client hierarchy load mode for the DetailGrid.

<MasterTableView DataSourceID="SqlDataSource1" DataKeyNames="CustomerID" AllowMultiColumnSorting="True" HierarchyLoadMode="Client">

Data was pre-loaded to the DetailTable before It has been expanded.
Is that any way to deal with this instead of change to Server hierarchy load mode?

Thanks again :)
waiwai

[Sebastian]

Hello Wai,

Thank you for the additional information - I was able to replicate the discrepancy using HierarchyLoadMode = Client. It seems that with html Excel export hidden tr elements with tables inside them are interpreted in this way as we do not include any additional markup (you can examine the exported html to verify that).

To avoid the unwanted effect, simply set HierarchyLoadMode = ServerBind or ServerOnDemand prior to the call to the ExportToExcel() method, i.e.:

Protected Sub exportBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles exportBtn.Click
    RadGrid1.ExportSettings.ExportOnlyData = True
    RadGrid1.ExportSettings.IgnorePaging = True
    RadGrid1.ExportSettings.OpenInNewWindow = True
    RadGrid1.MasterTableView.HierarchyLoadMode = GridChildLoadMode.ServerBind
    RadGrid1.MasterTableView.ExportToExcel()
End Sub

Note that this will not affect the grid client hierarchy load in the browser because this setting will be applied for the exported Excel file only.

Best regards,
Sebastian
the Telerik team

[Wai Wai Chan]

Hi Sebastian,

It is just perfect!
Appreciate your concern on this case and thanks for your help :)

Nice Day.
waiwai

[Peter]

This was really good advise... I was also struggling with empty lines for detail tables in exported Excel document, when I came accros this thread... 

  RadGridResults.MasterTableView.HierarchyLoadMode = GridChildLoadMode.ServerOnDemand;

Thanks,

Peter