Excel Export with ClosedXML in C# and .NET Core

Aug 12, 2023
•
1 min read
•
.NET Core

Used library: ClosedXML (docs site; github)

The simplest way to export any objects IEnumerable to Excel is by using ClosedXML's IXLTable.InsertTable<T>(IEnumerable<T> data) method.

IEnumerable<AuthorDto> items = GetAuthors();

using var wb = new XLWorkbook();
var ws = wb.AddWorksheet();

// Inserts the collection to Excel as a table with a header row.
ws.Cell("A1").InsertTable(items);

// Adjust column size to contents.
ws.Columns().AdjustToContents();

// Save to local file system.
var filename = $"Export - {DateTime.UtcNow:yyyyMMddHHmmss}.xlsx";
wb.SaveAs(filename);

In ASP.NET you can use the following code to return a downloadable Excel file:

// <-- the above code -->
using var stream = new MemoryStream();
wb.SaveAs(stream);
var content = stream.ToArray();
var contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
return File(content, contentType, filename);

The AuthorDto can be any C# class object. Here is this example's class:

public class AuthorDto
{
    public int Id { get; set; }
    public string? FirstName { get; set; }
    public string? LastName { get; set; }
    public string? ContactEmail { get; set; } = "";
}

And here is the generated Excel table:

For further table customization check the tables feature in the official docs.