Gridview Hirarchy Exporting to Excel

[Prasad]

       gviewFormBlok.PreRender += new EventHandler(gviewFormBlok_PreRender);
       gviewFormBlok.ExportSettings.ExportOnlyData = true;
       gviewFormBlok.HeaderStyle.Wrap = false;
       gviewFormBlok.ExportSettings.OpenInNewWindow = true;
       gviewFormBlok.MasterTableView.ExportToExcel();
protected void gviewFormBlok_PreRender(object sender, EventArgs e)
   {

           for (int i = 0; i <= gviewFormBlok.MasterTableView.Items.Count - 1; i++)
           {
               gviewFormBlok.MasterTableView.Items[i].Expanded = true;

               for (int j = 0; j <= gviewFormBlok.MasterTableView.Items[i].ChildItem.NestedTableViews[0].Items.Count - 1; j++)
               {
                   gviewFormBlok.MasterTableView.Items[i].ChildItem.NestedTableViews[0].Items[j].Expanded = true;
                   gviewFormBlok.MasterTableView.Items[i].ChildItem.NestedTableViews[0].Items[j]["ExpandColumn"].Visible = false;
                   for (int K = 0; K <= gviewFormBlok.MasterTableView.Items[i].ChildItem.NestedTableViews[0].Items[j].ChildItem.NestedTableViews[0].Items.Count - 1; K++)
                   {
                       gviewFormBlok.MasterTableView.Items[i].ChildItem.NestedTableViews[0].Items[j].ChildItem.NestedTableViews[0].Items[K].Expanded = true;
                       gviewFormBlok.MasterTableView.Items[i].ChildItem.NestedTableViews[0].Items[j].ChildItem.NestedTableViews[0].Items[K]["ExpandColumn"].Visible = false;
                   }
               }

           }
       }

1) I've resolved the issue displaying hirarchy button simbles in exported excel sheet but it is showing the data inproper way means user doesn't understand columns with respected values like attachment #1 (ExportToExcel).

To generate above sheet I'm using above block code

2)I've done some more R&D on this finally I got similar one and it is displaying data properly with user understanding but problem is it is showing only 2 hirarchys instead of 4 hirarchys.

For this I used below code and I got the attachment2 (ExportToExcel_LooksGood)

gviewFormBlok.MasterTableView.HierarchyDefaultExpanded = true;
       gviewFormBlok.ExportSettings.ExportOnlyData = true;
       gviewFormBlok.ExportSettings.IgnorePaging = true;
       gviewFormBlok.MasterTableView.PageSize = 10000;
       gviewFormBlok.MasterTableView.Rebind();
       //add some big enough number;
       gviewFormBlok.ExportSettings.OpenInNewWindow = true;
       (gviewFormBlok.MasterTableView.GetItems(GridItemType.Header)[0] as GridHeaderItem)["ExpandColumn"].Visible = false;
       foreach (GridDataItem dataItem in gviewFormBlok.MasterTableView.Items)
       {
           dataItem["ExpandColumn"].Style["display"] = "none";
           dataItem["ExpandColumn"].Visible = false;
           foreach (GridDataItem dataItem1 in gviewFormBlok.MasterTableView.Items[dataItem.ItemIndex].ChildItem.NestedTableViews[0].Items)
           {
               dataItem1["ExpandColumn"].Style["display"] = "none";
               dataItem1["ExpandColumn"].Visible = false;
           }
       }
       gviewFormBlok.MasterTableView.ExportToExcel();

Finally my query is how to dispaly attachment1 (ExportToExcel) data using above code.

[Shinu]

Hello Prasad,

Take a look at the sample project attached in this link which exporting with hierarchy.
RadGrid Export to ExcelML *with* Hierarchy Intact.

Thanks,
Shinu.

[Prasad]

Hi Shinu,

This is my gridview source

<%@ Page Title="Specimen Tracking System - Block Tracker" Language="C#" MasterPageFile="~/NSABP.master" AutoEventWireup="true" CodeFile="FormBlockTracker.aspx.cs" Inherits="FormBlockTracker" %>
<%@ Register assembly="Telerik.Web.UI" namespace="Telerik.Web.UI" tagprefix="telerik" %>

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
    <link rel="stylesheet" href="css/default.css" media="screen,projection" type="text/css" />
<link rel="stylesheet" href="css/lightbox.css" media="screen,projection" type="text/css" />

