MultiColumn Headers and export to Excel

[Tijl]

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?

[Daniel]

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

[Luciano Kaesemodel]

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?

[Daniel]

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

[Luciano Kaesemodel]

What about the feature request? Was it logged?
Do I have to submit a support ticket for this issue?

[Kostadin]

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

[Amit]

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,

[Kostadin]

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

[Miky]

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,

[Kostadin]

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

 

 

[Miky]

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,

[Kostadin]

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

 

 

[Miky]

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,

[Kostadin]

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

 

 

[Carlos]

Hi,

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

Thanks.

[Rodney Foley]

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.

[Attila Antal]

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

[Rodney Foley]

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. 

[Attila Antal]

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

[Rodney Foley]

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.

[Attila Antal]

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