“Can we have an export to Excel feature?” is a common refrain among Business Analysts and Product Owners. Over the years, (dotnet) developers have found clever ways to solve this problem.
- Create
csvfiles instead. Unlike a true Excel file, CSV files are simpler text-based files that separate data elements using commas. And unlike an Excel file, you can open and read them in any text-editor. As an added bonus, on machines with Microsoft Office installed, Excel usually takes over the responsibility of opening CSV files so end-users usually don’t see much of a difference. This works in basic scenarios where you simply want to export out some tabular data but if you want to do any sort of formatting, formulas, visualizations, multiple sheets, you’re out of luck. - Create an HTML table and save it with an
xlsextension. This was another clever trick that developers used over the years but it doesn’t work with the newerxlsxextension. Also, Excel will display a warning message that something could be fishy with the file. - Using SQL Server Reporting Services (SSRS) / RDLC files. This was a robust solution for many years if you developed .NET applications for the enterprise. However, this was a Windows-centric solution and doesn’t work too well in the newer, leaner cross-platform dotnet.
- Using the Office engine. Yes, in on-prem environments or any setup where you have actual access to the server, you can certainly install the Microsoft Office redistributable and write some automation around that. However, this would require you to have admin level access to your server infrastructure and of course, it would only work on a Windows machine.
- Go with third-party libraries. You can find a handful of worthy options by searching on NuGet. EPPlus has a large following but keep in mind that they have changed their licensing model. If your use-case is personal, it’s still free but if you are using it for your company projects, you should obtain a valid commercial license. If you are building an app that generates Excel files, it’s more than likely not for personal use.
Recently, I came across another open-source and free package – NPOI. This one is still free and with over 20 million downloads (as of the time of this writing, July 2022), it is one worth considering. The API is a bit clunky but provides a working, free solution. Let’s look at a simple example.
Prepare Your Data(Table)
One of the quickest and cleanest way to generate your Excel file is to first prepare your data in the form of a DataTable. A DataTable in many ways has an analogous structure to a spreadsheet. It has rows and columns and column headings. Once your data is in that form, you can create a reusable method that can iterate over that structure and generate your Excel sheet. If you’re using System.Data for data access, you are likely dealing with your data in DataSet, DataTable and DataReader objects and this structure should be very familiar and comfortable territory. If on the other hand, you’re using an ORM or data access method where you’re dealing with higher level objects such as an IEnumerable<T>, you’ll want to write a helper method to convert that collection into a DataTable. An extension method like this should do the trick:
public static DataTable ToDataTable<T>(this IEnumerable<T> items)
{
var tb = new DataTable(typeof(T).Name);
PropertyInfo[] props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (var prop in props)
{
tb.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
}
foreach (var item in items)
{
var values = new object[props.Length];
for (var i = 0; i < props.Length; i++)
{
values[i] = props[i].GetValue(item, null)!;
}
tb.Rows.Add(values);
}
return tb;
}
Generate Excel File
Below is a helper class that I wrote that has a Create method that takes data in the form of a DataTable and returns an Excel blob in the form of a byte array that you can then write to disk or pass along to a caller from an API endpoint.
public class ExcelFileGenerator
{
public byte[] Create(DataTable table, string sheetName = "Sheet1")
{
byte[] bytes = default!;
using (var ms = new MemoryStream())
{
var workbook = new XSSFWorkbook();
var excelSheet = workbook.CreateSheet(sheetName);
var columns = new List<string>();
var row = excelSheet.CreateRow(0);
var columnIndex = 0;
foreach (DataColumn column in table.Columns)
{
columns.Add(column.ColumnName);
row.CreateCell(columnIndex).SetCellValue(column.ColumnName);
columnIndex++;
}
int rowIndex = 1;
foreach (DataRow dsrow in table.Rows)
{
row = excelSheet.CreateRow(rowIndex);
int cellIndex = 0;
foreach (string col in columns)
{
row.CreateCell(cellIndex).SetCellValue(dsrow[col].ToString());
cellIndex++;
}
rowIndex++;
}
workbook.Write(ms, true);
ms.Position = 0;
bytes = ms.ToArray();
}
return bytes;
}
}
Finally, if you want an ASP.NET WebAPI endpoint that will ship out the Excel file as a raw, binary file, you can take the byte[] you get above and pass it through an ASP.NET MVC FileContentResult helper, like so:
[HttpGet]
public FileContentResult GetExcelFile()
{
byte[] bytes = MakeExcelFile();
return File(bytes, "application/vnd.ms-excel", "super-awesome-excel.xlsx");
}
Closing Remarks
NPOI offers a free, open-source solution for generating Excel files in your dotnet apps without a lot of fuss. For a working example, check out my GitHub repo, here:
tvaidyan/npoi-excel-file-generator: Companion repo to my “Generate Excel Files in .NET (in 2022) with NPOI” blog post on tvaidyan.com (github.com)