<!-- JavaScript -->
<script type="text/javascript" src="scripts/prototype.js"></script>
<script type="text/javascript" src="scripts/lightbox.js"></script>
   <script language="javascript" type="text/javascript">
       changeBackground('A2');
       function CallPrint(strprint) {
           if (strprint != 'No Records Found') {
               getPrint(strprint);
           }
           else {

               window.location.reload(true);
           }
       }


</script>
<script type="text/javascript">
    function onRequestStart(sender, args) {
        if (args.get_eventTarget().indexOf("ExportToExcelButton") >= 0) {
            args.set_enableAjax(false);
        }
    }
    function Showalert(message) {
        if (message) {
            alert(message);
            return;
        }
    }

</script>

    <table width="760" border="0" cellpadding="0" cellspacing="0" bgcolor="#FFFFFF">
               <tr>
                <td height="5"></td>
              </tr>
              <tr>
                <td height="23" align="right">

         <table width="160" border="0" cellspacing="0" cellpadding="0">
                      <tr>

                      </tr>
                    </table>
                </td>
              </tr>
              <tr>
                <td align="left" style="border: solid 1px #9ce3ff; padding-left:5px;">
   <!-- Edit Table Start Here -->
                <table width="750" border="0" cellspacing="0" cellpadding="0">
                  <tr>
                    <td height="5"></td>
                  </tr>
                  <tr>
                    <td align="left" valign="top">
                    <h3><img src="images/SpecimenTracking-title.gif" alt="Specimen Tracking" width="315" height="34" /></h3>                    </td>
                  </tr>
                  <tr>
                    <td height="5"></td>
                  </tr>
                  <tr><td align="right"><asp:UpdatePanel ID="UpdatePanel2" runat="server">
                   <ContentTemplate>
                       <asp:Label ID="lblCount" runat="server" Text="Count"
    CssClass="count2" Visible="False"></asp:Label>
                   </ContentTemplate>
               </asp:UpdatePanel> </td></tr>

                  <tr>
                    <td  align="left" valign="top">
                   <div style="width:750px; height:370px; overflow:auto; border-bottom: solid 1px #000;">

                   <%--  <telerik:RadAjaxPanel ID="RadAjaxPanel1" runat="server" LoadingPanelID="RadAjaxLoadingPanel1">--%>
                    <asp:UpdatePanel ID="UpdatePanel1" runat="server">
                        <ContentTemplate>
                        <telerik:RadAjaxManager ID="RadAjaxManager1" runat="server">
        <ClientEvents OnRequestStart="onRequestStart" />
        <AjaxSettings>
            <telerik:AjaxSetting AjaxControlID="gviewTracker">
                <UpdatedControls>
                    <telerik:AjaxUpdatedControl ControlID="gviewTracker" />
                </UpdatedControls>
            </telerik:AjaxSetting>
        </AjaxSettings>
    </telerik:RadAjaxManager>
                            <telerik:RadGrid ID="gviewFormBlok" runat="server" AllowScroll="True"
                                AllowSorting="True" GridLines="None" Width="100%"

        AutoGenerateColumns="False" Skin="Office2007"

                               EnableLinqExpressions="False"
                                Font-Bold="False" Font-Italic="False" Font-Overline="False"
                                 Font-Underline="False"
                                AllowFilteringByColumn="True"

                                ondetailtabledatabind="gviewFormBlok_DetailTableDataBind"
                                onitemcommand="gviewFormBlok_ItemCommand" onneeddatasource="gviewFormBlok_NeedDataSource"
                                OnItemCreated="gviewFormBlok_ItemCreated"
                                onitemdatabound="gviewFormBlok_ItemDataBound"


                                >
                                <HeaderContextMenu>
                    <CollapseAnimation Duration="200" Type="OutQuint" />
                </HeaderContextMenu>
           <ExportSettings IgnorePaging="true" OpenInNewWindow="true" ExportOnlyData="true" Excel-Format="HTML">
</ExportSettings>

