【.net core】Excel导入导出之Npoi.Mapper

引入Npoi.Mapper的nuget包

<PackageReference Include="Npoi.Mapper" Version="3.5.1" />

导出操作

新建类作为数据承载的载体，以下定义：

  public class MortgageInfo
    {

        /// <summary>
        /// 编号
        /// </summary>
        public string JYCode { get; set; }

        /// <summary>
        /// 签约日期
        /// </summary>
        public string OnlineSignTime { get; set; }

        /// <summary>
        /// 状态CD
        /// </summary>
        [Ignore]
        public string MortgageStatusCd { get; set; }

        /// <summary>
        /// 状态名称
        /// </summary>
        public string MortgageStatusName { get; set; }

        /// <summary>
        /// 中止类型value
        /// </summary>
        [Ignore]
        public string TransStopTypeValue { get; set; }

        /// <summary>
        /// 中止类型Name
        /// </summary>
        public string TransStopTypeName { get; set; }

        /// <summary>
        /// 中止原因
        /// </summary>
        public string TransPlanStopReason { get; set; }

        /// <summary>
        /// 公司信息
        /// </summary>
        [Ignore]
        public string CompanyID { get; set; }

        /// <summary>
        /// 转定日期
        /// </summary>
        [Ignore]
        public DateTimeOffset? DepositDate { get; set; }

    }

    //ColumnAttribute形式
    [Column("编号")]
    public string JYCode { get; set; }

    [Column("签约日期",CustomFormat = "yyyy-MM-dd")]
    public string OnlineSignTime { get; set; }
    
    [Column("状态CD")]
    public string MortgageStatusCd { get; set; }

    //注解说明

	//导出或导入数据可能想忽略某些列
    [Ignore] 
	public string IgnoredProperty { get; set; }
    //合并单元格  [此处说明：导入的数据有一列数据的值是共通值，在Excel上可以通过合并单元格的操作来显示这一列，对于合并单元格的列，对于程序来讲就是等价于所有列都是同一个值]
    [UseLastNonBlankValue]
	public string ClassName { get; set; }
	

  [HttpPost]
  [ProducesResponseType(typeof(FileStreamResult), (int)HttpStatusCode.OK)]
   public IActionResult ExportMortgageInfoByJyCodes([FromHeader(Name = "CompanyID")] string companyID,[FromBody] MortgageSupportMeritsParam input)
   {
       var mortgageExcel = _functionalSupportService.GetMortgageInfoByJyCodes(input,companyID);
       if (mortgageExcel.Count == 0) {
           throw new DomainException("未检索到数据，请调整检索条件");
       }
       //Fluent方式指定映射关系    | 也可通过ColumnAttribute的形式
       var mapper = new Mapper();
       mapper.Map<MortgageInfo>("编号", m => m.JYCode)
           .Map<MortgageInfo>("日期", m => m.OnlineSignTime)
           .Map<MortgageInfo>("当前状态", m => m.MortgageStatusName)
           .Map<MortgageInfo>("中止类型", m => m.TransStopTypeName)
           .Map<MortgageInfo>("中止原因", m => m.TransPlanStopReason);
       var memoryStream = new MemoryStream();
       //第一个参数作为导出excel名称
       //第二个参数作为Excel数据来源
       //第三个参数作为sheet名称
       //overwrite参数如果是要覆盖已存在的Excel或者新建Excel则为true，如果在原有Excel上追加数据则为false
       //xlsx参数是用于区分导出的数据格式为xlsx还是xls
       mapper.Save(memoryStream, mortgageExcel, "交按信息", overwrite:true,xlsx:true);
       return File(memoryStream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", $"交按-{DateTimeOffset.Now:yyyyMMddHHmmss}.xlsx");
   }

overwrite:如果是要覆盖已存在的Excel或者新建Excel则为true，如果在原有Excel上追加数据则为false，说白了就是控制是新建Excel文件还是在原有基础上直接追加。xlsx参数是用于区分导出的Excel格式为xlsx还是xls

导出多sheet

将不同的数据源通过一个Excel导出到不同Sheet中

static void Main(string[] args)
{
      //构建Student集合
      List<Student> students = new List<Student>
      {
          new Student{ Id = 1,Name="夫子",Sex="男",BirthDay=new DateTime(1999,10,11) },
          new Student{ Id = 2,Name="余帘",Sex="女",BirthDay=new DateTime(1999,12,12) }
      };
      //构建Person集合
      List<Person> persons = new List<Person>
      {
          new Person{ Id = 1,Name="陈某", Tel= 18833445566},
          new Person{ Id = 2,Name="柯浩然", Tel = 15588997766}
      };
      var mapper = new Mapper();
      //放入Mapper中
      //第一个参数是数据集合，第二个参数是Sheet名称，第三个参数表示是追加数据还是覆盖数据
      mapper.Put<Student>(students, "student",true);
      mapper.Put<Person>(persons, "person",true);
      mapper.Save("Human.xlsx");
}
//通过Web程序直接将数据转换为文件流返回的，并不会生成Excel文件，Npoi.Mapper很贴心的为我们提供了将数据读取到Stream的操作，操作方式如下
[HttpGet]
public ActionResult DownLoadFile()
{
    List<Student> students = new List<Student>
    {
        new Student{ Id = 1,Name="夫子",Sex="男",BirthDay=new DateTime(1999,10,11) },
        new Student{ Id = 2,Name="余帘",Sex="女",BirthDay=new DateTime(1999,12,12) },
        new Student{ Id = 3,Name="李慢慢",Sex="男",BirthDay=new DateTime(1999,11,11) },
        new Student{ Id = 4,Name="叶红鱼",Sex="女",BirthDay=new DateTime(1999,10,10) }
    };

    var mapper = new Mapper();
    MemoryStream stream = new MemoryStream();
    //将students集合生成的Excel直接放置到Stream中
    mapper.Save(stream, students, "sheet1", overwrite: true, xlsx: true);
    return File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet","Student.xlsx");
}

Save提供了几个重载方法，其中有一个就是将数据保存到Stream中，但是这里也踩到了一个坑，不过这个是Npoi的坑并不是Npoi.Mapper的坑，那就是Workbook.Write(stream)的时候会将stream关闭，如果继续操作这个Stream会报流已关闭的错误，而Npoi.Mapper的Save到Stream的方法恰恰是对这个方法的封装，这也是为何上面我没直接在File中直接返回Stream，而是将其转换为byte数组再返回的原因

导入操作

//Excel文件的路径
var mapper = new Mapper("Students.xlsx");
//读取的sheet信息
var studentRows = mapper.Take<Student>("sheet1");
foreach (var row in studentRows)
{
    //映射的数据保留在value中
    Student student = row.Value;
    Console.WriteLine($"姓名:[{student.Name}],学号:[{student.Id}],性别:[{student.Sex}],生日:[{student.BirthDay:yyyy-MM-dd}]");
}

//通过Take方法直接读取出来的是RowInfo集合，RowInfo是用来包装读取数据的包装类。通过它可以获取读取的行号，或读取过程中可能会出现异常情况，比如某一列读取失败，它会将列信息和报错信息记录下来，如果你不需要这些信息或者觉得遍历的时候比较麻烦想直接拿到需要的集合，可以通过如下方式转换一下

var studentRows = mapper.Take<Student>("sheet1");
//通过lambda获取到Student集合
var students = studentRows.Select(i => i.Value);

//有的时候你可能不想定义一个POCO去接收返回的结果，而是想直接拿到读取信息，转换成你需要的数据格式。比如你想读取Excel中的数据，将结果转换为实体类直接入库，但是你不想定义一个专门的映射类去接收读取结果，这时候你需要一个动态类型去接收，而Npoi.Mapper恰恰提供了这样的功能，可以将Excel中的数据直接读取到dynamic中去

var mapper = new Mapper("Students.xlsx");
var studentRows = mapper.Take<dynamic>("sheet1");
foreach (var row in studentRows)
{
    var student = row.Value;
    Console.WriteLine($"姓名:[{student.姓名}],学号:[{student.学号}],性别:[{student.性别}],生日:[{student.生日:yyyy-MM-dd}]");
}

//其中你要操作的字段名称和Excel的列名是一致的，比如我的Excel列名叫姓名，那么我读取的时候对应的属性名称也叫姓名。
//同样的情况也存在于导入操作，比如许多情况下我们是通过Web接口直接上传的文件，这种场景下，我们通常能拿到上传的流信息，Npoi.Mapper也支持读取Excel文件流的形式获取Excel数据

[HttpPost] 
public IEnumerable<Student> UploadFile(IFormFile formFile)
{
    //通过上传文件流初始化Mapper
    var mapper = new Mapper(formFile.OpenReadStream());
    //读取sheet1的数据
    return mapper.Take<Student>("sheet1").Select(i=>i.Value);
}

自定义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"。总之你想处理一下，自定义映射逻辑都可以使用这个功能。