I hear ya cluckin' big chicken! the nervous rustling of code, tech, and life

Revisiting the Kendo Grid Excel Export

When I initially heard that Telerik would be adding Excel export functionality to their data grid widget, I was ecstatic. The initial announcement from Telerik did not divulge how much of the grid’s current state would find its way in to the dynamically generated spreadsheet. For example, when the user executes the Excel export, would their applied sorts, filters, groups and aggregate functions all magically transfer to the spreadsheet?

 

At the time, I happened to be estimating a project that called for lots of high-grade data grids; both for data mining and CRUD operations. What I did know was that Telerik would be shipping a 100% client-side approach. I was tired enough of server-centric exporting to throw caution to the wind. For whatever reason, I just never found a slick way to reflect the grid's current state in a server generated spreadsheet.

 

Enter Kendo UI Q3 2014 (released Nov. 19, 2014)

For the most part my bet paid off. However, given it was the initial iteration of this nifty grid capability, I had to jump through a few hoops to deliver my solution on time. For example, foreign key column support was not included so instead of getting “John Doe” in a cell you would get the primary key for John Doe….say 15668. Say what? The Grid’s nifty built-in support for foreign key columns wouldn't make its way to the spreadsheet? Oh well, I had to ship, so I slammed in my own fix. Developers complained and support for foreign key columns was added in Q3 2014 SP1, (released in mid-January). It may not be free open source but they stand behind their product and that’s worth paying for. Right through to the most current release, the Telerik team is fixing bugs and adding functionality. Enough cheerleading, lets take it on a test drive.

 

Both of the live demo's use Kendo UI Q1 2015 SP2 (version 2015.1.429)

 

Demo #1 – easy peasy lemon squeezy!

I created a super simple model, view and grid using a bare-bones MVC Kendo wrapper configuration. The purpose is to show how easy it is to add the "Export to Excel" button to your grid. The data from both my demo's is borrowed from an application that tracks rigging used to pick stuff up with construction cranes. As always, it has been scrubbed to eliminate anything important or revealing. Crane rigging are safety-critical tools and equipment that undergo lots of wear and tear. And of course, can cause lots of damage if they fail; hence a database to help track age and condition.

1
2
3
4
5
6
7
8
9
10
11
<!-- Kendo Grid with Excel Export Demo (simple grid configuration) -->
@(Html.Kendo().Grid<Rigging>()
    .Name("simpleCraneRiggingGrid")
    .DataSource(ds => ds
        .Ajax()
        .Read("Get", "GridXlsExpSimple")
    )
    .ToolBar(tools => tools.Excel())
    .Excel(excel => excel.FileName("GridXlsxExpSimple.xlsx"))
)
<!-- End of Kendo Grid -->

In the snippet above, line #8 enables the grid's toolbar and inserts the "Export to Excel" button into it. Line #9 uses the GridExcelBuilder fluent API to configure the grid for Excel exporting. There are other options that can be configured but only the FileName is required. That's it! Adding two lines of code to your MVC view switches on client-side Excel exporting.

 

Demo #2 – a grid with lots of bells and whistles

 

To gain some appreciation for what's going on behind the scenes, iterate through different grid options, click the "Export to Excel" button, and see how your tweaks to the grid transfer to the spreadsheet. It works incredibly well!

 

