Alignment problem with hierarchical RadGrid export

[Pankaj Chowdhury]

Hi Telerik Team,

I am facing column alignment issue while exporting the generated RadGrid to Excel/Word. I am using relative width to align the UI (please refer to sample code below and attached 'UI_View.png' file), however when exported to Word/Excel the alignment is getting displaced, detail table columns are not following the master table columns (refer to 'Excel_Export.png' and 'Word_Export.png' files).

Would appreciate if you could come up with an idea on to keep the alignment intact so detail table columns are exactly in the same column of master table when exported.

Thanks,

Pankaj

<telerik:RadGrid ID="rgDTSItems" EnableAJAX="True" EnableAJAXLoadingTemplate="True"
               Skin="Windows7" OnColumnCreated="rgDTSItems_ColumnCreated" OnItemCreated="rgDTSItems_ItemCreated"
               OnItemDataBound="rgDTSItems_ItemDataBound" ShowStatusBar="true" runat="server"
               Width="99.9%" PageSize="10" AllowSorting="True" AllowMultiRowSelection="false"
               AllowPaging="True" GridLines="Horizontal" AutoGenerateColumns="False" OnDetailTableDataBind="rgDTSItems_DetailTableDataBind"
               OnNeedDataSource="rgDTSItems_NeedDataSource" OnItemCommand="rgDTSItems_ItemCommand">
               <GroupPanel Visible="False">
               </GroupPanel>
               <ClientSettings Selecting-AllowRowSelect="true" EnableRowHoverStyle="true">
               </ClientSettings>
               <MasterTableView HierarchyLoadMode="Client" DataKeyNames="RefNo,DeptDiv" DataMember="mcs_main"
                   ShowHeader="true" TableLayout="Fixed" Width="100%" CommandItemDisplay="Bottom"
                   HorizontalAlign="Right">
                   <Columns>
                       <telerik:GridBoundColumn DataField="RefNo" Visible="false">
                       </telerik:GridBoundColumn>
                       <telerik:GridBoundColumn DataField="DeptDiv" HeaderText="Div" HeaderStyle-Width="8%"
                           HeaderStyle-HorizontalAlign="Center" HeaderStyle-Font-Bold="true">
                       </telerik:GridBoundColumn>
                       <telerik:GridTemplateColumn UniqueName="TemplateColumn" HeaderText="Ref No" SortExpression="RefNo"
                           HeaderStyle-Width="8%" HeaderStyle-Font-Bold="true">
                           <ItemTemplate>
                               <asp:Label ID="lblRefNo" runat="server" Text='<%#Eval("Type").ToString().Substring(0, 1) + "-" + Eval("RefNo")%>'>
                               </asp:Label>
                           </ItemTemplate>
                       </telerik:GridTemplateColumn>
                       <telerik:GridBoundColumn DataField="DateDueDoc" UniqueName="DateDueDoc" Display="false">
                       </telerik:GridBoundColumn>
                       <telerik:GridTemplateColumn UniqueName="gtcDocDueDate" HeaderText="Due/Doc Date"
                           SortExpression="DateDueDoc" HeaderStyle-Width="12%" HeaderStyle-Font-Bold="true">
                           <ItemTemplate>
                               <asp:Label ID="lblDateDueDoc" runat="server" Text='<%#Eval("DateDueDoc","{0:d}") + " " + Eval("TimeDueDoc") %> '></asp:Label>
                           </ItemTemplate>
                       </telerik:GridTemplateColumn>
                       <telerik:GridBoundColumn DataField="Sender" HeaderText="From" HeaderStyle-Width="15%"
                           HeaderStyle-Font-Bold="true">
                       </telerik:GridBoundColumn>
                       <telerik:GridBoundColumn DataField="Recipient" HeaderText="Assigned To/Recipient"
                           HeaderStyle-Width="25%" ItemStyle-Wrap="true" HeaderStyle-Font-Bold="true">
                       </telerik:GridBoundColumn>
                       <telerik:GridTemplateColumn UniqueName="gtcSubject" HeaderText="Subject" SortExpression="Subject"
                           HeaderStyle-Width="32%" HeaderStyle-Font-Bold="true">
                           <ItemTemplate>
                               <asp:Label ID="lblSubject" runat="server" Text='<%#Eval("FormattedSubject")%>' Font-Bold="true"></asp:Label>
                               <telerik:RadToolTip ID="rttSubject" runat="server" TargetControlID="lblSubject" Width="200px"
                                   RelativeTo="Element" Position="MiddleRight" EnableShadow="true">
                                   <%#Eval("Subject")%>
                               </telerik:RadToolTip>
                           </ItemTemplate>
                       </telerik:GridTemplateColumn>
                   </Columns>
                   <DetailTables>
                       <telerik:GridTableView DataKeyNames="RefNo,DeptDiv" DataMember="mcs_main" ShowHeader="False"
                           EnableNoRecordsTemplate="false" Width="100%" ShowHeadersWhenNoRecords="false"
                           HorizontalAlign="Right" TableLayout="Fixed">
                           <Columns>
                               <telerik:GridBoundColumn DataField="RefNo" Visible="false">
                               </telerik:GridBoundColumn>
                               <telerik:GridBoundColumn DataField="DeptDiv" HeaderText="Div" HeaderStyle-Width="8%">
                               </telerik:GridBoundColumn>
                               <telerik:GridTemplateColumn UniqueName="gtcRefNo" HeaderText="Ref No" SortExpression="RefNo"
                                   HeaderStyle-Width="8%">
                                   <ItemTemplate>
                                       <asp:Label ID="lblRefNo" runat="server" Text='<%#Eval("Type").ToString().Substring(0, 1) + "-" + Eval("RefNo")%>'>
                                       </asp:Label>
                                   </ItemTemplate>
                               </telerik:GridTemplateColumn>
                               <telerik:GridBoundColumn DataField="DateDueDoc" UniqueName="DateDueDoc" Display="false">
                               </telerik:GridBoundColumn>
                               <telerik:GridTemplateColumn UniqueName="gtcDocDueDate" HeaderText="Due/Doc Date"
                                   SortExpression="DateDueDoc" HeaderStyle-Width="12%">
                                   <ItemTemplate>
                                       <asp:Label ID="lblDateDueDoc" runat="server" Text='<%#Eval("DateDueDoc","{0:d}") + " " + Eval("TimeDueDoc") %> '></asp:Label>
                                   </ItemTemplate>
                               </telerik:GridTemplateColumn>
                               <telerik:GridBoundColumn DataField="Sender" HeaderText="From" HeaderStyle-Width="15%">
                               </telerik:GridBoundColumn>
                               <telerik:GridBoundColumn DataField="Recipient" HeaderText="Assigned To/Recipient"
                                   HeaderStyle-Width="25%" ItemStyle-Wrap="true">
                               </telerik:GridBoundColumn>
                               <telerik:GridTemplateColumn UniqueName="gtcSubject" HeaderText="Subject" SortExpression="Subject"
                                   HeaderStyle-Width="32%" HeaderStyle-Font-Bold="true">
                                   <ItemTemplate>
                                       <asp:Label ID="lblSubject" runat="server" Text='<%#Eval("FormattedSubject")%>' Font-Bold="true"></asp:Label>
                                       <telerik:RadToolTip ID="rttSubject" runat="server" TargetControlID="lblSubject" Width="200px"
                                           RelativeTo="Element" Position="MiddleRight" EnableShadow="true">
                                           <%#Eval("Subject")%>
                                       </telerik:RadToolTip>
                                   </ItemTemplate>
                               </telerik:GridTemplateColumn>
                           </Columns>
                       </telerik:GridTableView>
                   </DetailTables>
                   <CommandItemSettings ShowExportToWordButton="true" ShowExportToExcelButton="true"
                       ShowExportToCsvButton="false" ShowExportToPdfButton="true" ShowAddNewRecordButton="false"
                       ShowRefreshButton="false" />
               </MasterTableView>
               <ClientSettings AllowExpandCollapse="true">
                   <DataBinding CountPropertyName="Count" />
                   <ClientEvents OnRowCreated="RowCreated" OnRowDblClick="RowDblClicked" />
               </ClientSettings>
               <ExportSettings HideStructureColumns="true" IgnorePaging="true" ExportOnlyData="true"
                   FileName="DTS_Items" OpenInNewWindow="true" Pdf-PageHeight="215mm" Pdf-PageWidth="356mm" />
               <FilterMenu EnableTheming="True" Skin="Outlook">
                   <CollapseAnimation Duration="200" Type="OutQuint" />
               </FilterMenu>
               <SelectedItemStyle CssClass="SelectedFolder"></SelectedItemStyle>
           </telerik:RadGrid>

