.Net Core+NPOI快速导入导出Excel

志不强者智不达，言不信者行不果。这篇文章主要讲述.Net Core+NPOI快速导入导出Excel相关的知识，希望能为你提供帮助。
Excel导入导出在开发中是非常常见的，对Excel操作，NPOI使用的是最常用的，但单单用NPOI，要写得代码还是比较多的，可以借助一个Npoi.Mapper库，操作起来就非常简单了，十来行代码就可以对Excel和List互相转换，非常简便。
一、简单封装首先NuGet引入NPOI和Npoi.Mapper

文章图片

增加一个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="https://www.songbingjia.com/File/DownLoad"> 导出excel文件< /a>
< form action="/File/Upload" method="post" enctype="multipart/form-data" >
选择excel文件:< input type="file" name="file" />
< input type="submit" value="https://www.songbingjia.com/android/上传excel" />
< /form>

【.Net Core+NPOI快速导入导出Excel】
运行后界面