Flattening Grid Data with Export to Excel

[Samir Vaidya]

I found this article which is a few years old which partially describes how to flatten data in the RadGrid:

http://www.telerik.com/community/forums/aspnet-ajax/grid/change-grid-structure-data-on-export-to-excel.aspx

However, based on what I can tell from the forum posting/thread, their implementation differs from my RadGrid structure.  I am specifically using the GridGroupBy fields rather than simply hiding columns in my RadGrid.

I essentially want to turn all of my GridGroupByFields into columns as well with their data aligned side by side with the detail columns when I export to Excel.  Is there some built in functionality that can accomplish this?  If not, is there a code sample someone can provide me with in order to accomplish this?

Below is my RadGrid code:

<telerik:radgrid id="gvStudentClassSchedule" runat="server"
                cellspacing="0" gridlines="None" skin="Outlook" showfooter="True" datasourceid="sqlDSClassRoster">
                <exportsettings excel-format="ExcelML" exportonlydata="true" ignorepaging="true" openinnewwindow="true" hidestructurecolumns="true" />
                <mastertableview autogeneratecolumns="False" datakeynames="CourseID" showgroupfooter="true" allowfilteringbycolumn="true" allowpaging="true" commanditemdisplay="Top">
                    <commanditemsettings showexporttoexcelbutton="true" showexporttocsvbutton="true" showaddnewrecordbutton="false" />
                    <rowindicatorcolumn filtercontrolalttext="Filter RowIndicator column">
                    </rowindicatorcolumn>
                    <expandcollapsecolumn filtercontrolalttext="Filter ExpandColumn column">
                    </expandcollapsecolumn>
                      <groupbyexpressions>
                        <telerik:gridgroupbyexpression>
                            <selectfields>
                                <telerik:gridgroupbyfield fieldalias="Name" fieldname="Name" />
                                <telerik:gridgroupbyfield fieldalias="Age" fieldname="Age" />
                                <telerik:gridgroupbyfield fieldname="Address1" fieldalias="Address" />
                                <telerik:gridgroupbyfield fieldname="City" />
                                <telerik:gridgroupbyfield fieldname="State" />
                                <telerik:gridgroupbyfield fieldname="Zip" />
                                <telerik:gridgroupbyfield fieldname="HomePhone" />
                                <telerik:gridgroupbyfield fieldname="CellPhone" />
                                <telerik:gridgroupbyfield fieldname="EmailAddress" />
                                <telerik:gridgroupbyfield fieldname="InvoiceNumber" fieldalias="InvoiceNumber" />
                            </selectfields>
                            <groupbyfields>
                                <telerik:gridgroupbyfield fieldalias="StudentID" fieldname="PersonID" sortorder="None" />
                                <telerik:gridgroupbyfield fieldname="Name" fieldalias="Name" sortorder="Ascending" />
                            </groupbyfields>
                        </telerik:gridgroupbyexpression>
                    </groupbyexpressions>
                    <columns>
                        <telerik:gridboundcolumn datafield="CourseID" datatype="System.Int32"
                            filtercontrolalttext="Filter CourseID column" headertext="CourseID"
                            readonly="True" sortexpression="CourseID" uniquename="CourseID" display="false">
                        </telerik:gridboundcolumn>
                        <telerik:griddatetimecolumn datafield="InvoiceDate" headertext="Transaction Date" allowfiltering="true" pickertype="DatePicker" dataformatstring="{0:d}" filtercontrolwidth="100px" uniquename="InvoiceDate" />
                        <telerik:gridboundcolumn datafield="CategoryDesc"
                            filtercontrolalttext="Filter AgeGroup column" headertext="AgeGroup"
                            sortexpression="AgeGroup" uniquename="AgeGroup">
                        </telerik:gridboundcolumn>
                          <telerik:gridboundcolumn datafield="SubjectDesc"
                            filtercontrolalttext="Filter Subject column" headertext="Subject"
                            sortexpression="Subject" uniquename="Subject">
                        </telerik:gridboundcolumn>
                        <telerik:gridboundcolumn datafield="Days"
                            filtercontrolalttext="Filter Days column" headertext="Days"
                            sortexpression="Days" uniquename="Days">
                        </telerik:gridboundcolumn>
                         <telerik:gridboundcolumn datafield="StartTime"
                            filtercontrolalttext="Filter StartTime column" headertext="StartTime"
                            sortexpression="StartTime" uniquename="StartTime">
                        </telerik:gridboundcolumn>
                        <telerik:gridboundcolumn datafield="EndTime"
                            filtercontrolalttext="Filter EndTime column" headertext="EndTime"
                            sortexpression="EndTime" uniquename="EndTime">
                        </telerik:gridboundcolumn>
                        <telerik:gridboundcolumn datafield="TermDesc"
                            filtercontrolalttext="Filter Term column" headertext="Term"
                            sortexpression="Term" uniquename="Term">
                        </telerik:gridboundcolumn>
                        <telerik:gridboundcolumn datafield="CourseLength" datatype="System.Int32"
                            filtercontrolalttext="Filter CourseLength column" headertext="Length (Weeks)"
                            sortexpression="CourseLength" uniquename="CourseLength">
                        </telerik:gridboundcolumn>
                        <telerik:gridboundcolumn datafield="Cost" datatype="System.Decimal"
                            filtercontrolalttext="Filter CostResident column" headertext="Cost"
                            sortexpression="CostResident" uniquename="CostResident" aggregate="Sum" dataformatstring="{0:C}" footeraggregateformatstring="Total Cost: {0:C}">
                        </telerik:gridboundcolumn>
                    </columns>
                    <editformsettings>
                        <editcolumn filtercontrolalttext="Filter EditCommandColumn column">
                        </editcolumn>
                    </editformsettings>
                </mastertableview>
                <filtermenu enableimagesprites="False">
                </filtermenu>
                <headercontextmenu cssclass="GridContextMenu GridContextMenu_Default">
                </headercontextmenu>
            </telerik:radgrid>

Please advise.

Thanks.

[Princy]

Hello Samir,

Try the following code snippet to achieve your requirement.

C#:

protected void Button1_Click(object sender, EventArgs e)

   {

       RadGrid1.ExportSettings.ExportOnlyData = true;

       RadGrid1.ExportSettings.OpenInNewWindow = true;     

       foreach (GridGroupHeaderItem groupItem in RadGrid1.MasterTableView.GetItems(GridItemType.GroupHeader))

       {

           groupItem.Visible = false;

       }

       RadGrid1.MasterTableView.ExportToExcel(); 

   } 

Thanks,
Princy.

[Samir Vaidya]

This code does not work for me since my columns are defined in GroupByExpressions.

Please advise.

[Daniel]

Hello Samir,

It is possible to manually flatten the control to some extent (hide items, columns) but I'm afraid there is no easy way to modify the structure in the described way.

Best regards,
Daniel
the Telerik team