protected void rgDTSItems_ItemCommand(object sender, Telerik.Web.UI.GridCommandEventArgs e)
    {
        if (e.CommandName == Telerik.Web.UI.RadGrid.ExportToExcelCommandName ||
            e.CommandName == Telerik.Web.UI.RadGrid.ExportToWordCommandName ||
            e.CommandName == Telerik.Web.UI.RadGrid.ExportToCsvCommandName ||
            e.CommandName == Telerik.Web.UI.RadGrid.ExportToPdfCommandName)
        {
            ConfigureExport();
        }
        else if (e.CommandName == "Print")
        {
            ConfigureExport();
            ConfigurePrint();
        }
        else if (e.CommandName == "EnablePaging")
        {
            rgDTSItems.MasterTableView.HierarchyLoadMode = GridChildLoadMode.Client;
            rgDTSItems.MasterTableView.HierarchyDefaultExpanded = false;
            rgDTSItems.MasterTableView.DetailTables[0].GetColumn("DateDueDoc").Visible = true;
            EnableDisablePaging(true);
        }
    }

    private void ConfigureExport()
    {
        rgDTSItems.MasterTableView.HierarchyLoadMode = GridChildLoadMode.ServerBind;
        rgDTSItems.MasterTableView.HierarchyDefaultExpanded = true;
        //Since while exporting hidden column DateDueDoc appears therefore hide the column before exporting
        rgDTSItems.MasterTableView.DetailTables[0].GetColumn("DateDueDoc").Visible = false;

    }

    //For printing we need to disable paging and call the PrintGird client function
    private void ConfigurePrint()
    {
        //Allowpaging to false
        EnableDisablePaging(false);

        foreach (GridItem item in rgDTSItems.MasterTableView.GetItems(new GridItemType[] { GridItemType.Pager, GridItemType.FilteringItem }))
            item.Display = false;

        ramHome.ResponseScripts.Add("PrintGrid('" + rgDTSItems.ClientID + "')");
    }

    //Enable/disable paging in case of Print
    private void EnableDisablePaging(bool doEnable)
    {
        rgDTSItems.AllowPaging = doEnable;
        rgDTSItems.Rebind();
    }

