ExcelMapper

ExcelMapper supports mapping hierarchical data structures (parent/child objects) automatically. If your Excel columns follow a naming convention that matches your nested class properties, no extra configuration is required.

This support extends to C# records. For positional records without a default constructor, the mapper uses the constructor with the highest number of arguments, provided the parameter names match the property names (case-insensitive).

public class Person
{
    public string Name { get; set; }
    public Address Address { get; set; }
}

public class Address
{
    public string Street { get; set; }
    public string City { get; set; }
}

// Works with records too
public record Person(string Name, Address Address);
public record Address(string Street, string City);

var customers = new ExcelMapper("customers.xlsx").Fetch<Person>();

To read data from an Excel file into a collection of POCO (Plain Old CLR Object) instances, instantiate ExcelMapper with the file path and use the Fetch<T>() method.

By default:

• It expects a header row where column names match property names (case-insensitive).
• It reads from the first worksheet.
• It autodetects the file format (.xlsx or .xls).

If your file does not have a header row, set the HeaderRow property to false.

var products = new ExcelMapper("products.xlsx").Fetch<Product>();

To modify existing Excel data and save only the changes, use Fetch<T>().ToList() to load the objects into memory. After modifying the objects in the list, call Save on the same ExcelMapper instance used to fetch them.

var excel = new ExcelMapper("products.xlsx");
var products = excel.Fetch<Product>().ToList();
products[1].Price += 1.0m;
excel.Save("products.out.xlsx");

You can prevent specific properties from being mapped using either the [Ignore] attribute or the Ignore<T>() method.

// Using Attribute
public class Product
{
    [Ignore]
    public int Number { get; set; }
}

// Using Fluent API
var excel = new ExcelMapper("products.xlsx");
excel.Ignore<Product>(p => p.Price);

If you need to instantiate types that cannot be created via a default constructor (such as interfaces), you can provide a custom factory using CreateInstance<T>(Func<T>).

public class Person
{
    public string Name { get; set; }
    public IAddress Address { get; set; }
}

// Register a factory for the interface
excel.CreateInstance<IAddress>(() => new Address());

Use the Save method to write a collection of objects to a worksheet.

If you use the same ExcelMapper instance to save objects that were previously read from a file, the workbook's existing styles and formatting are preserved. This is useful for filling out Excel templates.

var products = new List<Product> { ... };
new ExcelMapper().Save("products.xlsx", products, "Products");

ExcelMapper can automatically serialize and deserialize JSON strings within cells using the [Json] attribute or the .AsJson() method. This works for both single objects and lists.

public class ProductJson
{
    [Json]
    public Product Product { get; set; }
}

// Or via Fluent API
var excel = new ExcelMapper("products.xls");
excel.AddMapping<ProductJson>("Product", p => p.Product).AsJson();

If you do not provide a type to Fetch(), it returns an IEnumerable<dynamic>.

• The returned objects are instances of ExpandoObject.
• They include a special __indexes__ property (a dictionary) that maps property names to their corresponding column indexes.
• If HeaderRow is false, property names will correspond to Excel column letters (e.g., "A", "B").

var products = new ExcelMapper("products.xlsx").Fetch(); // -> IEnumerable<dynamic>
products.First().Price += 1.0;

You can fine-tune which rows are processed using the following properties on the ExcelMapper instance:

• HeaderRowNumber: The zero-based index of the header row (default is 0).
• MinRowNumber: The first row index containing data (default is 0).
• MaxRowNumber: The last row index containing data (default is int.MaxValue).

The header row does not need to fall within the MinRowNumber and MaxRowNumber range.