/// <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;
}
}
