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
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
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
The ExportToExcel Action Method takes 3 parameters:
In
/// <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,
In GetExcelFile, I check session for the spreadsheet. If it exists, I retrieve the spreadsheet from
/// <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,
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.
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
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.