ExcelML Export with aggregates in groupfooter

12 posts, 2 answers
  1. Yeroon
    Yeroon avatar
    87 posts
    Member since:
    Oct 2012

    Posted 27 Dec 2010 Link to this post

    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;
                }
            
    }

  2. Yeroon
    Yeroon avatar
    87 posts
    Member since:
    Oct 2012

    Posted 29 Dec 2010 Link to this post

    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?
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. Answer
    Daniel
    Admin
    Daniel avatar
    4946 posts

    Posted 30 Dec 2010 Link to this post

    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.
  5. Yeroon
    Yeroon avatar
    87 posts
    Member since:
    Oct 2012

    Posted 30 Dec 2010 Link to this post

    Hi Daniel,

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

    /Yeroon
  6. Yeroon
    Yeroon avatar
    87 posts
    Member since:
    Oct 2012

    Posted 14 Feb 2011 Link to this post

    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
  7. Answer
    Daniel
    Admin
    Daniel avatar
    4946 posts

    Posted 17 Feb 2011 Link to this post

    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
  8. Allan
    Allan avatar
    2 posts
    Member since:
    Oct 2012

    Posted 26 Oct 2012 Link to this post

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

    Thanks
    Allan
  9. Kostadin
    Admin
    Kostadin avatar
    1713 posts

    Posted 31 Oct 2012 Link to this post

    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.
  10. Allan
    Allan avatar
    2 posts
    Member since:
    Oct 2012

    Posted 31 Oct 2012 Link to this post

    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

  11. Kostadin
    Admin
    Kostadin avatar
    1713 posts

    Posted 05 Nov 2012 Link to this post

    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.
  12. Jose
    Jose avatar
    1 posts
    Member since:
    Jan 2014

    Posted 29 Oct 2014 in reply to Kostadin Link to this post

    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
                               

  13. Kostadin
    Admin
    Kostadin avatar
    1713 posts

    Posted 03 Nov 2014 Link to this post

    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.

     
Back to Top
UI for ASP.NET Ajax is Ready for VS 2017