public ProductService(SampleEntities entities) { this.entities = entities; }
/// <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"); } } }