Easy Excel Exports with Asp.net Core

CSV too.

TL;DR;

I babble on about an API I had to add export functionality to, flirt with how I did it, then do a shoddy job of linking you up with resources, so you can do the same.

Yesterday, I took on the task of adding data export functionality to via a Web API built with Asp.net Core.

My goal was to find an elegant system that would not require much change to the way the API worked, and would not require writing boilerplate code when I need to add export for a new model.

Some Explanation

Here’s a typical GET endpoint:

/api/persons

This endpoint returns a collection of Person entities, each belonging to a Person model which contains properties like a FirstName, LastName, and a CountryId, showing it’s related to a Country model.

In my search, I stumbled upon EPPlus, a powerful library for working with spreadsheets in dotnet. I liked that it’s powerful, and does a lot, but working with it felt very procedural, meaning there would definitely be lots of boilerplate for every endpoint I wanted to export.

Imagine having to write this code in many places:

worksheet.Cells[1, 1].Value = person.FirstName;
worksheet.Cells[1, 2].Value = person.LastName;

I knew I had to come up with some wrapper around EPPlus, and even though I did, while writing this article, I became too lazy, so I googled around, and found this library that does just what I spent hours yesterday doing.

I think I may have missed in my google searches, because I still wasn’t sure what I was looking for. Today, the word “fluent” came to mind, and googling “EPPlus Fluent” gave that repo as the first search result.

You lucky duck, you!

Now, you don’t have to look at my ugly code.

I also would have preferred to not create a route for each data export endpoint, like /api/persons/excel for excel exports, and /api/persons/csv for csv exports.

Asp.net Core Output Formatters

I’d used Output Formatters in Asp.net Web APIs, and I was happy to see that it was available in Asp.net Core. If you’re not sure what an output formatter is, it makes sure your endpoint can change the format it serves its data in, depending on the request’s Accept header.

So, when passed a text/csv header, my API should return its data as comma-separated values, and when passed an application/ms-excel header, one can expect to get an excel file in return.

Okay, okay … I’ll show you some code this time, but only for the excel output formatter. Check the official docs for more.

CSV go dey alright lass lass.

In other news, this is what postman thinks an excel file looks like.