ExcelDataReader.Mapping 3.0.3

ExcelMapper

A powerful, flexible .NET library for mapping Excel spreadsheet data to strongly-typed C# objects. ExcelMapper provides an intuitive fluent API with extensive customization options, robust type conversion, and comprehensive error handling.

Built on top of ExcelDataReader for reliable Excel file parsing.

Features

• ✨ Automatic mapping - Maps properties by convention with zero configuration
• 🎯 Type-safe fluent API - Strongly-typed mapping configuration using expressions
• 🔧 Extensive customization - Custom converters, transformers, and fallback strategies
• 📊 Multiple mapping strategies - One-to-one, many-to-one, collections, dictionaries
• 🏷️ Attribute-based mapping - Simple declarative mapping with attributes
• 🔄 Flexible column selection - By name, index, regex pattern, or custom predicate
• 🛡️ Robust error handling - Optional properties, default values, and custom fallbacks
• 🚀 High performance - Streaming API with lazy evaluation and caching
• 📦 Rich type support - Primitives, enums, DateTime, collections, nested objects, and more

Quick Start

using ExcelMapper;

// Define your model
public class Employee
{
    public string Name { get; set; }
    public string Department { get; set; }
    public decimal Salary { get; set; }
}

// Read Excel data
using var importer = new ExcelImporter("employees.xlsx");
var sheet = importer.ReadSheet();
var employees = sheet.ReadRows<Employee>().ToArray();

That's it! ExcelMapper automatically maps columns to properties by name.

Table of Contents

• Installation
• Basic Usage
• Reading Workbooks
• Reading Sheets
• Reading Rows
• Mapping Strategies
  • Automatic Mapping
  • Attribute-Based Mapping
  • Fluent API Mapping
• Error Handling
• Advanced Features
  • Value Mapping
  • Enums
  • Collections and Arrays
  • Dictionaries
  • Nested Objects
  • Custom Converters
  • Custom Transformers and Mappers
• Special Scenarios
  • Sheets Without Headers
  • Headers Not in First Row
  • Restricting Data Range
• Error Handling
• Performance Tips
• Thread Safety
• Supported Types
• CSV Support
• Common Issues & Troubleshooting
• Best Practices
• API Reference
• Contributing
• License

Installation

dotnet add package ExcelDataReader.Mapping

Basic Usage

Simple Example

using ExcelMapper;

public class Employee
{
    public string Name { get; set; }
    public string Department { get; set; }
    public decimal Salary { get; set; }
}

using var importer = new ExcelImporter("employees.xlsx");
var sheet = importer.ReadSheet();
var employees = sheet.ReadRows<Employee>().ToArray();

Console.WriteLine(employees[0].Name);      // Alice Johnson
Console.WriteLine(employees[1].Salary);    // 78000

Reading Workbooks

Create an ExcelImporter to read Excel or CSV files:

// From file path
using var importer = new ExcelImporter("data.xlsx");

// From stream
using var stream = File.OpenRead("data.xlsx");
using var importer = new ExcelImporter(stream);

// CSV file
using var importer = new ExcelImporter("data.csv", ExcelImporterFileType.Csv);

// From existing IExcelDataReader (for advanced scenarios)
using var reader = ExcelReaderFactory.CreateReader(stream);
using var importer = new ExcelImporter(reader);

Advanced: Access the underlying ExcelDataReader

using var importer = new ExcelImporter("data.xlsx");

// Access the underlying reader for advanced scenarios
IExcelDataReader reader = importer.Reader;

// Check number of sheets
int sheetCount = importer.NumberOfSheets;

Reading Sheets

Read All Sheets

foreach (var sheet in importer.ReadSheets())
{
    Console.WriteLine($"Sheet: {sheet.Name}");
    Console.WriteLine($"Visibility: {sheet.Visibility}");  // Visible, Hidden, or VeryHidden
    Console.WriteLine($"Index: {sheet.Index}");
    Console.WriteLine($"Columns: {sheet.NumberOfColumns}");
}

Sheet Visibility:

• ExcelSheetVisibility.Visible - Normal visible sheets
• ExcelSheetVisibility.Hidden - Hidden sheets (can be unhidden in Excel)
• ExcelSheetVisibility.VeryHidden - Very hidden sheets (requires VBA to unhide)

Read Sheets Sequentially

// Throws if no more sheets
var sheet1 = importer.ReadSheet();

// Returns false if no more sheets
if (importer.TryReadSheet(out var sheet2))
{
    // Process sheet2
}

Read Sheet by Name

// Throws if sheet doesn't exist
var sheet = importer.ReadSheet("Sales Data");

// Returns false if sheet doesn't exist
if (importer.TryReadSheet("Sales Data", out var salesSheet))
{
    // Process sheet
}

Read Sheet by Index

