Exporting the Kendo UI Grid Data to Excel in an ASP.NET Application

This website is no longer actively supported

Written by John DeVightJohn DeVight on 18 Feb 2013 19:01

Download ASP.NET MVC Sample Code
Download ASP.NET WebForm Sample Code

Overview

On my current project, I have been asked to provide the users with the ability to export the contents of any Kendo UI Grid to Excel. While researching possible solutions, I found a great article by Mike Wendelius called Creating basic Excel workbook with Open XML that provided what I needed to generate Excel spreadsheets using XML SDK 2.0 for Microsoft Office.

I needed to do provide the ability to Export to Excel for any grid, so I decided to create a generic REST end point that would receive any data from any grid and generate the Excel Spreadsheet. Fortunately this is possible to do since JSON.NET supports deserializing JSON data into the dynamic data type.

I am using the Grid widget in Kendo UI Web and didn’t want to have to write code for each grid to handle sending the data to a REST end point to create the Excel spreadsheet and then download the Excel spreadsheet. The Kendo UI framework supports inheritance. I was able to derive from the Grid widget to create a new widget called ExcelGrid. Once I created the ExcelGrid, all I had to do was switch the initialization of all my grids from kendoGrid to kendoExcelGrid.

Since I am using ASP.NET MVC, the first example that I put together is an ASP.NET MVC Application. Afterwards, I decided to put an example as an ASP.NET WebForms Application. I found that I had to make a few changes to make the code compatible with both ASP.NET MVC and ASP.NET WebForms. I also discovered that I had to make a change when I tried out the solution with Internet Explorer. I have a lot of respect for the people who develop these UI widgets and all the concerns that they need to be aware of. The original code used the jQuery.post function to post the data. This worked fine with the ASP.NET MVC Controller Action, but with the ASP.NET WebForm Web Service Method I needed to use the jQuery.ajax function so that I could specify the contentType and dataType. I also found that with the ASP.NET WebForm Web Service Method, I needed to use the JSON.stringify function to convert all the data to be sent to the server to a string. Finally, in the original code I was defining the options to export to Excel in a JSON object called "export". Internet Explorer didn't like this becuase it considers export to be a keyword, so I changed it to "excel".

KendoExcelGrid.png

Getting Started

To get started, you will need to do the following:

  1. Download and install XML SDK 2.0 for Microsoft Office
  2. Read the article by Mike Wendelius called Creating basic Excel workbook with Open XML.
  3. Install Visual Studio 2012.

Kendo UI References

Instead of downloading and including the Kendo UI javascript and css files in the project, I chose to reference the Kendo UI javascript and css files from the cdn.kendostatic.com website. The javascript and css files are in a folder that identifies the version of Kendo UI. In the sample code, I'm using version 2012.3.1114. Therefore, I referenced the Kendo UI javascript and css files as follows:

  1. JavaScript Files
    1. http://cdn.kendostatic.com/2012.3.1114/js/kendo.web.min.js
  2. CSS Files
    1. http://cdn.kendostatic.com/2012.3.1114/styles/kendo.common.min.css
    2. http://cdn.kendostatic.com/2012.3.1114/styles/kendo.default.min.css

I also Referenced jQuery from the code.jquery.com website as follows:

  1. http://code.jquery.com/jquery-1.8.2.min.js

Finally, to generate the data for the grid, I used the javascript file that the Kendo team uses to populate grids that is on the demos.kendoui.com website:

  1. http://demos.kendoui.com/content/shared/js/people.js

Modifying Excel.cs

First, I needed to make a few changes to Mike Wendelius' Excel.cs. I wanted to leave his code alone, so I changed his Excel class to be a partial class and added a new Excel partial class to the end of the Excel.cs code file. Since I wanted to create the spreadsheet in memory, I added a new CreateWorkbook method that takes a Stream as the parameter. I also wanted to add some additional styles for the column headers, so I added an AddAdditionalStyles method. Here is the code:

