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.
Convention-based mapper between strong typed object and Excel data via NPOI (Telegram group https://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...
v3 now support to import and export as
dynamictype.
In the Package Manager Console:
PM> Install-Package Npoi.Mapper
var mapper = new Mapper("Book1.xlsx"); var objs1 = mapper.Take("sheet2");// You can take objects from the same sheet with different type. var objs2 = mapper.Take("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. var objs3 = mapper.Take("sheet1").ToList(); DateTime date = obj3[0].DateColumn; double number = obj3[0].NumberColumn; string text = obj3[0].AC; // If the column doesn't have a header name, Excel display name like "AC" will be populated.
More use cases please check out source in "test" project.
Set
overwriteparameter to false to use existing columns and formats, otherwise always create new file.
C# //var objects = ... var mapper = new Mapper(); mapper.Save("test.xlsx", objects, "newSheet", overwrite: false);
Set
TrackObjectsproperty to true, objects can be tracked after a
Takemethod and then you can modify and save them back.
C# var mapper = new Mapper("Book1.xlsx"); mapper.TrackObjects = true; // It's default true. var objectInfos = mapper.Take("sheet2"); // You can Take first then modify tracked objects. var objectsDict = mapper.Objects; // Also you can directly access objects in a sheet by property. mapper.Save("test.xlsx", "sheet2");
Set
overwriteparameter to true, existing data rows will be overwritten, otherwise new rows will be appended.
C# var mapper = new Mapper("Book1.xlsx"); mapper.Put(products, "sheet1", true); mapper.Put(orders, "sheet2", false); mapper.Save("Book1.xlsx");
Map
ColumnAttribute
DisplayAttributeof property.
By fluent mapping methods:
mapper.Map("ColumnA", o => o.Property1) .Map(1, o => o.Property2) .Ignore(o => o.Property3) .UseLastNonBlankValue(o => o.Property1) .Format("yyyy/MM/dd", o => o.DateProperty) .DefaultResolverType = typeof (SampleColumnResolver);
Or by Attributes tagged on object properties:
public class SampleClass { // Other properties...[Display(Name = "Display Name")] public string DisplayNameProperty { get; set; } [Column(1)] public string Property1 { get; set; } [Column("ColumnABC")] public string Property2 { get; set; } [Column(CustomFormat = "0%")] public double CustomFormatProperty { get; set; } [UseLastNonBlankValue] public string UseLastNonBlankValueAttributeProperty { get; set; } [Ignore] public string IgnoredAttributeProperty { 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("yyyy/MM/dd", o => o.DateProperty) .Format("0%", o => o.DoubleProperty);
Or by
ColumnAttribute:
public class SampleClass { [Column(CustomFormat = "yyyy-MM-dd")] public DateTime DateTimeFormatProperty { get; set; }[Column(CustomFormat = "0%")] public double CustomFormatProperty { 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 at custom formats.
Use overload of
Mapmethod 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.var index = column.Attribute.Index; if ((index == 31 || index == 33) && column.HeaderValue is double) { // Assign back header value and use it from TryTake method. column.HeaderValue = DateTime.FromOADate((double)column.HeaderValue); return true; } return false; }, (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) return false; if (!(column.HeaderValue is DateTime)) return false; ((SampleClass)target).CollectionGenericProperty.Add(((DateTime)column.HeaderValue).ToLongDateString() + column.CurrentValue); return true; }, (column, source) => // tryPut resolver : Custom logic to put property value into cell. { if (column.HeaderValue is double) { column.HeaderValue = DateTime.FromOADate((double)column.HeaderValue); } var s = ((DateTime)column.HeaderValue).ToLongDateString(); // Custom logic to set the cell value. var sample = (SampleClass) source; if (column.Attribute.Index == 31 && sample.CollectionGenericProperty.Count > 0) { column.CurrentValue = sample.CollectionGenericProperty?.ToList()[0].Remove(0, s.Length); } else if (column.Attribute.Index == 33 && sample.CollectionGenericProperty.Count > 1) { column.CurrentValue = sample.CollectionGenericProperty?.ToList()[1].Remove(0, s.Length); } return true; } );