If your project involves generating many Excel documents, creating one generic Excel generator can save you time and effort. Instead of building custom generators for each scenario, a single, flexible generator can handle various object lists.

In this article we’ll explore how to achieve this using EPPlus, a library for managing Excel spreadsheets. We will use custom attributes to control how each cell in the spreadsheet is handled. This approach lets you manage formatting directly from your data objects, keeping the Excel generator free from implementation details.

Step 1 - Create a customizable Attribute

To control how each property is displayed in the generated Excel sheet, we’ll create a custom attribute. This attribute will allow us to specify details like the name, width, and order of columns for each property.

Here is the ExcelColumnAttribute:

    [AttributeUsage(AttributeTargets.Property)]
    public class ExcelColumnAttribute : Attribute
    {
        public ExcelColumnAttribute(
          string name = null!, // make it optional
          int width = -1, // make it optional
          [CallerLineNumber] int order = 0
        )
        {
            Name = name;
            Width = width >= 0 ? width : null;
            Order = order;
        }
        
        public int Order { get; }
        public string? Name { get; set; }
        public int? Width { get; set; }
    }

This attribute includes:

• Name: Optional column name for the spreadsheet.
• Width: Optional column width.
• Order: Automatically captures the property’s line number for ordering through [CallerLineNumber], keeping column layout consistent with class definition.

You can extend this further to include formatting options like text color or data formats.

Example of usage

Here’s how you can apply the ExcelColumnAttribute to a class:

public class UserModel
{
    [ExcelColumn("User name", width: 20)]
    public string Name { get; set; }
    
    [ExcelColumn("Phone number", width: 10)]
    public string PhoneNumber { get; set; }
    
    [ExcelColumn(width: 20)]
    public string Email { get; set; }
}

This setup lets the generator remain generic while giving you control over the spreadsheet layout directly from the data model.

Step 2 - Implementing the generator

Now, lets make the Excel generator itself.

We’ll use the EPPlus library to generate Excel files. To include this library in your project, add the NuGet package using the command:

dotnet add package EPPlus

Refer to the EPPlus documentation for detailed usage and licensing information. Please note: EPPlus requires a paid license for commercial projects.

Next, create the ExcelGenerator class with one method, CreateExcelDocument, which generates an Excel sheet from a generic list of objects.

Here is the starting point:

public class ExcelGenerator
{
    public byte[] CreateExcelDocument<T>(List<T> data, string sheetName)
    {
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        using var package = new ExcelPackage();
        // create a new spreadsheet with a specified name
        var worksheet = package.Workbook.Worksheets.Add(sheetName);

        // ... the rest of the code will come here
    }
}

Using reflection to define columns and rows

To dynamically create rows and columns, we use reflection to extract properties from the objects in the list. Only properties annotated with the ExcelColumnAttribute will be included. The attributes allow us to specify details such as column names, widths, and order.

Here’s how we retrieve and process these properties using reflection:

var properties = typeof(T).GetProperties()
    .Where(t => t.GetCustomAttribute<ExcelColumnAttribute>() != null)
    .Select(property => new
        {
            Type = property,
            Metadata = property
              .GetCustomAttribute<ExcelColumnAttribute>()!
        })
    .OrderBy(i => i.Metadata.Order).ToList();

Populate the Excel sheet

With the property metadata ready, we can now create headers, set column properties (e.g., width, format), and populate rows with data.

var columnIndex = 1;
// loop through each property (e.g Name, or Email)
foreach (var property in properties)
{
    // Add the header. 
    var rowIndex = 1;
    var cell = worksheet.Cells[rowIndex, columnIndex];
    // Use the name defined in the attribute or default to the name of the attribute
    cell.Value = property.Metadata.Name ?? property.Type.Name;
    // Make the font bold for the header
    cell.Style.Font.Bold = true;

    // Set column width if specified in the attribute
    if (property.Metadata.Width.HasValue)
    {
        worksheet
            .Column(columnIndex)
            .Width = property.Metadata.Width.Value;
    }

    // Set column format as text if the property is a string 
    // (this is convenient for number strings like phone numbers)
    if (property.Type.PropertyType == typeof(string))
    {
        worksheet
            .Column(columnIndex)
            .Style
            .Numberformat
            .Format = "@";
    }

    // ... You can define other formats here

    // Add the rows by looping through the list of data objects.
    // "row" is here one object like UserModel
    foreach (var row in data)
    {
        // Start by moving to the next row
        rowIndex++;
        cell = worksheet.Cells[rowIndex, columnIndex];
        // Sets the current cell to the value stored in the current propery
        // e.g Name, where the value might be "Tom"
        cell.Value = property.Type.GetValue(row);
    }

    columnIndex++;
}

Finalizing the Excel file

Lastly, we ensure the headers remain visible by freezing the first row. We then return the Excel file as a byte array:

// Freeze the first row to keep headers visible when scrolling
worksheet.View.FreezePanes(2, 1);

// Convert the Excel package to a byte array and return it
return package.GetAsByteArray();

Extra - Handeling formats and nullable types

When working with certain data types, like dates or numbers, it is important to define appropriate formats for clear presentation in Excel. Additionally, nullable types, such as DateTime?, can be challenging since they may not always have a value.

Identifying underlying types

Nullable types (e.g., DateTime?) need special attention. We can use reflection to determine the underlying type:

var underlyingType = property.Type.PropertyType.IsGenericType ? 
        Nullable.GetUnderlyingType(property.Type.PropertyType) : 
        property.Type.PropertyType;    

Here, if the property is a nullable type the Nullable.GetUnderlyingType retrieves the base type (e.g., DateTime from DateTime?). For non-nullable types, we directly use the PropertyType as is.

Applying date formats

If the property type is a date-like type (DateTime, DateTimeOffset, or DateOnly), we define a standard format for Excel:

if (underlyingType == typeof(DateTime) || 
    underlyingType == typeof(DateTimeOffset) || 
    underlyingType == typeof(DateOnly)
   ) {
      worksheet
        .Column(columnIndex)
        .Style
        .Numberformat
        .Format = "yyyy-mm-dd";
   }      

This ensures consistent formatting and improves readability in the Excel sheet.

And there you have it! With a single, flexible Excel generator powered by EPPlus, you can save time, reduce repetitive code, and handle all your Excel file needs in one place. By using custom attributes and reflection, the generator stays generic while giving you complete control over how your data is presented. Whether it’s for simple reports or complex spreadsheets, this approach has you covered. Happy coding, and may your Excel files be ever clean and organized!