<MasterTableView TableLayout="Fixed" Name="BlockMain" DataKeyNames="Form_Block_ID" Font-Bold="False" Font-Italic="False" Font-Overline="False"
                                    Font-Strikeout="False" Font-Underline="False" AllowNaturalSort="False"
                                    OverrideDataSourceControlSorting="True" HierarchyLoadMode="ServerOnDemand"  >
                                    <DetailTables >



                <telerik:GridTableView runat="server" DataKeyNames="T9_StudyNumber" Name="PanDetails" HierarchyLoadMode="ServerOnDemand"
                    AllowPaging="False" BackColor="#fffeee" >
                    <DetailTables >
                    <telerik:GridTableView runat="server" DataKeyNames="Submission_ID" Name="BlockDetails" HierarchyLoadMode="ServerOnDemand" AllowPaging="False" BackColor="#fffeee" >

                    <DetailTables>
                <telerik:GridTableView runat="server" DataKeyNames="T21_Form_Block_PAN_ID" Name="NewBlockDetails" HierarchyLoadMode="ServerOnDemand"
                    AllowPaging="False" BackColor="#fffeee" >

                     <columns>
                      <%-- <telerik:GridTemplateColumn HeaderText="Edit" AllowFiltering="false">
                        <HeaderStyle Width="30px" Font-Bold ="true"/>
                            <ItemTemplate>
                                <asp:ImageButton ID="imgbtnSubmissionEdit" runat="server"
                                   ImageUrl= "~/Images/edit.png" ToolTip="Activated" CommandName="BlockDetailsUpdate"  />
                            </ItemTemplate>
                        </telerik:GridTemplateColumn>--%>

                                     <telerik:GridBoundColumn DataField="T21_Form_Block_PAN_ID"  HeaderText="T21_Form_Block_PAN_ID" Visible="false"  AllowFiltering="false" >
                <HeaderStyle Width="4px"/>
                    </telerik:GridBoundColumn>
                   <%-- <telerik:GridBoundColumn DataField="Pathology Accession Number" HeaderText="Pathology Accession Number"    ItemStyle-Wrap ="true"  >
                <HeaderStyle Width="225px"  Wrap = "true" />
                <ItemStyle Wrap="True" />
           </telerik:GridBoundColumn>--%>
                    <telerik:GridBoundColumn DataField="Pathology Accession Number" HeaderText="DIT Pathology Accession Number"    ItemStyle-Wrap ="true" AllowFiltering="false" Visible="false"  >
                <HeaderStyle Width="225px"  Wrap = "true"  />
                <ItemStyle Wrap="True" />
           </telerik:GridBoundColumn>
                    <telerik:GridBoundColumn DataField="Block ID" HeaderText="Block ID"    ItemStyle-Wrap ="true"  AllowFiltering="false">
                <HeaderStyle Width="225px"  Wrap = "true" />
                <ItemStyle Wrap="True" />
           </telerik:GridBoundColumn>
                     <telerik:GridBoundColumn DataField="Re_Embed" HeaderText="Re Embed"    ItemStyle-Wrap ="true"  AllowFiltering="false">
                <HeaderStyle Width="225px"  Wrap = "true" />
                <ItemStyle Wrap="True" />
           </telerik:GridBoundColumn>
           <telerik:GridBoundColumn DataField="Re_process" HeaderText="Re Processs"    ItemStyle-Wrap ="true"  AllowFiltering="false">
                <HeaderStyle Width="225px"  Wrap = "true" />
                <ItemStyle Wrap="True" />
           </telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="Type_Of_Stain" HeaderText="Type Of Stain"    ItemStyle-Wrap ="true" AllowFiltering="false">
                <HeaderStyle Width="475px"  Wrap = "true" />
                <ItemStyle Wrap="True" Width ="1050px"/>
           </telerik:GridBoundColumn>
           <telerik:GridBoundColumn DataField="ER_Status" HeaderText="ER Status"    ItemStyle-Wrap ="true"  AllowFiltering="false">
                <HeaderStyle Width="225px"  Wrap = "true" />
                <ItemStyle Wrap="True" />
           </telerik:GridBoundColumn>

           <telerik:GridBoundColumn DataField="USS_Cut" HeaderText="USS Cut"    ItemStyle-Wrap ="true"  AllowFiltering="false">
                <HeaderStyle Width="225px"  Wrap = "true" />
                <ItemStyle Wrap="True" />
           </telerik:GridBoundColumn>
           <telerik:GridBoundColumn DataField="TMA" HeaderText="TMA"    ItemStyle-Wrap ="true"  AllowFiltering="false">
                <HeaderStyle Width="225px"  Wrap = "true" />
                <ItemStyle Wrap="True" />
           </telerik:GridBoundColumn>
           <telerik:GridBoundColumn DataField="mm_core" HeaderText="MM Core"    ItemStyle-Wrap ="true"  AllowFiltering="false">
                <HeaderStyle Width="225px"  Wrap = "true" />
                <ItemStyle Wrap="True" />
           </telerik:GridBoundColumn>
           <telerik:GridBoundColumn DataField="Extraction_Process" HeaderText="Extraction Process"    ItemStyle-Wrap ="true"  AllowFiltering="false">
                <HeaderStyle Width="225px"  Wrap = "true" />
                <ItemStyle Wrap="True" />
           </telerik:GridBoundColumn>

           <telerik:GridBoundColumn DataField="Pathology_Note" HeaderText="Pathology Note"    ItemStyle-Wrap ="true"  AllowFiltering="false">
                <HeaderStyle Width="225px"  Wrap = "true" />
                <ItemStyle Wrap="True" />
           </telerik:GridBoundColumn>
           <telerik:GridBoundColumn DataField="QNST" HeaderText="QNST"    ItemStyle-Wrap ="true"  AllowFiltering="false">
                <HeaderStyle Width="225px"  Wrap = "true" />
                <ItemStyle Wrap="True" />
           </telerik:GridBoundColumn>
             <telerik:GridBoundColumn DataField="Return Date" HeaderText="Return Date"   AllowFiltering="false">
                <HeaderStyle Width="150px"   />
           </telerik:GridBoundColumn>
           </columns>
            </telerik:GridTableView>
                        </DetailTables>

                                    <columns>
                                   <%-- <telerik:GridBoundColumn DataField="ID"  HeaderText="ID" Visible="false"   >
                <HeaderStyle Width="4px"/>
                           </telerik:GridBoundColumn>--%>


                                     <telerik:GridBoundColumn DataField="Submission_ID"  HeaderText="T9_Form_Block_ID" Visible="false"  AllowFiltering="false" >
                <HeaderStyle Width="4px"/>
                           </telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="Pathology Accession Number" HeaderText="Pathology Accession Number"    ItemStyle-Wrap ="true"  AllowFiltering="false">
                <HeaderStyle Width="225px"  Wrap = "true" />
                <ItemStyle Wrap="True" />
           </telerik:GridBoundColumn>

            <telerik:GridBoundColumn DataField="Block Type" HeaderText="Block Type" AllowFiltering="false" >
                <HeaderStyle Width="225px" />
           </telerik:GridBoundColumn>
            <%--<telerik:GridBoundColumn DataField="Number of Blocks" HeaderText="Number of Blocks"   >--%>
            <telerik:GridBoundColumn DataField="Number_OF_Blocks" HeaderText="# of Samples" AllowFiltering="false">
                <HeaderStyle Width="225px"   />
           </telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="Cores" HeaderText="Cores"   AllowFiltering="false">
                <HeaderStyle Width="225px"   />
           </telerik:GridBoundColumn>
           <telerik:GridBoundColumn DataField="Unstained" HeaderText="Unstained"  AllowFiltering="false" >
                <HeaderStyle Width="225px"   />
           </telerik:GridBoundColumn>

            <telerik:GridBoundColumn DataField="Speciment_Node_ID" HeaderText="Specimen_Node_ID" Visible="false" AllowFiltering="false">
                <HeaderStyle Width="225px"   />
           </telerik:GridBoundColumn>

                                     </columns>
                                     </telerik:GridTableView>


            </DetailTables>
            <Columns>


            <telerik:GridBoundColumn DataField="T9_StudyNumber" HeaderText="StudyNumber" Visible="false" AllowFiltering="false"
           FilterControlWidth ="40px"  ><HeaderStyle Width="70px"   />
            </telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="Lab Number" HeaderText="Lab Number" AllowFiltering="false"
           FilterControlWidth ="40px"  ><HeaderStyle Width="70px"   />

           </telerik:GridBoundColumn>
           <telerik:GridBoundColumn DataField="Block Submission Type" HeaderText="Block Submission Type" AllowFiltering="false" >
                <HeaderStyle Width="225px" />
           </telerik:GridBoundColumn>
           <telerik:GridBoundColumn DataField="Date Specimen Received By NSABP Pathology Lab" HeaderText="Date Specimen Received By NSABP Pathology Lab" DataFormatString="{0:D}" FilterControlWidth ="60px" AllowFiltering="false" ><HeaderStyle Width="90px"   />
            </telerik:GridBoundColumn>

            <telerik:GridBoundColumn DataField="Return Request" AllowFiltering="false" HeaderText="Return Request" ><HeaderStyle Width="40px"   />

           </telerik:GridBoundColumn>
           <telerik:GridBoundColumn DataField="Submission_ID" AllowFiltering="false" HeaderText="Submission_ID" Visible="false" ><HeaderStyle Width="40px"   />

           </telerik:GridBoundColumn>
