This is a migrated thread and some comments may be shown as answers.

ExcelML Export with aggregates in groupfooter

11 Answers 144 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Yeroon
Top achievements
Rank 2
Yeroon asked on 27 Dec 2010, 12:30 PM
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;
            }
        
}

11 Answers, 1 is accepted

Sort by
0
Yeroon
Top achievements
Rank 2
answered on 29 Dec 2010, 01:07 PM
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?
0
Accepted
Daniel
Telerik team
answered on 30 Dec 2010, 11:38 AM
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
Browse the vast support resources we have to jump start your development with RadControls for ASP.NET AJAX. See how to integrate our AJAX controls seamlessly in SharePoint 2007/2010 visiting our common SharePoint portal.
0
Yeroon
Top achievements
Rank 2
answered on 30 Dec 2010, 12:07 PM
Hi Daniel,

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

/Yeroon
0
Yeroon
Top achievements
Rank 2
answered on 14 Feb 2011, 01:28 PM
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
0
Accepted
Daniel
Telerik team
answered on 17 Feb 2011, 01:07 PM
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
0
Allan
Top achievements
Rank 1
answered on 26 Oct 2012, 08:21 PM
Were you able to find a proper index for putting the row at the bottom of each group.

Thanks
Allan
0
Kostadin
Telerik team
answered on 31 Oct 2012, 12:50 PM
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
If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.
0
Allan
Top achievements
Rank 1
answered on 31 Oct 2012, 06:05 PM
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

0
Kostadin
Telerik team
answered on 05 Nov 2012, 04:52 PM
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
If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.
0
Jose
Top achievements
Rank 1
answered on 29 Oct 2014, 09:08 PM
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
                           

0
Kostadin
Telerik team
answered on 03 Nov 2014, 11:56 AM
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
 

Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

 
Tags
Grid
Asked by
Yeroon
Top achievements
Rank 2
Answers by
Yeroon
Top achievements
Rank 2
Daniel
Telerik team
Allan
Top achievements
Rank 1
Kostadin
Telerik team
Jose
Top achievements
Rank 1
Share this question
or