ExcelML Export with aggregates in groupfooter

[Yeroon]

Hello,

I have below code and when I drag groups to the groupingbar the grid shows me the group totals in the groupfooter. However when I export the grid to ExcelML, the grouping structure is there, but the groupfooters with their totals are lost. I have read several suggestions through using the OnExcelMLExportRowCreated and OnExcelMLExportStylesCreated to get what I want, but somehow I can't get it to work. Could you point me in the right direction?

ASPX Code (the grid is ajaxified through telerik RadAjaxManager)

<telerik:RadGrid ID="rg" runat="server" Skin="Office2007"
    AllowFilteringByColumn="True" AllowPaging="True" AllowSorting="True"
    GridLines="None" ShowStatusBar="True" OnNeedDataSource="rg_NeedDataSource" EnableLinqExpressions="False"
    PageSize="20" Width="100%" ShowGroupPanel="True"
    OnColumnCreated="rg_ColCreated" OnItemDataBound="rg_ItemDataBound"
    OnExcelMLExportRowCreated="RadGrid1_ExcelMLExportRowCreated"
    OnExcelMLExportStylesCreated="RadGrid1_ExcelMLExportStylesCreated"
    meta:resourcekey="rgResource1">
    <GroupingSettings ShowUnGroupButton="True" />
    <ExportSettings ExportOnlyData="True" IgnorePaging="True" >
        <Excel Format="ExcelML" />
    </ExportSettings>
    <ClientSettings AllowColumnsReorder="True" AllowDragToGroup="True"
        ColumnsReorderMethod="Reorder" ReorderColumnsOnClient="True">
        <Selecting AllowRowSelect="True" />
        <Scrolling AllowScroll="True" UseStaticHeaders="True" />
        <Resizing AllowColumnResize="True" />
        <Animation AllowColumnReorderAnimation="True" />
    </ClientSettings>
    <MasterTableView AllowMultiColumnSorting="True" ClientDataKeyNames="oid"
        DataKeyNames="oid" GroupLoadMode="Client" ShowFooter="True"
        ShowGroupFooter="True" UseAllDataFields="True">
        <NoRecordsTemplate>
            <div>
                Geen items gevonden.</div>
        </NoRecordsTemplate>
        <CommandItemSettings ExportToPdfText="Export to Pdf" />
    </MasterTableView>
    <HeaderContextMenu CssClass="GridContextMenu GridContextMenu_Office2007"
        EnableImageSprites="True">
    </HeaderContextMenu>
</telerik:RadGrid>

Code behind (C#):

protected void rg_NeedDataSource(object source, GridNeedDataSourceEventArgs e)
    {


        DataTable dt = new DataTable();


        if (ddlGebruikers.SelectedValue == "-2") return;
        if (cbQuarter.Checked)
        {
            dt = getQuarterData();
        }
        else if (cbMonth.Checked)
        {
            dt = getMonthData();
        }

        rg.DataSource = dt;
}

protected void rg_ColCreated(object sender, GridColumnCreatedEventArgs e)
    {
        if (e.Column is GridBoundColumn)
        {
            GridBoundColumn col = (GridBoundColumn)e.Column;
            if (col.UniqueName == "Q1" || col.UniqueName == "Q2" || col.UniqueName == "Q3" || col.UniqueName == "Q4")
            {
                col.Aggregate = GridAggregateFunction.Sum;
            }
            else if (col.UniqueName == "QR1" || col.UniqueName == "QR2" || col.UniqueName == "QR3" || col.UniqueName == "QR4")
            {
                col.Aggregate = GridAggregateFunction.Sum;
            }
        }
}

[Yeroon]

Hello,

I tried several solutions, but it seems most samples implement pre-defined columns with aggregates, and not for autogenerated columns as per my requirement.

Tried the following samples, but to no avail. 

http://www.telerik.com/community/forums/aspnet-ajax/grid/excelml-not-exporting-totals.aspx

http://www.telerik.com/community/forums/aspnet-ajax/grid/gridgroupfooteritem-excel-export-formatting.aspx

I also tried manipulating the generated ExcelML markup with help of this thread:

http://www.telerik.com/community/forums/aspnet-ajax/grid/save-directly-to-file-by-export-to-excel-feature-of-radgrid.aspx

, but that seems a dead end as the grouping is done by the user and could be on any (dynamic) column. It would make things pretty complex.

Any pointers to get this done?

[Daniel]

Hello Yeroon,

You have to add the missing content manually since RadGrid does not export the group footers to ExcelML by default. I attached a simple demo that should give you some ideas as to how to implement this functionailty in your project.

Kind regards,
Daniel
the Telerik team

[Yeroon]

Hi Daniel,

Your sample code works out of the box :) Awesome. Thank you for this solution, you saved my day!

/Yeroon

[Yeroon]

Hi,

I have an additional question. It seems you sample inserts the total row above each group, instead of below. I inspected many properties of the item, columns, groups etc, but can't find a proper index for putting the row at the bottom of each group.

Any pointers?

Thanks

/Yeroon

[Daniel]

Hello Yeroon,

The sample project works fine on my end when using a single level grouping. It won't work properly in all scenarios especially if you group by more than one field.
As to your question - RowElement object does not have a built-in index - this is why I use the following workaround:

int rowIndex = e.Worksheet.Table.Rows.IndexOf(e.Row);

Best regards,
Daniel
the Telerik team

[Allan]

Were you able to find a proper index for putting the row at the bottom of each group.

Thanks
Allan

[Kostadin]

Hi Allan,

The proper index is as follow:

int rowIndex = e.Worksheet.Table.Rows.IndexOf(e.Row) + 1;

Additionally you have to make the modification when the RowType is DataRow. I made the appropriate changes in the sample from my colleague Daniel and attached it again.

All the best,
Kostadin
the Telerik team

[Allan]

Thank-you;

Unfortunately this does not work in a hierarchal grid export.  The subtotals show up every other line not as a footer for each section.

It actuall seems to work closer to what i need in the section:

if(e.RowType == GridExportExcelMLRowType.GroupByHeaderRow)

Do you have another suggestion?

Allan

[Kostadin]

Hello Allan,

Another possible solution is to hook ExcelMLWorkBookCreated event handler and loop through all the header groups and insert a footer row before each header. I prepared a sample where you could see how this logic is implemented.  

Greetings,
Kostadin
the Telerik team

[Jose]

Hi, I've been working with the latest Example, you've uploaded.

But unfortunately, my scenario is when you have two groups levels, so I guess the code just go wild.

Using your example, you would have:

Group  Manufacturer
    Subgroup  Price
            Detail Item
            Detail Item
    Footer Price                        Count 2
Footer Manufacturer               Count 2

Is there any way to show both footers when exporting it to Excel?

Thanks
                           

[Kostadin]

Hi Jose,

A colleague of mine have already answered the support ticket which you have opened. I would recommend you to continue the conversation with them and close this forum thread.

Regards,
Kostadin
Telerik