Introduction
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.
Background
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 started 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 from Microsoft, the OOXML extensions from James Westgate, and I was off and running.
- More information on OpenXml and file formats are available here.
- Get the Open XML SDK here.
- Get the OOXML API here.
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 Excel file and sheet.
Using the Code
Extending the Controller
If you have read Steve or Phil’s blog, 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;
public static class ExcelControllerExtensions
{
public static ActionResult Excel(this Controller controller,
string fileName, string excelWorkSheetName, IQueryable rows)
{
return new ExcelResult(fileName, excelWorkSheetName, rows);
}
public static ActionResult Excel(this Controller controller,
string fileName, string excelWorkSheetName,
IQueryable rows, string[] headers)
{
return new ExcelResult(fileName, excelWorkSheetName, rows, headers);
}
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 three 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 an
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, an
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.
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 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;
public class ExcelResult : ActionResult
{
private string excelFileName;
private string excelWorkSheetName;
private IQueryable rowData;
private string[] headerData = null;
private string[] rowPointers = null;
public ExcelResult(string fileName, string workSheetName, IQueryable rows)
: this(fileName, workSheetName, rows, null, null)
{
}
namespace YourNamespaceGoesHere.Controllers.Results
{
using System;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using YourNamespaceGoesHere.Controllers.ControllerExtensions;
public class ExcelResult : ActionResult
{
private string excelFileName;
private string excelWorkSheetName;
private IQueryable rowData;
private string[] headerData = null;
private string[] rowPointers = null;
public ExcelResult(string fileName, string workSheetName, IQueryable rows)
: this(fileName, workSheetName, rows, null, null)
{
}
public ExcelResult(string fileName, string workSheetName,
IQueryable rows, string[] headers)
: this(fileName, workSheetName, rows, headers, null)
{
}
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;
}
public string ExcelFileName
{
get { return this.excelFileName; }
}
public string ExcelWorkSheetName
{
get { return this.excelWorkSheetName; }
}
public IQueryable ExcelRowData
{
get { return this.rowData; }
}
public override void ExecuteResult(ControllerContext context)
{
MemoryStream stream = ExcelDocument.Create(this.excelFileName,
this.excelWorkSheetName, this.rowData,
this.headerData, this.rowPointers);
WriteStream(stream, this.excelFileName);
}
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 mock-able. You'll need to weigh 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;
public static class ExcelDocument
{
private const string DefaultSheetName = "Sheet1";
public static MemoryStream Create(string documentName,
string excelWorkSheetName, IQueryable rowData,
string[] headerData, string[] rowPointers)
{
return CreateSpreadSheet(documentName, excelWorkSheetName,
rowData, headerData, rowPointers, null);
}
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);
}
SpreadsheetWriter.Save(spreadSheet);
spreadSheet.Close();
spreadSheet.Dispose();
return xmlStream;
}
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;
}
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>());
}
private static void SetStyleSheet(SpreadsheetDocument spreadSheet)
{
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();
}
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;
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();
}
private static string ReplaceSpecialCharacters(string value)
{
value = value.Replace("’", "'");
value = value.Replace(""", "\"");
value = value.Replace(""", "\"");
value = value.Replace("–", "-");
value = value.Replace("…", "...");
return value;
}
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);
}
}
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++;
}
}
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;
}
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++;
}
}
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++;
}
}
Points of Interest
I hope you found this useful. I'm not going to go into great detail here about the OpenXml API. This is all working code, so I would actually recommend you step through the code yourself and get acquainted with Microsoft’s OpenXml developer website.
Read more at my blog here.
History
- 24th September, 2009: Initial post
- 25th September, 2009: Added source files
Chris Aloia lives in the Seattle area and works as a Senior Software Development Engineer for Microsoft Corporation.