Extending the Scrollable Table jQuery Plugin to Export to Excel

This website is no longer actively supported

Written by John DeVightJohn DeVight on 15 Mar 2013 19:06

Download ASP.NET MVC Sample Code
ASP.NET WebForms Code Coming Soon

Overview

I recently wrote an article about Exporting the Kendo UI Grid Data to Excel in an ASP.NET Application and while that is great for people who use Kendo UI, I thought it would be nice to demonstrate how to export data from an HTML table to Excel. I created a Creating a Scrollable Table jQuery Plugin as a nice way of displaying 500 records. To demonstrate exporting to Excel, I extended the Scrollable Table to have a new function called exportToExcel. The exportToExcel function takes an optional parameter containing filter criteria to filter what data is exported to Excel.

ScrollableTableExcel.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. Read the article I wrote on Creating a Scrollable Table jQuery Plugin.
  4. Install Visual Studio 2012.

The excel Option for the ScrollableTable Module

For the ScrollableTable module to where to send the data to create the Excel spreadsheet and then know where to get the Excel spreadsheet that has been created, I needed to pass in some additional values when initializing the Scrollable Table plugin. I adding the following option:

  1. excel: JSON object containing the values to export to Excel. The JSON object has the following attributes:
    1. title: the title of the spreadsheet.
    2. createUrl: url to post the information that is needed to create the Excel spreadsheet.
    3. downloadUrl: url to get the spreadsheet that has been created.

Here is an example of the excel option passed into the Scrollable Table plugin:

excel: {
    title: "people",
    createUrl: "/Home/ExportToExcel",
    downloadUrl: "/Home/GetExcelFile"
}

Extending the ScrollableTable Module

To extend the ScrollableTable module, I define the extension methods and then use the jQuery.extend function to add the extension methods to the ScrollableTable module. The extension method that I wanted to add to the ScrollableTable module is exportToExcel. exportToExcel takes an optional parameter that contains the values needed to filter the data before sending it to the server to create the Excel spreadsheet. 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:

(function ($) {
    var extensionMethods = {
        exportToExcel: function () {
            var that = this;
 
            var records = null;
 
            if (arguments.length > 0) {
                var filter = arguments[0];
                records = [];
                $.each(that.options.data, function (idx, item) {
                    if (filter.exp.test(item[filter.field]) == true) {
                        records.push(item);
                    }
                });
            } else {
                records = that.options.data;
            }
 
            // Define the data to be sent to the server to create the spreadsheet.
            var data = {
                model: JSON.stringify(that.options.columns),
                data: JSON.stringify(records),
                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 = that.options.excel.downloadUrl + "?title=" + that.options.excel.title;
            });
        }
    };
 
    $.extend(true, ScrollableTable.prototype, extensionMethods);
})(jQuery);

Modifying Excel.cs

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;
    }
}

Exporting to Excel

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

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.

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);
}

Downloading the Excel File

In the HomeController.cs, I added a Controller Action called GetExcelFile that takes one parameter, title which is the title of the spreadsheet. 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.

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));
    }
}

Implementing the Extended ScrollableTable

I created an Index module with an init function. The first thing that the Index.init function does is generate some data to use. To do this, I used the createRandomData function defined in people.js that can be found on the Kendo UI demos website. Here is the url: http://demos.kendoui.com/content/shared/js/people.js. I then use a jQuery selector to get the div that will display the scrollable table and call the scrollableTable plugin passing the options containing the height, columns and data. Next I add click event handlers to the button that exports all the data to Excel and the button that will define a filter for the FirstName and only the filtered data is exported to Excel.

Here is the code:

var ExportExcel = ExportExcel || {};
 
ExportExcel.Index = function ($) {
    var init = function () {
        var people = createRandomData(500);
 
        $("#people").scrollableTable({
            height: 400,
            columns: [
                { title: "First Name", field: "FirstName", width: 100 },
                { title: "Last Name", field: "LastName", width: 100 },
                { title: "City", field: "City", width: 100 },
                { title: "Title", field: "Title", width: 200 },
                { title: "Birth Date", field: "BirthDate", isDate: true, width: 200 },
                { title: "Age", field: "Age", width: 50 }
            ],
            data: people,
            excel: {
                title: "people",
                createUrl: "/Home/ExportToExcel",
                downloadUrl: "/Home/GetExcelFile"
            }
        });
 
        $("#exportToExcel").on("click", function () {
            $("#people").data("scrollableTable").exportToExcel();
        });
 
        $("#exportFilteredToExcel").on("click", function () {
            var startsWith = $("#firstNameStartsWith").val();
            if (startsWith.length > 0) {
                $("#people").data("scrollableTable").exportToExcel({ field: "FirstName", exp: new RegExp("^" + startsWith) });
            } else {
                alert("Please enter a value in the textbox to filter on");
            }
        });
    };
 
    return {
        init: init
    };
}(jQuery);
 
$(function () {
    ExportExcel.Index.init();
});

References

  1. Creating a Scrollable Table jQuery Plugin
  2. Exporting the Kendo UI Grid Data to Excel in an ASP.NET Application
  3. Creating basic Excel workbook with Open XML
  4. XML SDK 2.0 for Microsoft Office

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