Export a list of .NET objects to Excel

Exporting a list of data as a list of customers to Excel is a fairly common need in business. Since the .NET framework does not provide anything at this level, I decided to create a library to answer this need: ExportTable.

To generate an XLSX file (Excel 2007+) it is recommended to use the Open XML SDK. I will not teach you how to use this SDK, Microsoft has already done all the work with its tool "Open XML SDK Productivity Tool" (included in the SDK). Just open a file in Open XML format to get the code to generate it:

I will show you the different possibilities offered by the library. Take the following class:

public class Customer
{
    [Display(Name = "Identifier")]
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public DateTime DateOfBirth { get; set; }
    [DisplayFormat(ConvertEmptyStringToNull = true, NullDisplayText = "<Not provided>")]
    public string Email { get; set; }
    public bool EmailConfirmed { get; set; }
}

To generate an Excel file these few lines of code are enough:

IEnumerable<Customer> customers = ...;
customers.ToTable(showHeader: true)
    .AddColumn(customer => customer.Id)
    .AddColumn(customer => customer.FirstName)
    .AddColumn(customer => customer.LastName)
    .AddColumn(customer => customer.Email)
    .AddColumn(customer => customer.EmailConfirmed)
    .AddColumn(customer => customer.DateOfBirth, format: "mm/yyyy")
    .GenerateSpreadsheet("customers.xlsx");

And here is the output Excel file:

Column names are inferred automatically from the property name (decamelized) or certain attributes such as DisplayAttribute, DisplayFormatAttribute, and so on. Of course, everything is customizable:

customers.ToTable()
  .AddColumn(
      expression: customer => customer.FirstName,
      select: customer => customer.FirstName.ToUpper(), // Value of the column
      title: "FirstName",
      dataType: typeof(string),
      format: "{0}", // Display value
      nullDisplayText: "<not provided>", // Text when the value is null
      convertEmptyStringToNull: true, // Convert empty string to null (useful when combine with nullDisplayText)
      culture: CultureInfo.CurrentCulture // Culture to use to convert values
  )

Note that all parameters are optional including the expression. One can thus create a column "FullName" combining the properties "FirstName" and the "LastName":

.AddColumn(title: "Full Name", select: customer => customer.FirstName + " " + customer.LastName)

#Bonus

ExportTable also allows you to export your data as HTML (table) and CSV (separators and quotes are configurable):

customers.ToTable(showHeader: true)
    .AddColumn(...)
    .GenerateCsv("customers.csv") // Generate file
    .GenerateCsv()                // return string
    .GenerateCsv(textWriter)      // write to stream
    .GenerateHtmlTable("customers.csv") // Generate file
    .GenerateHtmlTable()                // return string
    .GenerateHtmlTable(textWriter)      // write to stream

The code and examples are available on GitHub: https://github.com/meziantou/ExportTable

Do you have a question or a suggestion about this post? Contact me!