.Net Core+NPOI快速导入导出Excel
来源:.Net Core+NPOI快速导入导出Excel - 简书 (jianshu.com)
地址:放手去做/Npoi.Mapper (gitee.com)
Excel导入导出在开发中是非常常见的,对Excel操作,NPOI使用的是最常用的,但单单用NPOI,要写得代码还是比较多的,可以借助一个Npoi.Mapper库,操作起来就非常简单了,十来行代码就可以对Excel和List互相转换,非常简便。
首先NuGet引入NPOI和Npoi.Mapper
Npoi.Mapper为我们提供了两种处理方式,一种是通过Fluent的方式指定映射关系如下所示(添加映射):
var mapper = new Mapper(); //第一个参数表示导出的列名,第二个表示对应的属性字段 mapper.Map<Student>("姓名", s => s.Name) .Map<Student>("学号", s => s.Id) .Map<Student>("性别", s => s.Sex) .Map<Student>("生日", s => s.BirthDay) //格式化操作,第一个参数表示格式,第二表示对应字段 //Format不仅仅只支持时间操作,还可以是数字或金额等 .Format<Student>("yyyy-MM-dd", s => s.BirthDay); mapper.Save("Students.xlsx", students, "sheet1", overwrite: true, xlsx:true);
![](https://img2020.cnblogs.com/blog/2074361/202109/2074361-20210918172037367-439115965.png)
增加一个ExcelHelper
public class ExcelHelper { /// <summary> /// List转Excel /// </summary> /// <typeparam name="T"></typeparam> /// <param name="list">数据</param> /// <param name="sheetName">表名</param> /// <param name="overwrite">true,覆盖单元格,false追加内容(list和创建的excel或excel模板)</param> /// <param name="xlsx">true-xlsx,false-xls</param> /// <returns>返回文件</returns> public static MemoryStream ParseListToExcel<T>(List<T> list, string sheetName = "sheet1", bool overwrite = true, bool xlsx = true) where T : class { var mapper = new Mapper(); MemoryStream ms = new MemoryStream(); mapper.Save<T>(ms, list, sheetName, overwrite, xlsx); return ms; } /// <summary> /// Excel转为List /// </summary> /// <typeparam name="T"></typeparam> /// <param name="fileStream"></param> /// <param name="sheetname"></param> /// <returns></returns> public static List<T> ParseExcelToList<T>(Stream fileStream, string sheetname = "") where T : class { List<T> ModelList = new List<T>(); var mapper = new Mapper(fileStream); List<RowInfo<T>> DataList = new List<RowInfo<T>>(); if (!string.IsNullOrEmpty(sheetname)) { DataList = mapper.Take<T>(sheetname).ToList(); } else { DataList = mapper.Take<T>().ToList(); } if (DataList != null && DataList.Count > 0) { foreach (var item in DataList) { ModelList.Add(item.Value); } } return ModelList; } }
增加一个User Model用作导出,Column指明导出时表头显示的列名
public class User
{
[Column("用户ID")]
public int UserId { get; set; }
[Column("用户名")]
public string UserName { get; set; }
[Column("手机号")]
public string Phone { get; set; }
[Column("邮箱")]
public string Email { get; set; }
}
增加一个FileController控制器
public class FileController : Controller { public IActionResult Index() { return View(); } [HttpPost] public IActionResult Upload() { //用户List List<User> users = new List<User>(); //获取到上传的文件 var file = Request.Form.Files[0]; //文件转成流 using (var fileStream = file.OpenReadStream()) { //excel转实体 users = ExcelHelper.ParseExcelToList<User>(fileStream, "Sheet1"); } return Content(JsonConvert.SerializeObject(users)); } public IActionResult DownLoad() { //生成List List<User> usrs = new List<User>(); for(int i=1;i<=10;i++) { User user = new User() { UserId = i, UserName = "用户" + i, Phone = "13800000000", Email = $"user{i}@qq.com" }; usrs.Add(user); } //List转为Excel文件 var fileStream = ExcelHelper.ParseListToExcel(usrs); return File(fileStream.ToArray(), "application/vnd.ms-excel", "用户信息.xlsx"); } }
Index的视图
@{ } <a href="/File/DownLoad">导出excel文件</a> <form action="/File/Upload" method="post" enctype="multipart/form-data" > 选择excel文件: <input type="file" name="file" /> <input type="submit" value="上传excel" /> </form>
运行后界面
![](https://img2020.cnblogs.com/blog/2074361/202109/2074361-20210918172149685-280092055.png)
导出,点击导出excel文件
![](https://img2020.cnblogs.com/blog/2074361/202109/2074361-20210918172159760-788563660.png)
打开导出的文件,内容正是生成的列表信息
![](https://img2020.cnblogs.com/blog/2074361/202109/2074361-20210918172210370-1597225746.png)
导入,把我们刚下载的用户信息.xlsx上传到后台
![](https://img2020.cnblogs.com/blog/2074361/202109/2074361-20210918172218940-1033924816.png)
可以看到刚才的excel已经转换为User的集合了
![](https://img2020.cnblogs.com/blog/2074361/202109/2074361-20210918172231147-1489024418.png)
![](https://img2020.cnblogs.com/blog/2074361/202109/2074361-20210918172239953-957104228.png)
这个组件操作Excel和Model的互相转换真的是简单快捷,推荐使用。
源码地址:https://github.com/weixiaolong325/ExcelDemo
作者:baoziwxl
链接:https://www.jianshu.com/p/03f6ea698e3f
来源:简书