MultiColumn Headers and export to Excel

21 posts, 0 answers
  1. Tijl
    Tijl avatar
    1 posts
    Member since:
    Jun 2012

    Posted 19 Jun 2012 Link to this post

    I implemented MultiColumn Headers on my radGrid, very cool!

    Exporting to Excel however is not doing so great. It only takes the lowest level headers, but with column span of the top level, causing all sorts of wierdness....

    Any suggestions on how to export the grid as is, meaning all levels of the headers and data?
  2. Daniel
    Admin
    Daniel avatar
    4949 posts

    Posted 22 Jun 2012 Link to this post

    Hi Tijl,

    I'm afraid multi column headers are not supported by the RadGrid export yet.
    Please excuse us for any inconvenience caused.

    Kind regards,
    Daniel
    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.
  3. Luciano Kaesemodel
    Luciano Kaesemodel avatar
    29 posts
    Member since:
    Apr 2007

    Posted 22 Jan 2013 Link to this post

    I work with two different types of multi column/row header grid. One type defines ColumnGroups directly in the aspx file and the other creates the ColumnGroups dynamically at runtime.

    When exporting in regular Excel(html) format, it exports all headers correctly only when defining ColumnGroups previously in the aspx.

    When exporting in ExcelML format, it doesn't export headers correctly in any case.

    I want to use ExcelML format for its advantages. Is it possible to export multi column/row headers with ExcelML? If so, how?


  4. Daniel
    Admin
    Daniel avatar
    4949 posts

    Posted 25 Jan 2013 Link to this post

    Hello Luciano,

    I'm afraid that only the HTML format supports the Multi-Column headers feature at this point. I asked our developers if it is possible to implement this for the ExcelML and BIFF formats. If their answer is positive, we will log a feature request for this.

    Best regards,
    Daniel
    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.
  5. Luciano Kaesemodel
    Luciano Kaesemodel avatar
    29 posts
    Member since:
    Apr 2007

    Posted 13 Feb 2013 Link to this post

    What about the feature request? Was it logged?
    Do I have to submit a support ticket for this issue?
  6. Kostadin
    Admin
    Kostadin avatar
    1733 posts

    Posted 19 Feb 2013 Link to this post

    Hello Luciano,

    This feature request is already logged in our system. You could track the progress in our Ideas & Feedback portal.

    Kind regards,
    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.
  7. Amit
    Amit avatar
    9 posts
    Member since:
    Dec 2013

    Posted 10 Jan 2014 Link to this post

    Hi Telerik support,

    Is there any update on this request ? I am facing the same issue while exporting the multi columns radgrid.
    Please let me know.

    Thanks,
  8. Kostadin
    Admin
    Kostadin avatar
    1733 posts

    Posted 15 Jan 2014 Link to this post

    Hello Amit,

    Our developers are currently working of implementing this feature and I hope it will be available in our next official release.

    Regards,
    Kostadin
    Telerik
    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 the blog feed now.
  9. Miky
    Miky avatar
    13 posts
    Member since:
    Dec 2011

    Posted 26 Aug 2014 in reply to Kostadin Link to this post

    Hello,

    is there any news about this feature?  Because it's getting a bigger problem as time goes by.
    My users are awaiting for this.

    Kind regards,
  10. Kostadin
    Admin
    Kostadin avatar
    1733 posts

    Posted 27 Aug 2014 Link to this post

    Hello Miky,

    The issue is already fixed. Also the status of the item in our Ideas&Feedback portal has been changed as well.

    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.

     
  11. Miky
    Miky avatar
    13 posts
    Member since:
    Dec 2011

    Posted 27 Aug 2014 in reply to Kostadin Link to this post

    Hi,

    Then can you please tell me what special thing I have to do to make it work, because all of our grid that uses multi-headers don't work with the export and I have the very latest version UI for ASP.NET AJAX Q2 2014 SP1. I only have one line of header in the .XLS file.

    Kind regards,
  12. Kostadin
    Admin
    Kostadin avatar
    1733 posts

    Posted 01 Sep 2014 Link to this post

    Hi Miky,

    The multi-column headers are exported automatically and you shouldn't do anything special. I prepared a small sample and attached it to this thread. The sample works correctly on my end, so please give it a try and let me know about the result.

    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.

     
  13. Miky
    Miky avatar
    13 posts
    Member since:
    Dec 2011

    Posted 02 Sep 2014 in reply to Kostadin Link to this post

    Hi,

    your sample is using Biff and I use ExcelML
    They fixed Biff but not the other and it's ExcelML I need, like the other person int he previous posts.

    Your sample doesn't work either if I change it to ExcelML.

    My export setting is like this :
      <ExportSettings IgnorePaging="True" ExportOnlyData="True" OpenInNewWindow="True">
                    <Excel Format="ExcelML"></Excel>
                </ExportSettings>



    Regards,
  14. Kostadin
    Admin
    Kostadin avatar
    1733 posts

    Posted 05 Sep 2014 Link to this post

    Hello Miky,

    I am afraid that exporting of multi-column header is supported only for Biff and Html based export format. Note that ExcelML format generates the export structure directly from the database and not from the rendered html which makes implementation of this feature hardly achievable.

    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.

     
  15. Carlos
    Carlos avatar
    8 posts
    Member since:
    Dec 2012

    Posted 19 May 2015 in reply to Kostadin Link to this post

    Hi,

    What is the status on exporting multi-column headers at this point.   Radgrid Winforms /  ExcelML.

    Thanks.

  16. Rodney Foley
    Rodney Foley avatar
    72 posts
    Member since:
    Oct 2009

    Posted 12 Feb 2019 Link to this post

    Wait a crazy thread.... I user wants to Export Excel with Column Groups in it. They are told the feature is already requested and then it is eventually being worked on but that is the BIFF export not ExcelML.  Then they say they can't do it because it would be to hard because of the implementation.

    WTF!?!? Fix your implementation, its been about 9 years now and we still do not have Column group export support for ExcelML. This is totally possible to do, as excel supports this functionality.  If your implementation makes it hard, then fix the implementation so that it makes it easy. Not turn away feature requests from users because they are just to hard.

    This is crazy that this is not support for an ExcelML export? It should be support in all Export formats that the RadGrid supports. There should be no acceptable reason not to support an export that  has the data shown as close as possible to the way it is in the web page.

    I am very disappointed in Telerik for how they have been handling this issue.

  17. Attila Antal
    Admin
    Attila Antal avatar
    609 posts

    Posted 21 Feb 2019 Link to this post

    Hi Rodney,

    I understand that exporting to ExcelML format along with MultiColumnHeaders is important for you and in most cases we try to further adjust the components/features as needed. There are edge cases when the product is not suitable for adjustments due to its nature and design. On the other hand, HTML structure, MultiColumnHeaders can be exported using the other formats such as the HTML based Excel, Biff and XLSX. For the newest version of Excel (XLSX Open XML SpreadSheet) RadGrid is using the Telerik Document Processing Library which provides with a big variety of options for customizing the Excel Document.

    While the Html, Biff and Xlsx options are designed to export the RadGrid's structure, the ExcelML is building the output based on the datasource structure, thus I am afraid exporting grid structure layout functionalities which are not related directly to the database like MultiColumnHeaders/Groups, visibility of filtering, paging or footer items is not supported with ExcelML. Even when you want to apply formatting or styling to the exported cells, they are not coming from the grid automatically, but need to be set manually as explained and demonstrated in this article: ExcelML basics
     

    I hope this will clarify your concerns.

    Kind regards,
    Attila Antal
    Telerik
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
  18. Rodney Foley
    Rodney Foley avatar
    72 posts
    Member since:
    Oct 2009

    Posted 25 Feb 2019 in reply to Attila Antal Link to this post

    I would love XLSX to be supported natively within the RadGrid (ajax) however per the documentation is not build in support for RadGrid (https://docs.telerik.com/devtools/aspnet-ajax/controls/grid/functionality/exporting/overview) that the docs for it require manually implemented it which is a pain when  several Excel formats are supported.

    Yes multi-columns are supported in those other formats, but you loose the "table" concept that the data is exported in when using ExcelML.  XLSX (openxml) exporting could support tables as well and multiple columns however I don't want to do the work myself when we use a Telerik Control that supports exporting, we want it to do the heavy lifting for everything, otherwise why are we not just using the asp:grid and doing all the heavy lifting ourselves.

    ExcelML just works with almost no effort, short of it ignoring the column grouping making the output confusing as it only has the shorter name. I mean if it at the least Combined the group name with the child name in the export at least context would be maintained. 

  19. Attila Antal
    Admin
    Attila Antal avatar
    609 posts

    Posted 28 Feb 2019 Link to this post

    Hi Rodney,

    Assuming that you are using the latest version of the Telerik controls, RadGrid can export the MultiColumn headers out of the box and no additional coding is required.

    You can try the following code snippets to see it in action:

    ASP Markup code containing 3 buttons for the different export formats, and one RadGrid that has Multi Column headers enabled.

    <h3>Export</h3>
    <telerik:RadButton ID="RadButton1" runat="server" Skin="Default" Text="XLSX" OnClick="RadButton1_Click"></telerik:RadButton>
    <telerik:RadButton ID="RadButton2" runat="server" Skin="Default" Text="Html" OnClick="RadButton2_Click"></telerik:RadButton>
    <telerik:RadButton ID="RadButton3" runat="server" Skin="Default" Text="Biff" OnClick="RadButton3_Click"></telerik:RadButton>
    <br />
    <br />
    <telerik:RadGrid ID="RadGrid1" runat="server" Width="800px" Skin="Default" OnNeedDataSource="RadGrid1_NeedDataSource">
        <MasterTableView AutoGenerateColumns="False" DataKeyNames="OrderID">
            <CommandItemSettings ShowExportToExcelButton="true" ShowAddNewRecordButton="false" ShowRefreshButton="false" />
     
            <ColumnGroups>
                <telerik:GridColumnGroup HeaderText="Group 1" Name="Group1"></telerik:GridColumnGroup>
                <telerik:GridColumnGroup HeaderText="Group 2" Name="Group2"></telerik:GridColumnGroup>
                <telerik:GridColumnGroup HeaderText="Group 3" Name="Group3" ParentGroupName="Group2"></telerik:GridColumnGroup>
            </ColumnGroups>
     
            <Columns>
                <telerik:GridBoundColumn DataField="OrderID" DataType="System.Int32"
                    FilterControlAltText="Filter OrderID column" HeaderText="OrderID"
                    ReadOnly="True" SortExpression="OrderID" UniqueName="OrderID">
                </telerik:GridBoundColumn>
                <telerik:GridDateTimeColumn DataField="OrderDate" DataType="System.DateTime" ColumnGroupName="Group1"
                    FilterControlAltText="Filter OrderDate column" HeaderText="OrderDate"
                    SortExpression="OrderDate" UniqueName="OrderDate">
                </telerik:GridDateTimeColumn>
                <telerik:GridNumericColumn DataField="Freight" DataType="System.Decimal"  ColumnGroupName="Group2"
                    FilterControlAltText="Filter Freight column" HeaderText="Freight"
                    SortExpression="Freight" UniqueName="Freight">
                </telerik:GridNumericColumn>
                <telerik:GridBoundColumn DataField="ShipName"
                    FilterControlAltText="Filter ShipName column" HeaderText="ShipName" ColumnGroupName="Group2"
                    SortExpression="ShipName" UniqueName="ShipName">
                </telerik:GridBoundColumn>
                <telerik:GridBoundColumn DataField="ShipCountry"
                    FilterControlAltText="Filter ShipCountry column" HeaderText="ShipCountry" ColumnGroupName="Group3"
                    SortExpression="ShipCountry" UniqueName="ShipCountry">
                </telerik:GridBoundColumn>
            </Columns>
        </MasterTableView>
    </telerik:RadGrid>


    C# - Code behind containing the methods required by the Markup.

    // XLSX
    protected void RadButton1_Click(object sender, EventArgs e)
    {
        RadGrid1.ExportSettings.Excel.Format = GridExcelExportFormat.Xlsx;
        RadGrid1.ExportToExcel();
    }
    // HTML
    protected void RadButton2_Click(object sender, EventArgs e)
    {
        RadGrid1.ExportSettings.Excel.Format = GridExcelExportFormat.Html; // HTML is the default value
        RadGrid1.ExportToExcel();
    }
    // BIFF
    protected void RadButton3_Click(object sender, EventArgs e)
    {
        RadGrid1.ExportSettings.Excel.Format = GridExcelExportFormat.Biff;
        RadGrid1.ExportToExcel();
    }
     
    protected void RadGrid1_NeedDataSource(object sender, GridNeedDataSourceEventArgs e)
    {
        RadGrid1.DataSource = OrdersTable();
    }
     
    private DataTable OrdersTable()
    {
        DataTable dt = new DataTable();
     
        dt.Columns.Add(new DataColumn("OrderID", typeof(int)));
        dt.Columns.Add(new DataColumn("OrderDate", typeof(DateTime)));
        dt.Columns.Add(new DataColumn("Freight", typeof(decimal)));
        dt.Columns.Add(new DataColumn("ShipName", typeof(string)));
        dt.Columns.Add(new DataColumn("ShipCountry", typeof(string)));
     
        dt.PrimaryKey = new DataColumn[] { dt.Columns["OrderID"] };
     
        for (int i = 0; i < 4; i++)
        {
            int index = i + 1;
     
            DataRow row = dt.NewRow();
     
            row["OrderID"] = index;
            row["OrderDate"] = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 0, 0, 0).AddHours(index);
            row["Freight"] = index * 0.1 + index * 0.01;
            row["ShipName"] = "Name " + index;
            row["ShipCountry"] = "Country " + index;
     
            dt.Rows.Add(row);
        }
     
        return dt;
    }


    Kind regards,
    Attila Antal
    Progress Telerik
    Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
  20. Rodney Foley
    Rodney Foley avatar
    72 posts
    Member since:
    Oct 2009

    Posted 28 Feb 2019 in reply to Attila Antal Link to this post

    I don't think you understand the issue Attila.

    Those 3 formats are supported in even 2017 version and support column grouping . That is not the issue the issue is ExcelML does not and this is the only format that exports the grid as an Excel Table native within RadGrid. However ExcelML causes an issue making my users a little annoyed is that it is just am XML file with an xls extension. It causes a warning every time you open it in excel until they save as something else.  

    Biff I exports cause the same error message to pop up and does support column grouping. HTML well its HTML so not acceptable. The XLSX one requires a dependency on the Spreadsheets Telerik library so is not supported naively by RadGrid and I hope this would work, but since it would introduce another library we currently have not pushed to production its a big deal here and I have to go through a process to do that and then not all our users are on a supported version of Excel yet, and are in the progress of migrating this year though.

    So I will have to test XLSX type and see if it works as expected, and then if it removes the format warning on opening and if it supports exporting the grid as an Excel Table.  If it does all this and more and we can get the rest of the user base on an XLSX supported version of excel (2010+) then I can go through the process to deploy new assembly to production. This is why if ExcelML export type just supported it then it would be a little nicer.

  21. Attila Antal
    Admin
    Attila Antal avatar
    609 posts

    Posted 05 Mar 2019 Link to this post

    Hi Rodney,

    I understand you now. What the RadGrid currently offers are the features you have described and there is the Feature Request you've created to ADD: RadGrid ExcelML format to support Multi-Column headers feature. Please visit the item and vote up for it as every vote counts, and hopefully other developers would also find it important for their project and vote as well. This will help raise the priority of the item and eventually get implemented sooner.

    Kind regards,
    Attila Antal
    Progress Telerik
    Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
Back to Top