/// <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();
}
}
}
