First some background. During a recent project we had built an ASP.NET MVC 3 application that allowed users to display lists of data filtering by search criteria. It was all pretty standard stuff, controller actions taking search parameters, requesting data from a repository and passing this data as model content in ViewResult for display. We had a fair number of these actions defined when the customer requested the capability to download the result lists into an excel format for offline analysis. So we wanted to come up with a solution that re-used the existing actions, with minimal impact.
Firstly we built an ActionResult that would return the model data in an xlsx format. This was actually easier than I expected thanks to the Open XML SDK. The solution was really cheap:
public class DownloadViewAsExcelResult : PartialViewResult
{
public DownloadViewAsExcelResult(string viewName, object model)
{
base.ViewName = viewName;
base.ViewData.Model = model;
}
public override void ExecuteResult(ControllerContext context)
{
StringBuilder builder = new StringBuilder();
StringWriter writer = new StringWriter(builder);
ViewEngineResult result = null;
if (View == null)
{
result = FindView(context);
View = result.View;
}
ViewContext viewContext = new ViewContext(context, View, ViewData, TempData, writer);
View.Render(viewContext, writer);
XDocument format = XDocument.Load(new StringReader(builder.ToString()));
Stream xlsxStream = new SpreadsheetBuilder().FromFormatXml(format);
WriteFile(context.HttpContext, xlsxStream);
if (result != null)
result.ViewEngine.ReleaseView(context, View);
}
private static void WriteFile(HttpContextBase context, Stream content)
{
context.Response.Clear();
context.Response.AddHeader("content-disposition", "attachment;filename=download.xlsx");
context.Response.Charset = "";
context.Response.Cache.SetCacheability(HttpCacheability.NoCache);
context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
content.CopyTo(context.Response.OutputStream);
context.Response.End();
}
}
It extends the PartialViewResult to allow location of a named partial view; this view renders the model as an xml document structured into a known format so that it can be easilt built into a spreadsheet and returned to the response. The re-use of view engine and razor was pragmatic – seemed overkill to add anything else! The format used in this case was:
<book>
<sheet name="mandatory sheet tab name" header="optional header and footer text">
<row>
<cell>@Html.DisplayFor(m => m.Property)</cell>
</row>
</sheet>
</book>
Really simple – a book element containing sheets, which in turn contain rows with cells of data. With this format the spreadhseet builder just reads the xml writing the output to an Open Xml SDK SpreadsheetDocument like so:
public class SpreadsheetBuilder
{
public Stream FromFormatXml(XDocument format)
{
MemoryStream stream = new MemoryStream();
using (SpreadsheetDocument document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookpart = document.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
document.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
var sheets = from element in format.Elements("book").Elements("sheet") select element;
foreach (var element in sheets)
{
AddWorksheet(document, element);
}
}
stream.Position = 0;
return stream;
}
private void AddWorksheet(SpreadsheetDocument document, XElement sheetFormat)
{
SheetData sheetData = BuildSheetData(sheetFormat);
WorksheetPart worksheetPart = document.WorkbookPart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(sheetData);
XAttribute headerAttribute = sheetFormat.Attribute("header");
if (headerAttribute != null)
worksheetPart.Worksheet.AppendChild<HeaderFooter>(CreateHeaderFooter(headerAttribute.Value));
Sheets sheets = document.WorkbookPart.Workbook.Descendants<Sheets>().First();
XAttribute nameAttribute = sheetFormat.Attribute("name");
Sheet sheet = new Sheet()
{
SheetId = (UInt32)(sheets.Count() + 1),
Id = document.WorkbookPart.GetIdOfPart(worksheetPart),
Name = (nameAttribute == null) ? "Sheet " + (sheets.Count() + 1) : nameAttribute.Value
};
sheets.AppendChild(sheet);
}
private HeaderFooter CreateHeaderFooter(string message)
{
HeaderFooter header = new HeaderFooter();
OddHeader oddHeader = new OddHeader();
oddHeader.Text = "&C" + message;
OddFooter oddFooter = new OddFooter();
oddFooter.Text = "&C" + message;
header.AppendChild<OddHeader>(oddHeader);
header.AppendChild<OddFooter>(oddFooter);
return header;
}
private SheetData BuildSheetData(XElement sheetFormat)
{
SheetData sheetData = new SheetData();
int rowIndex = 0;
var rows = from element in sheetFormat.Elements("row") select element;
foreach (var rowElement in rows)
{
rowIndex++;
Row row = new Row() { RowIndex = (UInt32)rowIndex };
var cells = from element in rowElement.Elements("cell") select element;
foreach (var cellElement in cells)
{
Cell c = new Cell { DataType = CellValues.InlineString };
InlineString inlineString = new InlineString();
Text t = new Text { Text = cellElement.Value };
inlineString.AppendChild(t);
c.AppendChild(inlineString);
row.AppendChild(c);
}
sheetData.AppendChild(row);
}
return sheetData;
}
}
Now all that was needed was a mechanism to use this result – for this we chose to add an ActionFilterAttribute to each action supporting excel download. This attribute just checks for the existence of a format value equal to “excel”, replacing the result with an instance of our DownloadViewAsExcelResult with view name changed to read from Export sub folder in views when found.
public class ExcelViewDownloadAttribute : ActionFilterAttribute
{
public string ExportViewName { get; set; }
public override void OnActionExecuted(ActionExecutedContext filterContext)
{
base.OnActionExecuted(filterContext);
object model = filterContext.Controller.ViewData.Model;
if (model == null)
return;
ValueProviderResult value = filterContext.Controller.ValueProvider.GetValue("format");
if (value != null && value.AttemptedValue.Equals("excel", StringComparison.InvariantCultureIgnoreCase))
{
var exportView = GetExportViewName(filterContext.ActionDescriptor.ActionName);
DownloadViewAsExcelResult result = new DownloadViewAsExcelResult(exportView, model);
filterContext.Result = result;
}
}
private string GetExportViewName(string actionName)
{
if (string.IsNullOrEmpty(ExportViewName))
ExportViewName = actionName;
return "Export/" + ExportViewName;
}
}
This attribute is then added (along with export view) to all actions requiring excel download support:
[ExcelViewDownload(ExportViewName = "Index")]