[Daniel]

Hello Pankaj,

I'm afraid this approach wont work. In fact there is no easy way to control the allocation of nested tables/cells when exporting to Excel.

You can try the following workaround, however it is not guaranteed that it will work in your scenario:

protected void rgDTSItems_GridExporting(object sender, GridExportingArgs e)
{
    if (e.ExportType == ExportType.Excel)
    {
        int visibleCols = 0;
        foreach (GridColumn col in rgDTSItems.MasterTableView.RenderColumns)
            if (col.Display && col.Visible)
                visibleCols++;
        e.ExportOutput = e.ExportOutput.Replace("colspan=\"" + (visibleCols - 2), "colspan=\"" + (visibleCols - 1));
    }
}

Best regards,
Daniel
the Telerik team

[Pankaj Chowdhury]

Daniel,

Thanks so much for the workaround idea. I did a slight change to get it work for excel export:

protected void rgDTSItems_GridExporting(object sender, GridExportingArgs e)
    {
        if (e.ExportType == ExportType.Excel)
        {
            int visibleCols = 0;
            foreach (GridColumn col in rgDTSItems.MasterTableView.RenderColumns)
                if (col.Display && col.Visible)
                    visibleCols++;
            e.ExportOutput = e.ExportOutput.Replace("colspan=\"" + (visibleCols - 1), "colspan=\"" + visibleCols);

        }
    }

However, in case of word and pdf this is not working. Any input on Word/Pdf export?

Regards,

Pankaj

[Pankaj Chowdhury]

Daniel,

FYI, in case of pdf and word export following code has been used:

protected void rgDTSItems_GridExporting(object sender, GridExportingArgs e)
    {
        if (e.ExportType == ExportType.Excel || e.ExportType == ExportType.Pdf || e.ExportType == ExportType.Word)
        {
            int visibleCols = 0;
            foreach (GridColumn col in rgDTSItems.MasterTableView.RenderColumns)
                if (col.Display && col.Visible)
                    visibleCols++;
            e.ExportOutput = e.ExportOutput.Replace("colspan=\"" + (visibleCols - 1), "colspan=\"" + visibleCols);

        }
    }

Thanks,

Pankaj

[Daniel]

Hello Pankaj,

I'm glad to hear that the suggested workaround did the trick. Unfortunately there is no convenient way to make the exported file to look as in the browser in such scenarios.
I recommend that you examine the contents of the exported file - this could you give you some ideas as to what can be changed in order to get a better presentation.

Regards,
Daniel
the Telerik team

[Pankaj Chowdhury]

Daniel,

Thanks for your inputs. In fact after changing the colspan the html was looking good, however interestingly when exported to word the alignment was not proper as it was supposed to be. And for PDF export the changing scope is very little since it generates encoded chracters. Anyway, I have applied few other workarounds and got it done:

1. For word export, I am altering the generated html using Html Agility Pack, stripping the detail table definition and adding the detail table rows to the master table so the whole content comes within one table and there is no sub table.
2. In case of PDF I have added an additional empty string column only to the master table. This new column's display is set to false, however when exported display will be set to true and thus it counts an additional column and colspan is 6 instead of 5.

Thanks once again for your support.

Regards,

Pankaj

[Maulik]

hello

when i was trying maths format export as a word using software then no formatting in word

every equation like this how solve this problem

untitled actual file

but i want untitled7 when export as word using asp.net