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.
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
ContentResult, which return
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
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
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:
- The only abstract method is the
WriteResponseBodyAsync, so we definitely have to implement this.
- The virtual
WriteAsyncmethod acts as a wrapper of
WriteResponseBodyAsync. We want to return Content Disposition Headers, so we will also need to override
- The virtual
CanWriteTypealways 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.
- There is a
SupportedMediaTypesproperty which is used in
GetSupportedContentTypesto 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:
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
There is so much code that looks redundant (specially the
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:
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.DataTypeto the correct CellValues Enum, based on our
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:
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.