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

MultiColumn Headers and export to Excel

20 Answers 187 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Tijl
Top achievements
Rank 1
Tijl asked on 19 Jun 2012, 10:42 AM
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?

20 Answers, 1 is accepted

Sort by
0
Daniel
Telerik team
answered on 22 Jun 2012, 03:28 PM
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.
0
Luciano Kaesemodel
Top achievements
Rank 1
answered on 22 Jan 2013, 05:01 PM
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?


0
Daniel
Telerik team
answered on 25 Jan 2013, 03:28 PM
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.
0
Luciano Kaesemodel
Top achievements
Rank 1
answered on 13 Feb 2013, 03:54 PM
What about the feature request? Was it logged?
Do I have to submit a support ticket for this issue?
0
Kostadin
Telerik team
answered on 19 Feb 2013, 02:42 PM
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.
0
Amit
Top achievements
Rank 1
answered on 10 Jan 2014, 10:45 PM
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,
0
Kostadin
Telerik team
answered on 15 Jan 2014, 01:51 PM
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.
0
Miky
Top achievements
Rank 1
answered on 26 Aug 2014, 01:03 PM
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,
0
Kostadin
Telerik team
answered on 27 Aug 2014, 11:15 AM
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.

 
0
Miky
Top achievements
Rank 1
answered on 27 Aug 2014, 11:22 AM
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,
0
Kostadin
Telerik team
answered on 01 Sep 2014, 06:50 AM
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.

 
0
Miky
Top achievements
Rank 1
answered on 02 Sep 2014, 11:16 AM
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,
0
Kostadin
Telerik team
answered on 05 Sep 2014, 06:39 AM
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.

 
0
Carlos
Top achievements
Rank 1
answered on 19 May 2015, 06:10 PM

Hi,

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

Thanks.

0
Rodney Foley
Top achievements
Rank 1
answered on 12 Feb 2019, 06:16 PM

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.

0
Attila Antal
Telerik team
answered on 21 Feb 2019, 09:09 AM
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
0
Rodney Foley
Top achievements
Rank 1
answered on 25 Feb 2019, 03:53 PM

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. 

0
Attila Antal
Telerik team
answered on 28 Feb 2019, 03:06 PM
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.
0
Rodney Foley
Top achievements
Rank 1
answered on 28 Feb 2019, 03:40 PM

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.

0
Attila Antal
Telerik team
answered on 05 Mar 2019, 11:27 AM
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.
Tags
Grid
Asked by
Tijl
Top achievements
Rank 1
Answers by
Daniel
Telerik team
Luciano Kaesemodel
Top achievements
Rank 1
Kostadin
Telerik team
Amit
Top achievements
Rank 1
Miky
Top achievements
Rank 1
Carlos
Top achievements
Rank 1
Rodney Foley
Top achievements
Rank 1
Attila Antal
Telerik team
Share this question
or