As most of us, I already know my next week work schedule. It includes writing some actions in respond to a requirement for excel exports. It’s nothing new and its’s relatively easy with libraries like NPOI: you just create your excel on the fly and return a FileResult.

Read how to create an excel on the fly inside your action here: http://www.talkingdotnet.com/import-export-excel-asp-net-core-2-razor-pages/

And that could be the end of story for that requirement, but while walking home for the weekend I remembered about the XmlSerializerOutputFormatter I read on Microsoft Docs. The idea was to write my own ExcelOutputFormatter to respond to client requests with an Excel instead of a JSON.

OutputFormatter

ASP.NET Core has built-in support for formatting response data, using fixed formats or in response to client requests. Fixed formats could be achieved by using specific action result types like JsonResult or ContentResult, which return application/json or text/plain as Content-Type regardless of client preferences. On the other hand, if we want to format our data based on a client request (e.g. based on the Accept header), we should choose ObjectResult as an action result type that has content negotiation built in to it.

Just a bit off-topic, in total there are 19 action result types that derive from ActionResult. One of them is the ObjectResult that we want, and from that another 16 action result types derive! Just check a bigger version of the graph below, to see the entire ActionResult tree.

Returning to our topic, since we want to use ObjectResult for its build-in content negotiation and we want to avoid returning an ObjectResult for all our actions, we will take advantage of another .NET framework goody:

When returning a model type (a class you’ve defined as your data transfer type), the framework will automatically wrap it in an ObjectResult for you.

This way, our actions will return a typed result and framework will do the dirty job of choosing the right formatter based on the Accept header. All we have to do to take advantage of the ObjectResult build-in goody, is inheriting from the abstract OutputFormatter class and implement what we need.

By studying the OutputFormatter, we can see a few interesting things that we can use in our formatter:

1. The only abstract method is the WriteResponseBodyAsync, so we definitely have to implement this.
2. The virtual WriteAsync method acts as a wrapper of WriteResponseHeaders and WriteResponseBodyAsync. We want to return Content Disposition Headers, so we will also need to override WriteResponseHeaders
3. The virtual CanWriteType always returns true, but our formatter can only be applied over a List of objects and not just an object (each excel row will be an object). We can override this method and add this check.
4. There is a SupportedMediaTypes property which is used in GetSupportedContentTypes to check if a formatter is supporting the requested media type. We should add our Media Type in this Collection.

By adding all our observations in a class, we end up with the following skeleton of what we want to achieve:

public class ExcelOutputFormatter : OutputFormatter
{
    public ExcelOutputFormatter()
    {
        //TODO: Add the supported media types
    }

    public bool CanWriteType(OutputFormatterCanWriteContext context)
    {
        //TODO: Check if context.ObjectType is IEnumerable<object>
    }

    public override Task WriteResponseBodyAsync(OutputFormatterWriteContext context)
    {
        //TODO: Write the transformation from context.Object to an XLSX
    }
}

You can continue reading more details about Custom Formatters and about how to Format response data!

Open XML SDK 2.5 for Office

Although the first library that came to mind was NPOI, I thought I could grab the chance of learning something new and try the well documented Microsoft Open XML SDK than a port of a java project. The SDK provides a strongly-typed way to manipulate documents that adhere to the Office Open XML File Formats Specification.

A file with the XLSX file extension is a Microsoft Excel Open XML Format Spreadsheet file. It’s an XML-based spreadsheet file created by Microsoft Excel version 2007 and later. Rename .xlsx to .zip and use your favourite ZIP tool explore your Excel Workbooks file structure.

Unfortunately, after some time of reading, Microsoft Open XML SDK turned out to be unnecessary complicated -a lot more that NPOI- and the reason is that the SDK is an one-to-one representation of the XML specification. You end up writing code that seems meaningless. To back up what I say, here is the code I had to write to create an empty Excel in a MemoryStream.

var ms = new MemoryStream();

