I find with many of our projects that we need to provide Excel exports of tabular data for our end users. We have many options for creating these exports, but there are so many options that it becomes hard to understand the "right" solution for the right time.
When evaluating options, we found various solutions, from open-source to expensive closed source solutions. Microsoft also publishes the OpenXml library for the creation of these files.
The Common Pattern
After evaluating our projects, we found that almost all Excel generation needs could be met with a consistent pattern. We were always exporting a List<T> and every report needed a header row. Upon further review, we also needed to add an optional title & subtitle and occasionally create a file with multiple sheets.
Armed with this need, we started reviewing the existing libraries and tried to work on a simple, repeatable solution that required low/no custom coding per export.
Introducing NetCore.Utilities.Spreadsheet
All of our testing and research lead to a solution utilizing OpenXml and NetCore.Utilities.Spreadsheet was born. The actual syntax for creating the documents was a bit cumbersome and would be a large amount of code to duplicate from project to project so we decided to create a simple utility and make it available for open-source consumption.
Installation
The installation package is available via NuGet and can be installed using Install-Package NetCore.Utilities.Spreadsheet, once installed you need to add the following single line to your Startup.cs file within the ConfigureServices method.
services.UseIcgNetCoreUtilitiesSpreadsheet()
Usage
Once installed you can now injectISpreadsheetGenerator to gain access to the generation utility. For ease of use and to support all types of projects the API's exposed by the SpreadsheetGenerator return byte arrays so you can do anything you want with the file.
Exporting your data is as simple as providing an export configuration to the Generator and doing something with the resultant bytes. The following example shows the basic exporting options & configurations.
var exportDefinition = new SpreadsheetConfiguration
{
RenderTitle = true,
DocumentTitle = "Sample Export of 100 Records",
RenderSubTitle = true,
DocumentSubTitle = "Showing the full options",
ExportData = GetSampleExportData(100),
WorksheetName = "Sample"
};
var fileContent = exportGenerator.CreateSingleSheetSpreadsheet(exportDefinition);
You can see from here that the overall export process is very simple. We provide configuration for the exported sheet which includes title, subtitle, and data information. Then we simply request that our injected generator create the file.
Performance
Since we are utilizing the OpenXml library, and no other platforms the performance of this library excels, being more than 3 times faster than all other systems that we tested.
Customizing the Spreadsheet
The current release of the application supports two basic configuration options; custom column headers and basic date formatting. If you add a [DisplayName("")] attribute to your individual properties on the exported data the name here will be used for display on the export.
We find this library to be incredibly useful, and have made it open source to allow others to review/contribute/benefit from this. Please direct any suggestions or improvements to the project on GitHub.