C#_.Net Core 3.1 WebAPI_Excel数据读取与写入_自定义解析封装类_支持设置标题行位置&使用excel表达式收集数据&单元格映射&标题映射&模板
本篇博客园是被任务所逼，而已有的使用nopi技术的文档技术经验又不支持我需要的应对各种复杂需求的苛刻要求，只能自己造轮子封装了，由于需要应对很多总类型的数据采集需求，因此有了本篇博客的代码封装，下面一点点介绍吧：
项目框架：.net Core 3.1
Nuget包：DotNetCore.NPOI 1.2.2
收集excel你有没有遇到过一下痛点：
1-需要收集指定行标题位置的数据，我的标题行不一定在第一行。 这个和我的csv的文档需求是一致的
2-需要采集指定单元格位置的数据生成一个对象，而不是一个列表。 这里我的方案是制定一个单元格映射类解决问题。 单元格映射类，支持表达式数据采集（我可能需要一个单元格的数据+另一个单元格的数据作为一个属性等等）
3-应对不规范标题无法转出字符串进行映射时，能不能通过制定标题的列下标建立对应关系，进行列表数据采集呢？ 本博客同时支持标题字符串数据采集和标题下标数据采集，这个就牛逼了。
4-存储含有表达式的数据，这个并不是难点，由于很重要，就在这里列一下
5-应对Excel模板文件的数据指定位置填入数据，该位置可能会变动的解决方案。本文为了应对该情况，借助了单元格映射关系，添加了模板参数名的属性处理，可以应对模板文件调整时的位置变动问题。
6-一个能同时处理excel新老版本（.xls和.xlsx），一个指定excel位置保存数据，保存含有表达式的数据，一个可以将多个不同的数据组合存放到一个excel中的需求都可以满足。
痛点大概就是上面这些了，下面写主要代码吧，供大家参考，不过封装的类方法有点多：
本文借助了NPOI程序包做了业务封装：
1-主要封装类-ExcelHelper：
该类包含很多辅助功能：比如自动帮助寻找含有指定标题名所在的位置、表达式元素A1,B2对应单元格位置的解析等等：
/// <summary> /// EXCEL帮助类 /// </summary> /// <typeparam name="T">泛型类</typeparam> /// <typeparam name="TCollection">泛型类集合</typeparam> public class ExcelHelper { public ILogger Logger { get; set; } public ExcelHelper(ILogger<ExcelHelper> logger) { this.Logger = logger; } #region 创建工作表 /// <summary> /// 将列表数据生成工作表 /// </summary> /// <param name="tList">要导出的数据集</param> /// <param name="fieldNameAndShowNameDic">键值对集合（键：字段名，值：显示名称）</param> /// <param name="workbook">更新时添加：要更新的工作表</param> /// <param name="sheetName">指定要创建的sheet名称时添加</param> /// <param name="excelFileDescription">读取或插入定制需求时添加</param> /// <returns></returns> public IWorkbook CreateOrUpdateWorkbook<T>(List<T> tList, Dictionary<string, string> fieldNameAndShowNameDic, IWorkbook workbook = null, string sheetName = "sheet1", ExcelFileDescription excelFileDescription = null) where T : new() { List<ExcelTitleFieldMapper> titleMapperList = ExcelTitleFieldMapper.GetModelFieldMapper<T>(fieldNameAndShowNameDic); workbook = this.CreateOrUpdateWorkbook<T>(tList, titleMapperList, workbook, sheetName, excelFileDescription); return workbook; } /// <summary> /// 将列表数据生成工作表（T的属性需要添加：属性名列名映射关系） /// </summary> /// <param name="tList">要导出的数据集</param> /// <param name="workbook">更新时添加：要更新的工作表</param> /// <param name="sheetName">指定要创建的sheet名称时添加</param> /// <param name="excelFileDescription">读取或插入定制需求时添加</param> /// <returns></returns> public IWorkbook CreateOrUpdateWorkbook<T>(List<T> tList, IWorkbook workbook = null, string sheetName = "sheet1", ExcelFileDescription excelFileDescription = null) where T : new() { List<ExcelTitleFieldMapper> titleMapperList = ExcelTitleFieldMapper.GetModelFieldMapper<T>(); workbook = this.CreateOrUpdateWorkbook<T>(tList, titleMapperList, workbook, sheetName, excelFileDescription); return workbook; } private IWorkbook CreateOrUpdateWorkbook<T>(List<T> tList, List<ExcelTitleFieldMapper> titleMapperList, IWorkbook workbook, string sheetName, ExcelFileDescription excelFileDescription = null) { //xls文件格式属于老版本文件，一个sheet最多保存65536行；而xlsx属于新版文件类型； //Excel 07 - 2003一个工作表最多可有65536行，行用数字1—65536表示; 最多可有256列，列用英文字母A—Z，AA—AZ，BA—BZ，……，IA—IV表示；一个工作簿中最多含有255个工作表，默认情况下是三个工作表； //Excel 2007及以后版本，一个工作表最多可有1048576行，16384列； if (workbook == null) { workbook = new XSSFWorkbook(); //workbook = new HSSFWorkbook(); } ISheet worksheet = null; if (workbook.GetSheetIndex(sheetName) >= 0) { worksheet = workbook.GetSheet(sheetName); } else { worksheet = workbook.CreateSheet(sheetName); } IRow row1 = null; ICell cell = null; int defaultBeginTitleIndex = 0; if (excelFileDescription != null) { defaultBeginTitleIndex = excelFileDescription.TitleRowIndex; } PropertyInfo propertyInfo = null; T t = default(T); int tCount = tList.Count; int currentRowIndex = 0; int dataIndex = 0; do { row1 = worksheet.GetRow(currentRowIndex); if (row1 == null) { row1 = worksheet.CreateRow(currentRowIndex); } if (currentRowIndex >= defaultBeginTitleIndex) { //到达标题行 if (currentRowIndex == defaultBeginTitleIndex) { int cellIndex = 0; foreach (var titleMapper in titleMapperList) { cell = row1.GetCell(cellIndex); if (cell == null) { cell = row1.CreateCell(cellIndex); } this.SetCellValue(cell, titleMapper.ExcelTitle, outputFormat: null); cellIndex++; } } //到达内容行 else { dataIndex = currentRowIndex - defaultBeginTitleIndex - 1; if (dataIndex <= tCount - 1) { t = tList[dataIndex]; int cellIndex = 0; foreach (var titleMapper in titleMapperList) { propertyInfo = titleMapper.PropertyInfo; cell = row1.GetCell(cellIndex); if (cell == null) { cell = row1.CreateCell(cellIndex); } this.SetCellValue<T>(cell, t, titleMapper); cellIndex++; } //重要：设置行宽度自适应(大批量添加数据时，该行代码需要注释，否则会极大减缓Excel添加行的速度！) //worksheet.AutoSizeColumn(i, true); } } } currentRowIndex++; } while (dataIndex < tCount - 1); //设置表达式重算（如果不添加该代码，表达式更新不出结果值） worksheet.ForceFormulaRecalculation = true; return workbook; } /// <summary> /// 将单元格数据列表生成工作表 /// </summary> /// <param name="commonCellList">所有的单元格数据列表</param> /// <param name="workbook">更新时添加：要更新的工作表</param> /// <param name="sheetName">指定要创建的sheet名称时添加</param> /// <returns></returns> public IWorkbook CreateOrUpdateWorkbook(CommonCellModelColl commonCellList, IWorkbook workbook = null, string sheetName = "sheet1") { //xls文件格式属于老版本文件，一个sheet最多保存65536行；而xlsx属于新版文件类型； //Excel 07 - 2003一个工作表最多可有65536行，行用数字1—65536表示; 最多可有256列，列用英文字母A—Z，AA—AZ，BA—BZ，……，IA—IV表示；一个工作簿中最多含有255个工作表，默认情况下是三个工作表； //Excel 2007及以后版本，一个工作表最多可有1048576行，16384列； if (workbook == null) { workbook = new XSSFWorkbook(); //workbook = new HSSFWorkbook(); } ISheet worksheet = null; if (workbook.GetSheetIndex(sheetName) >= 0) { worksheet = workbook.GetSheet(sheetName); } else { worksheet = workbook.CreateSheet(sheetName); } //设置首列显示 IRow row1 = null; int rowIndex = 0; int maxRowIndex = commonCellList.Max(m => m.ColumnIndex); int columnIndex = 0; int maxColumnIndex = 0; Dictionary<int, CommonCellModel> rowColumnIndexCellDIC = null; ICell cell = null; object cellValue = null; do { rowColumnIndexCellDIC = commonCellList.GetRawCellList(rowIndex).ToDictionary(m => m.ColumnIndex); maxColumnIndex = rowColumnIndexCellDIC.Count > 0 ? rowColumnIndexCellDIC.Keys.Max() : 0; if (rowColumnIndexCellDIC != null && rowColumnIndexCellDIC.Count > 0) { row1 = worksheet.GetRow(rowIndex); if (row1 == null) { row1 = worksheet.CreateRow(rowIndex); } columnIndex = 0; do { cell = row1.GetCell(columnIndex); if (cell == null) { cell = row1.CreateCell(columnIndex); } if (rowColumnIndexCellDIC.ContainsKey(columnIndex)) { cellValue = rowColumnIndexCellDIC[columnIndex].CellValue; this.SetCellValue(cell, cellValue, outputFormat: null, rowColumnIndexCellDIC[columnIndex].IsCellFormula); } columnIndex++; } while (columnIndex <= maxColumnIndex); } rowIndex++; } while (rowIndex <= maxRowIndex); //设置表达式重算（如果不添加该代码，表达式更新不出结果值） worksheet.ForceFormulaRecalculation = true; return workbook; } /// <summary> /// 更新模板文件数据：将使用单元格映射的数据T存入模板文件中 /// </summary> /// <param name="filePath">所有的单元格数据列表</param> /// <param name="t">添加了单元格参数映射的数据对象</param> /// <returns></returns> public IWorkbook CreateOrUpdateWorkbook<T>(string filePath, T t) { //该方法默认替换模板数据在首个sheet里 IWorkbook workbook = null; CommonCellModelColl commonCellColl = this._ReadCellList(filePath, out workbook); ISheet worksheet = workbook.GetSheetAt(0); //获取t的单元格映射列表 Dictionary<string, ExcelCellFieldMapper> tParamMapperDic = ExcelCellFieldMapper.GetModelFieldMapper<T>().ToDictionary(m => m.CellParamName); var rows = worksheet.GetRowEnumerator(); IRow row; ICell cell; string cellValue; ExcelCellFieldMapper cellMapper; while (rows.MoveNext()) { row = (XSSFRow)rows.Current; int cellCount = row.Cells.Count; for (int i = 0; i < cellCount; i++) { cell = row.Cells[i]; cellValue = cell.ToString(); if (tParamMapperDic.ContainsKey(cellValue)) { cellMapper = tParamMapperDic[cellValue]; this.SetCellValue<T>(cell, t, cellMapper); } } } if (tParamMapperDic.Count > 0) { //循环所有单元格数据替换指定变量数据 foreach (var cellItem in commonCellColl) { cellValue = cellItem.CellValue.ToString(); if (tParamMapperDic.ContainsKey(cellValue)) { cellItem.CellValue = tParamMapperDic[cellValue].PropertyInfo.GetValue(t); } } } //设置表达式重算（如果不添加该代码，表达式更新不出结果值） worksheet.ForceFormulaRecalculation = true; return workbook; } #endregion #region 保存工作表到文件 /// <summary> /// 保存Workbook数据为文件 /// </summary> /// <param name="workbook"></param> /// <param name="fileDirectoryPath"></param> /// <param name="fileName"></param> public void SaveWorkbookToFile(IWorkbook workbook, string filePath) { //xls文件格式属于老版本文件，一个sheet最多保存65536行；而xlsx属于新版文件类型； //Excel 07 - 2003一个工作表最多可有65536行，行用数字1—65536表示; 最多可有256列，列用英文字母A—Z，AA—AZ，BA—BZ，……，IA—IV表示；一个工作簿中最多含有255个工作表，默认情况下是三个工作表； //Excel 2007及以后版本，一个工作表最多可有1048576行，16384列； MemoryStream ms = new MemoryStream(); //这句代码非常重要，如果不加，会报：打开的EXCEL格式与扩展名指定的格式不一致 ms.Seek(0, SeekOrigin.Begin); workbook.Write(ms); byte[] myByteArray = ms.GetBuffer(); string fileDirectoryPath = filePath.Split('\\')[0]; if (!Directory.Exists(fileDirectoryPath)) { Directory.CreateDirectory(fileDirectoryPath); } string fileName = filePath.Replace(fileDirectoryPath, ""); if (File.Exists(filePath)) { File.Delete(filePath); } File.WriteAllBytes(filePath, myByteArray); } /// <summary> /// 保存workbook到字节流中(提供给API接口使用) /// </summary> /// <param name="workbook"></param> /// <returns></returns> public byte[] SaveWorkbookToByte(IWorkbook workbook) { MemoryStream stream = new MemoryStream(); stream.Seek(0, SeekOrigin.Begin); workbook.Write(stream); byte[] byteArray = stream.GetBuffer(); return byteArray; } #endregion #region 读取Excel数据 /// <summary> /// 读取Excel数据1_手动提供属性信息和标题对应关系 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="filePath"></param> /// <param name="fieldNameAndShowNameDic"></param> /// <param name="excelFileDescription"></param> /// <returns></returns> public List<T> ReadTitleDataList<T>(string filePath, Dictionary<string, string> fieldNameAndShowNameDic, ExcelFileDescription excelFileDescription) where T : new() { //标题属性字典列表 List<ExcelTitleFieldMapper> titleMapperList = ExcelTitleFieldMapper.GetModelFieldMapper<T>(fieldNameAndShowNameDic); List<T> tList = this._GetTList<T>(filePath, titleMapperList, excelFileDescription); return tList ?? new List<T>(0); } /// <summary> /// 读取Excel数据2_使用Excel标记特性和文件描述自动创建关系 /// </summary> /// <param name="filePath"></param> /// <param name="excelFileDescription"></param> /// <returns></returns> public List<T> ReadTitleDataList<T>(string filePath, ExcelFileDescription excelFileDescription) where T : new() { //标题属性字典列表 List<ExcelTitleFieldMapper> titleMapperList = ExcelTitleFieldMapper.GetModelFieldMapper<T>(); List<T> tList = this._GetTList<T>(filePath, titleMapperList, excelFileDescription); return tList ?? new List<T>(0); } private List<T> _GetTList<T>(string filePath, List<ExcelTitleFieldMapper> titleMapperList, ExcelFileDescription excelFileDescription) where T : new() { List<T> tList = new List<T>(500 * 10000); T t = default(T); using (FileStream fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { IWorkbook workbook = null; IFormulaEvaluator formulaEvaluator = null; try { workbook = new XSSFWorkbook(fileStream); formulaEvaluator = new XSSFFormulaEvaluator(workbook); } catch (Exception) { workbook = new HSSFWorkbook(fileStream); formulaEvaluator = new HSSFFormulaEvaluator(workbook); } int sheetCount = workbook.NumberOfSheets; int currentSheetIndex = 0; int currentSheetRowTitleIndex = -1; do { var sheet = workbook.GetSheetAt(currentSheetIndex); //标题下标属性字典 Dictionary<int, ExcelTitleFieldMapper> sheetTitleIndexPropertyDic = new Dictionary<int, ExcelTitleFieldMapper>(0); //如果没有设置标题行，则通过自动查找方法获取 if (excelFileDescription.TitleRowIndex < 0) { string[] titleArray = titleMapperList.Select(m => m.ExcelTitle).ToArray(); currentSheetRowTitleIndex = this.GetSheetTitleIndex(sheet, titleArray); } else { currentSheetRowTitleIndex = excelFileDescription.TitleRowIndex; } var rows = sheet.GetRowEnumerator(); bool isHaveTitleIndex = false; //含有Excel行下标 if (titleMapperList.Count > 0 && titleMapperList[0].ExcelTitleIndex >= 0) { isHaveTitleIndex = true; foreach (var titleMapper in titleMapperList) { sheetTitleIndexPropertyDic.Add(titleMapper.ExcelTitleIndex, titleMapper); } } PropertyInfo propertyInfo = null; int currentRowIndex = 0; while (rows.MoveNext()) { IRow row = (IRow)rows.Current; currentRowIndex = row.RowNum; //到达标题行 if (isHaveTitleIndex == false && currentRowIndex == currentSheetRowTitleIndex) { ICell cell = null; string cellValue = null; Dictionary<string, ExcelTitleFieldMapper> titleMapperDic = titleMapperList.ToDictionary(m => m.ExcelTitle); for (int i = 0; i < row.Cells.Count; i++) { cell = row.Cells[i]; cellValue = cell.StringCellValue; if (titleMapperDic.ContainsKey(cellValue)) { sheetTitleIndexPropertyDic.Add(i, titleMapperDic[cellValue]); } } } //到达内容行 if (currentRowIndex > currentSheetRowTitleIndex) { t = new T(); ExcelTitleFieldMapper excelTitleFieldMapper = null; foreach (var titleIndexItem in sheetTitleIndexPropertyDic) { ICell cell = row.GetCell(titleIndexItem.Key); excelTitleFieldMapper = titleIndexItem.Value; //没有数据的单元格默认为null string cellValue = cell?.ToString() ?? ""; propertyInfo = excelTitleFieldMapper.PropertyInfo; try { if (excelTitleFieldMapper.IsCheckContentEmpty) { if (string.IsNullOrEmpty(cellValue)) { t = default(T); break; } } if (excelTitleFieldMapper.IsCoordinateExpress || cell.CellType == CellType.Formula) { //读取含有表达式的单元格值 cellValue = formulaEvaluator.Evaluate(cell).StringValue; propertyInfo.SetValue(t, Convert.ChangeType(cellValue, propertyInfo.PropertyType)); } else if (propertyInfo.PropertyType.IsEnum) { object enumObj = propertyInfo.PropertyType.InvokeMember(cellValue, BindingFlags.GetField, null, null, null); propertyInfo.SetValue(t, Convert.ChangeType(enumObj, propertyInfo.PropertyType)); } else { propertyInfo.SetValue(t, Convert.ChangeType(cellValue, propertyInfo.PropertyType)); } } catch (Exception e) { this.Logger.LogError($"文件_{filePath}读取{currentRowIndex + 1}行内容失败！"); t = default(T); break; } } if (t != null) { tList.Add(t); } } } currentSheetIndex++; } while (currentSheetIndex + 1 <= sheetCount); } return tList ?? new List<T>(0); } /// <summary> /// 读取文件的所有单元格数据 /// </summary> /// <param name="filePath">文件路径</param> /// <returns></returns> public CommonCellModelColl ReadCellList(string filePath) { IWorkbook workbook = null; CommonCellModelColl commonCellColl = this._ReadCellList(filePath, out workbook); return commonCellColl; } /// <summary> /// 读取文件的所有单元格数据 /// </summary> /// <param name="filePath">文件路径</param> /// <returns></returns> public CommonCellModelColl ReadCellList(string filePath, out IWorkbook workbook) { CommonCellModelColl commonCellColl = this._ReadCellList(filePath, out workbook); return commonCellColl; } private CommonCellModelColl _ReadCellList(string filePath, out IWorkbook workbook) { CommonCellModelColl commonCellColl = new CommonCellModelColl(10000); CommonCellModel cellModel = null; workbook = null; using (FileStream fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { try { workbook = new XSSFWorkbook(fileStream); } catch (Exception) { workbook = new HSSFWorkbook(fileStream); } var sheet = workbook.GetSheetAt(0); var rows = sheet.GetRowEnumerator(); List<ICell> cellList = null; ICell cell = null; //从第1行数据开始获取 while (rows.MoveNext()) { IRow row = (IRow)rows.Current; cellList = row.Cells; int cellCount = cellList.Count; for (int i = 0; i < cellCount; i++) { cell = cellList[i]; cellModel = new CommonCellModel { RowIndex = row.RowNum, ColumnIndex = i, CellValue = cell.ToString(), IsCellFormula = cell.CellType == CellType.Formula ? true : false }; commonCellColl.Add(cellModel); } } } return commonCellColl; } /// <summary> /// 获取文件单元格数据对象 /// </summary> /// <typeparam name="T">T的属性必须标记了ExcelCellAttribute</typeparam> /// <param name="filePath">文建路径</param> /// <returns></returns> public T ReadCellData<T>(string filePath) where T : new() { T t = new T(); this.Logger.LogInformation($"开始读取{filePath}的数据..."); CommonCellModelColl commonCellColl = this.ReadCellList(filePath); Dictionary<PropertyInfo, ExcelCellFieldMapper> propertyMapperDic = ExcelCellFieldMapper.GetModelFieldMapper<T>().ToDictionary(m => m.PropertyInfo); string cellExpress = null; string pValue = null; PropertyInfo propertyInfo = null; foreach (var item in propertyMapperDic) { cellExpress = item.Value.CellCoordinateExpress; propertyInfo = item.Key; pValue = this.GetVByExpress(cellExpress, propertyInfo, commonCellColl).ToString(); if (!string.IsNullOrEmpty(pValue)) { propertyInfo.SetValue(t, Convert.ChangeType(pValue, propertyInfo.PropertyType)); } } return t; } /// <summary> /// 获取文件首个sheet的标题位置 /// </summary> /// <typeparam name="T">T必须做了标题映射</typeparam> /// <param name="filePath"></param> /// <returns></returns> public int FileFirstSheetTitleIndex<T>(string filePath) { int titleIndex = 0; if (File.Exists(filePath)) { using (FileStream fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { IWorkbook workbook = null; try { workbook = new XSSFWorkbook(fileStream); } catch (Exception) { workbook = new HSSFWorkbook(fileStream); } string[] titleArray = ExcelTitleFieldMapper.GetModelFieldMapper<T>().Select(m => m.ExcelTitle).ToArray(); ISheet sheet = workbook.GetSheetAt(0); titleIndex = this.GetSheetTitleIndex(sheet, titleArray); } } return titleIndex; } /// <summary> /// 获取文件首个sheet的标题位置 /// </summary> /// <param name="filePath"></param> /// <param name="titleNames"></param> /// <returns></returns> public int FileFirstSheetTitleIndex(string filePath, params string[] titleNames) { int titleIndex = 0; if (File.Exists(filePath)) { using (FileStream fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { IWorkbook workbook = null; try { workbook = new XSSFWorkbook(fileStream); } catch (Exception) { workbook = new HSSFWorkbook(fileStream); } ISheet sheet = workbook.GetSheetAt(0); titleIndex = this.GetSheetTitleIndex(sheet, titleNames); } } return titleIndex; } #endregion #region 辅助方法 /// <summary> /// 返回单元格坐标横坐标 /// </summary> /// <param name="cellPoint">单元格坐标（A1,B15...）</param> /// <param name="columnIndex">带回：纵坐标</param> /// <returns></returns> private int GetValueByZM(string cellPoint, out int columnIndex) { int rowIndex = 0; columnIndex = 0; Regex columnIndexRegex = new Regex("[a-zA-Z]+", RegexOptions.IgnoreCase); string columnZM = columnIndexRegex.Match(cellPoint).Value; rowIndex = Convert.ToInt32(cellPoint.Replace(columnZM, "")) - 1; int zmLen = 0; if (!string.IsNullOrEmpty(columnZM)) { zmLen = columnZM.Length; } for (int i = zmLen - 1; i > -1; i--) { columnIndex += (int)Math.Pow((int)columnZM[i] - 64, (zmLen - i)); } columnIndex = columnIndex - 1; return rowIndex; } /// <summary> /// 根据单元格表达式和单元格数据集获取数据 /// </summary> /// <param name="cellExpress">单元格表达式</param> /// <param name="commonCellColl">单元格数据集</param> /// <returns></returns> private object GetVByExpress(string cellExpress, PropertyInfo propertyInfo, CommonCellModelColl commonCellColl) { object value = null; //含有单元格表达式的取表达式值，没有表达式的取单元格字符串 if (!string.IsNullOrEmpty(cellExpress)) { MatchCollection matchCollection = Regex.Matches(cellExpress, "\\w+"); string point = null; int rowIndex = 0; int columnIndex = 0; string cellValue = null; System.Data.DataTable dt = new System.Data.DataTable(); foreach (var item in matchCollection) { point = item.ToString(); rowIndex = this.GetValueByZM(point, out columnIndex); cellValue = commonCellColl[rowIndex, columnIndex]?.CellValue?.ToString() ?? ""; if (propertyInfo.PropertyType == typeof(decimal) || propertyInfo.PropertyType == typeof(double) || propertyInfo.PropertyType == typeof(int)) { if (!string.IsNullOrEmpty(cellValue)) { cellValue = cellValue.Replace(",", ""); } else { cellValue = "0"; } } else { cellValue = $"'{cellValue}'"; } cellExpress = cellExpress.Replace(item.ToString(), cellValue); } //执行字符和数字的表达式计算（字符需要使用单引号包裹，数字需要移除逗号） value = dt.Compute(cellExpress, ""); } return value ?? ""; } /// <summary> /// 将数据放入单元格中 /// </summary> /// <typeparam name="T">泛型类</typeparam> /// <param name="cell">单元格对象</param> /// <param name="t">泛型类数据</param> /// <param name="cellFieldMapper">单元格映射信息</param> private void SetCellValue<T>(ICell cell, T t, ExcelCellFieldMapper cellFieldMapper) { object cellValue = cellFieldMapper.PropertyInfo.GetValue(t); this.SetCellValue(cell, cellValue, cellFieldMapper?.OutputFormat); } /// <summary> /// 将数据放入单元格中 /// </summary> /// <typeparam name="T">泛型类</typeparam> /// <param name="cell">单元格对象</param> /// <param name="t">泛型类数据</param> /// <param name="cellFieldMapper">单元格映射信息</param> private void SetCellValue<T>(ICell cell, T t, ExcelTitleFieldMapper cellFieldMapper) { object cellValue = cellFieldMapper.PropertyInfo.GetValue(t); this.SetCellValue(cell, cellValue, cellFieldMapper?.OutputFormat, cellFieldMapper?.IsCoordinateExpress ?? false); } /// <summary> /// 将数据放入单元格中 /// </summary> /// <param name="cell">单元格对象</param> /// <param name="cellValue">数据</param> /// <param name="outputFormat">格式化字符串</param> /// <param name="isCoordinateExpress">是否是表达式数据</param> private void SetCellValue(ICell cell, object cellValue, string outputFormat, bool isCoordinateExpress = false) { if (cell != null) { if (isCoordinateExpress) { cell.SetCellFormula(cellValue.ToString()); } else { if (!string.IsNullOrEmpty(outputFormat)) { string formatValue = null; IFormatProvider formatProvider = null; if (cellValue is DateTime) { formatProvider = new DateTimeFormatInfo(); ((DateTimeFormatInfo)formatProvider).ShortDatePattern = outputFormat; } formatValue = ((IFormattable)cellValue).ToString(outputFormat, formatProvider); cell.SetCellValue(formatValue); } else { if (cellValue is decimal || cellValue is double || cellValue is int) { cell.SetCellValue(Convert.ToDouble(cellValue)); } else if (cellValue is DateTime) { cell.SetCellValue((DateTime)cellValue); } else if (cellValue is bool) { cell.SetCellValue((bool)cellValue); } else { cell.SetCellValue(cellValue.ToString()); } } } } } /// <summary> /// 根据标题名称获取标题行下标位置 /// </summary> /// <param name="sheet">要查找的sheet</param> /// <param name="titleNames">标题名称</param> /// <returns></returns> private int GetSheetTitleIndex(ISheet sheet, params string[] titleNames) { int titleIndex = -1; if (sheet != null && titleNames != null && titleNames.Length > 0) { var rows = sheet.GetRowEnumerator(); List<ICell> cellList = null; List<string> rowValueList = null; //从第1行数据开始获取 while (rows.MoveNext()) { IRow row = (IRow)rows.Current; cellList = row.Cells; rowValueList = new List<string>(cellList.Count); foreach (var cell in cellList) { rowValueList.Add(cell.ToString()); } bool isTitle = true; foreach (var title in titleNames) { if (!rowValueList.Contains(title)) { isTitle = false; break; } } if (isTitle) { titleIndex = row.RowNum; break; } } } return titleIndex; } #endregion }
View Code
2-自定义单元格类：
public class CommonCellModel { public CommonCellModel() { } public CommonCellModel(int rowIndex, int columnIndex, object cellValue, bool isCellFormula = false) { this.RowIndex = rowIndex; this.ColumnIndex = columnIndex; this.CellValue = cellValue; this.IsCellFormula = isCellFormula; } public int RowIndex { get; set; } public int ColumnIndex { get; set; } public object CellValue { get; set; } /// <summary> /// 是否是单元格公式 /// </summary> public bool IsCellFormula { get; set; } } public class CommonCellModelColl : List<CommonCellModel>, IList<CommonCellModel> { public CommonCellModelColl() { } public CommonCellModelColl(int capacity) : base(capacity) { } /// <summary> /// 根据行下标，列下标获取单元格数据 /// </summary> /// <param name="rowIndex"></param> /// <param name="columnIndex"></param> /// <returns></returns> public CommonCellModel this[int rowIndex, int columnIndex] { get { CommonCellModel cell = this.FirstOrDefault(m => m.RowIndex == rowIndex && m.ColumnIndex == columnIndex); return cell; } set { CommonCellModel cell = this.FirstOrDefault(m => m.RowIndex == rowIndex && m.ColumnIndex == columnIndex); if (cell != null) { cell.CellValue = value.CellValue; } } } /// <summary> /// 所有一行所有的单元格数据 /// </summary> /// <param name="rowIndex">行下标</param> /// <returns></returns> public List<CommonCellModel> GetRawCellList(int rowIndex) { List<CommonCellModel> cellList = null; cellList = this.FindAll(m => m.RowIndex == rowIndex); return cellList ?? new List<CommonCellModel>(0); } /// <summary> /// 所有一列所有的单元格数据 /// </summary> /// <param name="columnIndex">列下标</param> /// <returns></returns> public List<CommonCellModel> GetColumnCellList(int columnIndex) { List<CommonCellModel> cellList = null; cellList = this.FindAll(m => m.ColumnIndex == columnIndex); return cellList ?? new List<CommonCellModel>(0); } }
View Code
3-单元格特性类：
/// <summary> /// Excel单元格标记特性 /// </summary> [System.AttributeUsage(System.AttributeTargets.Field | System.AttributeTargets.Property, AllowMultiple = false)] public class ExcelCellAttribute : System.Attribute { /// <summary> /// 该参数用于收集数据存于固定位置的单元格数据（单元格坐标表达式（如：A1,B2,C1+C2...横坐标使用26进制字母，纵坐标使用十进制数字）） /// </summary> public string CellCoordinateExpress { get; set; } /// <summary> /// 该参数用于替换模板文件的预定义变量使用（{A} {B}） /// </summary> public string CellParamName { get; set; } /// <summary> /// 字符输出格式（数字和日期类型需要） /// </summary> public string OutputFormat { get; set; } public ExcelCellAttribute(string cellCoordinateExpress = null, string cellParamName = null) { CellCoordinateExpress = cellCoordinateExpress; CellParamName = cellParamName; } public ExcelCellAttribute(string cellCoordinateExpress, string cellParamName, string outputFormat) : this(cellCoordinateExpress, cellParamName) { OutputFormat = outputFormat; } }
View Code
4-单元格属性映射帮助类：
/// <summary> /// 单元格字段映射类 /// </summary> internal class ExcelCellFieldMapper { /// <summary> /// 属性信息 /// </summary> public PropertyInfo PropertyInfo { get; set; } /// <summary> /// 该参数用于收集数据存于固定位置的单元格数据（单元格坐标表达式（如：A1,B2,C1+C2...横坐标使用26进制字母，纵坐标使用十进制数字）） /// </summary> public string CellCoordinateExpress { get; set; } /// <summary> /// 该参数用于替换模板文件的预定义变量使用（{A} {B}） /// </summary> public string CellParamName { get; set; } /// <summary> /// 字符输出格式（数字和日期类型需要） /// </summary> public string OutputFormat { get; set; } /// <summary> /// 获取对应关系_T属性添加了单元格映射关系 /// </summary> /// <typeparam name="T"></typeparam> /// <returns></returns> public static List<ExcelCellFieldMapper> GetModelFieldMapper<T>() { List<ExcelCellFieldMapper> fieldMapperList = new List<ExcelCellFieldMapper>(100); List<PropertyInfo> tPropertyInfoList = typeof(T).GetProperties().ToList(); ExcelCellAttribute cellExpress = null; foreach (var item in tPropertyInfoList) { cellExpress = item.GetCustomAttribute<ExcelCellAttribute>(); if (cellExpress != null) { fieldMapperList.Add(new ExcelCellFieldMapper { CellCoordinateExpress = cellExpress.CellCoordinateExpress, CellParamName = cellExpress.CellParamName, OutputFormat = cellExpress.OutputFormat, PropertyInfo = item }); } } return fieldMapperList; } }
View Code
5-Excel文件描述类：
public class ExcelFileDescription { /// <summary> /// 默认从第1行数据开始读取标题数据 /// </summary> public ExcelFileDescription() : this(0) { } public ExcelFileDescription(int titleRowIndex) { this.TitleRowIndex = titleRowIndex; } /// <summary> /// 标题所在行位置（默认为0，没有标题填-1） /// </summary> public int TitleRowIndex { get; set; } }
View Code
6-Excel标题特性类：
/// <summary> /// Excel标题标记特性 /// </summary> [System.AttributeUsage(System.AttributeTargets.Field | System.AttributeTargets.Property, AllowMultiple = false)] public class ExcelTitleAttribute : System.Attribute { /// <summary> /// Excel行标题(标题和下标选择一个即可) /// </summary> public string RowTitle { get; set; } /// <summary> /// Excel行下标(标题和下标选择一个即可,默认值-1) /// </summary> public int RowTitleIndex { get; set; } /// <summary> /// 单元格是否要检查空数据（true为检查，为空的行数据不添加） /// </summary> public bool IsCheckContentEmpty { get; set; } /// <summary> /// 字符输出格式（数字和日期类型需要） /// </summary> public string OutputFormat { get; set; } /// <summary> /// 是否是公式列 /// </summary> public bool IsCoordinateExpress { get; set; } /// <summary> /// 标题特性构造方法 /// </summary> /// <param name="title">标题</param> /// <param name="isCheckEmpty">单元格是否要检查空数据</param> /// <param name="isCoordinateExpress">是否是公式列</param> /// <param name="outputFormat">是否有格式化输出要求</param> public ExcelTitleAttribute(string title, bool isCheckEmpty = false, bool isCoordinateExpress = false, string outputFormat = "") { RowTitle = title; IsCheckContentEmpty = isCheckEmpty; IsCoordinateExpress = isCoordinateExpress; OutputFormat = outputFormat; RowTitleIndex = -1; } public ExcelTitleAttribute(int titleIndex, bool isCheckEmpty = false, bool isCoordinateExpress = false, string outputFormat = "") { RowTitleIndex = titleIndex; IsCheckContentEmpty = isCheckEmpty; IsCoordinateExpress = isCoordinateExpress; OutputFormat = outputFormat; } }
View Code
7-Ecel标题属性映射帮助类：
/// <summary> /// 标题字段映射类 /// </summary> internal class ExcelTitleFieldMapper { /// <summary> /// 属性信息 /// </summary> public PropertyInfo PropertyInfo { get; set; } /// <summary> /// 行标题 /// </summary> public string ExcelTitle { get; set; } /// <summary> /// 行标题下标位置 /// </summary> public int ExcelTitleIndex { get; set; } /// <summary> /// 是否要做行内容空检查 /// </summary> public bool IsCheckContentEmpty { get; set; } /// <summary> /// 字符输出格式（数字和日期类型需要） /// </summary> public string OutputFormat { get; set; } /// <summary> /// 是否是公式列 /// </summary> public bool IsCoordinateExpress { get; set; } /// <summary> /// 获取对应关系_T属性添加了标题映射关系 /// </summary> /// <typeparam name="T"></typeparam> /// <returns></returns> public static List<ExcelTitleFieldMapper> GetModelFieldMapper<T>() { List<ExcelTitleFieldMapper> fieldMapperList = new List<ExcelTitleFieldMapper>(100); List<PropertyInfo> tPropertyInfoList = typeof(T).GetProperties().ToList(); ExcelTitleAttribute excelTitleAttribute = null; foreach (var tPropertyInfo in tPropertyInfoList) { excelTitleAttribute = (ExcelTitleAttribute)tPropertyInfo.GetCustomAttribute(typeof(ExcelTitleAttribute)); if (excelTitleAttribute != null) { fieldMapperList.Add(new ExcelTitleFieldMapper { PropertyInfo = tPropertyInfo, ExcelTitle = excelTitleAttribute.RowTitle, ExcelTitleIndex = excelTitleAttribute.RowTitleIndex, IsCheckContentEmpty = excelTitleAttribute.IsCheckContentEmpty, OutputFormat = excelTitleAttribute.OutputFormat, IsCoordinateExpress = excelTitleAttribute.IsCoordinateExpress }); } } return fieldMapperList; } /// <summary> /// 获取对应关系_手动提供映射关系 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="fieldNameAndShowNameDic"></param> /// <returns></returns> public static List<ExcelTitleFieldMapper> GetModelFieldMapper<T>(Dictionary<string, string> fieldNameAndShowNameDic) { List<ExcelTitleFieldMapper> fieldMapperList = new List<ExcelTitleFieldMapper>(100); List<PropertyInfo> tPropertyInfoList = typeof(T).GetProperties().ToList(); PropertyInfo propertyInfo = null; foreach (var item in fieldNameAndShowNameDic) { propertyInfo = tPropertyInfoList.Find(m => m.Name.Equals(item.Key, StringComparison.OrdinalIgnoreCase)); fieldMapperList.Add(new ExcelTitleFieldMapper { PropertyInfo = propertyInfo, ExcelTitle = item.Value, ExcelTitleIndex = -1, OutputFormat = null, IsCheckContentEmpty = false, IsCoordinateExpress = false }); } return fieldMapperList; } /// <summary> /// 获取对应关系_未提供（默认属性名和标题名一致） /// </summary> /// <returns></returns> public static List<ExcelTitleFieldMapper> GetModelDefaultFieldMapper<T>() { List<ExcelTitleFieldMapper> fieldMapperList = new List<ExcelTitleFieldMapper>(100); List<PropertyInfo> tPropertyInfoList = typeof(T).GetProperties().ToList(); foreach (var item in tPropertyInfoList) { fieldMapperList.Add(new ExcelTitleFieldMapper { PropertyInfo = item, ExcelTitle = item.Name, ExcelTitleIndex = -1, OutputFormat = null, IsCheckContentEmpty = false, IsCoordinateExpress = false }); } return fieldMapperList; } }
View Code
示例代码1-单元格映射类：
/// <summary> /// 账户_多币种交易报表_数据源 /// </summary> public class AccountMultiCurrencyTransactionSource { /// <summary> /// 期初 /// </summary> [ExcelCellAttribute("B9")] public decimal BeginingBalance { get; set; } /// <summary> /// 收款 /// </summary> [ExcelCellAttribute("B19+C19")] public decimal TotalTransactionPrice { get; set; } /// <summary> /// 收到非EBay款项_主要指从其他账户转给当前账户的钱 /// </summary> [ExcelCellAttribute("B21+C21")] public decimal TransferAccountInPrice { get; set; } /// <summary> /// 退款（客户不提交争议直接退款） /// </summary> [ExcelCellAttribute("B23+C23")] public decimal TotalRefundPrice { get; set; } /// <summary> /// 手续费 /// </summary> [ExcelCellAttribute("B25+C25")] public decimal TotalFeePrice { get; set; } /// <summary> /// 争议退款 /// </summary> [ExcelCellAttribute("B37+C37")] public decimal TotalChargebackRefundPrice { get; set; } /// <summary> /// 转账与提现（币种转换） /// </summary> [ExcelCellAttribute("B45+C45")] public decimal CurrencyChangePrice { get; set; } /// <summary> /// 转账与提现（转账到paypal账户）_提现失败退回金额 /// </summary> [ExcelCellAttribute("B47+C47")] public decimal CashWithdrawalInPrice { get; set; } /// <summary> /// 转账与提现（从paypal账户转账）_提现金额 /// </summary> [ExcelCellAttribute("B49+C49")] public decimal CashWithdrawalOutPrice { get; set; } /// <summary> /// 购物_主要指从当前账户转给其他账户的钱 /// </summary> [ExcelCellAttribute("B51+C51")] public decimal TransferAccountOutPrice { get; set; } /// <summary> /// 其他活动 /// </summary> [ExcelCellAttribute("B85+C85")] public decimal OtherPrice { get; set; } /// <summary> /// 期末 /// </summary> [ExcelCellAttribute("C9")] public decimal EndingBalance { get; set; } }
View Code
示例代码2-标题映射类（标题映射分类字符串映射和下标位置映射，这里使用下标位置映射）：
/// <summary> /// 美元币种转换_数据源 /// </summary> public class CurrencyChangeUSDSource { /// <summary> /// 日期[y/M/d] /// </summary> [ExcelTitleAttribute(0, true)] public DateTime Date { get; set; } /// <summary> /// 类型 /// </summary> [ExcelTitleAttribute(1, true)] public string Type { get; set; } /// <summary> /// 交易号 /// </summary> [ExcelTitleAttribute(2)] public string TX { get; set; } /// <summary> /// 商家/接收人姓名地址第1行地址第2行/区发款账户名称_发款账户简称 /// </summary> [ExcelTitleAttribute(3)] public string SendedOrReceivedName { get; set; } /// <summary> /// 电子邮件编号_发款账户全称 /// </summary> [ExcelTitleAttribute(4)] public string SendedOrReceivedAccountName { get; set; } /// <summary> /// 币种 /// </summary> [ExcelTitleAttribute(5)] public string CurrencyCode { get; set; } /// <summary> /// 总额 /// </summary> [ExcelTitleAttribute(6)] public decimal TotalPrice { get; set; } /// <summary> /// 净额 /// </summary> [ExcelTitleAttribute(7)] public decimal NetPrice { get; set; } /// <summary> /// 费用 /// </summary> [ExcelTitleAttribute(8)] public decimal FeePrice { get; set; } }
View Code
示例代码3-模板文件数据替换-单元格映射类：
/// <summary> /// 多账户美元汇总金额_最终模板使用展示类 /// </summary> public class AccountUSDSummaryTransaction { /// <summary> /// 期初 /// </summary> [ExcelCellAttribute(cellParamName: "{DLZ_BeginingBalance}")] public decimal DLZ_BeginingBalance { get; set; } /// <summary> /// 收款 /// </summary> [ExcelCellAttribute(cellParamName: "{DLZ_TotalTransactionPrice}")] public decimal DLZ_TotalTransactionPrice { get; set; } /// <summary> /// 收到非EBay款项_主要指从其他账户转给当前账户的钱 /// </summary> [ExcelCellAttribute(cellParamName: "{DLZ_TransferAccountInPrice}")] public decimal DLZ_TransferAccountInPrice { get; set; } /// <summary> /// 退款（客户不提交争议直接退款） /// </summary> [ExcelCellAttribute(cellParamName: "{DLZ_TotalRefundPrice}")] public decimal DLZ_TotalRefundPrice { get; set; } /// <summary> /// 手续费 /// </summary> [ExcelCellAttribute(cellParamName: "{DLZ_TotalFeePrice}")] public decimal DLZ_TotalFeePrice { get; set; } /// <summary> /// 争议退款 /// </summary> [ExcelCellAttribute(cellParamName: "{DLZ_TotalChargebackRefundPrice}")] public decimal DLZ_TotalChargebackRefundPrice { get; set; } /// <summary> /// 转账与提现（币种转换） /// </summary> [ExcelCellAttribute(cellParamName: "{DLZ_CurrencyChangePrice}")] public decimal DLZ_CurrencyChangePrice { get; set; } /// <summary> /// 转账与提现（转账到paypal账户）_提现失败退回金额 /// </summary> [ExcelCellAttribute(cellParamName: "{DLZ_CashWithdrawalInPrice}")] public decimal DLZ_CashWithdrawalInPrice { get; set; } /// <summary> /// 转账与提现（从paypal账户转账）_提现金额 /// </summary> [ExcelCellAttribute(cellParamName: "{DLZ_CashWithdrawalOutPrice}")] public decimal DLZ_CashWithdrawalOutPrice { get; set; } /// <summary> /// 购物_主要指从当前账户转给其他账户的钱 /// </summary> [ExcelCellAttribute(cellParamName: "{DLZ_TransferAccountOutPrice}")] public decimal DLZ_TransferAccountOutPrice { get; set; } /// <summary> /// 其他活动 /// </summary> [ExcelCellAttribute(cellParamName: "{DLZ_OtherPrice}")] public decimal DLZ_OtherPrice { get; set; } /// <summary> /// 期末 /// </summary> [ExcelCellAttribute(cellParamName: "{DLZ_EndingBalance}")] public decimal DLZ_EndingBalance { get { decimal result = this.DLZ_BeginingBalance + this.DLZ_TotalTransactionPrice + this.DLZ_TransferAccountInPrice + this.DLZ_TotalRefundPrice + this.DLZ_TotalFeePrice + this.DLZ_TotalChargebackRefundPrice + this.DLZ_CurrencyChangePrice + this.DLZ_CashWithdrawalInPrice + this.DLZ_CashWithdrawalOutPrice + this.DLZ_TransferAccountOutPrice + this.DLZ_OtherPrice; return result; } } /// <summary> /// 期末汇率差 /// </summary> [ExcelCellAttribute(cellParamName: "{DLZ_EndingBalanceDifferenceValue}")] public decimal DLZ_EndingBalanceDifferenceValue { get { return this.DLZ_RealRateEndingBalance - this.DLZ_EndingBalance; } } /// <summary> /// 真实汇率计算的期末余额 /// </summary> [ExcelCellAttribute(cellParamName: "{DLZ_RealRateEndingBalance}")] public decimal DLZ_RealRateEndingBalance { get; set; } }
View Code
示例代码4-存储多个数据源到一个Excel中（这里我是保存到了不同的sheet页里，当然也可以保持到同一个sheet的不同位置）：
IWorkbook workbook = null; workbook = ExcelHelper.CreateOrUpdateWorkbook(dlzShopList, workbook, "独立站"); workbook = ExcelHelper.CreateOrUpdateWorkbook(ebayShopList, workbook, "EBay"); ExcelHelper.SaveWorkbookToFile(workbook, ConfigSetting.SaveReceivedNonEBayReportFile);
示例代码5-读取一个标题位置在第8行的标题行数据：
ExcelHelper.ReadTitleDataList<T>("文件路径", new ExcelFileDescription(7))
示例代码6-使用.net core API控制器方法返回文件流数据：
[Route("api/[controller]")] [ApiController] public class TestController : ControllerBase { public ExcelHelper ExcelHelper { get; set; } public TestController(ExcelHelper excelHelper) { this.ExcelHelper = excelHelper; } [HttpGet] [Route("")] public async Task<ActionResult> Test() { IWorkbook workbook = null; CommonCellModelColl accountCellColl = new CommonCellModelColl(10 * 10000); for (var i = 0; i < 10; i++) { accountCellColl.Add(new CommonCellModel(0, i, "第" + i + "列")); //标题行数据 } // 最后循环数据列： vList 循环加到结合中去。 int beginRowIndex = 2; List<dynamic> vList = new List<dynamic>(5); vList.Add(new { a = 2, b = 1 }); vList.Add(new { a = 2, b = 2 }); vList.Add(new { a = 2, b = 3 }); vList.Add(new { a = 3, b = 1 }); vList.Add(new { a = 3, b = 2 }); vList.Add(new { a = 3, b = 3 }); int objPropertyCount = 2; //这里需要用反射获取 for (var i = 0; i < vList.Count; i++) { for (int j = 0; j < objPropertyCount; j++) { //值这里实际应根据属性反射获取属性值 int testValue = 0; if (j == 0) { testValue = ((dynamic)vList[i]).a; } else { testValue = ((dynamic)vList[i]).b; } accountCellColl.Add(new CommonCellModel(beginRowIndex, j, "测试数据" + testValue)); //内容行数据 } beginRowIndex++; } workbook = this.ExcelHelper.CreateOrUpdateWorkbook(accountCellColl, workbook, "sheet1"); //生成字节流 byte[] myFileByteArray = this.ExcelHelper.SaveWorkbookToByte(workbook); //设置导出文件名 this.Response.Headers.Add("content-disposition", $"attachment;filename=test.xls"); await this.Response.Body.WriteAsync(myFileByteArray, 0, myFileByteArray.Length); return Ok("success"); } }
赞 (0)