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

MultiColumn Headers and export to Excel

20 Answers 887 Views

Grid

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

20 Answers, 1 is accepted

answered on 01 Sep 2014, 06:50 AM

MultiColumnHeader.zip

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.

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.

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

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.

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 />

<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.

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.

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.