netaera.OpenXml.Excel.SheetMapper 2.0.0

netaera.OpenXml.Excel.SheetMapper

Upgrade Notice: This package includes breaking changes in version 2.0.
See UPGRADE.md (included in the NuGet package under contentFiles) for details.

netaera.OpenXml.Excel.SheetMapper is a powerful extension for the netaera.OpenXml.Excel library, enabling automatic mapping of Excel data into objects.
Similar to how an ORM transforms database records into structured entities, SheetMapper converts raw spreadsheet rows directly into typed DTOs—eliminating the need for manual data parsing.

🚀 Why SheetMapper?

• No manual parsing – Retrieve structured objects instead of raw table rows.
• Flexible mapping – Supports both attribute-based and code-based configurations.
• Handles complex data types – Manages lists, related objects, and type conversion.
• Ideal for business applications – Perfect for CRM imports, reporting, AI data sources, etc.

🔹 Installation

Install SheetMapper via NuGet:

dotnet add package netaera.OpenXml.Excel.SheetMapper

🛠 Usage & Examples

1. Mapping Using Attributes

The simplest way to define your mapping is by using annotations:

public interface ICustomer
{
    [SheetMappingData(columnName: "B")]
    string ExternNumber { get; set; }

    [SheetMappingData(columnName: "C")]
    string Number { get; set; }

    [SheetMappingData(columnName: "D")]
    string Name { get; set; }

    [SheetMappingData(columnName: "H")]
    string FirstName { get; set; }

    [SheetMappingData(columnName: "G")]
    string Street { get; set; }

    [SheetMappingData(columnName: "M")]
    string City { get; set; }

    [SheetMappingData(columnName: "J")]
    string DefaultEmailAddress { get; set; }

    List<IPhone> Phones { get; set; }
}

2. Code-Based Mapping Configuration

For more control, you can configure your mappings programmatically using MappingInfoBuilder<T>.
This approach allows you to define how values are read from and written to Excel cells using lambda expressions.

internal class CustomerMappingInfo : MappingInfoBuilder<ICustomer>, IMappingInfoBuilder
{
    public override IMappingInfoBuilder Build(IMappingInfoManager mappingInfoManager)
    {
        mappingInfoManager
            .Build<ICustomer>(rc => rc.FirstRowIsHeaderRow = true)
            .Build<ICustomer>(mapper => mapper
                .Add<ICustomer>("B", cus => cus.Number)
                .Add<ICustomer>("D", cus => cus.Name)
                .Add<ICustomer>("H", cus => cus.FirstName)
                .Add<ICustomer>("I",
                    expressionUsedWhenReading: (cus, value) => cus.Phones.ParseExcelValue(value?.ToString()),
                    expressionUsedWhenWriting: cus => cus.Phones.JoinPhones())
                .Add<ICustomer>("Y", cus => cus.TradeRegistrationOn, dataType: DataType.DateTime)
            );
        return this;
    }
}

What do expressionUsedWhenReading and expressionUsedWhenWriting do?

• expressionUsedWhenReading defines how a raw Excel value is transformed and assigned to the target object.
  In the example above, it parses a string (e.g. "0123,0456") into a list of phone objects using ParseExcelValue.

• expressionUsedWhenWriting defines how the object’s value is serialized back into Excel.
  Here, JoinPhones() converts the list of phone numbers into a single string for export.

This mechanism allows you to handle complex types, collections, and custom transformations with minimal effort.

3. Mapping with Instantiation of Complex Types

If you want to map a scalar value from Excel into a dependent list or collection, use the classType parameter to instantiate the target object:

.Add<ICustomer>("I",
    expressionUsedWhenReading: (cus, value) => cus.Phones.Add((IPhone)value),
    classType: typeof(Phone))

This is ideal when each cell represents a single element of a collection (e.g., one phone number per row). The class type ensures that the object is created and the value is passed through the delegate.

4. Reading Mapped Data

Once mappings are registered, reading Excel data is straightforward:

var reader = DocumentBuilder.Build(config => config.FileName = "customers.xlsx").BuildReader();
var customers = reader.Read<ICustomer>();

5. Registering Mappings

Mappings must be registered before reading:

IMappingInfoManager sheetMappingInfoManager = MappingInfoManager.Instance
    .Register(new CustomerMappingInfo())
    .Register(new EmailMappingInfo());

6. Handling Dates and Types

Date values stored as OADate are automatically converted when using the appropriate DataType:

.Add<ICustomer>("Y", cus => cus.TradeRegistrationOn, dataType: DataType.DateTime)

No custom converter is needed.

📝 FAQ

How is SheetMapper different from netaera.OpenXml.Excel?

While netaera.OpenXml.Excel reads spreadsheet data as raw rows, SheetMapper transforms them into structured objects, easing downstream processing.

Can SheetMapper handle complex data models?

Yes. It supports mapping multiple columns, related entities, and delegate-based transformations.

Is SheetMapper useful for AI and machine learning applications?

Absolutely. By providing clean, well-typed data, SheetMapper serves as an excellent data source for analytical and AI-driven systems.

⚖ License

This package is free to use, but the source code may not be copied, decompiled, modified, or republished.
See LICENSE for details.

© 2025 Paul Berthold Goebel – creativesource. All rights reserved.