Exporting to Excel hierarchical grid : Empty Detail table rows?

[Daniel]

Hi

I searched  to find something about my problem but all I found does not seen to work :<

I have a hierarchical grid with the following definition. The grid is filled in ...NeedsDatasource and the detail table in the ...DetailDataBind and the UI result is very good

I use the following code to export to excel

==> But as result (see in the attached file), the detail lines in excel are are empty :(
I cannot figure why it is not working => I suppose that for some reason, the export cannot get content of the detail table...
 
Thanks for helping

RadGrid1.ExportSettings.FileName = "ExcelExport";
        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.GetColumn("Link").Visible = false;
          
        RadGrid1.MasterTableView.ExportToExcel();



<telerik:RadGrid ID="RadGrid1" runat="server" Width="90%" ShowStatusBar="true" AutoGenerateColumns="False"
                PageSize="10" AllowSorting="True" AllowMultiRowSelection="True" AllowPaging="True"
                OnDetailTableDataBind="RadGrid1_DetailTableDataBind" OnNeedDataSource="RadGrid1_NeedDataSource"
                OnPreRender="RadGrid1_PreRender">
                <PagerStyle Mode="NumericPages"></PagerStyle>
                <MasterTableView Width="100%" DataKeyNames="DocId" AllowMultiColumnSorting="True" AllowPaging="true" UseAllDataFields="true">
                    <DetailTables>
                        <telerik:GridTableView DataKeyNames="LineNumber" Name="Lines" Width="100%">
                            <Columns>
                                <telerik:GridBoundColumn SortExpression="LineNumber" HeaderText="Invoice line #" HeaderButtonType="TextButton"
                                    DataField="LineNumber">
                                </telerik:GridBoundColumn>
                                <telerik:GridBoundColumn SortExpression="Description" HeaderText="Description" HeaderButtonType="TextButton"
                                    DataField="Description">
                                </telerik:GridBoundColumn>
                                <telerik:GridBoundColumn SortExpression="Quantity" HeaderText="Quantity" HeaderButtonType="TextButton"
                                    DataField="Quantity" DataFormatString="{0:0.00}">
                                </telerik:GridBoundColumn>
                                <telerik:GridBoundColumn SortExpression="SKU" HeaderText="SKU" HeaderButtonType="TextButton"
                                    DataField="SKU">
                                </telerik:GridBoundColumn>
                                <telerik:GridBoundColumn SortExpression="UnitPrice" HeaderText="Unit price" HeaderButtonType="TextButton"
                                    DataField="UnitPrice" DataFormatString="{0:0.00}">
                                </telerik:GridBoundColumn>
                                <telerik:GridBoundColumn SortExpression="TotalAmount" HeaderText="Amount" HeaderButtonType="TextButton"
                                    DataField="TotalAmount" DataFormatString="{0:0.00}">
                                </telerik:GridBoundColumn>
                                <telerik:GridBoundColumn SortExpression="Banner" HeaderText="Banner" HeaderButtonType="TextButton"
                                    DataField="Banner">
                                </telerik:GridBoundColumn>
                                <telerik:GridBoundColumn SortExpression="BDF" HeaderText="BDF" HeaderButtonType="TextButton"
                                    DataField="BDF" DataFormatString="{0:0.00}">
                                </telerik:GridBoundColumn>
                               <telerik:GridBoundColumn SortExpression="Q1083" HeaderText="Q1083" HeaderButtonType="TextButton"
                                    DataField="Q1083" DataFormatString="{0:0.00}">
                                </telerik:GridBoundColumn>
                               <telerik:GridBoundColumn SortExpression="Q1084" HeaderText="Q1084" HeaderButtonType="TextButton"
                                    DataField="Q1084" DataFormatString="{0:0.00}">
                                </telerik:GridBoundColumn>

                            </Columns>
                        </telerik:GridTableView>
                    </DetailTables>
                    <Columns>
                        <telerik:GridClientSelectColumn UniqueName="ClientSelectColumn1">
                        </telerik:GridClientSelectColumn>
                        <telerik:GridBoundColumn SortExpression="DocumentNumber" HeaderText="Document number" HeaderButtonType="TextButton"
                            DataField="DocumentNumber">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn SortExpression="ReferenceNumber" HeaderText="Reference number" HeaderButtonType="TextButton"
                            DataField="ReferenceNumber">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn SortExpression="WeekEnding" HeaderText="Week ending" HeaderButtonType="TextButton"
                            DataField="WeekEnding" DataFormatString="{0:yyyy-MM-dd}">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn SortExpression="StartPeriod" HeaderText="Promo start" HeaderButtonType="TextButton"
                            DataField="StartPeriod" DataFormatString="{0:yyyy-MM-dd}">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn SortExpression="EndPeriod" HeaderText="Promo end" HeaderButtonType="TextButton"
                            DataField="EndPeriod" DataFormatString="{0:yyyy-MM-dd}">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn SortExpression="CostDealNumber" HeaderText="Cost Deal number" HeaderButtonType="TextButton"
                            DataField="CostDealNumber">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn SortExpression="TotalAmount" HeaderText="Total Amount" HeaderButtonType="TextButton"
                                    DataField="TotalAmount" DataFormatString="{0:0.00}"></telerik:GridBoundColumn>
                        <telerik:GridHyperLinkColumn HeaderText="Link" ImageUrl="~/Images/lens.png" Target="_blank" UniqueName="Link"
                             DataNavigateUrlFields= "DocId"
                             DataNavigateUrlFormatString= "~/dm_detail.aspx?DM_OBJECT_ID={0}">                            
                        </telerik:GridHyperLinkColumn>
                        <telerik:GridBoundColumn SortExpression="CheckNumber" HeaderText="Check #" HeaderButtonType="TextButton"
                            DataField="CheckNumber">
                        </telerik:GridBoundColumn>                        
                    </Columns>
                </MasterTableView>
                <ClientSettings>
            <Selecting AllowRowSelect="true"></Selecting>
 
        </ClientSettings>
                
            </telerik:RadGrid>

[Daniel]

Tried this morning: Export to PDF retrieves the lines of the detail table => rhis means there is something whrong in the Excel export...

[Daniel]

Setting the ExportSettings.Excel.Format to HTML also works! (detail lines exported)
The problem seems to be in the ExcelML format ... but I read somewhere it should be compatible with hierarchical export ...

[Daniel]

Hello Daniel,

HTML format would work best for complex scenarios with hierarchy and/or grouping. ExcelML format works in a different way and may not work as good with cases like this.
If you'd like to give the ExcelML format another chance, you may try to set the hierarchy load mode to server to see whether this helps.

Regards,
Daniel
Telerik

 

 

[Daniel]

Hi,

I tried with 
RadGrid1.MasterTableView.HierarchyLoadMode = GridChildLoadMode.ServerBind;
RadGrid1.MasterTableView.DetailTables[0].HierarchyLoadMode = GridChildLoadMode.ServerBind;
but it is not better => the detail lines are not inserted into the document (empty lines instead of data)

The other problem for my customer is that the format (when exporting to excel with html) is not really useful:
the customer would like to sort/filter the data by column but the hierarchical data is not really useful for that.
Without going to a totally custom export (i.e. CSV) is it possible to export in the Grid for only the detailed table(s)? 

[Angel Petrov]

Hi Daniel,

Indeed there seems to be a problem when exporting the detail tables contents in ExcelML format. I have already logged it into our system and hope that our dev team will resolve it as quickly as they can. You can monitor our progress on the matter from this link. As a token of gratitude for reporting the problem I have updated your Telerik points.

As for exporting only the detail tables currently such functionality is not supported. However you can log a feature request regarding it. If it proves commonly requested it may be included in a future release.

Regards,
Angel Petrov
Telerik

 

 

[Jorges]

try to do this

 RG_​grid.ExportSettings.FileName = "filename";
            RG_​grid.ExportSettings.IgnorePaging = true;
            RG_​grid.ExportSettings.ExportOnlyData = true;
            RG_​grid.ExportSettings.OpenInNewWindow = true;
            RG_​grid.MasterTableView.UseAllDataFields = true;
            RG_​grid.MasterTableView.DetailTables[0].UseAllDataFields = true; //add this to export all fieds of detailtables

            RG_​grid.ExportSettings.Excel.Format = GridExcelExportFormat.ExcelML;

            RG_​grid.MasterTableView.HierarchyDefaultExpanded = true;
            RG_​grid.MasterTableView.DetailTables[0].HierarchyDefaultExpanded = true;

            RG_​grid.MasterTableView.HierarchyLoadMode = GridChildLoadMode.Client;

            RG_​grid.MasterTableView.DetailTables[0].HierarchyLoadMode = GridChildLoadMode.Client;

            RG_​grid.MasterTableView.ExportToExcel();

 

Best Regards