Telerik blogs

Exporting the Kendo UI Grid Data to Excel

by John DeVight
March 12, 2013 Web 0 Comments


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:

  1. Download and install XML SDK 2.0 for Microsoft Office
  2. Read the article by called Creating basic Excel workbook with Open XML
  3. 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:

  1. model - contains the column definitions
  2. data - data from the grid
  3. 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.

Spring Keynote Banner

Kendo UI
About the Author

John DeVight

John DeVight is currently a Senior Principal Software Engineer with ManTech MCTS. 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.

Comments

Comments are disabled in preview mode.