BrowserCache.Extensions 0.0.1

XtractXcel

Overview

XtractXcel is a simple library for extracting data from Excel files using ClosedXML, transforming it as needed, and loading it into your objects with minimal effort. It supports a variety of data types, flexible column mapping, and both attribute-based and manual mapping approaches.

Getting Started

Installation (Coming soon)

Basic Usage

1. Define your model with ExcelColumn attributes

public class Person {
    [ExcelColumn("Full Name", "Name", "Employee Name")]
    public string? Name { get; set; }
    
    [ExcelColumn("Age", "Employee Age")]
    public int? Age { get; set; }
    
    [ExcelColumn("Salary")]
    public decimal? Salary { get; set; }
    
    [ExcelColumn("Join Date")]
    public DateTime JoinDate { get; set; }
    
    [ExcelColumn("Last Active", "Last Activity")]
    public DateTime? LastActive { get; set; }
}

The ExcelColumn attribute maps Excel column headers to C# properties. You can provide multiple possible column names to handle variations in your Excel files gracefully.

2. Extract data from Excel

From a Stream

// Get a stream from a file, memory, or any other source
using var stream = File.OpenRead("employees.xlsx");

// Extract the data using a fluent API
var people = new ExcelExtractor()
    .WithHeader(true)               // Excel file contains headers
    .WithWorksheetIndex(1)          // Use the first worksheet (1-based index)
    .FromStream(stream)             // Set the source stream
    .Extract<Person>();             // Perform the extraction

// Use the extracted data
foreach (var person in people) {
    Console.WriteLine($"Name: {person.Name}, Age: {person.Age}, Joined: {person.JoinDate:d}");
}

From a File

var people = new ExcelExtractor()
    .WithHeader(true)
    .WithWorksheetIndex(1)
    .FromFile("employees.xlsx")
    .Extract<Person>();

Advanced Features

Working with Files Without Headers

For Excel files without headers, you can use column position for extraction:

public class PersonNoHeader {
    // No attributes needed - properties are mapped by column position (1-based)
    // First column (A) maps to first property, second column (B) to second property, etc.
    public string? Name { get; set; }
    public int? Age { get; set; }
    public decimal? Salary { get; set; }
    public DateTime JoinDate { get; set; }
    public DateTime? LastActive { get; set; }
}

// Extract the data
var people = new ExcelExtractor()
    .WithHeader(false)              // Specify that there's no header row
    .WithWorksheetIndex(1)
    .FromFile("employees-no-header.xlsx")
    .Extract<PersonNoHeader>();

Manual Mapping

For more control over the extraction process, you can use manual mapping:

// Extract data with manual mapping
var people = new ExcelExtractor()
    .WithHeader(true)
    .WithWorksheetIndex(1)
    .FromStream(stream)
    .ExtractWithManualMapping(row => new Person {
        Name = row.Cell(1).GetString(),
        Age = !row.Cell(2).IsEmpty() ? (int)row.Cell(2).GetDouble() : null,
        Salary = !row.Cell(3).IsEmpty() ? (decimal)row.Cell(3).GetDouble() : null,
        JoinDate = row.Cell(4).GetDateTime(),
        LastActive = !row.Cell(5).IsEmpty() ? row.Cell(5).GetDateTime() : null
    });

Supported Data Types

ExcelTransformLoad supports a wide range of data types:

• Basic types: string, int, decimal, double, DateTime
• Nullable variants: int?, decimal?, DateTime?, etc.
• TimeSpan for time values
• Guid for unique identifiers
• Enums for categorized data

Selecting Specific Columns

If you only need certain columns from an Excel file:

public class PersonWithSpecificColumns {
    [ExcelColumn("Name")]
    public string? NameOnly { get; set; }
    
    [ExcelColumn("Salary")]
    public decimal SalaryOnly { get; set; }
}

var partialData = new ExcelExtractor()
    .WithHeader(true)
    .WithWorksheetIndex(1)
    .FromFile("employees.xlsx")
    .Extract<PersonWithSpecificColumns>();

Handling Enums

Enums are supported out of the box:

public enum UserStatus {
    None,
    Active,
    Inactive,
    Suspended
}

public class PersonWithEnumStatus {
    [ExcelColumn("Name")]
    public string? Name { get; set; }
    
    [ExcelColumn("Status")]
    public UserStatus Status { get; set; }
}

var people = new ExcelExtractor()
    .WithHeader(true)
    .WithWorksheetIndex(1)
    .FromFile("employees.xlsx")
    .Extract<PersonWithEnumStatus>();

Data Transformation During Extraction

Transform data as it's being extracted:

var transformedData = new ExcelExtractor()
    .WithHeader(true)
    .WithWorksheetIndex(1)
    .FromStream(stream)
    .ExtractWithManualMapping(row => new Person {
        // Convert names to uppercase
        Name = row.Cell(1).GetString().ToUpper(),
        // Double the age values
        Age = !row.Cell(2).IsEmpty() ? (int)(row.Cell(2).GetDouble() * 2) : null,
        // Halve the salary values
        Salary = !row.Cell(3).IsEmpty() ? (decimal)(row.Cell(3).GetDouble() / 2) : null,
        // Add a year to join dates
        JoinDate = row.Cell(4).GetDateTime().AddYears(1),
        // Use current date for missing activity dates
        LastActive = !row.Cell(5).IsEmpty() ? row.Cell(5).GetDateTime() : DateTime.Now
    });

Converting to Different Target Types

You can map Excel data to any object type:

public class CustomPerson {
    public string? FullName { get; set; }
    public int YearsOld { get; set; }
    public decimal AnnualSalary { get; set; }
    public DateTime StartDate { get; set; }
    public bool IsActive { get; set; }
}

var customData = new ExcelExtractor()
    .WithHeader(true)
    .WithWorksheetIndex(1)
    .FromStream(stream)
    .ExtractWithManualMapping(row => new CustomPerson {
        FullName = row.Cell(1).GetString(),
        YearsOld = !row.Cell(2).IsEmpty() ? (int)row.Cell(2).GetDouble() : 0,
        AnnualSalary = !row.Cell(3).IsEmpty() ? (decimal)row.Cell(3).GetDouble() : 0,
        StartDate = row.Cell(4).GetDateTime(),
        IsActive = !row.Cell(5).IsEmpty()
    });

Exporting to JSON or XML

You can also extract and directly convert your data into JSON or XML formats:

// Extract as JSON
string json = new ExcelExtractor()
    .WithHeader(true)
    .WithWorksheetIndex(1)
    .FromFile("employees.xlsx")
    .ExtractAsJson<Person>();

// Extract as XML
string xml = new ExcelExtractor()
    .WithHeader(true)
    .WithWorksheetIndex(1)
    .FromFile("employees.xlsx")
    .ExtractAsXml<Person>();

These methods use built-in serializers to quickly convert your extracted objects into JSON or XML strings, which can be saved, transmitted, or logged as needed.

Performance Considerations

Benchmark results show that both attribute-based and manual mapping perform well, but manual mapping has a slight edge in certain cases.

Why Use ExcelTransformLoad?

If you're already using ClosedXML or similar libraries extensively, this one might not add much extra value. But if you're looking for a simple way to read an Excel file and load it into your objects without any hassle, this might worth checking out.