Friday, October 14, 2011
Using ASP.NET MVC and OpenXML api to stream Excel files.
I’ve been heads down for the last several weeks Scrumming and it has been a while since I’ve updated my blog so I figured if I was going to keep your interest I should give you something really useful. We’ve been transitioning from web forms to ASP.NET MVC and we had a requirement to export the contents of a grid to an Excel spreadsheet from one of our ASP.NET MVC pages and prompt the user to open or save.
After some digging I wasn’t happy with the idea of porting the legacy solution over so I did a little digging. Opening an Excel spreadsheet is not anything new but I had a few requirements of my own.
- I wanted to call the function from my ASP.NET MVC controller.
- I wanted to send in a collection of data from my model.
- I wanted to minimize hard disk I/O.
- I wanted to add custom headers.
- I wanted to give my Excel file a meaningful name.
- I wanted to give my spreadsheet a meaningful name, say matching the page the user was exporting from to the sheet name.
- I wanted to be able to extend the class to add custom styles.
- I wanted to format the columns for a specific type of data
- I wanted Excel to recognize the file right away. We can’t have any “unrecognized file type” nag screens confusing our customers.
- Finally, I wanted to use this with web forms.
It seemed pretty obvious that I could minimize I/O by saving the file to a memory stream, but what about the rest of my requirements?
I’ll take you through the steps and provide source code in the following sections.
Step 1 Extending the controllers. Steve Sanderson’s blog has an excellent article on extending the controllers and returning an Excel file as an action result. Phil Haack also has an excellent post on the subject which you can find here: Steve’s example uses a DataContext and XML to stream the file however Excel doesn’t recognize the file and prompts you with a nag screen. Excel will eventually open the file and it looks perfect but I really wanted to eliminate this prompt. I also wanted to be able to pass in my own data from the model and localize the Headers and the data. I borrowed extensively from Steve’s tutorial with a little modification. So how do I get Excel to play nice?
I had to learn a little bit about Excel compatibility to get things to work so I starting with the Office Developer’s kit going back to Excel 97. If you have a few hours or an open weekend I would suggest you read up on BIFF8. While BIFF8 is well documented in the Office Developer’s SDK, and I did eventually get the file to stream in BIFF8 I found it almost incomprehensible when it comes to adding additional features and frankly I didn’t want to be the keeper of knowledge Excel spreadsheets within my group which is ultimately what would have happened. This solution needs to scale out because I work with a lot of other developers and they will have more complex requirements as the project grows. I came across the OpenXML api api from Microsoft the OOXML extensions from James Westgate and I was off and running. More information on OpenXml and file formats here.
Get the Open XML SDK
So to recap I’m going to extend my ASP.NET MVC controllers so all of my controllers will have access to the functionality, I will send in data from my model, next I will create a custom spreadsheet and workbook with styled headers, and finally I will assign a meaningful name to the the Excel file and sheet.
Step1. Extending the controller.
If you have read Steve or Phil’s blog, and I encourage you to read these blogs first, this will look like pretty standard stuff, no magic going on here.
namespace YourNamespaceGoesHere.Controllers.Extensions
{
using System.Linq;
using System.Web.Mvc;
using YourNamespaceGoesHere.Controllers.Results;
/// <summary>
/// Excel controller extensions class.
/// </summary>
public static class ExcelControllerExtensions
{
/// <summary>
/// Controller Extension: Returns an Excel result constructor for returning values from rows.
/// </summary>
/// <param>This controller.</param>
/// <param>Excel file name.</param>
/// <param>Excel worksheet name: default: sheet1.</param>
/// <param>Excel row values.</param>
/// <returns>Action result.</returns>
public static ActionResult Excel(this Controller controller, string fileName, string excelWorkSheetName, IQueryable rows)
{
return new ExcelResult(fileName, excelWorkSheetName, rows);
}
/// <summary>
/// Controller Extension: Excel result constructor for returning values from rows and headers.
/// </summary>
/// <param>This controller.</param>
/// <param>Excel file name.</param>
/// <param>Excel worksheet name: default: sheet1.</param>
/// <param>Excel row values.</param>
/// <param>Excel header values.</param>
/// <returns>Action result.</returns>
public static ActionResult Excel(this Controller controller, string fileName, string excelWorkSheetName, IQueryable rows, string[] headers)
{
return new ExcelResult(fileName, excelWorkSheetName, rows, headers);
}
/// <summary>
/// Controller Extension: Excel result constructor for returning values from rows and headers and row keys.
/// </summary>
/// <param>This controller.</param>
/// <param>Excel file name.</param>
/// <param>Excel worksheet name: default: sheet1.</param>
/// <param>Excel row values.</param>
/// <param>Excel header values.</param>
/// <param>Key values for the rows collection.</param>
/// <returns>Action result.</returns>
public static ActionResult Excel(this Controller controller, string fileName, string excelWorkSheetName, IQueryable rows, string[] headers, string[] rowKeys)
{
return new ExcelResult(fileName, excelWorkSheetName, rows, headers, rowKeys);
}
}
}
I have 3 overloads, here.
- Overload 1 takes the Excel File name and an IQueryable collection of rows. .
- Overload 2 takes the Excel File Name, a worksheet name, and IQueryable collection of rows and an array of headers.
- This method allows for passing in headers separately but they must match the keys for the rows.
- Overload 3 takes the Excel File Name, a worksheet name, and IQueryable collection of rows and an array of headers and a collection of row keys.
- This method allows you to have different header names for your rows.
Step 3 extending the action result. This class stores the values passed in when constructed. If you examine this class you’ll see that it overrides the ExcecuteResult function and adds the functionality to create the Excel file and save it to a memory stream. This is a pretty clever technique and I have deliberately separated the functionality of streaming the file from the functionality of creating the file so my Excel class can be reused used in non MVC applications.
namespace YourNamespaceGoesHere.Controllers.Results
{
using System;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using YourNamespaceGoesHere.Controllers.ControllerExtensions;
/// <summary>
/// Excel result class
/// </summary>
public class ExcelResult : ActionResult
{
/// <summary>
/// File Name.
/// </summary>
private string excelFileName;
/// <summary>
/// Sheet Name.
/// </summary>
private string excelWorkSheetName;
/// <summary>
/// Excel Row data.
/// </summary>
private IQueryable rowData;
/// <summary>
/// Excel Header Data.
/// </summary>
private string[] headerData = null;
/// <summary>
/// Row Data Keys.
/// </summary>
private string[] rowPointers = null;
/// <summary>
/// Action Result: Excel result constructor for returning values from rows.
/// </summary>
/// <param>Excel file name.</param>
/// <param>Excel worksheet name: default: sheet1.</param>
/// <param>Excel row values.</param>
public ExcelResult(string fileName, string workSheetName, IQueryable rows)
: this(fileName, workSheetName, rows, null, null)
{
}
/// <summary>
namespace YourNamespaceGoesHere.Controllers.Results
{
using System;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using YourNamespaceGoesHere.Controllers.ControllerExtensions;
/// <summary>
/// Excel result class
/// </summary>
public class ExcelResult : ActionResult
{
/// <summary>
/// File Name.
/// </summary>
private string excelFileName;
/// <summary>
/// Sheet Name.
/// </summary>
private string excelWorkSheetName;
/// <summary>
/// Excel Row data.
/// </summary>
private IQueryable rowData;
/// <summary>
/// Excel Header Data.
/// </summary>
private string[] headerData = null;
/// <summary>
/// Row Data Keys.
/// </summary>
private string[] rowPointers = null;
/// <summary>
/// Action Result: Excel result constructor for returning values from rows.
/// </summary>
/// <param>Excel file name.</param>
/// <param>Excel worksheet name: default: sheet1.</param>
/// <param>Excel row values.</param>
public ExcelResult(string fileName, string workSheetName, IQueryable rows)
: this(fileName, workSheetName, rows, null, null)
{
}
/// <summary>
/// Action Result: Excel result constructor for returning values from rows and headers.
/// </summary>
/// <param>Excel file name.</param>
/// <param>Excel worksheet name: default: sheet1.</param>
/// <param>Excel row values.</param>
/// <param>Excel header values.</param>
public ExcelResult(string fileName, string workSheetName, IQueryable rows, string[] headers)
: this(fileName, workSheetName, rows, headers, null)
{
}
/// <summary>
/// Action Result: Excel result constructor for returning values from rows and headers and row keys.
/// </summary>
/// <param>Excel file name.</param>
/// <param>Excel worksheet name: default: sheet1.</param>
/// <param>Excel row values.</param>
/// <param>Excel header values.</param>
/// <param>Key values for the rows collection.</param>
public ExcelResult(string fileName, string workSheetName, IQueryable rows, string[] headers, string[] rowKeys)
{
this.rowData = rows;
this.excelFileName = fileName;
this.excelWorkSheetName = workSheetName;
this.headerData = headers;
this.rowPointers = rowKeys;
}
/// <summary>
/// Gets a value for file name.
/// </summary>
public string ExcelFileName
{
get { return this.excelFileName; }
}
/// <summary>
/// Gets a value for file name.
/// </summary>
public string ExcelWorkSheetName
{
get { return this.excelWorkSheetName; }
}
/// <summary>
/// Gets a value for rows.
/// </summary>
public IQueryable ExcelRowData
{
get { return this.rowData; }
}
/// <summary>
/// Execute the Excel Result.
/// </summary>
/// <param>Controller context.</param>
public override void ExecuteResult(ControllerContext context)
{
MemoryStream stream = ExcelDocument.Create(this.excelFileName, this.excelWorkSheetName, this.rowData, this.headerData, this.rowPointers);
WriteStream(stream, this.excelFileName);
}
/// <summary>
/// Writes the memory stream to the browser.
/// </summary>
/// <param>Memory stream.</param>
/// <param>Excel file name.</param>
private static void WriteStream(MemoryStream memoryStream, string excelFileName)
{
HttpContext context = HttpContext.Current;
context.Response.Clear();
context.Response.AddHeader("content-disposition", String.Format("attachment;filename={0}", excelFileName));
memoryStream.WriteTo(context.Response.OutputStream);
memoryStream.Close();
context.Response.End();
}
}
}
Finally creating the Excel file. Just a note about static methods. I wanted to be sure that my Excel Document was thread safe so I created static methods at the expense of making this easily mockable. You’ll need to weight that decision on your own.
Any public static (Shared in Visual Basic) members of this type are thread safe. Any instance members are not guaranteed to be thread safe.
namespace YourNamespaceGoesHere.Controllers.ControllerExtensions
{
using System;
using System.IO;
using System.Linq;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Extensions;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
/// <summary>
/// Excel document.
/// </summary>
public static class ExcelDocument
{
/// <summary>
/// Default spread sheet name.
/// </summary>
private const string DefaultSheetName = "Sheet1";
/// <summary>
/// Create the exel document for streaming.
/// </summary>
/// <param>Excel file name.</param>
/// <param>Excel worksheet name: default: sheet1.</param>
/// <param>Row data to write.</param>
/// <param>Header data.</param>
/// <param>Row pointers.</param>
/// <returns>Memory stream.</returns>
public static MemoryStream Create(string documentName, string excelWorkSheetName, IQueryable rowData, string[] headerData, string[] rowPointers)
{
return CreateSpreadSheet(documentName, excelWorkSheetName, rowData, headerData, rowPointers, null);
}
/// <summary>
/// Create the spreadsheet.
/// </summary>
/// <param>Excel file name.</param>
/// <param>Excel worksheet name: default: sheet1.</param>
/// <param>Row data to write.</param>
/// <param>Header data.</param>
/// <param>Row pointers.</param>
/// <param>Style sheet.</param>
/// <returns>Memory stream.</returns>
private static MemoryStream CreateSpreadSheet(string documentName, string excelWorkSheetName, IQueryable rowData, string[] headerData, string[] rowPointers, Stylesheet styleSheet)
{
int rowNum = 0;
int colNum = 0;
int maxWidth = 0;
int minCol = 1;
int maxCol = rowPointers == null ? minCol : rowPointers.Length;
maxCol = maxCol == 1 && headerData == null ? 1 : headerData.Length;
MemoryStream xmlStream = SpreadsheetReader.Create();
SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(xmlStream, true);
SetSheetName(excelWorkSheetName, spreadSheet);
if (styleSheet == null)
{
SetStyleSheet(spreadSheet);
}
else
{
spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet = styleSheet;
spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.Save();
}
WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(spreadSheet, excelWorkSheetName);
WriteHeaders(headerData, out rowNum, out colNum, out maxWidth, spreadSheet, worksheetPart);
AddCellWidthStyles(Convert.ToUInt32(minCol), Convert.ToUInt32(maxCol), maxWidth, spreadSheet, worksheetPart);
if (rowPointers == null || rowPointers.Length == 0)
{
WriteRowsFromHeaders(rowData, headerData, rowNum, out maxWidth, spreadSheet, worksheetPart);
}
else
{
WriteRowsFromKeys(rowData, rowPointers, rowNum, out maxWidth, spreadSheet, worksheetPart);
}
// Save to the memory stream
SpreadsheetWriter.Save(spreadSheet);
spreadSheet.Close();
spreadSheet.Dispose();
return xmlStream;
}
/// <summary>
/// Set the name of the spreadsheet.
/// </summary>
/// <param>Spread sheet name.</param>
/// <param>Spread sheet.</param>
private static void SetSheetName(string excelSpreadSheetName, SpreadsheetDocument spreadSheet)
{
excelSpreadSheetName = excelSpreadSheetName ?? DefaultSheetName;
Sheet ss = spreadSheet.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == DefaultSheetName).SingleOrDefault<Sheet>();
ss.Name = excelSpreadSheetName;
}
/// <summary>
/// Add cell width styles.
/// </summary>
/// <param>Minimum column index.</param>
/// <param>Maximum column index.</param>
/// <param>Maximum column width.</param>
/// <param>Spread sheet.</param>
/// <param>Work sheet.</param>
private static void AddCellWidthStyles(uint minCol, uint maxCol, int maxWidth, SpreadsheetDocument spreadSheet, WorksheetPart workSheetPart)
{
Columns cols = new Columns(new Column() { CustomWidth = true, Min = minCol, Max = maxCol, Width = maxWidth, BestFit = false });
workSheetPart.Worksheet.InsertBefore<Columns>(cols, workSheetPart.Worksheet.GetFirstChild<SheetData>());
}
/// <summary>
/// Set the style sheet.
// Note: Setting the style here rather than passing it in ensures that all worksheets will have a common user interface design.
/// </summary>
/// <param>Spread sheet to change.</param>
private static void SetStyleSheet(SpreadsheetDocument spreadSheet)
{
// Note: Setting the style here rather than passing it in ensures that all worksheets will have a common user interface design.
Stylesheet styleSheet = spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet;
styleSheet.Fonts.AppendChild(
new Font(new FontSize() { Val = 11 }, new Color() { Rgb = "FFFFFF" }, new FontName() { Val = "Arial" }));
styleSheet.Fills.AppendChild(new Fill()
{
PatternFill = new PatternFill()
{
PatternType = PatternValues.Solid,
BackgroundColor = new BackgroundColor() { Rgb = "D8D8D8" }
}
});
spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.Save();
}
/// <summary>
/// Save the styl for worksheet headers.
/// </summary>
/// <param>Cell location.</param>
/// <param>Spreadsheet to change.</param>
/// <param>Worksheet to change.</param>
private static void SeatHeaderStyle(string cellLocation, SpreadsheetDocument spreadSheet, WorksheetPart workSheetPart)
{
Stylesheet styleSheet = spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet;
Cell cell = workSheetPart.Worksheet.Descendants<Cell>().Where(c => c.CellReference == cellLocation).FirstOrDefault();
if (cell == null)
{
throw new ArgumentNullException("Cell not found");
}
cell.SetAttribute(new OpenXmlAttribute("", "s", "", "1"));
OpenXmlAttribute cellStyleAttribute = cell.GetAttribute("s", "");
CellFormats cellFormats = spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats;
// pick tthe first cell format.
CellFormat cellFormat = (CellFormat)cellFormats.ElementAt(0);
CellFormat cf = new CellFormat(cellFormat.OuterXml);
cf.FontId = styleSheet.Fonts.Count;
cf.FillId = styleSheet.Fills.Count;
cellFormats.AppendChild(cf);
int a = (int)styleSheet.CellFormats.Count.Value;
cell.SetAttribute(cellStyleAttribute);
cell.StyleIndex = styleSheet.CellFormats.Count;
workSheetPart.Worksheet.Save();
}
/// <summary>
/// Replace special characters.
/// </summary>
/// <param>Value to input.</param>
/// <returns>Value with special characters replaced.</returns>
private static string ReplaceSpecialCharacters(string value)
{
value = value.Replace("’", "'");
value = value.Replace("“", "\"");
value = value.Replace("”", "\"");
value = value.Replace("–", "-");
value = value.Replace("…", "...");
return value;
}
/// <summary>
/// Write values to the spreadsheet.
/// </summary>
/// <param>Row Column Value.</param>
/// <param>Value to write.</param>
/// <param>Spreadsheet to write to. </param>
/// <param>Worksheet to write to. </param>
private static void WriteValues(string cellLocation, string strValue, SpreadsheetDocument spreadSheet, WorksheetPart workSheet)
{
WorksheetWriter workSheetWriter = new WorksheetWriter(spreadSheet, workSheet);
int intValue = 0;
if (strValue.Contains("$"))
{
strValue = strValue.Replace("$", "");
strValue = strValue.Replace(",", "");
workSheetWriter.PasteValue(cellLocation, strValue, CellValues.Number);
}
else if (int.TryParse(strValue, out intValue))
{
workSheetWriter.PasteValue(cellLocation, strValue, CellValues.Number);
}
else if (string.IsNullOrEmpty(strValue))
{
workSheetWriter.PasteText(cellLocation, strValue);
}
else
{
workSheetWriter.PasteText(cellLocation, strValue);
}
}
/// <summary>
/// Write the excel rows for the spreadsheet.
/// </summary>
/// <param>Excel row values.</param>
/// <param>Excel row-key values.</param>
/// <param>Row number.</param>
/// <param>Max width.</param>
/// <param>Spreadsheet to write to. </param>
/// <param>Worksheet to write to. </param>
private static void WriteRowsFromKeys(IQueryable rowData, string[] rowDataKeys, int rowNum, out int maxWidth, SpreadsheetDocument spreadSheet, WorksheetPart workSheet)
{
maxWidth = 0;
foreach (object row in rowData)
{
int colNum = 0;
foreach (string rowKey in rowDataKeys)
{
string strValue = row.GetType().GetProperty(rowKey).GetValue(row, null).ToString();
strValue = ReplaceSpecialCharacters(strValue);
maxWidth = strValue.Length > maxWidth ? strValue.Length : maxWidth;
string cellLocation = string.Format("{0}{1}", GetColumnLetter(colNum.ToString()), rowNum);
ExcelDocument.WriteValues(cellLocation, strValue, spreadSheet, workSheet);
colNum++;
}
rowNum++;
}
}
/// <summary>
/// Convert column number to alpha numeric value.
/// </summary>
/// <param>Column number.</param>
/// <returns>ASCII value for number.</returns>
private static string GetColumnLetter(string colNumber)
{
if (string.IsNullOrEmpty(colNumber))
{
throw new ArgumentNullException(colNumber);
}
string colName = null;
try
{
for (int i = 0; i < colNumber.Length; i++)
{
string colValue = colNumber.Substring(i, 1);
int asc = Convert.ToInt16(colValue) + 65;
colName += Convert.ToChar(asc);
}
}
finally
{
colName = colName ?? "A";
}
return colName;
}
/// <summary>
/// Write the values for the rows from headers.
/// </summary>
/// <param>Excel row values.</param>
/// <param>Excel header values.</param>
/// <param>Row number.</param>
/// <param>Max width.</param>
/// <param>Spreadsheet to write to. </param>
/// <param>Worksheet to write to. </param>
private static void WriteRowsFromHeaders(IQueryable rowData, string[] headerData, int rowNum, out int maxWidth, SpreadsheetDocument spreadSheet, WorksheetPart workSheet)
{
WorksheetWriter workSheetWriter = new WorksheetWriter(spreadSheet, workSheet);
maxWidth = 0;
foreach (object row in rowData)
{
int colNum = 0;
foreach (string header in headerData)
{
string strValue = row.GetType().GetProperty(header).GetValue(row, null).ToString();
strValue = ReplaceSpecialCharacters(strValue);
maxWidth = strValue.Length > maxWidth ? strValue.Length : maxWidth;
string cellLocation = string.Format("{0}{1}", GetColumnLetter(colNum.ToString()), rowNum);
ExcelDocument.WriteValues(cellLocation, strValue, spreadSheet, workSheet);
colNum++;
}
rowNum++;
}
}
/// <summary>
/// Write the excel headers for the spreadsheet.
/// </summary>
/// <param>Excel header values.</param>
/// <param>Row number.</param>
/// <param>Column Number.</param>
/// <param>Max column width</param>
/// <param>Maximum Column Width to write to. </param>
/// <param>Worksheet to write to. </param>
private static void WriteHeaders(string[] headerData, out int rowNum, out int colNum, out int maxWidth, SpreadsheetDocument spreadSheet, WorksheetPart workSheet)
{
rowNum = 1;
colNum = 0;
maxWidth = 0;
foreach (string header in headerData)
{
string strValue = ReplaceSpecialCharacters(header);
string cellLocation = string.Format("{0}{1}", GetColumnLetter(colNum.ToString()), rowNum);
maxWidth = strValue.Length > maxWidth ? strValue.Length : maxWidth;
ExcelDocument.WriteValues(cellLocation, strValue, spreadSheet, workSheet);
SeatHeaderStyle(cellLocation, spreadSheet, workSheet);
colNum++;
}
rowNum++;
}
}
}
