MultiColumn Headers Off On BIFF Export (Dynamic RadGrid)

[Joey]

I'm running into an issue while exporting in BIFF format, the column headers aren't correct on any of my grids that use multicolumn headers.  This is on dynamically created columns and multicolumn headers.  Anything that does not use multicolumn headers is good.

In the attached screenshot it shows that the second row in excel is not shifting the column headers to the right like it should, it is just cutting them off.  When I dig into the GridBiffExportingEventArgs table it is completely missing those cells that are cut off.  I tried changing lots of export settings before the export happens and nothing seemed to help.  Has anyone else run into this same issue?

[Eyup]

Hi,

Please make sure that you are using the latest release version of the controls and UseStaticHeaders property is not enabled.

Regards,
Eyup
Progress Telerik

[Joey]

Unfortunately they still don't export right with the latest release and setting UseStaticHeaders to false.  There isn't any horizontal scrolling on this grid.

[Joey]

I was able to get some more information on this.

It looks like the excel export is skipping certain columns that are inside the group headers, but I can't really figure out why it is skipping those.  The screenshot attached shows it picking just a few of them.

I tested all the excel export formats and it has the same problem on every single one.  When I switched over to word/pdf/csv and all the columns came out right, makes me think it is a bug with the excel export.

Here is what my markup looks like, everything else is done in code behind for the structure and data of the grid.

<telerik:RadGrid ID="rgGoalSummary"
    runat="server" Skin="WebBlue"
    OnNeedDataSource="rgGoalSummary_OnNeedDataSource"
    OnItemDataBound="rgGoalSummary_OnItemDataBound"
    OnSortCommand="AllGrids_SortGrid"
    OnBiffExporting="AllGrids_ExcelBiffExport"
    ExportSettings-Excel-Format="Biff">
</telerik:RadGrid>

[Eyup]

Hi Joey,

I've created a sample RadGrid web site with a similar configuration and the exporting works as expected. Please run the attached application and verify the result on your side.

Regards,
Eyup
Progress Telerik

[Joey]

Hi Eyup,

Thank you for your reply.  Your grid is similar to mine but your columns and group headers are built in the markup.  In my case I am adding them in c# when the page loads.  I add the group headers, then add the columns, then build out a datatable which binds to the grid.

[Joey]

I found the exact issue, it has to do with a dynamically created grid with invisible columns.  I created two grids, one defined in the markup, and one created dynamically.  The static grid is fine when exporting, but the dynamic one misses a column.  I used 4 columns with 2 of them inside a column group.  When I set one of the columns to visible = false on the dynamic grid it messes up the export.  In the static grid the export is fine.  The attached screenshot shows the different exports.  Is there any way to submit this as a bug, or a workaround for the issue?

protected void Page_Load(object sender, EventArgs e)
{
    rgDynamic.AutoGenerateColumns = false;

    if (!Page.IsPostBack)
    {
        var colPre1 = new GridBoundColumn();
        rgDynamic.MasterTableView.Columns.Add(colPre1);
        colPre1.UniqueName = "colPre1";
        colPre1.HeaderText = "Col Pre 1";

        //Invisible Column
        var colPre2 = new GridBoundColumn();
        rgDynamic.MasterTableView.Columns.Add(colPre2);
        colPre2.Visible = false;
        colPre2.UniqueName = "colPre2";
        colPre2.HeaderText = "Col Pre 2";

        var colGroup1 = new GridColumnGroup();
        rgDynamic.MasterTableView.ColumnGroups.Add(colGroup1);
        colGroup1.Name = "cg1";
        colGroup1.HeaderText = "Col Group 1";

        var col1 = new GridBoundColumn();
        rgDynamic.MasterTableView.Columns.Add(col1);
        col1.UniqueName = "col1";
        col1.HeaderText = "Col 1";
        col1.ColumnGroupName = "cg1";

        var col2 = new GridBoundColumn();
        rgDynamic.MasterTableView.Columns.Add(col2);
        col2.UniqueName = "col2";
        col2.HeaderText = "Col 2";
        col2.ColumnGroupName = "cg1";
    }

    rgDynamic.DataSource = String.Empty;
    rgDynamic.DataBind();

    rgStatic.DataSource = String.Empty;
    rgStatic.DataBind();
}

[Eyup]

Hello,

Please make sure that you are not using the DataBind() method to bind the grid. Performing complex grid operations such as Inserting, Deleting, Updating, Hierarchy relations, Grouping, Exporting, Paging, Sorting, Filtering, etc. require accommodating appropriate database operations.  Therefore, we suggest you to avoid Simple Databinding and strongly recommend the use of more advanced databinding methods, which automatically handle the aforementioned functions:

Declarative DataSource (DataSourceID property)
Programmatic Data Binding (NeedDataSource event, + DetailTableDataBind for hierarchy). You should set the DataSource property ONLY within these event handlers.

Also create the grid using the Page_Init event handler as demonstrated in the following section:
http://docs.telerik.com/devtools/aspnet-ajax/controls/grid/defining-structure/creating-a-radgrid-programmatically#creating-a-radgrid-on-page_init

I am also sending a sample RadGrid web site for a basic demonstration.

Regards,
Eyup
Progress Telerik

[Joey]

Hi Eyup,

I moved setting my grid's datasource into the OnNeedDataSource call, and also tried building the grid in Page_Init, but it is still giving me the same issue when exporting.  This really looks like a bug.  The columns are all correct in other exports (csv/pdf/word), only when I export to excel is it off.  It's like something is being missed when it builds the export structure for the excel document.

[Eyup]

Hello,

I've already replied to your query in ticket with ID: 1114628. I suggest that we continue our technical conversation on the mentioned thread.

Regards,
Eyup
Progress Telerik

[Michael]

I have exactly the same issue!

I create an edit and a delete button dynamicly in code.

Depending on a global permission (AllowDelete, AllowEdit) these buttons are visible or not.

In all cases where I use ColumnGroups the header lines in the excel-Biff exported are shifted by these 2 columns.

Is there any solution for "issue" ?

I can't read the ticket ID 114628

Regards,

Michael

[Joey]

Hey Michael,

I don't think this one ever got fixed by Telerik, we ended up just working around it and moving on to other technology later.

The issue happens if you try and export with hidden columns, it doesn't take that into account for the headers.  I ended up turning all the columns on when the export happens and adding some text to the top of any that should be hidden.  After that loop through and remove any text from a "hidden" column and set the width to 0 so it is kind of hidden in the sheet.  It's definitely not worth all the trouble, if you have a data table available it might make more sense to find a way to export and format that instead.

[Eyup]

Hi,

This can be related to the following issue which was fixed and will come live with the next release version:
https://feedback.progress.com/Project/108/feedback/Details/198024-hiding-columns-when-exporting-radgrid-with-multi-column-headers-to-binary-excel-f

I am also sending a sample RadGrid web site to demonstrate alternative approach.

Regards,
Eyup
Progress Telerik