public static partial class Excel
{
    /// <summary>
    /// Creates the workbook in memory.
    /// </summary>
    /// <returns>Spreadsheet created</returns>
    public static DocumentFormat.OpenXml.Packaging.SpreadsheetDocument CreateWorkbook(Stream stream)
    {
        DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadSheet = null;
        DocumentFormat.OpenXml.Packaging.SharedStringTablePart sharedStringTablePart;
        DocumentFormat.OpenXml.Packaging.WorkbookStylesPart workbookStylesPart;
 
        // Create the Excel workbook
        spreadSheet = DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Create(stream, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook, false);
 
        // Create the parts and the corresponding objects
 
        // Workbook
        spreadSheet.AddWorkbookPart();
        spreadSheet.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
        spreadSheet.WorkbookPart.Workbook.Save();
 
        // Shared string table
        sharedStringTablePart = spreadSheet.WorkbookPart.AddNewPart<DocumentFormat.OpenXml.Packaging.SharedStringTablePart>();
        sharedStringTablePart.SharedStringTable = new DocumentFormat.OpenXml.Spreadsheet.SharedStringTable();
        sharedStringTablePart.SharedStringTable.Save();
 
        // Sheets collection
        spreadSheet.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();
        spreadSheet.WorkbookPart.Workbook.Save();
 
        // Stylesheet
        workbookStylesPart = spreadSheet.WorkbookPart.AddNewPart<DocumentFormat.OpenXml.Packaging.WorkbookStylesPart>();
        workbookStylesPart.Stylesheet = new DocumentFormat.OpenXml.Spreadsheet.Stylesheet();
        workbookStylesPart.Stylesheet.Save();
 
        return spreadSheet;
    }
 
    /// <summary>
    /// Adds additional styles to the workbook
    /// </summary>
    /// <param name="spreadsheet">Spreadsheet to use</param>
    /// <returns>True if succesful</returns>
    public static bool AddAdditionalStyles(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet)
    {
        DocumentFormat.OpenXml.Spreadsheet.Stylesheet stylesheet = spreadsheet.WorkbookPart.WorkbookStylesPart.Stylesheet;
 
        // Additional Font for Column Heder.
        stylesheet.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Fonts>().InsertAt<DocumentFormat.OpenXml.Spreadsheet.Font>(
            new DocumentFormat.OpenXml.Spreadsheet.Font()
            {
                FontSize = new DocumentFormat.OpenXml.Spreadsheet.FontSize()
                {
                    Val = 12
                },
                FontName = new DocumentFormat.OpenXml.Spreadsheet.FontName()
                {
                    Val = "Calibri"
                },
                Bold = new DocumentFormat.OpenXml.Spreadsheet.Bold()
                {
                    Val = true
                }
            }, 1);
 
        // Additional Fill for Column Header.
        stylesheet.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Fills>().InsertAt<DocumentFormat.OpenXml.Spreadsheet.Fill>(
            new DocumentFormat.OpenXml.Spreadsheet.Fill()
            {
                PatternFill = new DocumentFormat.OpenXml.Spreadsheet.PatternFill()
                {
                    PatternType = new DocumentFormat.OpenXml.EnumValue<DocumentFormat.OpenXml.Spreadsheet.PatternValues>()
                    {
                        Value = DocumentFormat.OpenXml.Spreadsheet.PatternValues.Solid
                    },
                    BackgroundColor = new DocumentFormat.OpenXml.Spreadsheet.BackgroundColor
                    {
                        Indexed = 64U
                    },
                    ForegroundColor = new DocumentFormat.OpenXml.Spreadsheet.ForegroundColor
                    {
                        Rgb = "F2F2F2"
                    }
                }
            }, 2);
 
        // Column Header
        stylesheet.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.CellFormats>().InsertAt<DocumentFormat.OpenXml.Spreadsheet.CellFormat>(
            new DocumentFormat.OpenXml.Spreadsheet.CellFormat()
            {
                FormatId = 0,
                NumberFormatId = 0,
                FontId = 1,
                FillId = 2,
                ApplyFill = true,
                ApplyFont = true
            }, 4);
 
        stylesheet.Save();
 
        return true;
    }
}

Modifying the Server Side Code

The process of exporting to Excel required 2 calls to the server. The first call creates the Excel spreadsheet, and the second call downloads the Excel spreadsheet.

Exporting to Excel

In ExportToExcel I created a MemoryStream and call Excel.CreateWorkbook to create the workbook in memory. I them called the methods needed to add the basic styles and additional styles for the column headers and then I add the worksheet. Using JSON.NET, I converted the model and data parameters to dynamic objects. For each of the columns passed in as the model parameter, I created the columns and set the column width for each column in the spreadsheet. Next I add all the data to the spreadsheet. Finally, I store the spreadsheet in Session to be retrieved later when I download the spreadsheet.

In the HomeController.cs, I added a Controller Action called ExportToExcel to create the Excel spreadsheet. The ExportToExcel Action Method takes 3 parameters:

  1. model - contains the column definitions
  2. data - data from the grid
  3. title - title for the Spreadsheet

Here is the code:

