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.
To get started, you will need to do the following:
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; } }
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.
The ExportToExcel Action Method takes 3 parameters:
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); }
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)); } }
When deriving from an existing Kendo UI widget, there a few things that need to be done:
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");
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)); }); }
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");
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.
The complete code from this article can be downloaded from the ASP.NET samples repo on GitHub.
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.