// Throws if index is invalid
var sheet = importer.ReadSheet(0);  // First sheet

// Returns false if index is invalid
if (importer.TryReadSheet(1, out var secondSheet))
{
    // Process sheet
}

Reading Rows

Read All Rows

// Lazy evaluation - rows are read as you iterate
var rows = sheet.ReadRows<Employee>();

// Or materialize to array
var employees = sheet.ReadRows<Employee>().ToArray();

Read Specific Range

// Read 10 rows starting from row index 5 (after header at index 0)
// Note: startIndex is relative to the beginning of the file, not after the header
var rows = sheet.ReadRows<Employee>(startIndex: 5, count: 10);

// Example: If header is at row 0, data starts at row 1
// startIndex: 1 = first data row
// startIndex: 11 = 11th data row

Important Notes:

• startIndex is the zero-based row index from the start of the sheet
• The startIndex must be after the header row
• If HeadingIndex is 0 (default), startIndex must be at least 1
• The method will throw ExcelMappingException if rows don't exist

Read Rows Sequentially

// Throws if no more rows
var row1 = sheet.ReadRow<Employee>();

// Returns false if no more rows
if (sheet.TryReadRow<Employee>(out var row2))
{
    // Process row2
}

Skip Blank Lines

// Enable blank line skipping (off by default for performance)
importer.Configuration.SkipBlankLines = true;

var rows = sheet.ReadRows<Employee>();

Security: Column Count Limits

To protect against denial-of-service attacks from malicious Excel files with excessive columns, ExcelMapper enforces a maximum column limit per sheet:

using var importer = new ExcelImporter("data.xlsx");

// Default limit is 10,000 columns (sufficient for most use cases)
Console.WriteLine(importer.Configuration.MaxColumnsPerSheet);  // 10000

// Adjust the limit if needed for legitimate large files
importer.Configuration.MaxColumnsPerSheet = 20000;

// Or disable the limit entirely (not recommended for untrusted files)
importer.Configuration.MaxColumnsPerSheet = int.MaxValue;

Note: Excel .xlsx files support up to 16,384 columns (XFD). If a sheet exceeds MaxColumnsPerSheet, an ExcelMappingException is thrown with a clear error message.

Security Best Practices:

• Keep the default limit (10,000) for untrusted/user-uploaded files
• Only increase the limit when you control the file source
• Files exceeding the limit will fail immediately before allocating excessive memory

Mapping Strategies

ExcelMapper supports three approaches to mapping Excel rows to objects.

Automatic Mapping

ExcelMapper automatically maps public properties and fields by matching column names. Column name matching is case-insensitive by default using StringComparison.OrdinalIgnoreCase.

Important:

• Only public instance properties with setters are auto-mapped
• Only public instance fields are auto-mapped
• Static members, read-only properties, and indexers are ignored
• Use [ExcelIgnore] to exclude specific properties/fields Example:

public class Employee
{
    public string Name { get; set; }
    public string Department { get; set; }
    public string Position { get; set; }
    public DateTime HireDate { get; set; }
    public decimal Salary { get; set; }
    public bool Active { get; set; }
}

using var importer = new ExcelImporter("employees.xlsx");
var sheet = importer.ReadSheet();
var employees = sheet.ReadRows<Employee>().ToArray();

Console.WriteLine(employees[0].Name);       // Alice Johnson
Console.WriteLine(employees[0].Position);   // Senior Engineer
Console.WriteLine(employees[1].Salary);     // 78000

Attribute-Based Mapping

Use attributes to declaratively configure mapping behavior. Column name matching is case-insensitive by default (StringComparison.OrdinalIgnoreCase).

Column Name Mapping

Map properties to columns with different names:

public class Employee
{
    [ExcelColumnName("Full Name")]
    public string Name { get; set; }

    [ExcelColumnName("#Age")]
    public int Age { get; set; }
}

var employees = sheet.ReadRows<Employee>().ToArray();
Console.WriteLine(employees[0].Name);  // Alice Johnson
Console.WriteLine(employees[1].Age);   // 45

String Comparison Options:

Control how column names are matched using StringComparison:

public class Employee
{
    // Case-insensitive matching (default)
    [ExcelColumnName("Full Name")]
    public string Name { get; set; }

    // Case-sensitive matching
    [ExcelColumnName("Department", StringComparison.Ordinal)]
    public string Department { get; set; }

    // Culture-aware case-insensitive matching
    [ExcelColumnName("Città", StringComparison.CurrentCultureIgnoreCase)]
    public string City { get; set; }
}

Available StringComparison Options:

