/// <summary> /// Serves excel via the OpenXML library for the second Grid in the Index Page /// </summary> public FileResult ExportWithOpenXML([DataSourceRequest] DataSourceRequest request) { using (var northwind = new SampleEntities()) { //Get the data representing the current grid state - page, sort and filter var products = new List <Product>(northwind.Products.ToDataSourceResult(request).Data as IEnumerable <Product>); using (var stream = new MemoryStream()) { /* Create the worksheet. */ SpreadsheetDocument spreadsheet = Excel.CreateWorkbook(stream); Excel.AddBasicStyles(spreadsheet); Excel.AddAdditionalStyles(spreadsheet); Excel.AddWorksheet(spreadsheet, "Products"); Worksheet worksheet = spreadsheet.WorkbookPart.WorksheetParts.First().Worksheet; //create columns and set their widths Excel.SetColumnHeadingValue(spreadsheet, worksheet, 1, "Product ID", false, false); Excel.SetColumnWidth(worksheet, 1, 50); Excel.SetColumnHeadingValue(spreadsheet, worksheet, 2, "Product Name", false, false); Excel.SetColumnWidth(worksheet, 2, 50); Excel.SetColumnHeadingValue(spreadsheet, worksheet, 3, "Unit Price", false, false); Excel.SetColumnWidth(worksheet, 3, 50); Excel.SetColumnHeadingValue(spreadsheet, worksheet, 4, "Quantity Per Unit", false, false); Excel.SetColumnWidth(worksheet, 4, 50); /* Add the data to the worksheet. */ // For each row of data... for (int idx = 0; idx < products.Count; idx++) { // Set the field values in the spreadsheet for the current row. Excel.SetCellValue(spreadsheet, worksheet, 1, (uint)idx + 2, products[idx].ProductID.ToString(), false, false); Excel.SetCellValue(spreadsheet, worksheet, 2, (uint)idx + 2, products[idx].ProductName, false, false); Excel.SetCellValue(spreadsheet, worksheet, 3, (uint)idx + 2, products[idx].UnitPrice.ToString(), false, false); Excel.SetCellValue(spreadsheet, worksheet, 4, (uint)idx + 2, products[idx].QuantityPerUnit, false, false); } /* Save the worksheet and store it in Session using the spreadsheet title. */ worksheet.Save(); spreadsheet.Close(); return(File(stream.ToArray(), //The binary data of the XLS file "application/vnd.ms-excel", //MIME type of Excel files "GridExcelExport.xlsx")); } } }
/// <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)); }
public ExportToExcelResponse ExportToExcel(ExportToExcelRequest request) { try { using (var stream = new MemoryStream()) { // Create the worksheet. var spreadsheet = Excel.CreateWorkbook(stream); Excel.AddBasicStyles(spreadsheet); Excel.AddAdditionalStyles(spreadsheet); Excel.AddWorksheet(spreadsheet, request.Title); var worksheet = spreadsheet.WorkbookPart.WorksheetParts.First().Worksheet; var columns = request.Columns; var data = request.Data; /* Add the column titles to the worksheet. */ for (var mdx = 0; mdx < columns.Count; mdx++) { // If the column has a title, use it. Otherwise, use the field name. Excel.SetColumnHeadingValue(spreadsheet, worksheet, Convert.ToUInt32(mdx + 1), columns[mdx].Title ?? columns[mdx].Field, false, false); // Is there are column width defined? Excel.SetColumnWidth(worksheet, mdx + 1, columns[mdx].Width != null ? Convert.ToInt32(columns[mdx].Width.Replace("px", string.Empty)) / 4 : 25); } /* Add the data to the worksheet. */ // For each row of data... for (var idx = 0; idx < data.Count; idx++) { // For each column... for (var mdx = 0; mdx < columns.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), data[idx][columns[mdx].Field], false, false); } } // save the changed worksheet.Save(); // clean up excel spreadsheet.Close(); // generate a unique id for the response var key = Guid.NewGuid().ToString(); // Cache the response (60 seconds should be long enough) Cache.AddOrUpdate( new Tuple <string, byte[]>(request.Title, stream.ToArray()), // package the title and data into a tuple. key, ExportExcelRegion, 1); // return the response with the url to get the document return(new ExportToExcelResponse() { Expires = DateTime.Now.AddMinutes(1), Url = Request.RequestUri + "?key=" + key }); } } catch (Exception ex) { LogProvider.Get <QueuesController>().Error(ex); throw; } }