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.

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:

excel: JSON object containing the values to export to Excel. The JSON object has the following attributes:

title: the title of the spreadsheet.

createUrl: url to post the information that is needed to create the Excel spreadsheet.

downloadUrl: url to get the spreadsheet that has been created.

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

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.

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:

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:

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 later when I download the spreadsheet.

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.

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.