• StringComparison.OrdinalIgnoreCase (default) - Case-insensitive, culture-invariant
• StringComparison.Ordinal - Case-sensitive, culture-invariant
• StringComparison.CurrentCultureIgnoreCase - Case-insensitive using current culture
• StringComparison.CurrentCulture - Case-sensitive using current culture
• StringComparison.InvariantCultureIgnoreCase - Case-insensitive using invariant culture
• StringComparison.InvariantCulture - Case-sensitive using invariant culture

Multiple Column Name Variants

Try multiple column names in order of preference:

public class Employee
{
    public string Name { get; set; }

    // Try these column names in order (case-insensitive by default)
    [ExcelColumnNames("Age", "#Age", "Years")]
    public int Age { get; set; }

    // Or use multiple attributes with different comparison modes
    [ExcelColumnName("Dept", StringComparison.OrdinalIgnoreCase)]
    [ExcelColumnName("Department", StringComparison.Ordinal)]
    public string Department { get; set; }
}

Pattern Matching

Match columns using regex patterns or custom matchers:

public class Employee
{
    public string Name { get; set; }

    // Match columns like "2024 Salary", "2025 Projected Salary"
    [ExcelColumnMatching(@"\d{4}.*Salary", RegexOptions.IgnoreCase)]
    public decimal Salary { get; set; }
}

For advanced matching logic, implement IExcelColumnMatcher:

public class StartsWithMatcher : IExcelColumnMatcher
{
    private readonly string _prefix;

    public StartsWithMatcher(string prefix)
    {
        _prefix = prefix;
    }

    public bool IsMatch(string columnName) => columnName.StartsWith(_prefix);
}

public class Employee
{
    // Use custom matcher to match columns starting with "Bonus_"
    [ExcelColumnsMatching(typeof(StartsWithMatcher), ConstructorArguments = new object[] { "Bonus_" })]
    public decimal TotalBonus { get; set; }
}

Column Index Mapping

Map by zero-based column index (useful for sheets without headers):

public class Employee
{
    [ExcelColumnIndex(0)]
    public string Name { get; set; }

    [ExcelColumnIndex(1)]
    public int Age { get; set; }
}

var sheet = importer.ReadSheet();
sheet.HasHeading = false;  // No header row
var employees = sheet.ReadRows<Employee>().ToArray();

Multiple Index Variants

public class Data
{
    // Try column index 2, then 1, then 0
    [ExcelColumnIndices(2, 1, 0)]
    public string Value { get; set; }
}

Optional Properties

Skip properties if columns are missing:

public class Employee
{
    public string Name { get; set; }

    [ExcelOptional]
    public int? Age { get; set; }  // Won't throw if column missing
}

Default Values

Provide default values for empty cells: | Name | Age | |---------------|-----| | Alice Johnson | | | Bob Smith | 45 |

public class Employee
{
    public string Name { get; set; }

    [ExcelDefaultValue(-1)]
    public int Age { get; set; }  // -1 if cell is empty
}

Ignore Properties

Exclude properties from mapping:

public class Employee
{
    public string Name { get; set; }

    [ExcelIgnore]
    public int Age { get; set; }  // Never mapped from Excel

    [ExcelIgnore]
    public DateTime CreatedAt { get; set; }  // Computed property
}

Preserve Formatting

Read formatted string values instead of raw values:

public class Employee
{
    [ExcelPreserveFormatting]
    public string EmployeeID { get; set; }    // "00123" with leading zeros

    [ExcelPreserveFormatting]
    public string Salary { get; set; }  // "$95,000" with currency symbol
}

Trim String Values

Automatically trim whitespace from string values:

public class Employee
{
    [ExcelTrimString]
    public string Name { get; set; }  // "Alice Johnson", "Bob Smith" (trimmed)
}

Or use the fluent API:

public class EmployeeMap : ExcelClassMap<Employee>
{
    public EmployeeMap()
    {
        Map(e => e.Name).WithTrim();
    }
}

Date and Time Formats

Specify custom formats for parsing date, time, and duration types:

public class Event
{
    public string Event { get; set; }
    
    [ExcelFormats("yyyy-MM-dd", "dd/MM/yyyy", "MM/dd/yyyy")]
    public DateTime EventDate { get; set; }
    
    [ExcelFormats("HH:mm", "hh:mm tt")]
    public TimeOnly StartTime { get; set; }
    
    [ExcelFormats(@"hh\:mm\:ss", @"mm\:ss")]
    public TimeSpan Duration { get; set; }
}

Or use the fluent API:

public class EventMap : ExcelClassMap<Event>
{
    public EventMap()
    {
        Map(e => e.EventDate)
            .WithFormats("yyyy-MM-dd", "dd/MM/yyyy", "MM/dd/yyyy");
            
        Map(e => e.StartTime)
            .WithFormats("HH:mm", "hh:mm tt");
            
        Map(e => e.Duration)
            .WithFormats(@"hh\:mm\:ss", @"mm\:ss");
    }
}

