/// <summary> /// 按模板生成 Excel 文档 /// </summary> /// <typeparam name="T">实体类型</typeparam> /// <param name="templatePath">模板路径</param> /// <param name="templateSheetName">模板表单名称</param> /// <param name="excelPath">Excel 路径</param> /// <param name="dataMatchList">数据匹配,Dictionary<string, object> 或 new {}</param> /// <param name="dataList">数据列表</param> /// <param name="mergeCellDataList">合并单元格数据列表</param> /// <param name="reflectionType">反射类型</param> public static void ToExcel <T>(string templatePath, string templateSheetName, string excelPath, object dataMatchList, List <T> dataList, List <ExcelMergeCell> mergeCellDataList = null, ReflectionTypeEnum reflectionType = ReflectionTypeEnum.Expression) where T : class { Dictionary <string, object> propertyDict = CommonHelper.GetParameterDict(dataMatchList); FileStream fileStream = null; IWorkbook workbook = null; try { using (fileStream = new FileStream(templatePath, FileMode.Open, FileAccess.ReadWrite)) { workbook = ExcelHelper.ExecuteIWorkBookGet(fileStream); if (workbook == null) { throw new Exception(ExcelHelper.ExcelWorkbookNullException); } } Dictionary <int, string> propertyMapperDict = new Dictionary <int, string>(); List <ExcelTemplateFormulaItem> formulaItemList = new List <ExcelTemplateFormulaItem>(); int lastRowIndex = 0; int insertRowIndex = -1; ISheet sheet = workbook.GetSheet(templateSheetName); if (sheet != null) { lastRowIndex = sheet.LastRowNum; for (int rowIndex = 0; rowIndex <= lastRowIndex; rowIndex++) { IRow iRow = sheet.GetRow(rowIndex); if (iRow != null) { for (int colIndex = 0; colIndex <= iRow.LastCellNum; colIndex++) { ICell iCell = iRow.GetCell(colIndex); if (iCell != null && !string.IsNullOrEmpty(iCell.ToString())) { string cellText = iCell.ToString(); if (cellText.StartsWith("$")) { cellText = cellText.TrimStart(new char[] { '$' }); if (propertyDict != null && propertyDict.ContainsKey(cellText)) { iCell.SetCellValue(propertyDict[cellText].ToString()); } } else if (cellText.StartsWith("#")) { if (insertRowIndex == -1) { insertRowIndex = rowIndex; } cellText = cellText.TrimStart(new char[] { '#' }); propertyMapperDict.Add(colIndex, cellText); } else if (cellText.StartsWith("&=")) { if (rowIndex != insertRowIndex) { formulaItemList.Add(new ExcelTemplateFormulaItem() { Cell = iCell, FormulaText = cellText }); } } } } } } } if (propertyMapperDict != null && propertyMapperDict.Count > 0 && insertRowIndex != -1) { if (dataList != null && dataList.Count > 0) { dynamic propertyGetDict = null; if (reflectionType != ReflectionTypeEnum.Original) { propertyGetDict = ReflectionExtendHelper.PropertyGetCallDict <T>(reflectionType); } CopyRow(sheet, insertRowIndex, dataList, propertyMapperDict, propertyGetDict); if (formulaItemList != null && formulaItemList.Count > 0) { foreach (ExcelTemplateFormulaItem formulaItem in formulaItemList) { formulaItem.FormulaText = formulaItem.FormulaText.TrimStart(new char[] { '&', '=' }); formulaItem.FormulaText = formulaItem.FormulaText.Replace("_dataBegin", (insertRowIndex + 1).ToString()); formulaItem.FormulaText = formulaItem.FormulaText.Replace("_dataEnd", (insertRowIndex + dataList.Count).ToString()); formulaItem.Cell.SetCellFormula(formulaItem.FormulaText); formulaItem.Cell.SetCellType(CellType.Formula); } } } } sheet.ForceFormulaRecalculation = true; if (mergeCellDataList != null && mergeCellDataList.Count > 0) { foreach (ExcelMergeCell cellItem in mergeCellDataList) { sheet.AddMergedRegion(new CellRangeAddress(insertRowIndex + cellItem.BeginRow, insertRowIndex + cellItem.EndRow, cellItem.BeginColumn, cellItem.EndColumn)); } } if (System.IO.File.Exists(excelPath)) { System.IO.File.Delete(excelPath); } using (fileStream = new FileStream(excelPath, FileMode.Create, FileAccess.ReadWrite)) { workbook.Write(fileStream); } workbook.Close(); } catch { throw; } finally { if (fileStream != null) { fileStream.Close(); } if (workbook != null) { workbook.Close(); } } }