Building an Excel Document with CSharp and OpenXML

One of my customers has a requirement of generating an excel document on demand based on a daily feed of financial information. We were using SQL Server Reporting Services (SSRS) on premise, but in our move to AWS we wanted to remove that dependency for several reasons. First and formost, the report was basically a filtered data dump of what was in a table. Pretty simple and straight forward and didn’t need the overhead of SSRS. Secondly, in order to reduce costs in the cloud, we wanted to move from SQL Server as our data store to Aurora MySQL. While we probably could have made SSRS work with MySQL, it really wasn’t in our long term plan. To make a long story short, we rewrote the report and built it into our API application. On our first pass, we used the ClosedXML package. While this is a very powerful abstraction for generating an Excel document and worked well as a first pass, we quickly ran into an issue when compiling our app for Lambda. In short, the DocumentFormat.OpenXML dependency isn’t (wasn’t) up to date with the latest and we couldn’t optimize the dependencies for the AWS Lambda runtime. Given this limitation, we went directly to the source and used DocumentFormat.OpenXML.

In general, the documentation from Microsoft and examples on the internet are a mess of speghetti code which are difficult to follow. My goal here is to simplify and follow some coding best practices such as the single responsibility principle.

Let’s go over some tips, then we can get into the code.

Tips: #

• The first two Fills defined in the stylesheet are reserved by excel. The first is the default and uses PatternValues.None; the second uses PatternValues.Gray125. After defining these in your stylesheet, add your styles.
• To fill a cell with a solid color, use PatternValues.Solid and set the ForegroundColor to the desired color. Originally we thought we should be using BackgroundColor, but it turns out ForegroundColor and BackgroundColor work together when using a pattern defined by your PatternValues definition. A little confusing to us unfamiliar with the library.
• The CellFormats collection cannot be empty. It must have at least one value in the collection.
• Fills, Fonts, and Borders collections cannot be empty. They must have at least one value in the collection.
• The first item in the Borders collection is the default. Define an “empty” border object, then create the customized borders.
• Use the following creation order when defining your document
  • Sheet Views
  • Sheet View Formatting such as gridlines
  • Columns
  • Sheet Data (always last)
• When you map your settings to your columns, you MUST use the index of the item in the collection you wish to apply.

Here’s the code:

BuildDocument.cs #

BuildDocument.cs assembles the data with the styles and layout.

StyleSheetBuilder.cs #

StyleSheetBuilder.cs creates the stylesheet to be applied.

If you have questions, connect with me on twitter