using (SpreadsheetDocument spreedDoc = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
{
    //openxml stuff
    var wbPart = spreedDoc.AddWorkbookPart();
    wbPart.Workbook = new Workbook();
    var worksheetPart = wbPart.AddNewPart<WorksheetPart>();
    var sheetData = new SheetData();
    worksheetPart.Worksheet = new Worksheet(sheetData);
    wbPart.Workbook.AppendChild<Sheets>(new Sheets());
    var sheet = new Sheet()
    {
        Id = wbPart.GetIdOfPart(worksheetPart),
        SheetId = 1,
        Name = "Sheet1"
    };
    var workingSheet = ((WorksheetPart)wbPart.GetPartById(sheet.Id)).Worksheet;

    //Do something with the working sheet

    wbPart.Workbook.Sheets.AppendChild(sheet);
    wbPart.Workbook.Save();
}

return ms;

There is so much code that looks redundant (specially the WorkbookPart and WorksheetPart) and all of that just to get access to a newly created Excel Sheet. I think this SDK definitely needs a wrapper and ideally, a fluent interface wrapper .

Anyway, as above-mentioned, the plus of this API is that has a very good documentation. I only had to browser just a few minutes more on Microsoft Docs to find out how to actually insert text into a cell:

var row = new Row();
foreach (...)
{
    var cell = new Cell()
    {
        DataType = CellValues.InlineString
    };

    var inlineString = new InlineString();
    inlineString.AppendChild(new Text(text));
    
    row.AppendChild(
        cell.AppendChild(inlineString);
    );
}

Writing the ExcelOutputFormatter

There is no much left to research at this stage, since we already know everything we need. The complete code that follows is the fully functional formatter with two asterisks:

• We should decouple the dependency our formatter has on Microsoft Open XML SDK.
• We should set the Cell.DataType to the correct CellValues Enum, based on our propValue type

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using Microsoft.AspNetCore.Mvc.Formatters;
using Microsoft.Net.Http.Headers;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Threading.Tasks;

namespace MyCodePad.Formatters
{
    public class ExcelOutputFormatter : OutputFormatter
    {
        public ExcelOutputFormatter()
        {
            SupportedMediaTypes.Add(MediaTypeHeaderValue.Parse("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));
        }

        public bool CanWriteType(OutputFormatterCanWriteContext context)
        {
            return typeof(IEnumerable<object>).IsAssignableFrom(context.ObjectType);
        }


        public override Task WriteResponseBodyAsync(OutputFormatterWriteContext context)
        {
            if (context == null)
            {
                throw new ArgumentNullException(nameof(context));
            }

            var excelStream = CreateExcelFile(context.Object as IEnumerable<object>);

            var response = context.HttpContext.Response;

            response.ContentLength = excelStream.Length;
            return response.Body.WriteAsync(excelStream.ToArray()).AsTask();
        }

        public override void WriteResponseHeaders(OutputFormatterWriteContext context)
        {
            if (context == null)
            {
                throw new ArgumentNullException(nameof(context));
            }

            var fileName = (context.Object as IEnumerable<object>).GetType().GetGenericArguments()[0].Name;

            context.HttpContext.Response.Headers["Content-Disposition"] =
                new ContentDispositionHeaderValue("attachment")
                {
                    FileName = fileName + ".xlsx"
                }.ToString();
            context.HttpContext.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        }

        private MemoryStream CreateExcelFile(IEnumerable<object> data)
        {
            var ms = new MemoryStream();

            using (SpreadsheetDocument spreedDoc = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
            {
                //openxml stuff
                var wbPart = spreedDoc.AddWorkbookPart();
                wbPart.Workbook = new Workbook();
                var worksheetPart = wbPart.AddNewPart<WorksheetPart>();
                var sheetData = new SheetData();
                worksheetPart.Worksheet = new Worksheet(sheetData);
                wbPart.Workbook.AppendChild<Sheets>(new Sheets());
                var sheet = new Sheet()
                {
                    Id = wbPart.GetIdOfPart(worksheetPart),
                    SheetId = 1,
                    Name = "Sheet1"
                };
                var workingSheet = ((WorksheetPart)wbPart.GetPartById(sheet.Id)).Worksheet;

                //get model properties
                var props = new List<PropertyInfo>(data.First().GetType().GetProperties());

                //header
                var headerRow = new Row();
                foreach (var prop in props)
                {
                    headerRow.AppendChild(
                        GetCell(prop.Name)
                    );
                }
                sheetData.AppendChild(headerRow);

                //body
                foreach (var record in data)
                {
                    var row = new Row();
                    foreach (var prop in props)
                    {
                        var propValue = prop.GetValue(record, null).ToString();
                        row.AppendChild(
                            GetCell(propValue)
                        );
                    }
                    sheetData.AppendChild(row);
                }
                wbPart.Workbook.Sheets.AppendChild(sheet);
                wbPart.Workbook.Save();
            }

            return ms;
        }

        private Cell GetCell(string text)
        {
            var cell = new Cell()
            {
                DataType = CellValues.InlineString
            };
            var inlineString = new InlineString();
            inlineString.AppendChild(new Text(text));

            cell.AppendChild(inlineString);
            return cell;
        }
    }
}

Configuring ExcelOutputFormatter

When an Accept header appears in a request, the framework will try to find a formatter that can produce a response in one of the formats specified by the accept header, and to do this it will iterate through the FormatterCollection. This is a collection accessible through MVC’s configuration in Startup.cs, and formatters added there are evaluated in the order you insert them (the first one takes precedence).

Since no other available formatter can respond to an Accept:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet header, the ExcelOutputFormatter can be added at the end:

services.AddMvc(options =>
{
    options.OutputFormatters.Add(new ExcelOutputFormatter());
});

There are two additional ways to either force a particular response format in an action, or by allowing the client to request a particular format from the URL, both in Microsoft Docs.