<telerik:GridBoundColumn DataField="Return Address"
HeaderText="Return Address" FilterControlWidth ="60px" AllowFiltering="false" ><HeaderStyle Width="90px"   />

           </telerik:GridBoundColumn>
            </Columns>
             </telerik:GridTableView>
            </DetailTables>
                                    <ExpandCollapseColumn Visible="True">
                                    </ExpandCollapseColumn>
            <Columns>
                                    <telerik:GridBoundColumn DataField="Form_Block_ID" HeaderText="Form_Block_ID" Visible="false"  >

           </telerik:GridBoundColumn>

           <telerik:GridBoundColumn DataField="Protocol" HeaderText="Protocol" FilterControlWidth ="30px"><HeaderStyle Width="60px"   />

           </telerik:GridBoundColumn>
           <telerik:GridBoundColumn DataField="Institute" HeaderText="Institute" FilterControlWidth ="30px"> <HeaderStyle Width="60px"   />

           </telerik:GridBoundColumn>
           <telerik:GridBoundColumn DataField="Study Number" HeaderText="Study Number"
           FilterControlWidth ="40px"  ><HeaderStyle Width="70px"   />

           </telerik:GridBoundColumn>

           <telerik:GridBoundColumn DataField="Patient Initials" HeaderText="Patient Initials" FilterControlWidth ="30px" Visible="false" ><HeaderStyle Width="60px"   />

           </telerik:GridBoundColumn>
           <telerik:GridBoundColumn DataField="Date Specimen Collected" HeaderText=" Date Specimen Collected"  DataFormatString="{0:D}" FilterControlWidth ="60px" > <HeaderStyle Width="90px"   />

                    </telerik:GridBoundColumn>

                      <telerik:GridBoundColumn DataField="Date Specimen Received By DIT" HeaderText="Date Specimen Received By DIT" DataFormatString="{0:D}" FilterControlWidth ="60px" ><HeaderStyle Width="90px"   />
            </telerik:GridBoundColumn>

               <telerik:GridBoundColumn DataField="Date Specimen Sent to NSABP Pathology Lab" HeaderText="Date Specimen Sent to NSABP Pathology Lab" DataFormatString="{0:D}" FilterControlWidth ="60px" ><HeaderStyle Width="90px"   />
            </telerik:GridBoundColumn>



                                   </Columns>
                                     <ExpandCollapseColumn Visible="True">
                    </ExpandCollapseColumn>
                </MasterTableView>
                <ClientSettings>
                <Resizing AllowColumnResize="True" />
                </ClientSettings>
                <FilterMenu>
                    <CollapseAnimation Duration="200" Type="OutQuint" />
                </FilterMenu>
                                   <ClientSettings EnableRowHoverStyle="true" >
                <Selecting AllowRowSelect="true" />
                 <Resizing EnableRealTimeResize ="true" AllowColumnResize="True" ClipCellContentOnResize="false" ResizeGridOnColumnResize="true" />

            </ClientSettings>
                                   </telerik:RadGrid>

                        </ContentTemplate>
                    </asp:UpdatePanel>
                  <%--  </telerik:RadAjaxPanel>--%>



                          </div>
                      </td>
                  </tr>
                 <tr>
                        <td colspan="4" align="center" class="heading">
                        <table width="200" border="0" cellspacing="3" cellpadding="0">
                          <tr>
                                                            <td align="center">
                                <asp:UpdatePanel ID="UpdatePanel10" runat="server">
                                            <ContentTemplate>
                                                <asp:ImageButton ID="btnPrint" runat="server"
                                                    ImageUrl="~/Images/printall.gif" Visible="False" />
                                            </ContentTemplate>
                                        </asp:UpdatePanel>

                                </td>
                                 <td>
                               <%--  <asp:UpdatePanel ID="UpdatePanel3" runat="server">
                                            <ContentTemplate>--%>
                                                <asp:ImageButton ID="btnExport" runat="server"
                                                    ImageUrl="~/Images/ExportExcel-btn.gif" onclick="btnExport_Click" />
                                           <%--  </ContentTemplate>
                                        </asp:UpdatePanel>--%>
                                </td>
                          </tr>

                        </table></td>
                      </tr>
                </table>
   <!-- Edit Table End Here -->                </td>
                </tr>

                </table>
</asp:Content>

In above source I have 3 DetailTables but I'm getting Parent and one child for confirmation see above code and latm know what is the problem.