Access cells of exported Excel with column groups

[Scott Buchanan]

I have trouble applying formatting to the Excel export with one grid, due to column groups.

Here is the basic grid column definitions

.Columns(columns =>
{
    columns
        .Bound(t => t.SpecialtyCode)
        .Title("Specialty<br />Code")
    columns
        .Bound(t => t.ProviderName)
        .Title("Provider Name")
    columns
        .Bound(t => t.TotalReviewed)
        .Title("Total<br />Reviewed")
    columns
        .Group(group => group
        .Title("Met Medical Necessity")
        .Columns(metMedical =>
        {
            metMedical
                .Bound(t => t.MetMedical)
                .Title("Count")
            metMedical
                .Bound(t => t.MetMedicalPercent)
                .Title("Percent")
        }));
    columns
        .Group(group => group
        .Title("Met Documentation Criteria")
        {
            metDocumentation
                .Bound(t => t.MetDocumentation)
                .Title("Count")
            metDocumentation
                .Bound(t => t.MetDocumentationPercent)
                .Title("Percent")
        }));
    columns
        .Group(group => group
        .Title("Met Both")
        {
            metBoth
                .Bound(t => t.MetBoth)
                .Title("Count")
            metBoth
                .Bound(t => t.MetBothPercent)
                .Title("Percent")
        }));
    columns
        .Bound(t => t.IssuesFound)
        .Title("Issues<br />Found")
})

There are 10 columns total, with 3 pairs in a column group.  In Excel, this is rendered [nicely] with the group name in merged cells above the columns in the group on row 0, with the grouped column names on row 1.

I apply some formatting to the Excel workbook.  Normally, I place the setting on a custom attribute that I attach on document.ready, like so

function configureExcelExport() {
    var grid = $("#grid").data("kendoGrid");
    if (grid) {
        grid.columns[0].excelMetadata = { "width": 63, "align": "center" };
        grid.columns[2].excelMetadata = { "width": 69, "align": "center" };
        grid.columns[3].excelMetadata = { "width": 69 };
        grid.columns[4].excelMetadata = { "width": 78 };
        grid.columns[5].excelMetadata = { "width": 83 };
        grid.columns[6].excelMetadata = { "width": 99 };
        //grid.columns[7].excelMetadata = { "width": 62 };
        //grid.columns[8].excelMetadata = { "width": 64 };
        //grid.columns[9].excelMetadata = { "width": 64 };

With the column groups, I can only address up to grid column[6] - which makes sense as there are those 3 column groups.  I figured I could move those settings to the actual Excel manipulation.  In Excel, on row 0, there are again only 7 columns.  No problem addressing them.  On row 1, there are 10 columns but I still receive undefined errors when trying to address anything after the 7th column (index 6).  Here's a portion of the code

function OnExcelExport(e) {
    var sheet = e.workbook.sheets[0];
    var grid = $("#grid").data("kendoGrid");
    var row = sheet.rows[1];
    for (var colIndex = 3; colIndex < 9; colIndex++) {
        row.cells[colIndex].wrap = true;
        sheet.columns[colIndex].autoWidth = false;
        //sheet.columns[colIndex].width = grid.columns[colIndex].excelMetadata.width;
        sheet.columns[colIndex].width = 64;
}

This fails as soon as colIndex = 6, even though I am only working with the Excel file and It does have 10 columns.  I've attached a sample.  How can I use the column groups AND apply formatting to Excel on a column by column basis?

Best,
Scott

 

 

 

[Alex Gyoshev]

Hello Scott,

Do you need to fetch the column information from the grid columns and apply it to the excel export? If so, the following information should help. Otherwise, please elaborate.

When using multi-column headers, the column information for second-level columns is stored within each item of the grid.columns array:

var metMedicalNecessity = grid.columns[3];
var metMedicalCount = metMedicalNecessity.columns[0];

When exporting to excel, this information is converted to two excel rows. Thus, if you need to get the metadata for a given second-level column, you may need to go through the grid.columns array and count the indices of how many second-level columns have passed.

Regards,
Alex Gyoshev
Telerik

[Scott Buchanan]

Alex,

Thank you for the information about the nested arrays.  I should have realized that.

The data exporting was fine - I was primarily having trouble applying formatting rules to the grid.  My current implementation simply applies those rules to the Excel columns directly (but is fragile as would break if columns are modified).  I will revise based on these notes.

Best,
Scott