- Notifications
You must be signed in to change notification settings - Fork116
Use this tool to import or export data with Excel file. The tool is a convention based mapper between strong typed object and Excel data via NPOI.
License
donnytian/Npoi.Mapper
Folders and files
| Name | Name | Last commit message | Last commit date | |
|---|---|---|---|---|
Repository files navigation
| Branch | Build & Test | Publish |
|---|---|---|
| master |  |  |
| develop |  | N/A |
Convention-based mapper between strong typed object and Excel data via NPOI (Telegram grouphttps://t.me/npoidevs).
This project comes up with a task of my work, I am using it a lot in my project. Feel free to file bugs or raise pull requests...
Support to import and export asdynamic type.
Supportnested property mapping (only work for explicit mapping byMap method).
In the Package Manager Console:
PM> Install-Package Npoi.Mapper
varmapper=newMapper("Book1.xlsx");varobjs1=mapper.Take<SampleClass>("sheet2");// You can take objects from the same sheet with different type.varobjs2=mapper.Take<AnotherClass>("sheet2");// Even you can use dynamic type.// DateTime, double and string will be auto-detected for object properties.// You will get a DateTime property only if the cell in Excel was formatted as a date, otherwise it will be a double.varobjs3=mapper.Take<dynamic>("sheet1").ToList();DateTimedate=obj3[0].Value.DateColumn;doublenumber=obj3[0].Value.NumberColumn;stringtext=obj3[0].Value.AC;// If the column doesn't have a header name, Excel display name like "AC" will be populated.
Take<Dynamic> is a quick way to take data by conventions, all column types will be auto-detected from the first data row.
However, we may encounter unexpected data at the first row in the real world, that makes the mapper to infer a wrong type.For example, we actually want astring column, but a numeric value sit in the first row cell, this makes mapper take this column asdouble and report errors for the following non-numeric values.
TakeDynamicWithColumnType() allow you predefine the type for any column by accepting an extra parameter.
varmapper=newMapper(workbook);varobjs=mapper.TakeDynamicWithColumnType(header=>header.ColumnIndexswitch// Inspect column index or header cell to make decision.{0=>typeof(int),// Make the 1st column as int1=>typeof(DateTime),// Make the 2nd column as DateTime2=>typeof(string),// Make the 3rd column as string _=>null,// return null to let mapper detect from the first data row.});// Or simply take all columns as string.varobjs=mapper.TakeDynamicWithColumnType(_=>typeof(string));
If the target property is in the type ofstring, cell format and formula will be respected. That means you will get exact same string as what is displayed in Excel instead of underlying number or formula.
More use cases please check out source in "test" project.
Setoverwrite parameter to false to use existing columns and formats, otherwise always create new file.
//var objects = ...varmapper=newMapper();mapper.Save("test.xlsx",objects,"newSheet",leaveOpen:false,overwrite:false);
SetTrackObjects property to true, objects can be tracked after aTake method and then you can modify and save them back.
varmapper=newMapper("Book1.xlsx");mapper.TrackObjects=true;// It's default true.varobjectInfos=mapper.Take<SampleClass>("sheet2");// You can Take first then modify tracked objects.varobjectsDict=mapper.Objects;// Also you can directly access objects in a sheet by property.mapper.Save("test.xlsx","sheet2");
Setoverwrite parameter to true, existing data rows will be overwritten, otherwise new rows will be appended.
varmapper=newMapper("Book1.xlsx");mapper.Put(products,"sheet1",true);mapper.Put(orders,"sheet2",false);mapper.Save("Book1.xlsx",leaveOpen:false);
- Import POCOs from Excel file (XLS or XLSX) viaNPOI
- Export objects to Excel file (XLS or XLSX) (inspired byExcelMapper)
- No code required to map object properties and column headers by default naming convention (see below section)
- Support to escape and truncate chars in column header for mapping
- Also support explicit column mapping with attributes or fluent methods
- Support custom object factory injection
- Support custom header and cell resolver
- Support custom logic to handle multiple columns for collection property
- Support custom format for exporting (see Column format section)
- Fluent method
Map<T> ColumnAttribute- Default naming convention (see below section)
- Map column to property by name.
- Map column to the Name of
DisplayAttributeof property. - For column header, ignore non-alphabetical chars ("-", "_", "|' etc.), and truncate from first bracket ("(", "[", "{"), then map to property name. Ignored chars and truncation chars can be customized.
By fluent mapping methods.
Nested properties are supported.For the import, if any property in the middle of the chain is null, the mapper will create new instance -just make sure it has a public parameterless constructor.
mapper.Map<SampleClass>("ColumnA", o=>o.Property1).Map<SampleClass>(1, o=>o.Property2).Map<SampleClass>(2, o=>o.Customer.Address.ZipCode).Ignore<SampleClass>(o=>o.Property3).UseLastNonBlankValue<SampleClass>(o=>o.Property1).Format<SampleClass>("yyyy/MM/dd", o=>o.DateProperty).DefaultResolverType=typeof(SampleColumnResolver);
Or by Attributes tagged on object properties:
publicclassSampleClass{// Other properties...[Display(Name="Display Name")]publicstringDisplayNameProperty{get;set;}[Column(1)]publicstringProperty1{get;set;}[Column("ColumnABC")]publicstringProperty2{get;set;}[Column(CustomFormat="0%")]publicdoubleCustomFormatProperty{get;set;}[UseLastNonBlankValue]publicstringUseLastNonBlankValueAttributeProperty{get;set;}[Ignore]publicstringIgnoredAttributeProperty{get;set;}}
When you use a format during import, it will try to parse string value with specified format.
When you use a format during export, it will try to set Excel display format with specified format.
By method:
mapper.Format<SampleClass>("yyyy/MM/dd", o=>o.DateProperty).Format<SampleClass>("0%", o=>o.DoubleProperty);
Or byColumnAttribute:
publicclassSampleClass{[Column(CustomFormat="yyyy-MM-dd")]publicDateTimeDateTimeFormatProperty{get;set;}[Column(CustomFormat="0%")]publicdoubleCustomFormatProperty{get;set;}}
Or if you want to set format for all properties in a same type:
mapper.UseFormat(typeof(DateTime),"yyyy.MM.dd hh.mm.ss");
You can find format details atcustom formats.
Use overload ofMap method to handle complex scenarios. Such as data conversion or retrieve values cross columns for a collection property.
mapper.Map( column=>// column filter : Custom logic to determine whether or not to map and include an unmapped column.{// Header value is either in string or double. Try convert by needs.varindex=column.Attribute.Index;if((index==31||index==33)&&column.HeaderValueisdouble){// Assign back header value and use it from TryTake method.column.HeaderValue=DateTime.FromOADate((double)column.HeaderValue);returntrue;}returnfalse;},(column,target)=>// tryTake resolver : Custom logic to take cell value into target object.{// Note: return false to indicate a failure; and that will increase error count.if(column.HeaderValue==null||column.CurrentValue==null)returnfalse;if(!(column.HeaderValueisDateTime))returnfalse;((SampleClass)target).CollectionGenericProperty.Add(((DateTime)column.HeaderValue).ToLongDateString()+column.CurrentValue);column.RowTag="MyCustomObject";// this can be retrieved in the result items.returntrue;},(column,source)=>// tryPut resolver : Custom logic to put property value into cell.{if(column.HeaderValueisdouble){column.HeaderValue=DateTime.FromOADate((double)column.HeaderValue);}vars=((DateTime)column.HeaderValue).ToLongDateString();// Custom logic to set the cell value.varsample=(SampleClass)source;if(column.Attribute.Index==31&&sample.CollectionGenericProperty.Count>0){column.CurrentValue=sample.CollectionGenericProperty?.ToList()[0].Remove(0,s.Length);}elseif(column.Attribute.Index==33&&sample.CollectionGenericProperty.Count>1){column.CurrentValue=sample.CollectionGenericProperty?.ToList()[1].Remove(0,s.Length);}returntrue;});
About
Use this tool to import or export data with Excel file. The tool is a convention based mapper between strong typed object and Excel data via NPOI.