ExcelMapper 5.2.592
dotnet add package ExcelMapper --version 5.2.592
NuGet\Install-Package ExcelMapper -Version 5.2.592
<PackageReference Include="ExcelMapper" Version="5.2.592" />
paket add ExcelMapper --version 5.2.592
#r "nuget: ExcelMapper, 5.2.592"
// Install ExcelMapper as a Cake Addin #addin nuget:?package=ExcelMapper&version=5.2.592 // Install ExcelMapper as a Cake Tool #tool nuget:?package=ExcelMapper&version=5.2.592
ExcelMapper
A library to map POCO objects to Excel files.
Features
- Read and write Excel files
- Uses the pure managed NPOI library instead of the Jet database engine (NPOI users group)
- Map to Excel files using header rows (column names) or column indexes (no header row)
- Map nested objects (parent/child objects)
- Optionally skip blank lines when reading
- Preserve formatting when saving back files
- Optionally let the mapper track objects
- Map columns to properties through convention, attributes or method calls
- Use custom or builtin data formats for numeric and DateTime columns
- Map formulas or formula results depending on property type
- Map JSON
- Fetch/Save dynamic objects
- Use records
- Provide custom object factories
Read objects from an Excel file
var products = new ExcelMapper("products.xlsx").Fetch<Product>();
This expects the Excel file to contain a header row with the column names. Objects are read from the first worksheet. If the column names equal the property names (ignoring case) no other configuration is necessary. The format of the Excel file (xlsx or xls) is autodetected.
Map to specific column names
public class Product
{
public string Name { get; set; }
[Column("Number")]
public int NumberInStock { get; set; }
public decimal Price { get; set; }
}
This maps the column named Number
to the NumberInStock
property.
Map to column indexes
Column indexes start at 1.
public class Product
{
[Column(1)]
public string Name { get; set; }
[Column(Letter="C")]
public int NumberInStock { get; set; }
[Column(4)]
public decimal Price { get; set; }
}
var products = new ExcelMapper("products.xlsx") { HeaderRow = false }.Fetch<Product>();
Note that column indexes don't need to be consecutive. When mapping to column indexes, every property needs to be explicitly mapped through the ColumnAttribute
attribute or the AddMapping()
method. You can combine column indexes with column names to specify an explicit column order while still using a header row.
Map through method calls
var excel = new ExcelMapper("products.xls");
excel.AddMapping<Product>("Number", p => p.NumberInStock);
excel.AddMapping<Product>(1, p => p.NumberInStock);
excel.AddMapping(typeof(Product), "Number", "NumberInStock");
excel.AddMapping(typeof(Product), ExcelMapper.LetterToIndex("A"), "NumberInStock");
Multiple mappings
You can map a single column to multiple properties but you need to be aware of what should happen when mapping back from objects to Excel. To specify the single property you want to map back to Excel, add MappingDirections.ExcelToObject
in the Column
attribute of all other properties that map to the same column. Alternatively, you can use the FromExcelOnly()
method when mapping through method calls.
public class Product
{
public decimal Price { get; set; }
[Column("Price", MappingDirections.ExcelToObject)]
public string PriceString { get; set; }
}
// or
excel.AddMapping<Product>("Price", p => p.PriceString).FromExcelOnly();
Column
attributes are inherited by default, resulting in multiple mappings for a single overridden property if you add a Column
attribute to the property in base
and derived classes. To prevent this, set the Inherit
property to false on the Column
attribute in the base class.
Dynamic mapping
You don't have to specify a mapping to static types, you can also fetch a collection of dynamic objects.
var products = new ExcelMapper("products.xlsx").Fetch(); // -> IEnumerable<dynamic>
products.First().Price += 1.0;
The returned dynamic objects are instances of ExpandoObject
with an extra property called __indexes__
that is a dictionary specifying the mapping from property names to
column indexes. If you set the HeaderRow
property to false
on the ExcelMapper
object, the property names of the returned dynamic objects will match the Excel "letter" column names, i.e. "A" for column 1 etc.
Save objects
var products = new List<Product>
{
new Product { Name = "Nudossi", NumberInStock = 60, Price = 1.99m },
new Product { Name = "Halloren", NumberInStock = 33, Price = 2.99m },
new Product { Name = "Filinchen", NumberInStock = 100, Price = 0.99m },
};
new ExcelMapper().Save("products.xlsx", products, "Products");
This saves to the worksheet named "Products". If you save objects after having previously read from an Excel file using the same instance of ExcelMapper
the style of the workbook is preserved allowing use cases where an Excel template is filled with computed data.
Track objects
var products = new ExcelMapper("products.xlsx").Fetch<Product>().ToList();
products[1].Price += 1.0m;
excel.Save("products.out.xlsx");
Ignore properties
public class Product
{
public string Name { get; set; }
[Ignore]
public int Number { get; set; }
public decimal Price { get; set; }
}
// or
var excel = new ExcelMapper("products.xlsx");
excel.Ignore<Product>(p => p.Price);
Use specific data formats
public class Product
{
[DataFormat(0xf)]
public DateTime Date { get; set; }
[DataFormat("0%")]
public decimal Number { get; set; }
}
You can use both builtin formats and custom formats. The default format for DateTime cells is 0x16 ("m/d/yy h:mm").
Map formulas or results
Formula columns are mapped according to the type of the property they are mapped to: for string properties, the formula itself (e.g. "A1+B1") is mapped, for other property types the formula result is mapped. If you need the formula result in a string property, use the FormulaResult
attribute.
public class Product
{
[FormulaResult]
public string Result { get; set; }
}
// or
excel.AddMapping<Product>("Result" p => p.Result).AsFormulaResult();
If you want to save formulas you need to use the FormulaAttribute
attribute or call AsFormula()
if mapping manually.
It's not needed if you only want to map from Excel to objects (deserialize).
public class Product
{
[Formula]
public string Formula { get; set; }
}
// or
excel.AddMapping<Product>("Formula" p => p.Formula).AsFormula();
☝️ The string values of formula properties must not start with the =
sign. So instead of =A1+B1
set the property's value to A1+B1
.
Custom mapping
If you have specific requirements for mapping between cells and objects, you can use custom conversion methods. Here, cells that contain the string "NULL" are mapped to null:
public class Product
{
public DateTime? Date { get; set; }
}
excel.AddMapping<Product>("Date", p => p.Date)
.SetCellUsing((c, o) =>
{
if (o == null) c.SetCellValue("NULL"); else c.SetCellValue((DateTime)o);
})
.SetPropertyUsing(v =>
{
if ((v as string) == "NULL") return null;
return Convert.ChangeType(v, typeof(DateTime), CultureInfo.InvariantCulture);
});
Header row and data row range
You can specify the row number of the header row using the property HeaderRowNumber
(default is 0). The range of rows that are considered rows that may contain data can be specified using the properties MinRowNumber
(default is 0) and MaxRowNumber
(default is int.MaxValue
). The header row doesn't have to fall within this range, e.g. you can have the header row in row 5 and the data in rows 10-20.
JSON
You can easily serialize to and from JSON formatted cells by specifying the Json
attribute or AsJson()
method.
public class ProductJson
{
[Json]
public Product Product { get; set; }
}
// or
var excel = new ExcelMapper("products.xls");
excel.AddMapping<ProductJson>("Product", p => p.Product).AsJson();
This also works with lists.
public class ProductJson
{
[Json]
public List<Product> Products { get; set; }
}
Name normalization
If the header cell values are not uniform, perhaps because they contain varying amounts of whitespace, you can specify a normalization function that will be applied to header cell values before mapping to property names. This can be done globally or for specific classes only.
excel.NormalizeUsing(n => Regex.Replace(n, "\s", ""));
This removes all whitespace so that columns with the string " First Name " map to a property named FirstName
.
Records
Records are supported. If the type has no default constructor (as is the case for positional records) the constructor with the highest number of arguments is used to initialize objects. This constructor must have a parameter for each of the mapped properties with the same name as the corresponding property (ignoring case). The remanining parameters will receive the default value of their type.
Nested objects
Nested objects are supported and should work out of the box for most use cases. For example, if you have a sheet with columns Name, Street, City, Zip, Birthday, you can map to the following class hierarchy without any configuration:
public class Person
{
public string Name { get; set; }
public DateTime Birthday { get; set; }
public Address Address { get; set; }
}
public class Address
{
public string Street { get; set; }
public string City { get; set; }
public string Zip { get; set; }
}
var customers = new ExcelMapper("customers.xlsx").Fetch<Person>();
This works with records, too:
public record Person(string Name, DateTime Birthday, Address Address);
public record Address(string Street, string City, string Zip);
Object factories
You can specify a custom object factory for any type which will be used to create object instances for mapped properties of that type. This can be useful to handle cases where object creation is otherwise not possible (such as for properties that have interface types) or where you want to execute specific initialization logic.
public class Person
{
public string Name { get; set; }
public IAddress Address { get; set; }
}
excel.CreateInstance<IAddress>(() => new Address());
Product | Versions 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 | netcoreapp2.0 was computed. netcoreapp2.1 was computed. netcoreapp2.2 was computed. netcoreapp3.0 was computed. netcoreapp3.1 was computed. |
.NET Standard | netstandard2.0 is compatible. netstandard2.1 was computed. |
.NET Framework | net461 was computed. net462 is compatible. net463 was computed. net47 was computed. net471 was computed. net472 was computed. net48 was computed. net481 was computed. |
MonoAndroid | monoandroid was computed. |
MonoMac | monomac was computed. |
MonoTouch | monotouch was computed. |
Tizen | tizen40 was computed. tizen60 was computed. |
Xamarin.iOS | xamarinios was computed. |
Xamarin.Mac | xamarinmac was computed. |
Xamarin.TVOS | xamarintvos was computed. |
Xamarin.WatchOS | xamarinwatchos was computed. |
-
.NETFramework 4.6.2
- Microsoft.CSharp (>= 4.7.0)
- NPOI (>= 2.7.1)
- System.Text.Json (>= 8.0.4)
-
.NETStandard 2.0
- Microsoft.CSharp (>= 4.7.0)
- NPOI (>= 2.7.1)
- System.Text.Json (>= 8.0.4)
NuGet packages (6)
Showing the top 5 NuGet packages that depend on ExcelMapper:
Package | Downloads |
---|---|
UserProfileManagement.Data
Package Description |
|
AbacusLib.Common
Package Description |
|
rna.Core.Infrastructure
A complete set of libraries for rna Authorization and CRUD operations |
|
TrinityText.Utilities
Trinity Text utilities layer |
|
Halifax.Excel
Halifax Service Foundation Excel/CSV library |
GitHub repositories (1)
Showing the top 1 popular GitHub repositories that depend on ExcelMapper:
Repository | Stars |
---|---|
grandnode/grandnode2
Open-Source eCommerce Platform on .NET Core, MongoDB, AWS DocumentDB, Azure CosmosDB, LiteDB & Vue.js
|
Version | Downloads | Last updated |
---|---|---|
5.2.592 | 69,715 | 7/16/2024 |
5.2.591 | 5,049 | 7/10/2024 |
5.2.590 | 16,715 | 6/12/2024 |
5.2.588 | 41,292 | 5/24/2024 |
5.2.581 | 29,267 | 4/12/2024 |
5.2.580 | 23,985 | 3/25/2024 |
5.2.568 | 28,525 | 2/12/2024 |
5.2.564 | 37,075 | 12/13/2023 |
5.2.542 | 78,648 | 9/12/2023 |
5.2.535 | 14,174 | 8/24/2023 |
5.2.528 | 25,636 | 7/31/2023 |
5.2.483 | 232,096 | 2/8/2023 |
5.2.482 | 30,201 | 1/18/2023 |
5.2.472 | 43,196 | 12/13/2022 |
5.2.471 | 9,818 | 12/5/2022 |
5.2.467 | 29,471 | 11/17/2022 |
5.2.429 | 71,049 | 8/24/2022 |
5.2.427 | 6,128 | 8/16/2022 |
5.2.415 | 54,781 | 6/30/2022 |
5.2.413 | 1,374 | 6/29/2022 |
5.2.411 | 44,804 | 6/22/2022 |
5.2.405 | 84,506 | 5/24/2022 |
5.2.404 | 4,712 | 5/19/2022 |
5.2.403 | 5,025 | 5/12/2022 |
5.2.394 | 9,207 | 5/4/2022 |
5.2.393 | 7,535 | 4/27/2022 |
5.2.390 | 14,582 | 4/20/2022 |
5.2.388 | 114,302 | 4/19/2022 |
5.2.387 | 3,518 | 4/19/2022 |
5.2.383 | 55,886 | 3/21/2022 |
5.2.375 | 63,890 | 2/22/2022 |
5.2.352 | 124,834 | 11/30/2021 |
5.2.337 | 53,397 | 11/8/2021 |
5.2.328 | 63,035 | 9/23/2021 |
5.2.314 | 86,001 | 6/14/2021 |
5.2.313 | 5,578 | 6/12/2021 |
5.2.298 | 57,458 | 4/22/2021 |
5.1.294 | 6,320 | 4/19/2021 |
5.1.289 | 7,926 | 4/14/2021 |
5.1.279 | 20,887 | 3/23/2021 |
5.1.278 | 25,723 | 2/26/2021 |
5.1.277 | 1,148 | 2/25/2021 |
5.1.270 | 12,829 | 2/10/2021 |
5.1.267 | 1,066 | 2/9/2021 |
5.1.265 | 1,059 | 2/9/2021 |
5.1.264 | 1,150 | 2/9/2021 |
5.1.263 | 2,746 | 2/8/2021 |
5.1.253 | 29,517 | 1/15/2021 |
5.1.241 | 6,848 | 12/22/2020 |
5.1.227 | 13,128 | 11/25/2020 |
5.1.218 | 16,044 | 11/3/2020 |
5.1.216 | 1,231 | 11/2/2020 |
5.1.210 | 2,264 | 10/20/2020 |
5.1.194 | 9,273 | 10/15/2020 |
5.1.180 | 4,019 | 9/24/2020 |
5.1.174 | 4,729 | 8/19/2020 |
5.1.173 | 1,860 | 8/16/2020 |
5.0.166 | 33,002 | 7/1/2020 |
5.0.160 | 57,092 | 5/13/2020 |
5.0.151 | 67,779 | 4/6/2020 |
5.0.143 | 19,894 | 3/5/2020 |
5.0.141 | 4,090 | 3/1/2020 |
5.0.139 | 1,290 | 2/29/2020 |
5.0.138 | 1,272 | 2/29/2020 |
5.0.137 | 11,760 | 2/27/2020 |
5.0.118 | 75,833 | 12/7/2019 |
5.0.105 | 66,226 | 10/11/2019 |
5.0.92 | 44,057 | 9/17/2019 |
5.0.60 | 38,254 | 6/26/2019 |
5.0.46 | 18,278 | 4/2/2019 |
5.0.41 | 5,033 | 3/7/2019 |
4.0.39 | 3,354 | 2/6/2019 |
4.0.37 | 2,088 | 1/3/2019 |
4.0.34 | 4,037 | 11/26/2018 |
4.0.32 | 1,788 | 11/11/2018 |
4.0.31 | 1,675 | 11/5/2018 |
4.0.20 | 3,151 | 9/20/2018 |
1.0.18 | 1,787 | 9/12/2018 |
1.0.15 | 9,847 | 12/14/2015 |
1.0.7 | 4,535 | 11/29/2015 |