Access cells of exported Excel with column groups

3 posts, 0 answers
  1. Scott Buchanan
    Scott Buchanan avatar
    38 posts
    Member since:
    Dec 2008

    Posted 12 Apr Link to this post

    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

     

     

     

  2. Alex Gyoshev
    Admin
    Alex Gyoshev avatar
    2500 posts

    Posted 14 Apr Link to this post

    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
    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
  3. UI for ASP.NET MVC is VS 2017 Ready
  4. Scott Buchanan
    Scott Buchanan avatar
    38 posts
    Member since:
    Dec 2008

    Posted 26 Jul in reply to Alex Gyoshev Link to this post

    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

Back to Top