【.NET Core】Excel导入导出（NPOI.Mapper）
简介
好多功能都会涉及到Excel的操作。在.Net Core中大家可能使用Npoi比较多。但是直接使用Npoi大部分时候我们可能都会自己封装一下。
Npoi.Mapper是针对Npoi的二次封装增强了关于Mapper相关的操作。秉承着使用非常简单的原则，不过这样能够满足我们日常开发工作中很大一部分应用场景。
NPOI.Mapper源码：https://github.com/donnytian/Npoi.Mapper
案例源码：https://gitee.com/core-demo/excel
快速开始
1、安装Nuget
<PackageReference Include="Npoi.Mapper" Version="3.5.1" />
2、导出
using Npoi.Mapper;
[HttpGet]
public void Export()
{
List<Person> persons = new List<Person>();
persons.Add(new Person { Id=1,Name="fan1",Age=11,BirthDate=DateTime.Now.AddYears(11),IsActive=true});
persons.Add(new Person { Id = 1, Name = "fan2", Age = 12, BirthDate = DateTime.Now.AddYears(12), IsActive = true });
persons.Add(new Person { Id = 1, Name = "fan3", Age = 13, BirthDate = DateTime.Now.AddYears(13), IsActive = true });
persons.Add(new Person { Id = 1, Name = "fan4", Age = 14, BirthDate = DateTime.Now.AddYears(14), IsActive = false });
var mapper = new Mapper();
//第一个参数为导出Excel名称
//第二个参数为Excel数据来源
//第三个参数为导出的Sheet名称
//overwrite参数如果是要覆盖已存在的Excel或者新建Excel则为true，如果在原有Excel上追加数据则为false
//xlsx参数是用于区分导出的数据格式为xlsx还是xls
mapper.Save("Persons.xlsx", persons, "sheet1", overwrite: true, xlsx: true);
}
3、设置标题、输出配饰
mapper.Map<Person>("学生编号", p => p.Id)
.Map<Person>("姓名", p => p.Name, (column, obj) => { return true; })
.Map<Person>("年龄", p => p.Age)
.Map<Person>("生日", p => p.BirthDate)
.Map<Person>("状态", p => p.IsActive)
//格式化操作，第一个参数表示格式，第二表示对应字段
//Format不仅仅只支持时间操作，还可以是数字或金额等
.Format<Person>("yyyy-MM-dd HH", p => p.BirthDate);
4、通过Attribute配置
public class Person2
{
[Column("编号")]
public int Id { get; set; }
[Column("姓名")]
public string Name { get; set; }
[Column("年龄")]
public int Age { get; set; }
[Column("生日", CustomFormat = "yyyy-MM-dd")]
public DateTime BirthDate { get; set; }
[Column("状态")]
public bool IsActive { get; set; }
}
/// <summary>
/// 使用Attribute配置标题、格式
/// </summary>
[HttpGet]
public void Export2()
{
List<Person2> persons = new List<Person2>();
persons.Add(new Person2 { Id = 1, Name = "fan1", Age = 11, BirthDate = DateTime.Now.AddYears(11), IsActive = true });
persons.Add(new Person2 { Id = 1, Name = "fan2", Age = 12, BirthDate = DateTime.Now.AddYears(12), IsActive = true });
persons.Add(new Person2 { Id = 1, Name = "fan3", Age = 13, BirthDate = DateTime.Now.AddYears(13), IsActive = true });
persons.Add(new Person2 { Id = 1, Name = "fan4", Age = 14, BirthDate = DateTime.Now.AddYears(14), IsActive = false });
var mapper = new Mapper();
mapper.Save("Persons2.xlsx", persons, "sheet1", overwrite: true, xlsx: true);
}
5、生成多个sheet
/// <summary>
/// 多个sheet
/// </summary>
[HttpGet]
public void Export3()
{
List<Person2> persons = new List<Person2>();
persons.Add(new Person2 { Id = 1, Name = "fan1", Age = 11, BirthDate = DateTime.Now.AddYears(11), IsActive = true });
persons.Add(new Person2 { Id = 2, Name = "fan2", Age = 12, BirthDate = DateTime.Now.AddYears(12), IsActive = true });
List<Person2> persons2 = new List<Person2>();
persons2.Add(new Person2 { Id = 3, Name = "fan3", Age = 13, BirthDate = DateTime.Now.AddYears(13), IsActive = true });
persons2.Add(new Person2 { Id = 4, Name = "fan4", Age = 14, BirthDate = DateTime.Now.AddYears(14), IsActive = false });
var mapper = new Mapper();
//第一个参数是数据集合，第二个参数是Sheet名称，第三个参数表示是追加数据还是覆盖数据
mapper.Put(persons,"班级1",true);
mapper.Put(persons2,"班级2",true);
mapper.Save("Persons3.xlsx");
}
6、导出流
很多时候我们是通过Web程序直接将数据转换为文件流返回的，并不会生成Excel文件
/// <summary>
/// 导出流
/// </summary>
[HttpGet]
public IActionResult Export4()
{
List<Person2> persons = new List<Person2>();
persons.Add(new Person2 { Id = 1, Name = "fan1", Age = 11, BirthDate = DateTime.Now.AddYears(11), IsActive = true });
persons.Add(new Person2 { Id = 2, Name = "fan2", Age = 12, BirthDate = DateTime.Now.AddYears(12), IsActive = true });
persons.Add(new Person2 { Id = 3, Name = "fan3", Age = 13, BirthDate = DateTime.Now.AddYears(13), IsActive = true });
persons.Add(new Person2 { Id = 4, Name = "fan4", Age = 14, BirthDate = DateTime.Now.AddYears(14), IsActive = false });
var mapper = new Mapper();
MemoryStream stream = new MemoryStream();
mapper.Save(stream, persons, "sheet1", overwrite: true, xlsx: true);
return File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Persons.xlsx");
}
7、导入
/// <summary>
/// 导入（上传）
/// </summary>
/// <param name="formFile"></param>
/// <returns></returns>
[HttpPost]
public IEnumerable<Person2> Import(IFormFile formFile)
{
//通过上传文件流初始化Mapper
var mapper = new Mapper(formFile.OpenReadStream());
//读取sheet1的数据
var persons = mapper.Take<Person2>("sheet1").Select(i => i.Value);
return persons;
}
8、读取
/// <summary>
/// 读取excel
/// </summary>
/// <returns></returns>
[HttpPost]
public IEnumerable<Person2> Read()
{
var mapper = new Mapper("Persons3.xlsx");
//读取sheet1的数据
var persons = mapper.Take<Person2>("班级1").Select(i => i.Value);//如果不想定义poco类，也可以使用dynamic
return persons;
}
9、合并单元格？
[UseLastNonBlankValue]
10、自定义Map规则
虽然默认情况下Npoi.Mapper能帮我们满足大部分的类型映射关系，但是有时候我们需要根据我们自己的规则处理处理数据映射关系，这时候我们需要用到Map功能，他有许多重载的方法，我们就查看一个比较常用的方法做参数讲解
/// <param name="columnName">对应Excel列的名称</param>
/// <param name="propertyName">对应实体的属性名称</param>
/// <param name="tryTake">该函数用于处理从Excel读取时针对单元格数据的处理</param>
/// <param name="tryPut">该函数用于处理将数据导出到Excel是针对源数据的处理</param>
public static Mapper Map<T>(this Mapper mapper, string columnName, string propertyName,
Func<IColumnInfo, object, bool> tryTake = null,
Func<IColumnInfo, object, bool> tryPut = null)
{
}
其中tryTake用于处理从Excel导出时针对单元格数据的处理，IColumnInfo代表数据的来源，object代表对应将Row导入到某个实体中。tryPut恰恰相反，用于处理将数据导出到Excel是针对源数据的处理。其中IColumnInfo代表要导出到的列信息，object代表数据的源。简单演示一下，比如我想将上述示例中，读取到Excel里的性别数据映射到实体中的时候做一下中英文的处理，就可以使用以下操作
var mapper = new Mapper("Students.xlsx");
mapper.Map<Student>("性别", "Sex", (c, t) => {
Student student = t as Student;
student.Sex = c.CurrentValue == "男" ? "MAN" : "WOMAN";
return true;
}, null);
因为我是要读取Excel，所以使用tryTake函数，t代表target表示要映射到的实体，c代表读取到的单元格信息，我将读取到target里的数据做一下处理，如果在单元格中读取的是"男"那么对应到Student转换为"MAN"，反之则为"WOMAN"。总之你想处理一下，自定义映射逻辑都可以使用这个功能。
