前言：新的的封裝類，增加了單元格映射深度更新和讀取的功能，預留了標題映射的深度更新介面待擴展。。。（以後有時間和精力再完善吧） 【深度更新】：我這裡定義的深度更新策略，指的是：假如我們需要讀取一組單元格的映射數據為一個對象，但是有不止一組這樣的單元格數據對象，且這些對象的單元格位置排列是有規律的！ ...
前言：新的的封裝類，增加了單元格映射深度更新和讀取的功能，預留了標題映射的深度更新介面待擴展。。。（以後有時間和精力再完善吧）
【深度更新】：我這裡定義的深度更新策略，指的是：假如我們需要讀取一組單元格的映射數據為一個對象，但是有不止一組這樣的單元格數據對象，且這些對象的單元格位置排列是有規律的！
如：我要收集一個對象，在A1,A2，B1,B2的位置組成的一個數據對象，下一個對象位置在： A5,C6,B5,B6的位置，同理。。。
前面的文章介紹了使用單元格映射關係，我可以順利收集到其中一個對象，但是我不可能把所有的單元格都建立對象關聯起來，且數據又不符合標題行數據映射；那麼就提出了一個新的策略，我這裡叫：深度更新表達式讀取策略。
下麵放置完整代碼，這版本做了深度更新的介面的抽象和封裝，類有點多：
1-ExcelHelper 幫助類：
/// <summary> /// EXCEL幫助類 /// </summary> /// <typeparam name="T">泛型類</typeparam> /// <typeparam name="TCollection">泛型類集合</typeparam> public class ExcelHelper { private static Logger _Logger = LogManager.GetCurrentClassLogger(); public static IWorkbook GetExcelWorkbook(string filePath) { IWorkbook workbook = null; try { using (FileStream fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { try { workbook = new XSSFWorkbook(fileStream); } catch (Exception) { workbook = new HSSFWorkbook(fileStream); } } } catch (Exception e) { throw new Exception($"文件：{filePath}被占用！", e); } return workbook; } public static ISheet GetExcelWorkbookSheet(IWorkbook workbook, int sheetIndex = 0) { ISheet sheet = null; if (workbook != null) { if (sheetIndex >= 0) { sheet = workbook.GetSheetAt(sheetIndex); } } return sheet; } public static ISheet GetExcelWorkbookSheet(IWorkbook workbook, string sheetName = "sheet1") { ISheet sheet = null; if (workbook != null && !string.IsNullOrEmpty(sheetName)) { sheet = workbook.GetSheet(sheetName); if (sheet == null) { sheet = workbook.CreateSheet(sheetName); } } return sheet; } public static IRow GetOrCreateRow(ISheet sheet, int rowIndex) { IRow row = null; if (sheet != null) { row = sheet.GetRow(rowIndex); if (row == null) { row = sheet.CreateRow(rowIndex); } } return row; } public static ICell GetOrCreateCell(ISheet sheet, int rowIndex, int columnIndex) { ICell cell = null; IRow row = ExcelHelper.GetOrCreateRow(sheet, rowIndex); if (row != null) { cell = row.GetCell(columnIndex); if (cell == null) { cell = row.CreateCell(columnIndex); } } return cell; } /// <summary> /// 根據單元格表達式和單元格數據集獲取數據 /// </summary> /// <param name="cellExpress">單元格表達式</param> /// <param name="workbook">excel工作文件</param> /// <param name="currentSheet">當前sheet</param> /// <returns></returns> public static object GetVByExpress(string cellExpress, IWorkbook workbook, ISheet currentSheet) { object value = null; //含有單元格表達式的取表達式值，沒有表達式的取單元格字元串 if (!string.IsNullOrEmpty(cellExpress) && workbook != null && currentSheet != null) { IFormulaEvaluator formulaEvaluator = null; if (workbook is HSSFWorkbook) { formulaEvaluator = new HSSFFormulaEvaluator(workbook); } else { formulaEvaluator = new XSSFFormulaEvaluator(workbook); } //創建臨時行，單元格，執行表達式運算； IRow newRow = currentSheet.CreateRow(currentSheet.LastRowNum + 1); ICell cell = newRow.CreateCell(0); cell.SetCellFormula(cellExpress); cell = formulaEvaluator.EvaluateInCell(cell); value = cell.ToString(); currentSheet.RemoveRow(newRow); } return value ?? ""; } #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 static 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 = ExcelHelper.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 static 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 = ExcelHelper.CreateOrUpdateWorkbook<T>(tList, titleMapperList, workbook, sheetName, excelFileDescription); return workbook; } private static IWorkbook CreateOrUpdateWorkbook<T>(List<T> tList, List<ExcelTitleFieldMapper> titleMapperList, IWorkbook workbook, string sheetName, ExcelFileDescription excelFileDescription = null) { CellModelColl cellModelColl = new CellModelColl(0); int defaultBeginTitleIndex = 0; if (excelFileDescription != null) { defaultBeginTitleIndex = excelFileDescription.TitleRowIndex; } //補全標題行映射數據的標題和下標位置映射關係 ISheet sheet = ExcelHelper.GetExcelWorkbookSheet(workbook, sheetName: sheetName); IRow titleRow = null; if (sheet != null) { titleRow = sheet.GetRow(defaultBeginTitleIndex); } if (titleRow != null) { List<ICell> titleCellList = titleRow.Cells; foreach (var titleMapper in titleMapperList) { if (titleMapper.ExcelTitleIndex < 0) { foreach (var cellItem in titleCellList) { if (cellItem.ToString().Equals(titleMapper.ExcelTitle, StringComparison.OrdinalIgnoreCase)) { titleMapper.ExcelTitleIndex = cellItem.ColumnIndex; break; } } } else if (string.IsNullOrEmpty(titleMapper.ExcelTitle)) { ICell cell = titleRow.GetCell(titleMapper.ExcelTitleIndex); if (cell != null) { titleMapper.ExcelTitle = cell.ToString(); } } } } else { //如果是新建Sheet頁，則手動初始化下標關係 for (int i = 0; i < titleMapperList.Count; i++) { titleMapperList[i].ExcelTitleIndex = i; } } int currentRowIndex = defaultBeginTitleIndex; //添加標題單元格數據 foreach (var titleMapper in titleMapperList) { cellModelColl.Add(new CellModel { RowIndex = defaultBeginTitleIndex, ColumnIndex = titleMapper.ExcelTitleIndex, CellValue = titleMapper.ExcelTitle, IsCellFormula = false }); } currentRowIndex++; //將標題行數據轉出單元格數據 foreach (var item in tList) { foreach (var titleMapper in titleMapperList) { cellModelColl.Add(new CellModel { RowIndex = currentRowIndex, ColumnIndex = titleMapper.ExcelTitleIndex, CellValue = titleMapper.PropertyInfo.GetValue(item), IsCellFormula = titleMapper.IsCoordinateExpress }); } currentRowIndex++; } workbook = ExcelHelper.CreateOrUpdateWorkbook(cellModelColl, workbook, sheetName); return workbook; } /// <summary> /// 將單元格數據列表生成工作表 /// </summary> /// <param name="commonCellList">所有的單元格數據列表</param> /// <param name="workbook">更新時添加：要更新的工作表</param> /// <param name="sheetName">指定要創建的sheet名稱時添加</param> /// <returns></returns> public static IWorkbook CreateOrUpdateWorkbook(CellModelColl 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 = ExcelHelper.GetExcelWorkbookSheet(workbook, sheetName); if (worksheet != null && commonCellList != null && commonCellList.Count > 0) { //設置首列顯示 IRow row1 = null; int rowIndex = 0; int maxRowIndex = commonCellList.Max(m => m.RowIndex); Dictionary<int, CellModel> rowColumnIndexCellDIC = null; ICell cell = null; object cellValue = null; do { rowColumnIndexCellDIC = commonCellList.GetRawCellList(rowIndex).ToDictionary(m => m.ColumnIndex); int 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); } int columnIndex = 0; do { cell = row1.GetCell(columnIndex); if (cell == null) { cell = row1.CreateCell(columnIndex); } if (rowColumnIndexCellDIC.ContainsKey(columnIndex)) { cellValue = rowColumnIndexCellDIC[columnIndex].CellValue; CellFactory.SetCellValue(cell, cellValue, outputFormat: null, rowColumnIndexCellDIC[columnIndex].IsCellFormula); } columnIndex++; } while (columnIndex <= maxColumnIndex); } rowIndex++; } while (rowIndex <= maxRowIndex); //設置表達式重算（如果不添加該代碼，表達式更新不出結果值） worksheet.ForceFormulaRecalculation = true; } return workbook; } /// <summary> /// 更新模板文件數據：將使用單元格映射的數據T存入模板文件中 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="workbook"></param> /// <param name="sheet"></param> /// <param name="t"></param> /// <param name="excelFileDescription"></param> /// <returns></returns> public static IWorkbook UpdateTemplateWorkbook<T>(IWorkbook workbook, ISheet sheet, T t, ExcelFileDescription excelFileDescription = null) { //該方法預設替換模板數據在首個sheet里 CellModelColl commonCellColl = ExcelHelper.ReadCellList(workbook, sheet, false); List<IExcelCellPointDeepUpdate> excelCellPointDeepList = new List<IExcelCellPointDeepUpdate>(0); if (excelFileDescription != null) { excelCellPointDeepList.Add((IExcelCellPointDeepUpdate)excelFileDescription.ExcelDeepUpdateList); } //獲取t的單元格映射列表 List<ExcelCellFieldMapper> cellMapperList = ExcelCellFieldMapper.GetModelFieldMapper<T>(); foreach (var cellMapper in cellMapperList) { if (cellMapper.CellParamWriteList.Count > 0) { foreach (var cellParamWriteAttribute in cellMapper.CellParamWriteList) { CellModel cellModel = commonCellColl.GetCell(cellParamWriteAttribute.CellParamName); if (cellModel != null) { cellModel.CellValue = cellMapper.PropertyInfo.GetValue(t); } } } if (cellMapper.CellPointWriteList.Count > 0) { object cellValue = cellMapper.PropertyInfo.GetValue(t); ICellModel firstCellPosition = null; foreach (var cellPointWriteAttribute in cellMapper.CellPointWriteList) { firstCellPosition = CellFactory.GetCellByExcelPosition(cellPointWriteAttribute.CellPosition); CellFactory.SetDeepUpdateCellValue(sheet, firstCellPosition.RowIndex, firstCellPosition.ColumnIndex, cellValue, cellPointWriteAttribute.OutputFormat, false, excelCellPointDeepList); } } } workbook = ExcelHelper.CreateOrUpdateWorkbook(commonCellColl, workbook, sheet.SheetName); return workbook; } #endregion #region 保存工作表到文件 /// <summary> /// 保存Workbook數據為文件 /// </summary> /// <param name="workbook"></param> /// <param name="fileDirectoryPath"></param> /// <param name="fileName"></param> public static 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 static 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 static 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 = ExcelHelper._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 static List<T> ReadTitleDataList<T>(string filePath, ExcelFileDescription excelFileDescription) where T : new() { //標題屬性字典列表 List<ExcelTitleFieldMapper> titleMapperList = ExcelTitleFieldMapper.GetModelFieldMapper<T>(); List<T> tList = ExcelHelper._GetTList<T>(filePath, titleMapperList, excelFileDescription); return tList ?? new List<T>(0); } private static 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); try { IWorkbook workbook = ExcelHelper.GetExcelWorkbook(filePath); IFormulaEvaluator formulaEvaluator = null; if (workbook is XSSFWorkbook) { formulaEvaluator = new XSSFFormulaEvaluator(workbook); } else if (workbook is HSSFWorkbook) { 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 = ExcelHelper.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; if (currentSheetRowTitleIndex >= 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) { ExcelHelper._Logger.Debug($"文件_{filePath}讀取{currentRowIndex + 1}行內容失敗！"); t = default(T); break; } } if (t != null) { tList.Add(t); } } } } currentSheetIndex++; } while (currentSheetIndex + 1 <= sheetCount); } catch (Exception e) { throw new Exception($"文件：{filePath}被占用！", e); } return tList ?? new List<T>(0); } public static CellModelColl ReadCellList(IWorkbook workbook, ISheet sheet, bool isRunFormula = false) { CellModelColl commonCells = new CellModelColl(10000); IFormulaEvaluator formulaEvaluator = null; if (workbook != null) { if (workbook is HSSFWorkbook) { formulaEvaluator = new HSSFFormulaEvaluator(workbook); } else { formulaEvaluator = new XSSFFormulaEvaluator(workbook); } } if (sheet != null) { CellModel cellModel = null; var rows = sheet.GetRowEnumerator(); //從第1行數據開始獲取 while (rows.MoveNext()) { IRow row = (IRow)rows.Current; List<ICell> cellList = row.Cells; ICell cell = null; foreach (var cellItem in cellList) { cell = cellItem; if (isRunFormula && cell.CellType == CellType.Formula) { cell = formulaEvaluator.EvaluateInCell(cell); } cellModel = new CellModel { RowIndex = cell.RowIndex, ColumnIndex = cell.ColumnIndex, CellValue = cell.ToString(), IsCellFormula = cell.CellType == CellType.Formula }; commonCells.Add(cellModel); } } } return commonCells; } /// <summary> /// 獲取文件單元格數據對象 /// </summary> /// <typeparam name="T">T的屬性必須標記了ExcelCellAttribute</typeparam> /// <p
