NET 5 Execl导入数据处理（EppLus、NPOI、Npoi.Mapper）
先来简单介绍下市面上最广泛常见的三种操作excel库的优缺点
1.NPOI
优点:免费开源，无需装Office即可操作excel, 支持处理的文件格式包括xls, xlsx, docx.格式
缺点：不支持大数据量以及多sheet的导出
2.Aspose.Cells
优点：支持大数据量以及多sheet的导出，提供了应有尽有的文件格式支持，速度快性能佳
缺点：除了收费几乎没有缺点，试用版 限制打开文件数量100个，限制使用Aspose.Cells.GridWeb功能，生成的Excel会有水印
3.EPPlus
优点：开源免费，不需要安装office，支持图表的列印，导入导出速度快，支持高版本Excel格式，可以实现Excel上的各种基本功能
唯一缺点：仅支持xlsx格式，不支持古老的xlsx
基于业务需求和各大库优缺点对比，尽量选择合适业务需求的库，个人比较推荐的是EPPlus
本文使用的是EPPlus包来实现数据的导出，因为5.0以上的版本需要商业授权码，所以使用的是4.5.3.3的的版本
项目也是基于最新版本的.net core 3.1 web api
右键管理NuGet包添加EPPlus 选择版本添加项目引用
然后代码附上
创建excel导入帮助类Export2Excel.cs,为了使所有的地方通用，通过list泛型参数 传入数据源以及需要导出的字段标题，返回byte[],
以便直接写入文件流，也提供了基于DataTable 的操作
1、Excel .xls 和 .xlsx 有什么区别?#
区别如下： 1、文件格式不同。.xls 是一个特有的二进制格式，其核心结构是复合文档类型的结构，而.xlsx 的核心结构是 XML 类型的结构， 采用的是基于 XML 的压缩方式，使其占用的空间更小。.xlsx 中最后一个 x 的意义就在于此。 2、版本不同。.xls是excel2003及以前版本生成的文件格式，而.xlsx是excel2007及以后版本生成的文件格式。 3、兼容性不同。.xlsx格式是向下兼容的，可兼容.xls格式。
2、一号种子选手(EppLus)#
EPPlus是一个使用Open Office XML(xlsx)文件格式，能读写Excel 2007/2010 文件的开源组件， 在导出Excel的时候不需要电脑上安装office，官网为：http://epplus.codeplex.com/。 基本上Excel上的各种功能（例如图表、VBA、数据透视表、加密、数据验证等）Epplus都能实现， 它的一个缺点就是不支持导出2003版的Excel，也就是.XLS文件。
2.1 EppLus实现#
(1）添加包 EPPlus (注意:EPPlus.Core已弃用) （2）Execl导入数据使用EPPlus处理实例： /// <summary> /// 获取Exel批量用户数据（EppLus） /// </summary> /// <param name="context"></param> /// <param name="msg"></param> /// <returns></returns> public List<BatchUsersReq> GetBatchUsersData(HttpContext context,out string msg) { msg = "数据处理成功"; // 获取上传文件后缀 var extension = Path.GetExtension(context.Request.Form.Files[0].FileName).ToUpper(); if(!extension.Contains("XLSX")) { msg = "文件格式不正确,只支持XLSX文件"; return null; } // 限制单次只能上传5M float fileSize = context.Request.Form.Files[0].Length / 1024 / 1024; if(fileSize > 5) { msg = "文件大小超过限制"; return null; } try { Stream stream = context.Request.Form.Files[0].OpenReadStream(); using (var package = new ExcelPackage(stream)) { // 获取Exel指定工作簿，"Sheet1"也可以用索引代替 ExcelWorksheet worksheet = package.Workbook.Worksheets["Sheet1"]; // 获取数据行数 int RowNum = worksheet.Dimension.Rows; // 待处理数据存储列表 List<BatchUsersReq> usersData = new List<BatchUsersReq>(); // 获取每行数据 for (int row = 1; row <= RowNum; row++) { usersData.Add(new BatchUsersReq { // 获取每列数据 Account = worksheet.Cells[row, 1].Value.ToString(), Password = worksheet.Cells[row, 2].Value.ToString(), Name = worksheet.Cells[row, 3].Value.ToString(), Sex = worksheet.Cells[row, 4].Value.ToString(), UserRole = worksheet.Cells[row, 5].Value.ToString() }); } return usersData; } } catch(Exception e) { msg = "数据异常"; } return null; }
3、二号种子选手（NPOI）
NPOI是一个开源项目，可以读/写xls，doc，ppt文件，有着广泛的应用。NPIO官网地址：http://npoi.codeplex.com/ 使用NPOI能够帮助开发者在没有安装微软Office的情况下读写Office 97-2003的文件，支持的文件格式包括xls, doc, ppt等。 NPOI是构建在POI 3.x版本之上的，它可以在没有安装Office的情况 下对Word/Excel文档进行读写操作。
3.1 NPOI实现
（1）添加包 DotNetCore.NPOI （2）Execl导入数据使用EPPlus处理实例： /// <summary> /// 获取Execl批量用户数据 NPOI /// </summary> /// <param name="file">execl</param> /// <param name="msg"></param> /// <returns></returns> public List<BatchUsersReq> GetBatchUsersData(IFormFile file,out string msg) { msg = "数据处理成功"; // 获取上传文件后缀 string ext = Path.GetExtension(file.FileName).ToLower(); if(!ext.Contains("xls") && !ext.Contains("xlsx")) { msg = "文件有误，只支持上传XLS、XLSX文件"; return null; } // 限制单次只能上传5M float fileSize = file.Length / 1024 / 1024; if (fileSize > 5) { msg = "文件大小超过限制"; return null; } try { // 文件流处理 MemoryStream ms = new MemoryStream(); file.CopyTo(ms); ms.Seek(0, SeekOrigin.Begin); // 根据Excel版本进行处理 IWorkbook workbook = ext == ".xls" ? (IWorkbook)new HSSFWorkbook(ms) : new XSSFWorkbook(ms); // 获取Excel第一张工作簿 ISheet sheet = workbook.GetSheetAt(0); // 获取数据行数 int num = sheet.LastRowNum; // 待处理用户数据 List<BatchUsersReq> users = new List<BatchUsersReq>(); for (int i = 1; i <= num; i++) { // 获取指定行数据 IRow row = sheet.GetRow(i); BatchUsersReq user = new BatchUsersReq(); // 获取指定列数据 user.Account = row.GetCell(0).ToString(); user.Password = row.GetCell(1).ToString(); user.Name = row.GetCell(2).ToString(); user.Sex = row.GetCell(3).ToString(); user.UserRole = row.GetCell(4).ToString(); users.Add(user); } return users; } catch(Exception e) { msg = "数据处理出错"; } return null; }
4、踩坑心得
在使用一个库之前一定要多了解全面，多几个库对比然后选择符合自己需求的。
我刚开始参考的EppLus博文里面并没有说Epplus不支持.xls，IF判断逻辑也是两种都支持。
而我恰巧是上传的.xls格式，导致代码在读取工作簿的时候就报错，我以为是文件流的问题导致读取不到所以折腾了很久。
后来百度知道了Epplus不支持.xls，于是机智的我的直接手动把.xls改成了.xlsx。(哭唧唧)
结果当然还是不行，于是我又几番百度了解到了NPOI……
EppLus使用
using System; using System.Collections.Generic; using System.Data; using System.IO; using System.Linq; using System.Reflection; using Newtonsoft.Json; using OfficeOpenXml; using OfficeOpenXml.Style; namespace Common.Utils { public class Export2Excel { /// <summary> /// 生成excel /// </summary> /// <param name="dtSource">数据源</param> /// <param name="title">标题(Sheet名)</param> /// <param name="showTitle">是否显示</param> /// <returns></returns> public static MemoryStream Export(DataTable dtSource, string title, bool showTitle = true) { using (ExcelPackage package = new ExcelPackage()) { ExcelWorksheet workSheet = package.Workbook.Worksheets.Add(title); int maxColumnCount = dtSource.Columns.Count; int curRowIndex = 0; if (showTitle == true) { curRowIndex++; //主题 workSheet.Cells[curRowIndex, 1, 1, maxColumnCount].Merge = true; workSheet.Cells[curRowIndex, 1].Value = title; var headerStyle = workSheet.Workbook.Styles.CreateNamedStyle("headerStyle"); headerStyle.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; headerStyle.Style.Font.Bold = true; headerStyle.Style.Font.Size = 20; workSheet.Cells[curRowIndex, 1].StyleName = "headerStyle"; curRowIndex++; //导出时间栏 workSheet.Cells[curRowIndex, 1, 2, maxColumnCount].Merge = true; workSheet.Cells[curRowIndex, 1].Value = "导出时间：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm"); workSheet.Cells[curRowIndex, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; } curRowIndex++; var titleStyle = workSheet.Workbook.Styles.CreateNamedStyle("titleStyle"); titleStyle.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; titleStyle.Style.Font.Bold = true; //标题 for (var i = 0; i < maxColumnCount; i++) { DataColumn column = dtSource.Columns[i]; workSheet.Cells[curRowIndex, i + 1].Value = column.ColumnName; workSheet.Cells[curRowIndex, i + 1].StyleName = "titleStyle"; } workSheet.View.FreezePanes(curRowIndex, 1);//冻结标题行 //内容 for (var i = 0; i < dtSource.Rows.Count; i++) { curRowIndex++; for (var j = 0; j < maxColumnCount; j++) { DataColumn column = dtSource.Columns[j]; var row = dtSource.Rows[i]; object value = row[column]; var cell = workSheet.Cells[curRowIndex, j + 1]; var pType = column.DataType; pType = pType.Name == "Nullable`1" ? Nullable.GetUnderlyingType(pType) : pType; if (pType == typeof(DateTime)) { cell.Style.Numberformat.Format = "yyyy-MM-dd hh:mm"; cell.Value = Convert.ToDateTime(value); } else if (pType == typeof(int)) { cell.Value = Convert.ToInt32(value); } else if (pType == typeof(double) || pType == typeof(decimal)) { cell.Value = Convert.ToDouble(value); } else { cell.Value = value == null ? "" : value.ToString(); } workSheet.Cells[curRowIndex, j + 1].Value = row[column].ToString(); } } workSheet.Cells[workSheet.Dimension.Address].Style.Font.Name = "宋体"; workSheet.Cells[workSheet.Dimension.Address].AutoFitColumns();//自动填充 for (var i = 1; i <= workSheet.Dimension.End.Column; i++) { workSheet.Column(i).Width = workSheet.Column(i).Width + 2; }//在填充的基础上再加2 MemoryStream ms = new MemoryStream(package.GetAsByteArray()); return ms; } } /// <summary> /// 生成excel /// </summary> /// <typeparam name="T"></typeparam> /// <param name="dtSource">数据源</param> /// <param name="columns">导出字段表头合集</param> /// <param name="title">标题(Sheet名)</param> /// <param name="showTitle">是否显示标题</param> /// <returns></returns> public static byte[] Export<T>(IList<T> dtSource, ExportColumnCollective columns, string title, bool showTitle = true) { using (ExcelPackage package = new ExcelPackage()) { ExcelWorksheet workSheet = package.Workbook.Worksheets.Add(title); int maxColumnCount = columns.ExportColumnList.Count; int curRowIndex = 0; //Excel标题 if (showTitle == true) { curRowIndex++; workSheet.Cells[curRowIndex, 1, 1, maxColumnCount].Merge = true; workSheet.Cells[curRowIndex, 1].Value = title; var headerStyle = workSheet.Workbook.Styles.CreateNamedStyle("headerStyle"); headerStyle.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; headerStyle.Style.Font.Bold = true; headerStyle.Style.Font.Size = 20; workSheet.Cells[curRowIndex, 1].StyleName = "headerStyle"; curRowIndex++; //导出时间 workSheet.Cells[curRowIndex, 1, 2, maxColumnCount].Merge = true; workSheet.Cells[curRowIndex, 1].Value = "导出时间：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm"); workSheet.Cells[curRowIndex, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; } //数据表格标题(列名) for (int i = 0, rowCount = columns.HeaderExportColumnList.Count; i < rowCount; i++) { curRowIndex++; workSheet.Cells[curRowIndex, 1, curRowIndex, maxColumnCount].Style.Font.Bold = true; var curColSpan = 1; for (int j = 0, colCount = columns.HeaderExportColumnList[i].Count; j < colCount; j++) { var colColumn = columns.HeaderExportColumnList[i][j]; var colSpan = FindSpaceCol(workSheet, curRowIndex, curColSpan); if (j == 0) curColSpan = colSpan; var toColSpan = colSpan + colColumn.ColSpan; var cell = workSheet.Cells[curRowIndex, colSpan, colColumn.RowSpan + curRowIndex, toColSpan]; cell.Merge = true; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; workSheet.Cells[curRowIndex, colSpan].Value = colColumn.Title; curColSpan += colColumn.ColSpan; } } workSheet.View.FreezePanes(curRowIndex + 1, 1);//冻结标题行 Type type = typeof(T); PropertyInfo[] propertyInfos = type.GetProperties(); if (propertyInfos.Count() == 0 && dtSource.Count > 0) propertyInfos = dtSource[0].GetType().GetProperties(); //数据行 for (int i = 0, sourceCount = dtSource.Count(); i < sourceCount; i++) { curRowIndex++; for (var j = 0; j < maxColumnCount; j++) { var column = columns.ExportColumnList[j]; var cell = workSheet.Cells[curRowIndex, j + 1]; foreach (var propertyInfo in propertyInfos) { if (column.Field == propertyInfo.Name) { object value = propertyInfo.GetValue(dtSource[i]); var pType = propertyInfo.PropertyType; pType = pType.Name == "Nullable`1" ? Nullable.GetUnderlyingType(pType) : pType; if (pType == typeof(DateTime)) { cell.Style.Numberformat.Format = "yyyy-MM-dd hh:mm"; cell.Value = Convert.ToDateTime(value); } else if (pType == typeof(int)) { cell.Style.Numberformat.Format = "#0"; cell.Value = Convert.ToInt32(value); } else if (pType == typeof(double) || pType == typeof(decimal)) { if (column.Precision != null) cell.Style.Numberformat.Format = "#,##0.00";//保留两位小数 cell.Value = Convert.ToDouble(value); } else { cell.Value = value == null ? "" : value.ToString(); } } } } } workSheet.Cells[workSheet.Dimension.Address].Style.Font.Name = "宋体"; workSheet.Cells[workSheet.Dimension.Address].AutoFitColumns();//自动填充 for (var i = 1; i <= workSheet.Dimension.End.Column; i++) { workSheet.Column(i).Width = workSheet.Column(i).Width + 2; }//在填充的基础上再加2 return package.GetAsByteArray(); } } private static int FindSpaceCol(ExcelWorksheet workSheet, int row, int col) { if (workSheet.Cells[row, col].Merge) { return FindSpaceCol(workSheet, row, col + 1); } return col; } } //导出所需要映射的字段和表头集合 public class ExportColumnCollective { /// <summary> /// 字段列集合 /// </summary> public List<ExportColumn> ExportColumnList { get; set; } /// <summary> /// 表头或多表头集合 /// </summary> public List<List<ExportColumn>> HeaderExportColumnList { get; set; } } //映射excel实体 public class ExportColumn { /// <summary> /// 标题 /// </summary> [JsonProperty("title")] public string Title { get; set; } /// <summary> /// 字段 /// </summary> [JsonProperty("field")] public string Field { get; set; } /// <summary> /// 精度(只对double、decimal有效) /// </summary> [JsonProperty("precision")] public int? Precision { get; set; } /// <summary> /// 跨列 /// </summary> [JsonProperty("colSpan")] public int ColSpan { get; set; } /// <summary> /// 跨行 /// </summary> [JsonProperty("rowSpan")] public int RowSpan { get; set; } } }
OK,有了通用帮助类库，剩下的就是针对具体业务所需而提供相应字段和表头的隐射，既可以实现文件的导出
别忘了添加引用命名空间using Common.Utils;
我们来看一下API
[HttpGet("ExportExcel") public FileResult ExportExcel() { IList<Gogo> list = new List<Gogo> { new Gogo { Name = "张三", Age = 18, Card = "41234567890", CreateTime = DateTime.Now, }, new Gogo { Name = "李四", Age = 20, Card = "4254645461", CreateTime = DateTime.Now, }, }; //导出表头和字段集合 ExportColumnCollective ecc = new ExportColumnCollective(); //导出字段集合 ecc.ExportColumnList = new List<ExportColumn> { new ExportColumn{Field = "Name"}, new ExportColumn{Field = "Card"}, new ExportColumn{Field = "Age"}, new ExportColumn{Field = "CreateTime"}, }; //导出表头集合 ecc.HeaderExportColumnList = new List<List<ExportColumn>> { //使用list是为了后续可能有多表头合并列的需求，这里只需要单个表头所以一个list就ok了 new List<ExportColumn> { new ExportColumn{Title = "姓名"}, new ExportColumn{Title = "身份号"}, new ExportColumn{Title = "年龄"}, new ExportColumn{Title = "添加时间"} }, //new List<ExportColumn> //{ // new ExportColumn{Title = "子标题A",ColSpan = 1}, // new ExportColumn{Title = "子标题B",ColSpan = 1} //}, }; byte[] result = Export2Excel.Export<Gogo>(list, ecc, "测试导出", false); return File(result, "application/vnd.ms-excel", "导出报表.xlsx"); }
导入
[HttpPost] public List<ExcelDemoDto> Import([FromForm] ImportExcelInput input) { var list = new List<ExcelDemoDto>(); using (var package = new ExcelPackage(input.ExcelFile.OpenReadStream())) { // 获取到第一个Sheet，也可以通过 Worksheets["name"] 获取指定的工作表 var sheet = package.Workbook.Worksheets.First(); #region 获取开始和结束行列的个数，根据个数可以做各种校验工作 // +1 是因为第一行往往我们获取到的都是Excel的标题 int startRowNumber = sheet.Dimension.Start.Row + 1; int endRowNumber = sheet.Dimension.End.Row; int startColumn = sheet.Dimension.Start.Column; int endColumn = sheet.Dimension.End.Column; #endregion // 循环获取整个Excel数据表数据 for (int currentRow = startRowNumber; currentRow <= endRowNumber; currentRow++) { list.Add(new ExcelDemoDto { AAA = sheet.Cells[currentRow, 1].Text, BBB = sheet.Cells[currentRow, 2].Text, CCC = sheet.Cells[currentRow, 3].Text, DDD = sheet.Cells[currentRow, 4].Text, EEE = sheet.Cells[currentRow, 5].Text, FFF = sheet.Cells[currentRow, 6].Text }); } } return list; } public class ExcelDemoDto { public string AAA { get; set; } public string BBB { get; set; } public string CCC { get; set; } public string DDD { get; set; } public string EEE { get; set; } public string FFF { get; set; } } public class ImportExcelInput { public IFormFile ExcelFile { get; set; } }
导出
[HttpGet] public async Task<string> Export() { using var package = new ExcelPackage(); var worksheet = package.Workbook.Worksheets.Add("sheet1"); var headers = new string[] { "AAA", "BBB", "CCC", "DDD", "EEE", "FFF" }; for (int i = 0; i < headers.Length; i++) { worksheet.Cells[1, i + 1].Value = headers[i]; worksheet.Cells[1, i + 1].Style.Font.Bold = true; } // 模拟数据 var list = new List<ExcelDemoDto>(); for (int i = 1; i <= 10; i++) { list.Add(new ExcelDemoDto { AAA = $"A{i}", BBB = $"B{i}", CCC = $"C{i}", DDD = $"D{i}", EEE = $"E{i}", FFF = $"F{i}" }); } // 支持各种直接获取数据的方法 // worksheet.Cells.Load*... int row = 2; foreach (var item in list) { worksheet.Cells[row, 1].Value = item.AAA; worksheet.Cells[row, 2].Value = item.BBB; worksheet.Cells[row, 3].Value = item.CCC; worksheet.Cells[row, 4].Value = item.DDD; worksheet.Cells[row, 5].Value = item.EEE; worksheet.Cells[row, 6].Value = item.FFF; row++; } // 通常做法是，将excel上传至对象存储，获取到下载链接，这里将其输出到项目根目录。 var path = Path.Combine(Directory.GetCurrentDirectory(), $"excel.xlsx"); await package.GetAsByteArray().DownloadAsync(path); return path; }
Npoi.Mapper
https://www.cnblogs.com/wucy/p/14125392.html
