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