Supported Types:

• DateTime / DateTime?
• DateTimeOffset / DateTimeOffset?
• DateOnly / DateOnly? (.NET 6+)
• TimeOnly / TimeOnly? (.NET 6+)
• TimeSpan / TimeSpan?

Culture-Specific Date and Time Parsing

Specify a format provider (culture) for parsing date, time, and duration types. This is particularly useful when your Excel data uses locale-specific formatting:

using System.Globalization;

public class Event
{
    public string Event { get; set; }
    public DateTime EventDate { get; set; }
    public TimeOnly StartTime { get; set; }
}

public class EventMap : ExcelClassMap<Event>
{
    public EventMap()
    {
        var germanCulture = new CultureInfo("de-DE");
        
        Map(e => e.Event);
        
        Map(e => e.EventDate)
            .WithFormats("dd.MM.yyyy")
            .WithFormatProvider(germanCulture);
            
        Map(e => e.StartTime)
            .WithFormats("HH:mm")
            .WithFormatProvider(germanCulture);
    }
}

importer.Configuration.RegisterClassMap<EventMap>();
var events = sheet.ReadRows<Event>();

Why use format providers?

• Handle locale-specific number formats (1.234,56 vs 1,234.56)
• Parse dates with culture-specific month/day names
• Support culture-specific date separators (. vs / vs -)
• Ensure correct parsing of time formats across cultures

Supported Types with Format Providers:

• DateTime / DateTime?
• DateTimeOffset / DateTimeOffset?
• DateOnly / DateOnly? (.NET 6+)
• TimeOnly / TimeOnly? (.NET 6+)
• TimeSpan / TimeSpan?

Number Parsing with Number Styles

Control how numeric values are parsed using NumberStyles to handle thousands separators, currency symbols, and other formatting. This is particularly useful for financial data or numbers formatted with locale-specific conventions:

using System.Globalization;

public class Product
{
    public string ProductName { get; set; }
    
    [ExcelNumberStyle(NumberStyles.AllowThousands | NumberStyles.AllowDecimalPoint)]
    [ExcelDefaultValue(0.0)]
    [ExcelInvalidValue(0.0)]
    public decimal Price { get; set; }
    
    [ExcelNumberStyle(NumberStyles.AllowThousands)]
    [ExcelDefaultValue(0)]
    [ExcelInvalidValue(0)]
    public int Quantity { get; set; }
}

var products = sheet.ReadRows<Product>().ToArray();
// Successfully parses "1,234.56" as 1234.56 and "1000" as 1000

Using Fluent API:

For more complex scenarios, use the fluent API with .WithNumberStyle() and .WithFormatProvider():

public class ProductMap : ExcelClassMap<Product>
{
    public ProductMap()
    {
        Map(p => p.ProductName);
        
        Map(p => p.Price)
            .WithNumberStyle(NumberStyles.AllowThousands | NumberStyles.AllowDecimalPoint)
            .WithEmptyFallback(0.0m)
            .WithInvalidFallback(0.0m);
            
        Map(p => p.Quantity)
            .WithNumberStyle(NumberStyles.AllowThousands)
            .WithEmptyFallback(0)
            .WithInvalidFallback(0);
    }
}

importer.Configuration.RegisterClassMap<ProductMap>();

Combining Number Styles with Format Providers:

When working with locale-specific number formats (e.g., European format using comma as decimal separator):

public class ProductMap : ExcelClassMap<Product>
{
    public ProductMap()
    {
        var europeanFormat = new NumberFormatInfo
        {
            NumberGroupSeparator = ".",  // Period for thousands
            NumberDecimalSeparator = "," // Comma for decimals
        };
        
        Map(p => p.Price)
            .WithNumberStyle(NumberStyles.AllowThousands | NumberStyles.AllowDecimalPoint)
            .WithFormatProvider(europeanFormat)
            .WithEmptyFallback(0.0m)
            .WithInvalidFallback(0.0m);
    }
}

Common NumberStyles Values:

• NumberStyles.Integer - Simple integers (default for integer types)
• NumberStyles.Float - Floating-point numbers (default for float/double)
• NumberStyles.Number - Includes thousands separator and decimal point
• NumberStyles.AllowThousands - Allows thousands separators (e.g., 1,000)
• NumberStyles.AllowDecimalPoint - Allows decimal point (e.g., 123.45)
• NumberStyles.AllowCurrencySymbol - Allows currency symbols (e.g., $100)
• NumberStyles.AllowLeadingSign - Allows leading +/- sign
• NumberStyles.AllowTrailingSign - Allows trailing +/- sign
• NumberStyles.AllowParentheses - Allows parentheses for negative numbers (e.g., (100))

Supported Numeric Types:

The ExcelNumberStyle attribute and