/// <summary>
/// Create the Excel spreadsheet.
/// </summary>
/// <param name="model">Definition of the columns for the spreadsheet.</param>
/// <param name="data">Grid data.</param>
/// <param name="title">Title of the spreadsheet.</param>
/// <returns></returns>
public JsonResult ExportToExcel(string model, string data, string title)
{
    using (System.IO.MemoryStream stream = new System.IO.MemoryStream())
    {
        /* Create the worksheet. */
 
        SpreadsheetDocument spreadsheet = Excel.CreateWorkbook(stream);
        Excel.AddBasicStyles(spreadsheet);
        Excel.AddAdditionalStyles(spreadsheet);
        Excel.AddWorksheet(spreadsheet, title);
        Worksheet worksheet = spreadsheet.WorkbookPart.WorksheetParts.First().Worksheet;
 
        /* Get the information needed for the worksheet */
 
        var modelObject = JsonConvert.DeserializeObject<dynamic>(model);
        var dataObject = JsonConvert.DeserializeObject<dynamic>(data);
 
        /* Add the column titles to the worksheet. */
 
        // For each column...
        for (int mdx = 0; mdx < modelObject.Count; mdx++)
        {
            // If the column has a title, use it.  Otherwise, use the field name.
            Excel.SetColumnHeadingValue(spreadsheet, worksheet, Convert.ToUInt32(mdx + 1),
                (modelObject[mdx].title == null || modelObject[mdx].title == "&nbsp;") 
                    ? modelObject[mdx].field.ToString() 
                    : modelObject[mdx].title.ToString(),
                false, false);
 
            // Is there are column width defined?
            Excel.SetColumnWidth(worksheet, mdx + 1, modelObject[mdx].width != null
                ? int.Parse(LeadingInteger.Match(modelObject[mdx].width.ToString()).Value) / 4
                : 25);
        }
 
        /* Add the data to the worksheet. */
 
        // For each row of data...
        for (int idx = 0; idx < dataObject.Count; idx++)
        {
            // For each column...
            for (int mdx = 0; mdx < modelObject.Count; mdx++)
            {
                // Set the field value in the spreadsheet for the current row and column.
                Excel.SetCellValue(spreadsheet, worksheet, Convert.ToUInt32(mdx + 1), Convert.ToUInt32(idx + 2),
                    dataObject[idx][modelObject[mdx].field.ToString()].ToString(),
                    false, false);
            }
        }
 
        /* Save the worksheet and store it in Session using the spreadsheet title. */
 
        worksheet.Save();
        spreadsheet.Close();
        byte[] file = stream.ToArray();
        Session[title] = file;
    }
 
    return Json(new { success = true }, JsonRequestBehavior.AllowGet);
}

Downloading the Excel File

To download the file, I check session for the spreadsheet based on the spreadsheet title. If it exists, I retrieve the spreadsheet from session and then remove it from session. I then return the spreadsheet.

In the HomeController.cs, I added a Controller Action called GetExcelFile that takes one parameter, title which is the title of the spreadsheet.

Here is the code:

