ExcelEnumerable 2.0.0

dotnet add package ExcelEnumerable --version 2.0.0
NuGet\Install-Package ExcelEnumerable -Version 2.0.0
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="ExcelEnumerable" Version="2.0.0" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add ExcelEnumerable --version 2.0.0
#r "nuget: ExcelEnumerable, 2.0.0"
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
// Install ExcelEnumerable as a Cake Addin
#addin nuget:?package=ExcelEnumerable&version=2.0.0

// Install ExcelEnumerable as a Cake Tool
#tool nuget:?package=ExcelEnumerable&version=2.0.0

ExcelEnumerable

Lightweight .NET library which enables mapping POCO objects to Excel rows, with ability to apply C# LINQ expressions. It uses ExcelDataReader for reading Excel files.

Any contribution is very welcome, so, please feel free to fork and create pull requests, including any issue you find with the library.

Migration to version 2.x.x

IExcelEnumerable<T>, XcelEnumerable<T> and [XcelEnumerableColumn("ColumnName")] are still existing, but are obsolete and will be removed in future versions. Consider using ExcelIteratorCreator and IExcelIterator<T>.

Basic Usage

As an example, let's take following Excel file structure:

Id FirstName LastName IsActive
1 First User TRUE
2 Second User FALSE

First, we need a class which represents a row:

public class User
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public bool IsActive { get; set; }
}

Then, we create a new instance of IExcelIterator<T> by using ExcelIteratorCreator, by specifying the above type as a generic argument of Create method, passing a file stream, and then apply LINQ expressions:

using (var excelIterator = ExcelIteratorCreator.Create<User>(stream))
{
    var items = excelIterator.Where(u => u.IsActive).ToList();
}

IExcelIterator<T> implements IEnumerable<T>, which means that it can be used in foreach statements, and various LINQ expressions can be applied to it.

Configuration

Additional configuration for column mapping, value conversion and default behaviour can be passed as a second argument of the ExcelIteratorCreator.Create(stream, config) method.

var builder = ExcelIteratorConfigurationBuilder<ExampleRow>.Default();

//  Select sheet by name. Without this configuration, the first sheet will be used by default.
builder.UseSheetName("Some Sheet");

//  Specifying that first row in excel file contains column names. Default: true
builder.FirstRowContainsColumnNames(flag: true);

//  Skips any empty column names, only works when FirstRowContainsColumnNames is true. Default: true
builder.SkipEmptyColumnNames(flag: true);

//  Trim any whitespace when reading colum names from excel file. This can be useful for easy 
//  mapping properties like FirstName, LastName, IsActive with column names 'First Name', 
//  '   Last   Name', ' Is Active' without manually configuring map for each property.
//  When using this behaviour, all columns will be read from excel without whitespace, so 
//  for example, using .MapByName(p => p.FirstName, "First Name") will not work anymore.
//  Only works when FirstRowContainsColumnNames is true. Default: false
builder.TrimWhitespaceForColumnNames(flag: true);

//  Map property by column name (case insensitive).
//  Without this configuration, the column name must exactly match property name (case insensitive)
//  works only when FirstRowContainsColumnNames is true
builder.MapByName(p => p.FirstName, "First NAME");

//  Map property by column index. When FirstRowContainsColumnNames is false, all columns have 
//  to be mapped by index.
builder.MapByIndex(p => p.LastName, 2);

//  Ignore property mapping
builder.Ignore(p => p.Address);

//  Configure custom value conversion for property or implement ISourceValueConverter 
//  and pass an instance of it.
builder.ConvertSourceValue(p => p.IsActive, sourceValue => sourceValue?.ToString() == "TRUE");
builder.ConvertSourceValue(p => p.IsActive, new MySourceValueConverter());

//  Build the configuration
var config = builder.Build();

//  Pass the configuration
using (var excelIterator = ExcelIteratorCreator.Create<User>(stream), config)
{
    ...
}

Important note when targeting .NET Core Application

When targeting .NET Core, it's important to add dependency on System.Text.Encoding.CodePages and register the code page provider during application initialization:

System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
Product Compatible and additional computed target framework versions.
.NET net5.0 was computed.  net5.0-windows was computed.  net6.0 was computed.  net6.0-android was computed.  net6.0-ios was computed.  net6.0-maccatalyst was computed.  net6.0-macos was computed.  net6.0-tvos was computed.  net6.0-windows was computed.  net7.0 was computed.  net7.0-android was computed.  net7.0-ios was computed.  net7.0-maccatalyst was computed.  net7.0-macos was computed.  net7.0-tvos was computed.  net7.0-windows was computed.  net8.0 was computed.  net8.0-android was computed.  net8.0-browser was computed.  net8.0-ios was computed.  net8.0-maccatalyst was computed.  net8.0-macos was computed.  net8.0-tvos was computed.  net8.0-windows was computed. 
.NET Core netcoreapp3.0 was computed.  netcoreapp3.1 was computed. 
.NET Standard netstandard2.1 is compatible. 
MonoAndroid monoandroid was computed. 
MonoMac monomac was computed. 
MonoTouch monotouch was computed. 
Tizen tizen60 was computed. 
Xamarin.iOS xamarinios was computed. 
Xamarin.Mac xamarinmac was computed. 
Xamarin.TVOS xamarintvos was computed. 
Xamarin.WatchOS xamarinwatchos was computed. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

NuGet packages

This package is not used by any NuGet packages.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last updated
2.0.0 807 5/8/2021
1.2.0 419 12/5/2020
1.1.0 390 9/4/2020
1.0.0 476 6/22/2020

- Configuration which supports:
   - Mapping properties by column name and index
   - Ignoring property
   - Trimming white-space while reading excel columns
   - Optionally having column names in first row
   - Custom value conversion from source value (excel) to destination value (property)
   - Skipping empty column names
- New reader and creator
- Internal improvements