Exporting the Kendo UI Grid Data to Excel

Tuesday, March 12, 2013 by Kendo UI Team Blog | Comments 8

On my current project, I have been asked to provide the users with the ability to export the contents of any grid to Excel. To do this, I decided to use the XML SDK 2.0 for Microsoft Office to create the Excel spreadsheet. I’ve used the XML SDK 2.0 for Microsoft Office plenty of times for generating Word documents, but this was my first time using it to generate Excel spreadsheets. I found a great article by Mike Wendelius called Creating basic Excel workbook with Open XML that provided what I needed to get started with the XML SDK 2.0 for Microsoft Office.

Since I needed to do provide the ability to Export to Excel for any grid, 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.

Getting Started

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

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

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 HomeController.cs

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. In the HomeController I added 2 Controller Actions, ExportToExcel and GetExcelFile.

ExportToExcel

The ExportToExcel Action Method takes 3 parameters:

model - contains the column definitions
data - data from the grid
title - title for the Spreadsheet

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. 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].field.ToString() : modelObject[mdx].title.ToString(),
                false, false);

            // Is there are column width defined?
            Excel.SetColumnWidth(worksheet, mdx + 1, modelObject[mdx].width != null
                ? Convert.ToInt32(modelObject[mdx].width.ToString()) / 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);
}

GetExcelFile

The GetExcelFile takes one parameter, title which is the title of the spreadsheet.

In GetExcelFile, I check session for the spreadsheet. If it exists, I retrieve the spreadsheet from session and then remove it from session. Finally I return 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:

Use the extend method to derive from the existing Kendo UI widget.
Implement the init function and call the base class init function.
implement the options JSON object with the name attribute set to the name of the new widget.
"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 export. Here is the definition of the new export configuration setting:

export: {
  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 export configuration setting has been passed in. If it has, then I create a toolbar button configuration setting for the Export toolbar button. Next I then 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.export) {
        // If the exportCssClass is not defined, then set a default image.
        options.export.cssClass = options.export.cssClass || "k-i-expand";

        // 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.export.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 export.createUrl configuration setting. When the server is finished creating the Excel spreadsheet, the spreadsheet is downloaded using the export.downloadUrl configuration setting. Here is the code:

exportToExcel: function () {
    var that = this;

    // Define the data to be sent to the server to create the spreadsheet.
    data = {
        model: JSON.stringify(that.columns),
        data: JSON.stringify(that.dataSource.data().toJSON()),
        title: that.options.export.title
    };

    // Create the spreadsheet.
    $.post(that.options.export.createUrl, data, function () {
        // Download the spreadsheet.
        window.location.replace(kendo.format("{0}?title={1}", 
            that.options.export.downloadUrl, 
            that.options.export.title));
    });
}

Implementing the ExcelGrid

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

_grid = $("#grid").kendoExcelGrid({
    dataSource: {
        data: createRandomData(500),
        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
        }
    ],
    export: {
        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. That's it.

Grab The Code

The complete code from this article can be downloaded from the ASP.NET samples repo on GitHub.

About the Author
John DeVight is currently a Senior Principal Software Engineer with ManTech and a Telerik MVP. He has been developing software since 1995; building client server applications, network management and monitoring systems, web applications, mobile applications, and Windows App Store applications. John is currently leading an effort to modernize a suite of enterprise systems for a government customer. John has a passion for exploring technologies and sharing what he has learned through his website "ASP.NET Wiki" at http://www.aspnetwiki.com and speaking engagements.

8 Comments

1 Achilles Xu 14 Mar 2013
there is a spell error in the get started page: http://stackoverflow.com/questions/tagged/kendo-ui

in the mobile complete code:
var app = new kendo.mobile.Applilcation();

there is a extra 'l'.

Sorry to post it to here, since I can't find a place to submit bugs.
2 Brandon Satrom 15 Mar 2013
Hi Achilles,

Thanks for letting us know. This has been fixed in our docs source and will be fixed on the site shortly.
3 Cliff Gibson 12 Mar 2013
Interested to know if this works on an iPad, having problems getting the standard AJAX Excel grid export to work on an iPad (works fine on PC and Android).

If this works on iPad then I might swap over and start using
4 Daniel 13 Mar 2013
Cliff, I'd recommend that you try the Excel (BIFF) format:
http://demos.telerik.com/aspnet-ajax/grid/examples/export/biffexport/defaultcs.aspx
5 Some Dude 13 Mar 2013
I wonder if the export to excel (well, csv) functionality of the jquery plugin DataTables would be an easier option to implement? Granted, it's not true excel, but for most people it gets them what they want.
6 Ranganath 21 Mar 2013
Thanks for the detailed solution. Does the current solution not work with Visual Studio 2010?
7 Shaun Poore 31 Mar 2013
This appears that it sends your model back to the server as JSON. For a larger data set it would be better to send back all the grouping/sorting info/columns to show, etc then apply them on the server.

It's also confusing in the example how you'd apply the JavaScript extension to a grid assembled with MVC and not initialized via JavaScript.
8 Simon Trem 03 May 2013
Hello,

How can I use the sample in my KendoUi MVC Wrapper?

Thank you!

Comment

Your name Click to add
Email (optional) Click to add
Website (optional) Click to add

Comment

.NET Developer Tools DevTools

Hybrid Mobile Development Icenium

HTML5 / JavaScript Development Kendo UI

Testing Tools TestStudio

Web Presence Platform Sitefinity CMS

Agile Project Management TeamPulse

Contact us

About Telerik

Blogs