/// <summary>
/// Download the spreadsheet.
/// </summary>
/// <param name="title">Title of the spreadsheet.</param>
/// <returns></returns>
public FileResult GetExcelFile(string title)
{
    // Is there a spreadsheet stored in session?
    if (Session[title] != null)
    {
        // Get the spreadsheet from seession.
        byte[] file = Session[title] as byte[];
        string filename = string.Format("{0}.xlsx", title);
 
        // Remove the spreadsheet from session.
        Session.Remove(title);
 
        // Return the spreadsheet.
        Response.Buffer = true;
        Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", filename));
        return File(file, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", filename);
    }
    else
    {
        throw new Exception(string.Format("{0} not found", title));
    }
}

Creating the ExcelGrid Widget

Deriving from the Grid Widget

When deriving from an existing Kendo UI widget, there a few things that need to be done:

  1. Use the extend method to derive from the existing Kendo UI widget.
  2. Implement the init function and call the base class init function.
  3. implement the options JSON object with the name attribute set to the name of the new widget.
  4. "Register" the new widget using the kendo.ui.plugin function.

Here is the ExcelGrid that inherits from the Grid widget, but doesn't implement any of the new functionality yet:

(function($) {
    var kendo = window.kendo;
 
    // Create the ExcelGrid.
    var ExcelGrid = kendo.ui.Grid.extend({
        init: function (element, options) {
            // Initialize the grid.
            kendo.ui.Grid.fn.init.call(that, element, options);
        },
 
        options: {
            name: "ExcelGrid"
        }
    });
 
    // "Register" the new ExcelGrid with kendo.
    kendo.ui.plugin(ExcelGrid);
})(jQuery);

The kendo.ui.plugin function creates a new kendoExcelGrid function to initialize the widget. To use the ExcelGrid, all you have to do is use the kendoExcelGrid function like this:

var _grid = $("#grid").kendoExcelGrid().data("kendoExcelGrid");

Adding the Export to Excel Functionality

To add the Export to Excel functionality, I need to pass in a few extra configuration settings to the ExcelGrid. The new settings will be containing within a JSON object that will be called excel. Here is the definition of the new excel configuration setting:

excel: {
    cssClass: // CSS class the defines the image to be displayed in the "Export" toolbar button.
    title: // The title to be given to the Excel spreadsheet
    createUrl: // The url for the REST end point that will create the Excel spreadsheet.
    downloadUrl: // The url for the REST end point to download the Excel spreadsheet.
}

In the init function, I check to see if the excel configuration setting has been passed in. If it has, then I create a toolbar button configuration setting for the Export toolbar button. Next I initialize the grid. Finally I add the click event handler for the export toolbar button that will call a new function called exportToExcel. Here is the code:

init: function (element, options) {
    var that = this;
 
    if (options.excel) {
        options.excel = $.extend(
            {
                cssClass: "k-i-expand"      // If the exportCssClass is not defined, then set a default image.
            },
            options.excel);
 
        // Add the export toolbar button.
        options.toolbar = $.merge([
            {
                name: "export",
                template: kendo.format("<a class='k-button k-button-icontext k-grid-export' title='Export to Excel'><div class='{0} k-icon'></div>Export</a>", options.excel.cssClass)
            }
        ], options.toolbar || []);
    }
 
    // Initialize the grid.
    kendo.ui.Grid.fn.init.call(that, element, options);
 
    // Add an event handler for the Export button.
    $(element).on("click", ".k-grid-export", { sender: that }, function (e) {
        e.data.sender.exportToExcel();
    });
}

The exportToExcel function sends the columns, data and the title for the spreadsheet to the server using the url set in the excel.createUrl configuration setting. When the server is finished creating the Excel spreadsheet, the spreadsheet is downloaded using the excel.downloadUrl configuration setting. Here is the code:

exportToExcel: function () {
    var that = this;
 
    // Create a datasource for the export data.
    var ds = new kendo.data.DataSource({
        data: that.dataSource.data()
    });
    ds.query({
        aggregate: that.dataSource._aggregate,
        filter: that.dataSource._filter,
        sort: that.dataSource._sort
    });
 
    // Define the data to be sent to the server to create the spreadsheet.
    data = {
        model: JSON.stringify(that.columns),
        data: JSON.stringify(ds._view),
        title: that.options.excel.title
    };
 
    // Create the spreadsheet.
    $.ajax({
        type: "POST",
        url: that.options.excel.createUrl,
        contentType: "application/json; charset=utf-8",
        dataType: "json",
        data: JSON.stringify(data)
    })
    .done(function (e) {
        // Download the spreadsheet.
        window.location = kendo.format("{0}?title={1}",
            that.options.excel.downloadUrl,
            that.options.excel.title);
    });
}

Implementing the ExcelGrid

To use the ExcelGrid with the excel configuration settings, simply pass in the excel configuration setting along with all the other configuration settings for the Grid. Here is the code:

_grid = $("#grid").kendoExcelGrid({
    dataSource: {
        data: createRandomData(500),    // createRandomData is found in http://demos.kendoui.com/content/shared/js/people.js
        pageSize: 10,
        schema: {
            model: {
                fields: {
                    FirstName: { type: "string" },
                    LastName: { type: "string" },
                    City: { type: "string" },
                    Title: { type: "string" },
                    BirthDate: { type: "date" },
                    Age: { type: "number" }
                }
            }
        }
    },
    columns: [
        {
            field: "FirstName",
            title: "First Name",
            width: 100
        },
        {
            field: "LastName",
            title: "Last Name",
            width: 100
        },
        {
            field: "City",
            width: 100
        },
        {
            field: "Title"
        },
        {
            field: "BirthDate",
            title: "Birth Date",
            template: '#= kendo.toString(BirthDate,"MM/dd/yyyy") #'
        },
        {
            field: "Age",
            width: 50
        }
    ],
    sortable: true,
    groupable: true,
    filterable: true,
    pageable: {
        refresh: true,
        pageSizes: true
    },
    excel: {
        cssClass: "k-grid-export-image",
        title: "people",
        createUrl: "/Home/ExportToExcel",
        downloadUrl: "/Home/GetExcelFile"
    }
}).data("kendoExcelGrid");

Conclusion

Now every grid where I want to be able to export the data to Excel, I change kendoGrid to kendoExcelGrid and pass in the export settings.


Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License