The beauty of the Kendo grid is how easy it is to switch on powerful functionality. The second demo looks at how the Excel export performs in a more realistic and "real world" setting. The Kendo MVC fluent grid for this example is configured to support:

  • multi-column sorting, primary, secondary, tertiary, etc. Columns that allow sorting make use of a tri-state switch; click the column header once for ascending order, again for descending, and a third time to turn sorting off.
  • multi-column grouping, the row above the column headings is a special row used to define groups when "Groupable" is configured. To activate grouping for a column, drag/drop it's header into this special row. To remove a column's group you click its respective "x" icon. Grouping is on by default, for both crane unit and rigging type, when the grid first loads.
  • aggregate functions are used to add quantitative context to a group. For example, the SUM function would add up values in a  numeric column that is part of a grouping. This demo uses COUNT to display the number of rigging items when grouping is activated for rigging type.
  • multi-column filtering, the little icon in the right-hand corner of a column header activates the column's filter options. If filters are added to more than one column, all filters must be satisfied for the record to be included; logical AND. 
  • frozen/locked columns are helpful when there are lots of columns, and scrolling is required to bring them all into view. Locking one or more identifying columns to the left edge of the grid can make the overall grid more user-friendly.
  • Other configurations settings that affect the Excel spreadsheet include, paging, scrolling, column widths, column width resizing, and column reordering. Column reordering allows the user to drag/drop columns into different ordinal positions from left to right. And yes, even this telegraphs thru to the spreadsheet!
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
<!-- Kendo Grid with Excel Export Demo (complex grid configuration) -->
@(Html.Kendo().Grid<Rigging>()
    .Name("complexCraneRiggingGrid")
    .DataSource(ds => ds
        .Ajax()
        .Read("Get", "GridXlsExpComplex")
        .PageSize(20)
        .Sort(s =>
        {
            s.Add("UnitID");
            s.Add("RiggingType");
            s.Add("Size");
        })
        .Group(g =>
        {
            g.Add(u => u.UnitID);
            g.Add(t => t.RiggingType);
        })
        .Aggregates(agg =>
        {
            agg.Add(r => r.UnitID).Count();
            agg.Add(r => r.RiggingType).Count();
        })
    )
    .Columns(c =>
    {
        c.ForeignKey(u =>u.UnitID, (IEnumerable)ViewData["Units"], "Id", "Name")
            .Title("Unit")
            .Locked(true)
            .Width(350);
        ;
        c.Bound(rg => rg.RiggingType)
            .ClientGroupHeaderTemplate("#= value # (count = #=count#)")
            .Width(150);
        c.Bound(rg => rg.Size).Width(200);
        c.Bound(rg => rg.SerialNumber).Width(100);
        c.Bound(rg => rg.Description).Width(200);
        c.Bound(rg => rg.InDate).Width(90);
            
    })
    .Sortable(s => s.SortMode(GridSortMode.MultipleColumn))
    .Filterable()
    .Groupable()
    .Pageable()
    .Scrollable()
    .Resizable(resizeableCols => resizeableCols.Columns(true))
    .Reorderable(reorderableCols => reorderableCols.Columns(true))
    .ToolBar(tools => tools.Excel())
    .Excel(excel => excel
        .FileName("GridXlsxExpComplex.xlsx")
        .AllPages(true)
        .Filterable(true)
    )
    .HtmlAttributes(new { style = "max-width:1136px" })        
)
<!-- End of Kendo Grid -->

The following lists a few key points about this more complex  grid configuration.

  • When the page loads, the sorting and grouping that are defined in the DataSource section are applied. These settings can be overridden by the user, (see multi-column sorting and multi-column filtering above)
  • When utilizing grid paging, the ".AllPages(true)" setting will insure that all pages are included in the spreadsheet, otherwise only the current page will be exported. An "All Pages" option could be added to allow the user to choose.
  • The ".Filterable(true)" tells the Kendo OOXML routine to switch on Excel column filtering in the worksheet. Again, this could be an option for the user.
  • Although not addressed in this example, it is easy to toggle the visibility of grid columns, (either programmatically or using the grid's built in column menu option), only visible columns will be sent to the spreadsheet. COOL!

To gain some appreciation for what's going on behind the scenes, iterate through different grid options, click the "Export to Excel" button, and see how your tweaks to the grid transfer to the spreadsheet. It works incredibly well!

Summary

Telerik has upped their game with adding easy to use and powerful Excel export functionality to an already impressive data grid.