public class ExcelExporter { /// <summary> /// 报表保存路径 /// </summary> protected static string ReportSavePath = ConfigurationManager.AppSettings["ReportSavePath"]; /// <summary> /// 报表模板路径 /// </summary> protected static string ReportTemplatePath = ConfigurationManager.AppSettings["ReportTemplatePath"]; /// <summary> /// 报表HTML保存路径 /// </summary> protected static string HtmlReportSavePath = ConfigurationManager.AppSettings["HtmlReportSavePath"]; private static Regex _rgxBrace1 = new Regex(@"(?i)(?<=\$\{)(.*)(?=\})"); //大括号里面的内容 private static Regex _rgxBrace = new Regex(@"(?i)(?<=\{)(.*)(?=\})"); //大括号里面的内容 private static Regex _rgxBracket = new Regex(@"(?i)(?<=\[)(.*)(?=\])"); //中括号里面的内容 private static Regex _rgx = new Regex(@"(?=)[+\-\*\/]"); #region 使用datatable生成excel /// <summary> /// 从mongdb数据库中获取数据并写入excel /// </summary> /// <param name="dataTable">数据</param> /// <param name="fileNameCombinationType">报表类型:1-日报;2-月报; 3-年报;</param> /// <param name="fileName">文件名称</param> /// <param name="sheetName">sheet 名称(重复会报错)</param> /// <param name="filePath">如果为空时是新增文件,不为空传旧的文件地址或者模版文件</param> /// <param name="sheetIndex">需要操作execl的选项卡,值为-1时动态获取sheet递增操作,否则按指定下标读写数据</param> /// <param name="copyIndex">复制sheet的下标,值为-1时不复制sheet,否则按指定下标复制sheet并新建</param> /// <param name="sheetRename">重命名sheetName</param> /// <param name="imgObj">图片对象</param> /// <param name="collection"></param> /// <param name="reportDate"></param> /// <param name="clearIndex">当datatable为空时clearIndex=-1默认清除模版所有东西,否则按clearIndex指定下标</param> /// <param name="reportType">1、日报 2、月报 3、年报</param> public static void ExportDataTableToExcel(DataTable dataTable, int fileNameCombinationType, string fileName, string sheetName = null, string filePath = null, int sheetIndex = -1, int copyIndex = -1, bool sheetRename = false, object imgObj = null, string collection = null, DateTime? reportDate = null, int clearIndex = -1, int reportType = -1) { bool suc = false; //生成报表是否成功 IWorkbook m_Book = null; ISheet m_Sheet; FileStream fs = null; try { var dt = dataTable; string fileExt = string.Empty; var tupleData = OpenWorkbook(fileNameCombinationType, fileName, filePath, reportDate: reportDate); m_Book = tupleData.Item3; if (m_Book == null) { return; } fileExt = tupleData.Item2; filePath = tupleData.Item1; m_Sheet = GetSheet(m_Book, sheetName, copyIndex, sheetIndex); if (m_Sheet == null) { return; } if (dataTable != null && dataTable.Rows.Count > 0) { var comment_data = GetCellComment(m_Book, m_Sheet, copyIndex); Dictionary<string, CountRuleInfo> countColumnRuleDic = comment_data.Item1; Dictionary<string, CountRuleInfo> countRowRuleDic = comment_data.Item2; Dictionary<string, ICellStyle> colorFillDic = comment_data.Item3; #region 判断当前sheet的row是否为空或存在占位数据替换 int rowNum = m_Sheet.LastRowNum; //获取当前sheet的总行数 -1 说明当前sheet没有数据 ConcurrentDictionary<string, object> segnapostoDic = null; List<string> colNameTypes = null; int currentRow = 0; //工作表操作行 if (rowNum >= 0) //rowNum > 0 说明当前sheet已经存在数据 { segnapostoDic = new ConcurrentDictionary<string, object>(); for (int i = 0; i <= rowNum; i++) { var row = m_Sheet.GetRow(i); if (row == null) continue; short cellNum = row.LastCellNum; //获取当前行的列数内容之间的空格也算一个列 for (int j = 0; j < cellNum; j++) { var cell = row.GetCell(j); if (cell == null) continue; if (cell.CellType == CellType.Numeric) continue; var cellValue = Convert.ToString(cell); if (cellValue == null) continue; if (!IsSegnaposto(cellValue) && cell.CellType != CellType.Formula) continue; //如果是占位数据则保存起来 var segnaposto = new { RowIndex = i, CellIndex = j, CellValue = cellValue, }; string key = Regex.Match(cellValue, @"\{(.*)\}", RegexOptions.Singleline).Groups[1] .Value; if (cell.CellType == CellType.Formula) { if (segnapostoDic.ContainsKey("formula_column")) { var data = segnapostoDic["formula_column"] as List<object>; data.Add(segnaposto); segnapostoDic["formula_column"] = data; continue; } segnapostoDic.TryAdd("formula_column", new List<object> { segnaposto }); continue; } if (string.IsNullOrEmpty(key)) { key = Regex.Match(cellValue, @"\[(.*)\]", RegexOptions.Singleline).Groups[1].Value; if (segnapostoDic.ContainsKey("joint_Array")) { var data = segnapostoDic["joint_Array"] as List<object>; data.Add(new KeyValuePair<string, object>(key, segnaposto)); segnapostoDic["joint_Array"] = data; continue; } segnapostoDic.TryAdd("joint_Array", new List<object> { new KeyValuePair<string, object>(key, segnaposto) }); continue; } if (key.StartsWith("specify_", StringComparison.OrdinalIgnoreCase)) { //按属性名往下插入数据 if (segnapostoDic.ContainsKey("specify_Array")) { var data = segnapostoDic["specify_Array"] as List<object>; data.Add(new KeyValuePair<string, object>(key, segnaposto)); segnapostoDic["specify_Array"] = data; continue; } segnapostoDic.TryAdd("specify_Array", new List<object> { new KeyValuePair<string, object>(key, segnaposto) }); continue; } if (key.StartsWith("shift_", StringComparison.OrdinalIgnoreCase)) { //按属性名在当前行往上插入数据 if (segnapostoDic.ContainsKey("shift_Array")) { var data = segnapostoDic["shift_Array"] as List<object>; data.Add(new KeyValuePair<string, object>(key, segnaposto)); segnapostoDic["shift_Array"] = data; continue; } segnapostoDic.TryAdd("shift_Array", new List<object> { new KeyValuePair<string, object>(key, segnaposto) }); continue; } if (key.Equals("ShiftRecordList", StringComparison.OrdinalIgnoreCase) || key.Equals("RecordList", StringComparison.OrdinalIgnoreCase)) { segnapostoDic.TryAdd("dataStartRow", row.RowNum); } segnapostoDic.TryAdd(key, segnaposto); } } colNameTypes = new List<string>(); colNameTypes.Clear(); //为了防止重复添加表头 // 此处可以使用foreach循环但使用linq更简洁效率更高 colNameTypes = (from DataColumn column in dt.Columns let dataType = column.DataType select column.ColumnName + "|" + dataType.Name).ToList(); //表头 } else { //写入表头 #region 设置表头 dataStartRow 为零的时候才设置表头 colNameTypes = new List<string>(); IFont headerFont = m_Book.CreateFont(); headerFont.FontName = "新細明體"; headerFont.FontHeightInPoints = 14; //設置數據行字體大小 headerFont.Boldweight = (short)FontBoldWeight.Bold; //數據行字體加粗 //添加表头 ICellStyle headerCellStyle = m_Book.CreateCellStyle(); headerCellStyle.SetFont(headerFont); headerCellStyle.WrapText = true; headerCellStyle.VerticalAlignment = VerticalAlignment.Center; var headerParam = sheetName; //paramList.FirstOrDefault(p => p.ParameterName == "@header"); if (headerParam != null && !string.IsNullOrEmpty(headerParam.ToString())) { var row = m_Sheet.CreateRow(currentRow); row.HeightInPoints = 33; //设置当前行高度 ICell cell = row.CreateCell(0); cell.SetCellValue(headerParam.ToString()); cell.CellStyle = headerCellStyle; CellRangeAddress region = new CellRangeAddress(currentRow, currentRow, 0, dt.Columns.Count - 1); //合并单元格 m_Sheet.AddMergedRegion(region); currentRow += 1; } colNameTypes.Clear(); //为了防止重复添加表头 // 此处可以使用foreach循环但使用linq更简洁效率更高 colNameTypes = (from DataColumn column in dt.Columns let dataType = column.DataType select column.ColumnName + "|" + dataType.Name).ToList(); //表头 //any()方法用于判断集合中是否有元素,如果有元素则返回true,否则返回false if (colNameTypes.Any()) { var row = m_Sheet.CreateRow(currentRow); row.HeightInPoints = 33; //设置当前行高度 //添加表头 //标题样式 IFont titleFont = m_Book.CreateFont(); titleFont.FontName = "新細明體"; titleFont.FontHeightInPoints = 11; //設置數據行字體大小 titleFont.Boldweight = (short)FontBoldWeight.Bold; //數據行字體加粗 ICellStyle titleCellStyle = SetCellStyle(mBook: m_Book, contentFont: titleFont, fillForegroundColor: "#808080"); titleCellStyle.Alignment = HorizontalAlignment.Center; titleCellStyle.VerticalAlignment = VerticalAlignment.Center; titleCellStyle.BorderDiagonalLineStyle = BorderStyle.Thin; for (int i = 0; i < colNameTypes.Count; i++) { var colNameType = colNameTypes[i].Split('|'); ICell cell = row.CreateCell(i); cell.SetCellValue(colNameType[0]); cell.CellStyle = titleCellStyle; } currentRow += 1; } #endregion } #endregion #region 设置每列的样式 List<ICellStyle> cellStyleList = null; if (colNameTypes != null) { //设置每列的样式 IFont contentFont = m_Book.CreateFont(); contentFont.FontName = "新細明體"; //設置數據行字體 contentFont.FontHeightInPoints = 11; //設置數據行字體大小 contentFont.Boldweight = (short)FontBoldWeight.Normal; int styleCol = 0; cellStyleList = new List<ICellStyle>(); foreach (DataColumn column in dt.Columns) { //列样式 //内容 ICellStyle cellStyle = SetCellStyle(mBook: m_Book, contentFont: contentFont); //.CreateCellStyle(); //设置列格式 var colNameType = colNameTypes[styleCol].Split('|'); var colType = colNameType[1]; switch (colType) { case "DateTime": // XSSFDataFormat cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("yyyy/MM/dd HH:mm:ss"); break; case "Int32": case "Int64": cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("#,##0"); break; case "Decimal": case "Double": case "Single": cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("#,##0.00"); break; default: cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@"); break; } cellStyleList.Add(cellStyle); styleCol++; } } #endregion int insertLastRow = 0; //写入数据 判断是否有占位数据需要替换 if (segnapostoDic.Count > 0 && currentRow == 0) { foreach (var item in segnapostoDic) { if (item.Key.Equals("formula_column") || item.Key.Equals("dataStartRow")) continue; if (item.Key.Equals("specify_Array", StringComparison.OrdinalIgnoreCase)) { // single_你的列名 insertLastRow = WriteSpecifyColumnNameExcelData(dt, m_Sheet, item.Value, cellStyleList); continue; } if (item.Key.Equals("shift_Array", StringComparison.OrdinalIgnoreCase)) { // shift_你的列名 insertLastRow = WriteShiftColumnNameExcelData(dt, m_Sheet, item.Value, cellStyleList); continue; } if (item.Key.Equals("joint_Array", StringComparison.OrdinalIgnoreCase)) { JoinStr(m_Sheet, item.Value); continue; } int row = Convert.ToInt32(ReflectTool.GetInstanceValue(item.Value, "RowIndex").ToString()); int col = Convert.ToInt32(ReflectTool.GetInstanceValue(item.Value, "CellIndex").ToString()); if (item.Key.Equals("RecordList")) { if (dt.Rows.Count <= 0) { m_Sheet.GetRow(row).GetCell(col).SetCellValue(""); continue; } insertLastRow = WriteExcelData(dt, row, m_Sheet, colNameTypes, cellStyleList, countRowRuleDic, colorFillDic); } else if (item.Key.Equals("ShiftRecordList")) { if (dt.Rows.Count <= 0) { // m_Sheet.ShiftRows(m_Sheet.LastRowNum+1, m_Sheet.LastRowNum+2, -2); // 调整行索引 if (m_Sheet.GetRow(row + 1) != null) { m_Sheet.RemoveRow(m_Sheet.GetRow(row + 1)); } if (m_Sheet.GetRow(row) != null) { m_Sheet.RemoveRow(m_Sheet.GetRow(row)); } continue; } insertLastRow = WriteExcelShiftRowsData(dt, row, m_Sheet, colNameTypes, cellStyleList, countRowRuleDic, colorFillDic); } else if (item.Key.StartsWith("images", StringComparison.OrdinalIgnoreCase)) { if (imgObj == null) { continue; } string rgx_str = new Regex(@"(?i)(?<=\()(.*)(?=\))").Match(item.Key).Value; //小括号里面的内容 /** * HSSFClientAnchor 详解 * dx1:图片左边相对excel格的位置(x偏移) 范围值为:0~1023;即输100 偏移的位置大概是相对于整个单元格的宽度的100除以1023大概是10分之一 * dy1:图片上方相对excel格的位置(y偏移) 范围值为:0~256 原理同上。 * dx2:图片右边相对excel格的位置(x偏移) 范围值为:0~1023; 原理同上。 * dy2:图片下方相对excel格的位置(y偏移) 范围值为:0~256 原理同上。 * col1和row1 :图片左上角的位置,以excel单元格为参考,比喻这两个值为(1,1),那么图片左上角的位置就是excel表(1,1)单元格的右下角的点(A,1)右下角的点。 * col2和row2:图片右下角的位置,以excel单元格为参考,比喻这两个值为(2,2),那么图片右下角的位置就是excel表(2,2)单元格的右下角的点(B,2)右下角的点。 */ object imgPath = imgObj.GetType().GetProperty(item.Key.Split('_')[1]).GetValue(imgObj); if (imgPath == null) { m_Sheet.GetRow(row).GetCell(col).SetCellValue(""); continue; } m_Sheet.GetRow(row).GetCell(col).SetCellValue(imgPath.ToString()); if (!AlphaFS.File.Exists(imgPath.ToString())) { //图片不存在 continue; } byte[] bytes = AlphaFS.File.ReadAllBytes(imgPath.ToString()); int pictureIdx = m_Book.AddPicture(bytes, PictureType.JPEG); var patriarch = m_Sheet.CreateDrawingPatriarch(); switch (fileExt) { //如果是XLSX格式选择XSSFWorkbook ,如果是XLS格式选择HSSFWorkbook case ".xlsx": var pict_xss = patriarch.CreatePicture( new XSSFClientAnchor(1, 1, 0, 0, col, row, col + 2, row + 2), pictureIdx); pict_xss.Resize(); break; case ".xls": var pict_hss = patriarch.CreatePicture( new HSSFClientAnchor(1, 1, 0, 0, col, row, col + 2, row + 2), pictureIdx); pict_hss.Resize(); break; } } else if (item.Key.Equals("TableName", StringComparison.OrdinalIgnoreCase)) { m_Sheet.GetRow(row).GetCell(col).SetCellValue(sheetName); } else if (item.Key.Equals("title_date", StringComparison.OrdinalIgnoreCase)) { if (reportDate != null) { m_Sheet.GetRow(row).GetCell(col).SetCellValue(reportDate.Value.ToString("yyyy-MM-dd")); continue; } m_Sheet.GetRow(row).GetCell(col).SetCellValue(DateTime.Now); } else if (item.Key.Equals("sheet_date", StringComparison.OrdinalIgnoreCase)) { if (!string.IsNullOrWhiteSpace(sheetName)) { m_Sheet.GetRow(row).GetCell(col).SetCellValue(sheetName); continue; } m_Sheet.GetRow(row).GetCell(col).SetCellValue(DateTime.Now); } } if (segnapostoDic.ContainsKey("formula_column")) { if (segnapostoDic.ContainsKey("ShiftRecordList") || segnapostoDic.ContainsKey("shift_Array")) { object formulaColumn = segnapostoDic["formula_column"]; if (segnapostoDic.ContainsKey("dataStartRow")) { object dataStartRow = segnapostoDic["dataStartRow"]; FormulaColumn(m_Sheet, dt.Rows.Count, formulaColumn, dataStartRow); } else { FormulaColumn(m_Sheet, dt.Rows.Count, formulaColumn, null); } } else { m_Sheet.Workbook.GetCreationHelper().CreateFormulaEvaluator().EvaluateAll(); } } } else { insertLastRow = WriteExcelData(dt, currentRow, m_Sheet, colNameTypes, cellStyleList, countRowRuleDic, colorFillDic); } if (countColumnRuleDic != null) { WriteCurrentValueColumnRule(m_Sheet, countColumnRuleDic, insertLastRow); } m_Sheet.GetRow(0).GetCell(0)?.RemoveCellComment(); // 删除(0,0)单元格注释 } else { NullDataTabel(m_Book: m_Book, clearIndex: clearIndex, reportDate: reportDate); } //转为字节数组 using (MemoryStream stream = new MemoryStream()) { m_Book.Write(stream); var buf = stream.ToArray(); //保存为Excel文件 using (fs = AlphaFS.File.Open(filePath, FileMode.Create, FileAccess.Write)) { fs.Write(buf, 0, buf.Length); fs.Flush(); } } suc = true; } catch (Exception ex) { Logger.Error($"NPOIGenerateReport error - 工作表名称:{fileName}: \r\n" + ex.ToString()); suc = false; } finally { #region 释放Excel资源 m_Sheet = null; m_Book = null; if (fs != null) { fs.Close(); } GC.WaitForPendingFinalizers(); GC.Collect(); #endregion //保存报表记录 if (!string.IsNullOrEmpty(filePath)) { GarbageManager.SaveReport($"v2-{collection}", filePath.Replace(ReportSavePath, ""), reportType, "SYS", suc, reportDate); } } } /// <summary> /// 从mongdb数据库中获取数据并写入excel /// </summary> /// <param name="dataTable"></param> /// <param name="m_Book"></param> /// <param name="m_Sheet"></param> /// <param name="fileExt"></param> /// <param name="filePath"></param> /// <param name="sheetName"></param> /// <param name="imgObj"></param> public static void ExportDataTableToExcel(DataTable dataTable, IWorkbook m_Book, string fileExt, string filePath, string sheetName, int sheetIndex = 0, object imgObj = null, string collection = null, DateTime? reportDate = null, int clearIndex = -1, int reportType = -1) { bool suc = false; //生成报表是否成功 FileStream fs = null; ISheet m_Sheet = null; try { var dt = dataTable; if (dataTable != null && dataTable.Rows.Count > 0) { int copyIndex = -1; m_Sheet = GetSheet(m_Book, sheetName, copyIndex, sheetIndex); var comment_data = GetCellComment(m_Book, m_Sheet, copyIndex); Dictionary<string, CountRuleInfo> countColumnRuleDic = comment_data.Item1; Dictionary<string, CountRuleInfo> countRowRuleDic = comment_data.Item2; Dictionary<string, ICellStyle> colorFillDic = comment_data.Item3; #region 判断当前sheet的row是否为空或存在占位数据替换 int rowNum = m_Sheet.LastRowNum; //获取当前sheet的总行数 -1 说明当前sheet没有数据 ConcurrentDictionary<string, object> segnapostoDic = null; List<string> colNameTypes = null; int currentRow = 0; //工作表操作行 if (rowNum >= 0) //rowNum > 0 说明当前sheet已经存在数据 { segnapostoDic = new ConcurrentDictionary<string, object>(); for (int i = 0; i <= rowNum; i++) { var row = m_Sheet.GetRow(i); if (row == null) continue; short cellNum = row.LastCellNum; //获取当前行的列数内容之间的空格也算一个列 for (int j = 0; j < cellNum; j++) { var cell = row.GetCell(j); if (cell == null) continue; if (cell.CellType == CellType.Numeric) continue; var cellValue = Convert.ToString(cell); if (cellValue == null) continue; if (!IsSegnaposto(cellValue) && cell.CellType != CellType.Formula) continue; //如果是占位数据则保存起来 var segnaposto = new { RowIndex = i, CellIndex = j, CellValue = cellValue, }; string key = Regex.Match(cellValue, @"\{(.*)\}", RegexOptions.Singleline).Groups[1] .Value; if (cell.CellType == CellType.Formula) { if (segnapostoDic.ContainsKey("formula_column")) { var data = segnapostoDic["formula_column"] as List<object>; data.Add(segnaposto); segnapostoDic["formula_column"] = data; continue; } segnapostoDic.TryAdd("formula_column", new List<object> { segnaposto }); continue; } if (string.IsNullOrEmpty(key)) { key = Regex.Match(cellValue, @"\[(.*)\]", RegexOptions.Singleline).Groups[1].Value; if (segnapostoDic.ContainsKey("joint_Array")) { var data = segnapostoDic["joint_Array"] as List<object>; data.Add(new KeyValuePair<string, object>(key, segnaposto)); segnapostoDic["joint_Array"] = data; continue; } segnapostoDic.TryAdd("joint_Array", new List<object> { new KeyValuePair<string, object>(key, segnaposto) }); continue; } if (key.StartsWith("specify_", StringComparison.OrdinalIgnoreCase)) { //按属性名往下插入数据 if (segnapostoDic.ContainsKey("specify_Array")) { var data = segnapostoDic["specify_Array"] as List<object>; data.Add(new KeyValuePair<string, object>(key, segnaposto)); segnapostoDic["specify_Array"] = data; continue; } segnapostoDic.TryAdd("specify_Array", new List<object> { new KeyValuePair<string, object>(key, segnaposto) }); continue; } if (key.StartsWith("shift_", StringComparison.OrdinalIgnoreCase)) { //按属性名在当前行往上插入数据 if (segnapostoDic.ContainsKey("shift_Array")) { var data = segnapostoDic["shift_Array"] as List<object>; data.Add(new KeyValuePair<string, object>(key, segnaposto)); segnapostoDic["shift_Array"] = data; continue; } segnapostoDic.TryAdd("shift_Array", new List<object> { new KeyValuePair<string, object>(key, segnaposto) }); continue; } if (key.Equals("ShiftRecordList", StringComparison.OrdinalIgnoreCase) || key.Equals("RecordList", StringComparison.OrdinalIgnoreCase)) { segnapostoDic.TryAdd("dataStartRow", row.RowNum); } segnapostoDic.TryAdd(key, segnaposto); } } colNameTypes = new List<string>(); colNameTypes.Clear(); //为了防止重复添加表头 // 此处可以使用foreach循环但使用linq更简洁效率更高 colNameTypes = (from DataColumn column in dt.Columns let dataType = column.DataType select column.ColumnName + "|" + dataType.Name).ToList(); //表头 } else { //写入表头 #region 设置表头 dataStartRow 为零的时候才设置表头 colNameTypes = new List<string>(); IFont headerFont = m_Book.CreateFont(); headerFont.FontName = "新細明體"; headerFont.FontHeightInPoints = 14; //設置數據行字體大小 headerFont.Boldweight = (short)FontBoldWeight.Bold; //數據行字體加粗 //添加表头 ICellStyle headerCellStyle = m_Book.CreateCellStyle(); headerCellStyle.SetFont(headerFont); headerCellStyle.WrapText = true; headerCellStyle.VerticalAlignment = VerticalAlignment.Center; var headerParam = sheetName; //paramList.FirstOrDefault(p => p.ParameterName == "@header"); if (headerParam != null && !string.IsNullOrEmpty(headerParam.ToString())) { var row = m_Sheet.CreateRow(currentRow); row.HeightInPoints = 33; //设置当前行高度 ICell cell = row.CreateCell(0); cell.SetCellValue(headerParam.ToString()); cell.CellStyle = headerCellStyle; CellRangeAddress region = new CellRangeAddress(currentRow, currentRow, 0, dt.Columns.Count - 1); //合并单元格 m_Sheet.AddMergedRegion(region); currentRow += 1; } colNameTypes.Clear(); //为了防止重复添加表头 // 此处可以使用foreach循环但使用linq更简洁效率更高 colNameTypes = (from DataColumn column in dt.Columns let dataType = column.DataType select column.ColumnName + "|" + dataType.Name).ToList(); //表头 //any()方法用于判断集合中是否有元素,如果有元素则返回true,否则返回false if (colNameTypes.Any()) { var row = m_Sheet.CreateRow(currentRow); row.HeightInPoints = 33; //设置当前行高度 //添加表头 //标题样式 IFont titleFont = m_Book.CreateFont(); titleFont.FontName = "新細明體"; titleFont.FontHeightInPoints = 11; //設置數據行字體大小 titleFont.Boldweight = (short)FontBoldWeight.Bold; //數據行字體加粗 ICellStyle titleCellStyle = SetCellStyle(mBook: m_Book, contentFont: titleFont, fillForegroundColor: "#808080"); titleCellStyle.Alignment = HorizontalAlignment.Center; titleCellStyle.VerticalAlignment = VerticalAlignment.Center; titleCellStyle.BorderDiagonalLineStyle = BorderStyle.Thin; for (int i = 0; i < colNameTypes.Count; i++) { var colNameType = colNameTypes[i].Split('|'); ICell cell = row.CreateCell(i); cell.SetCellValue(colNameType[0]); cell.CellStyle = titleCellStyle; } currentRow += 1; } #endregion } #endregion #region 设置每列的样式 List<ICellStyle> cellStyleList = null; if (colNameTypes != null) { //设置每列的样式 IFont contentFont = m_Book.CreateFont(); contentFont.FontName = "新細明體"; //設置數據行字體 contentFont.FontHeightInPoints = 11; //設置數據行字體大小 contentFont.Boldweight = (short)FontBoldWeight.Normal; int styleCol = 0; cellStyleList = new List<ICellStyle>(); foreach (DataColumn column in dt.Columns) { //列样式 //内容 ICellStyle cellStyle = SetCellStyle(mBook: m_Book, contentFont: contentFont); //.CreateCellStyle(); //设置列格式 var colNameType = colNameTypes[styleCol].Split('|'); var colType = colNameType[1]; switch (colType) { case "DateTime": // XSSFDataFormat cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("yyyy/MM/dd HH:mm:ss"); break; case "Int32": case "Int64": cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("#,##0"); break; case "Decimal": case "Double": case "Single": cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("#,##0.00"); break; default: cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@"); break; } cellStyleList.Add(cellStyle); styleCol++; } } #endregion int insertLastRow = 0; //写入数据 判断是否有占位数据需要替换 if (segnapostoDic.Count > 0 && currentRow == 0) { foreach (var item in segnapostoDic) { if (item.Key.Equals("formula_column") || item.Key.Equals("dataStartRow")) continue; if (item.Key.Equals("specify_Array", StringComparison.OrdinalIgnoreCase)) { // single_你的列名 insertLastRow = WriteSpecifyColumnNameExcelData(dt, m_Sheet, item.Value, cellStyleList); continue; } if (item.Key.Equals("shift_Array", StringComparison.OrdinalIgnoreCase)) { // shift_你的列名 insertLastRow = WriteShiftColumnNameExcelData(dt, m_Sheet, item.Value, cellStyleList); continue; } if (item.Key.Equals("joint_Array", StringComparison.OrdinalIgnoreCase)) { JoinStr(m_Sheet, item.Value); continue; } int row = Convert.ToInt32(ReflectTool.GetInstanceValue(item.Value, "RowIndex").ToString()); int col = Convert.ToInt32(ReflectTool.GetInstanceValue(item.Value, "CellIndex").ToString()); if (item.Key.Equals("RecordList")) { if (dt.Rows.Count <= 0) { m_Sheet.GetRow(row).GetCell(col).SetCellValue(""); continue; } insertLastRow = WriteExcelData(dt, row, m_Sheet, colNameTypes, cellStyleList, countRowRuleDic, colorFillDic); } else if (item.Key.Equals("ShiftRecordList")) { if (dt.Rows.Count <= 0) { // m_Sheet.ShiftRows(m_Sheet.LastRowNum+1, m_Sheet.LastRowNum+2, -2); // 调整行索引 if (m_Sheet.GetRow(row + 1) != null) { m_Sheet.RemoveRow(m_Sheet.GetRow(row + 1)); } if (m_Sheet.GetRow(row) != null) { m_Sheet.RemoveRow(m_Sheet.GetRow(row)); } continue; } insertLastRow = WriteExcelShiftRowsData(dt, row, m_Sheet, colNameTypes, cellStyleList, countRowRuleDic, colorFillDic); } else if (item.Key.StartsWith("images", StringComparison.OrdinalIgnoreCase)) { if (imgObj == null) { continue; } string rgx_str = new Regex(@"(?i)(?<=\()(.*)(?=\))").Match(item.Key).Value; //小括号里面的内容 /** * HSSFClientAnchor 详解 * dx1:图片左边相对excel格的位置(x偏移) 范围值为:0~1023;即输100 偏移的位置大概是相对于整个单元格的宽度的100除以1023大概是10分之一 * dy1:图片上方相对excel格的位置(y偏移) 范围值为:0~256 原理同上。 * dx2:图片右边相对excel格的位置(x偏移) 范围值为:0~1023; 原理同上。 * dy2:图片下方相对excel格的位置(y偏移) 范围值为:0~256 原理同上。 * col1和row1 :图片左上角的位置,以excel单元格为参考,比喻这两个值为(1,1),那么图片左上角的位置就是excel表(1,1)单元格的右下角的点(A,1)右下角的点。 * col2和row2:图片右下角的位置,以excel单元格为参考,比喻这两个值为(2,2),那么图片右下角的位置就是excel表(2,2)单元格的右下角的点(B,2)右下角的点。 */ object imgPath = imgObj.GetType().GetProperty(item.Key.Split('_')[1]).GetValue(imgObj); if (imgPath == null) { m_Sheet.GetRow(row).GetCell(col).SetCellValue(""); continue; } m_Sheet.GetRow(row).GetCell(col).SetCellValue(imgPath.ToString()); if (!AlphaFS.File.Exists(imgPath.ToString())) { //图片不存在 continue; } byte[] bytes = AlphaFS.File.ReadAllBytes(imgPath.ToString()); int pictureIdx = m_Book.AddPicture(bytes, PictureType.JPEG); var patriarch = m_Sheet.CreateDrawingPatriarch(); switch (fileExt) { //如果是XLSX格式选择XSSFWorkbook ,如果是XLS格式选择HSSFWorkbook case ".xlsx": var pict_xss = patriarch.CreatePicture( new XSSFClientAnchor(1, 1, 0, 0, col, row, col + 2, row + 2), pictureIdx); pict_xss.Resize(); break; case ".xls": var pict_hss = patriarch.CreatePicture( new HSSFClientAnchor(1, 1, 0, 0, col, row, col + 2, row + 2), pictureIdx); pict_hss.Resize(); break; } } else if (item.Key.Equals("TableName", StringComparison.OrdinalIgnoreCase)) { m_Sheet.GetRow(row).GetCell(col).SetCellValue(sheetName); } else if (item.Key.Equals("title_date", StringComparison.OrdinalIgnoreCase)) { m_Sheet.GetRow(row).GetCell(col).SetCellValue(DateTime.Now); } } if (segnapostoDic.ContainsKey("formula_column")) { if (segnapostoDic.ContainsKey("ShiftRecordList") || segnapostoDic.ContainsKey("shift_Array")) { object formulaColumn = segnapostoDic["formula_column"]; if (segnapostoDic.ContainsKey("dataStartRow")) { object dataStartRow = segnapostoDic["dataStartRow"]; FormulaColumn(m_Sheet, dt.Rows.Count, formulaColumn, dataStartRow); } else { FormulaColumn(m_Sheet, dt.Rows.Count, formulaColumn, null); } } else { m_Sheet.Workbook.GetCreationHelper().CreateFormulaEvaluator().EvaluateAll(); } } } else { insertLastRow = WriteExcelData(dt, currentRow, m_Sheet, colNameTypes, cellStyleList, countRowRuleDic, colorFillDic); } if (countColumnRuleDic != null) { WriteCurrentValueColumnRule(m_Sheet, countColumnRuleDic, insertLastRow); } m_Sheet.GetRow(0).GetCell(0)?.RemoveCellComment(); // 删除(0,0)单元格注释 } else { NullDataTabel(m_Book, clearIndex: clearIndex, reportDate: reportDate); } //转为字节数组 using (MemoryStream stream = new MemoryStream()) { m_Book.Write(stream); var buf = stream.ToArray(); //保存为Excel文件 using (fs = AlphaFS.File.Open(filePath, FileMode.Create, FileAccess.Write)) { fs.Write(buf, 0, buf.Length); fs.Flush(); } } suc = true; //var savePath = Path.Combine(HtmlReportSavePath, AlphaFS.Path.GetFileNameWithoutExtension(filePath) + ".html"); //if (!Directory.Exists(HtmlReportSavePath)) //{ // Directory.CreateDirectory(HtmlReportSavePath); //} //ExcelToHtml.ConvertExcelToHtml(filePath, savePath); //生成HTML文件 } catch (Exception ex) { Logger.Error($"NPOIGenerateReport error - 工作表名称:{sheetName}: \r\n" + ex.ToString()); suc = false; } finally { #region 释放Excel资源 m_Sheet = null; m_Book = null; if (fs != null) { fs.Close(); } GC.WaitForPendingFinalizers(); GC.Collect(); #endregion //保存报表记录 if (!string.IsNullOrEmpty(filePath)) { GarbageManager.SaveReport($"v2-{collection}", filePath.Replace(ReportSavePath, ""), reportType, "SYS", suc, reportDate); } } } /// <summary> /// datatable为空时清空数据表中的数据 /// </summary> /// <param name="m_Book"></param> /// <param name="clearIndex"></param> private static void NullDataTabel(IWorkbook m_Book, int clearIndex = -1, DateTime? reportDate = null) { for (int i = (clearIndex == -1 ? 0 : clearIndex); i <= (clearIndex == -1 ? m_Book.NumberOfSheets - 1 : clearIndex); i++) { ISheet sheet = m_Book.GetSheetAt(i); for (int q = 0; q <= sheet.LastRowNum; q++) { var row = sheet.GetRow(q); if (row == null) continue; short cellNum = row.LastCellNum; //获取当前行的列数内容之间的空格也算一个列 for (int j = 0; j < cellNum; j++) { var cell = row.GetCell(j); if (cell == null) continue; if (cell.CellType == CellType.Numeric) continue; var cellValue = Convert.ToString(cell); if (cell.CellType == CellType.Formula) { cell.SetCellValue(""); continue; } string key = Regex.Match(cellValue, @"\{(.*)\}", RegexOptions.Singleline).Groups[1] .Value; if (!string.IsNullOrWhiteSpace(key)) { if (key.StartsWith("title_date") || key.StartsWith("sheet_date")) { cell.SetCellValue(reportDate?.ToString("yyyy/MM")); continue; } else if (key.Equals("ShiftRecordList")) { if (sheet.GetRow(row.RowNum + 1) != null) { sheet.RemoveRow(sheet.GetRow(row.RowNum + 1)); } if (sheet.GetRow(row.RowNum) != null) { sheet.RemoveRow(sheet.GetRow(row.RowNum)); } } cell.SetCellValue(string.Empty); } key = Regex.Match(cellValue, @"\[(.*)\]", RegexOptions.Singleline).Groups[1] .Value; if (key.StartsWith("month")) { cell.SetCellValue(cellValue.Replace("[month]", $"{reportDate?.ToString("MM")}月")); } else if (key.StartsWith("year")) { cell.SetCellValue(reportDate?.ToString("yyyy")); } } } sheet.GetRow(0).GetCell(0)?.RemoveCellComment(); // 删除(0,0)单元格注释 } } /// <summary> /// 复制模板列到目标列 /// </summary> /// <param name="fileNameCombinationType"></param> /// <param name="fileName"></param> /// <param name="sheetName"></param> /// <param name="filePath"></param> /// <param name="sheetIndex"></param> /// <param name="startIndex">在目标那行或列开始插入 默认值:0</param> /// <param name="copyStartRowIndex">从那行开始复制(下标)默认值:0</param> /// <param name="copyStartColumnIndex">从那列开始复制(下标)默认值:0</param> /// <returns>IWorkbook,ISheet,fileExt,filePath,endColumnIndex</returns> /// <exception cref="Exception"></exception> public static Tuple<IWorkbook, string, string> CopyTemplateColumnsToTargetColumns(int fileNameCombinationType, string fileName, string filePath = null, List<ExcelParam> list = null) { IWorkbook m_Book; ISheet m_Sheet = null; try { string fileExt = string.Empty; var tupleData = OpenWorkbook(fileNameCombinationType, fileName, filePath); m_Book = tupleData.Item3; if (m_Book == null) { throw new Exception("IWorkbook is null"); } fileExt = tupleData.Item2; filePath = tupleData.Item1; int copyIndex = -1; if (list == null) { throw new Exception("sourceColumnIndexes is null"); } int endColumnIndex = 0; int endRowIndex = 0; ISheet templateSheet = null; List<int> recordTemplateIndex = new List<int>(); List<string> filter = null; for (int i = 0; i < list?.Count; i++) { var item = list[i]; if (item.IsFilter && filter == null) filter = new List<string>(); if (m_Sheet == null || (i > 0 && item.SheetIndex != list[i - 1].SheetIndex)) { endColumnIndex = 0; endRowIndex = 0; filter = new List<string>(); m_Sheet = GetSheet(m_Book, item.SheetName, copyIndex, item.SheetIndex); } if (templateSheet == null || (i > 0 && item.CopyTemplateIndex != list[i - 1].CopyTemplateIndex)) { int copyCellIndex = item.CopyTemplateIndex; templateSheet = m_Book.GetSheetAt(copyCellIndex); //假设模板1的数据在第一个工作表中 } if (item.StartRowIndex > 0 && endRowIndex == 0) endRowIndex = item.StartRowIndex; if (item.StartColumnIndex > 0 && endColumnIndex == 0) endColumnIndex = item.StartColumnIndex; for (int j = 0; j < item.CopyCount; j++) { Tuple<int, int> index = null; index = CopyTemplateRowsColumnsAndStyles(templateSheet, m_Sheet, item.SourceRowIndexes, item.SourceColumnIndexes, endRowIndex, endColumnIndex, item.CopyStartRowIndex, item.CopyStartColumnIndex, item.IntervalRow, item.IntervalColumn, item, filter); endColumnIndex = item.IsColumnAddUp ? index.Item1 : item.StartColumnIndex; endRowIndex = item.IsRowAddUp ? index.Item2 : item.StartRowIndex; } recordTemplateIndex.Add(item.CopyTemplateIndex); } //将模版下标去重再从大到小排序然后执行删除操作 List<int> distinctList = recordTemplateIndex.Distinct().ToList(); distinctList.Sort((x, y) => y.CompareTo(x)); foreach (var t in distinctList) { m_Book.RemoveSheetAt(t); } return new Tuple<IWorkbook, string, string>(m_Book, fileExt, filePath); } catch (Exception ex) { #region 释放Excel资源 m_Sheet = null; m_Book = null; GC.WaitForPendingFinalizers(); GC.Collect(); #endregion Logger.Error($"NPOIGenerateReport error - 工作表名称:{fileName}: \r\n" + ex.ToString()); throw new Exception(fileName, ex); } } /// <summary> /// 将DataTable数据写入Excel /// </summary> /// <param name="dt"> datatable </param> /// <param name="startDataRow"> 写入数据的起始行 </param> /// <param name="m_Sheet"></param> /// <param name="colNameTypes"></param> /// <param name="cellStyleList"></param> /// <param name="fillForegroundColor">颜色填充</param> /// <returns></returns> /// <exception cref="Exception"></exception> private static int WriteExcelData(DataTable dt, int startDataRow, ISheet m_Sheet, List<string> colNameTypes, List<ICellStyle> cellStyleList, IDictionary<string, CountRuleInfo> countRowRuleDic = null, IDictionary<string, ICellStyle> fillForegroundColor = null) { try { foreach (DataRow row in dt.Rows) { IRow newRow = m_Sheet.CreateRow(startDataRow); // 创建新行 newRow.HeightInPoints = 16; //设置当前行高度 int currentCol = 0; int moveColumn = 0; //移动的列数 foreach (DataColumn column in dt.Columns) { ICell cell = newRow.CreateCell(currentCol + moveColumn); string[] colNameType = colNameTypes[currentCol].Split('|'); //设置列格式 string colType = colNameType[1]; if (countRowRuleDic == null) { func(row, column, colType, cell, cellStyleList, currentCol, fillForegroundColor); } else { int columnIndex = cell.ColumnIndex; if (!countRowRuleDic.ContainsKey(columnIndex.ToString())) { func(row, column, colType, cell, cellStyleList, currentCol, fillForegroundColor); } else { moveColumn++; moveColumn = WriteCurrentValueRowRule(countRowRuleDic, moveColumn, row, column, colType, cell, newRow, currentCol, cellStyleList, fillForegroundColor); } } currentCol++; } startDataRow++; } return startDataRow; } catch (Exception ex) { Logger.Error($"WriteExcelData error - : \r\n" + ex.ToString()); return -1; } } /// <summary> /// 在当前行插入一行数据 /// </summary> /// <param name="dt"></param> /// <param name="startDataRow"></param> /// <param name="m_Sheet"></param> /// <param name="colNameTypes"></param> /// <param name="cellStyleList"></param> /// <param name="countRowRuleDic"></param> /// <param name="fillForegroundColor"></param> /// <returns></returns> private static int WriteExcelShiftRowsData(DataTable dt, int startDataRow, ISheet m_Sheet, List<string> colNameTypes, List<ICellStyle> cellStyleList, IDictionary<string, CountRuleInfo> countRowRuleDic = null, IDictionary<string, ICellStyle> fillForegroundColor = null) { try { foreach (DataRow row in dt.Rows) { m_Sheet.ShiftRows(startDataRow, m_Sheet.LastRowNum, 1, true, false); // 插入一行,将后面的行向下移动 IRow newRow = m_Sheet.CreateRow(startDataRow); // 创建新行 newRow.HeightInPoints = 16; //设置当前行高度 int currentCol = 0; int moveColumn = 0; //移动的列数 foreach (DataColumn column in dt.Columns) { ICell cell = newRow.CreateCell(currentCol + moveColumn); string[] colNameType = colNameTypes[currentCol].Split('|'); //设置列格式 string colType = colNameType[1]; if (countRowRuleDic == null) { func(row, column, colType, cell, cellStyleList, currentCol, fillForegroundColor); } else { int columnIndex = cell.ColumnIndex; if (!countRowRuleDic.ContainsKey(columnIndex.ToString())) { func(row, column, colType, cell, cellStyleList, currentCol, fillForegroundColor); } else { moveColumn++; moveColumn = WriteCurrentValueRowRule(countRowRuleDic, moveColumn, row, column, colType, cell, newRow, currentCol, cellStyleList, fillForegroundColor); } } currentCol++; } startDataRow++; } m_Sheet.ShiftRows(startDataRow + 1, m_Sheet.LastRowNum, -1); return startDataRow; } catch (Exception ex) { Logger.Error($"WriteExcelData error - : \r\n" + ex.ToString()); return -1; } } /// <summary> /// 写入当前行的值并设置样式 /// </summary> /// <summary> /// 写入当前行的值并设置样式 /// </summary> private static void func(DataRow row, DataColumn column, string colType, ICell cell, List<ICellStyle> cellStyleList, int currentCol, IDictionary<string, ICellStyle> fillForegroundColor) { // if (IsNumeric(row[column.ColumnName].ToString())) // { // string asdas = ""; // } if (row[column.ColumnName] != DBNull.Value) { switch (colType) { case "DateTime": cell.SetCellValue(DateTime.Parse(row[column.ColumnName].ToString())); break; case "Int32": case "Int64": cell.SetCellValue(Convert.ToInt64(row[column.ColumnName])); break; case "Decimal": case "Double": case "Single": cell.SetCellValue(double.Parse(row[column.ColumnName].ToString())); break; default: cell.SetCellValue(row[column.ColumnName].ToString()); break; } } else { cell.SetCellValue(row[column.ColumnName].ToString()); } if (cellStyleList == null) return; if (fillForegroundColor == null || fillForegroundColor.Count == 0) { cell.CellStyle = cellStyleList[currentCol]; } else { if (fillForegroundColor.ContainsKey(cell.ColumnIndex.ToString())) { cell.CellStyle = fillForegroundColor[cell.ColumnIndex.ToString()]; } } } /// <summary> /// 每行的规则统计 /// </summary> /// <param name="countRowRuleDic"></param> /// <param name="moveColumn"></param> /// <param name="row"></param> /// <param name="column"></param> /// <param name="colType"></param> /// <param name="cell"></param> /// <param name="currRow"></param> /// <param name="currentCol"></param> /// <returns></returns> private static int WriteCurrentValueRowRule(IDictionary<string, CountRuleInfo> countRowRuleDic, int moveColumn, DataRow row, DataColumn column, string colType, ICell cell, IRow currRow, int currentCol, List<ICellStyle> cellStyleList, IDictionary<string, ICellStyle> fillForegroundColor) { int columnIndex = cell.ColumnIndex; int rowIndex = cell.RowIndex; CountRuleInfo countRuleInfo = countRowRuleDic[columnIndex.ToString()]; if (string.IsNullOrWhiteSpace(countRuleInfo.RowRule)) return moveColumn; MatchCollection countRule = _rgxBrace.Matches(countRuleInfo.RowRule); // 提取大括号的内容出来 if (countRule.Count <= 0) { if (!string.IsNullOrWhiteSpace(countRuleInfo.RowRule)) { cell.CellFormula = $"{countRuleInfo.RowRule}"; cell.SetCellType(CellType.Formula); XSSFFormulaEvaluator evaluator = new XSSFFormulaEvaluator(currRow.Sheet.Workbook); evaluator.EvaluateFormulaCell(cell); } } else { string formula = string.Empty; foreach (Match regItem in countRule) { string value = regItem.Value; //替换成当前列 MatchCollection matches_bracket = new Regex(@"(?i)(\[)(.*)(\])").Matches(value); // 提取中括号的内容出来转换成字母 //没有中括号直接替换行、列写入公式 if (matches_bracket.Count <= 0) { formula = countRuleInfo.RowRule.Replace("column", columnIndex.ToString()) .Replace("{", "") .Replace("}", ""); continue; } //处理中括号的内容 foreach (Match bracketItem in matches_bracket) { string cellValue = _rgxBracket.Match(bracketItem.Value).Value .Replace("column", columnIndex.ToString()); int col = _rgx.IsMatch(cellValue) ? CalcByDataTable(cellValue) : Convert.ToInt32(cellValue); //计算公式 string letter = ExcelIndexToLetter(col); //列号转字母 value = value.Replace(bracketItem.Value, $"{letter}{rowIndex + 1}"); //替换成字母 } formula = countRuleInfo.RowRule.Replace(regItem.Value, value) .Replace("{", "") .Replace("}", ""); } cell.CellFormula = $"{formula}"; cell.SetCellType(CellType.Formula); XSSFFormulaEvaluator evaluator = new XSSFFormulaEvaluator(currRow.Sheet.Workbook); evaluator.EvaluateFormulaCell(cell); } if (fillForegroundColor == null || fillForegroundColor.Count == 0) { if (cellStyleList == null) { cell.CellStyle = cellStyleList[currentCol]; } } else { if (fillForegroundColor.ContainsKey(cell.ColumnIndex.ToString())) { cell.CellStyle = fillForegroundColor[cell.ColumnIndex.ToString()]; } } //判断下一列是否是占位符 int nextCell = currentCol + moveColumn; if (!countRowRuleDic.ContainsKey(nextCell.ToString())) { // 不是占位符时当前列的值加上该移动的列数 func(row, column, colType, currRow.CreateCell(nextCell), cellStyleList, currentCol, fillForegroundColor); return moveColumn; } moveColumn = moveColumn++; //如果是占位符,递归计算 WriteCurrentValueRowRule(countRowRuleDic, moveColumn, row, column, colType, currRow.CreateCell(nextCell), currRow, currentCol, cellStyleList, fillForegroundColor); return moveColumn; } /// <summary> /// 指定列名写入数据 /// </summary> /// <param name="dt"></param> /// <param name="m_Sheet"></param> /// <param name="data"></param> /// <returns></returns> private static int WriteSpecifyColumnNameExcelData(DataTable dt, ISheet m_Sheet, object columnNameList, List<ICellStyle> cellStyleList) { if (dt.Rows.Count <= 0) { foreach (var item in columnNameList as List<object>) { string key = item.GetType().GetProperty("Key").GetValue(item, null).ToString(); object data = item.GetType().GetProperty("Value").GetValue(item, null); int _row = Convert.ToInt32(ReflectTool.GetInstanceValue(data, "RowIndex").ToString()); int _col = Convert.ToInt32(ReflectTool.GetInstanceValue(data, "CellIndex").ToString()); m_Sheet.GetRow(_row).GetCell(_col).SetCellValue(""); } return 0; } Dictionary<string, object> columnNameDic = new Dictionary<string, object>(); foreach (var item in columnNameList as List<object>) { string key = ReflectTool.GetInstanceValue(item, "Key").ToString(); // item.GetType().GetProperty("Key").GetValue(item, null).ToString(); object value = item.GetType().GetProperty("Value").GetValue(item, null); columnNameDic.Add(key.ToUpper(), value); } int rows = 0; int row = 0; int col = 0; foreach (DataRow dr in dt.Rows) { IRow _rows = null; bool isIndex = false; foreach (DataColumn column in dt.Columns) { if (!columnNameDic.ContainsKey($"specify_{column.ColumnName}".ToUpper())) { continue; } var data = columnNameDic[$"specify_{column.ColumnName}".ToUpper()]; row = Convert.ToInt32(ReflectTool.GetInstanceValue(data, "RowIndex").ToString()); col = Convert.ToInt32(ReflectTool.GetInstanceValue(data, "CellIndex").ToString()); if (_rows == null) { _rows = m_Sheet.GetRow(row + rows) ?? m_Sheet.CreateRow(row + rows); } ICell cell = null; if (columnNameDic.ContainsKey("specify_index".ToUpper()) && !isIndex) { int _col = Convert.ToInt32(ReflectTool .GetInstanceValue(columnNameDic["specify_index".ToUpper()], "CellIndex").ToString()); isIndex = true; cell = _rows.GetCell(_col) ?? _rows.CreateCell(_col); cell.SetCellValue(Convert.ToDouble(rows + 1)); if (cellStyleList.Count > 0) { cell.CellStyle = SetCellFormat("Int32", cellStyleList[0]); } } string input = dr[column.ColumnName].ToString(); cell = _rows.GetCell(col) ?? _rows.CreateCell(col); switch (column.DataType.Name) { case "DateTime": cell.SetCellValue(input); break; case "Int32": case "Int64": cell.SetCellValue(Convert.ToInt64(input)); break; case "Decimal": case "Double": case "Single": cell.SetCellValue(double.Parse(input)); break; default: cell.SetCellValue(input); break; } if (cellStyleList.Count > 0) { ICellStyle style = SetCellFormat(column.DataType.Name, cellStyleList[0]); cell.CellStyle = style; } } rows++; } return rows + row; } /// <summary> /// 指定列名写入数据(在当前行往前插入一行) /// </summary> /// <param name="dt"></param> /// <param name="m_Sheet"></param> /// <param name="columnNameList"></param> /// <param name="cellStyleList"></param> /// <returns></returns> private static int WriteShiftColumnNameExcelData(DataTable dt, ISheet m_Sheet, object columnNameList, List<ICellStyle> cellStyleList) { if (dt.Rows.Count <= 0) { foreach (var item in columnNameList as List<object>) { string key = item.GetType().GetProperty("Key").GetValue(item, null).ToString(); object data = item.GetType().GetProperty("Value").GetValue(item, null); int _row = Convert.ToInt32(ReflectTool.GetInstanceValue(data, "RowIndex").ToString()); int _col = Convert.ToInt32(ReflectTool.GetInstanceValue(data, "CellIndex").ToString()); m_Sheet.GetRow(_row).GetCell(_col).SetCellValue(""); } return 0; } Dictionary<string, object> columnNameDic = new Dictionary<string, object>(); foreach (var item in columnNameList as List<object>) { string key = ReflectTool.GetInstanceValue(item, "Key").ToString(); // item.GetType().GetProperty("Key").GetValue(item, null).ToString(); object value = item.GetType().GetProperty("Value").GetValue(item, null); columnNameDic.Add(key.ToUpper(), value); } int rows = 0; int row = 0; int col = 0; foreach (DataRow dr in dt.Rows) { IRow _rows = null; bool isIndex = false; int currentCol = 0; int moveColumn = 0; //移动的列数 foreach (DataColumn column in dt.Columns) { if (!columnNameDic.ContainsKey($"shift_{column.ColumnName}".ToUpper())) { continue; } var data = columnNameDic[$"shift_{column.ColumnName}".ToUpper()]; row = Convert.ToInt32(ReflectTool.GetInstanceValue(data, "RowIndex").ToString()); //获取当前单元格的行索引 col = Convert.ToInt32(ReflectTool.GetInstanceValue(data, "CellIndex").ToString()); //获取当前单元格的列索引 if (_rows == null) { m_Sheet.ShiftRows(row + rows, m_Sheet.LastRowNum, 1, true, false); // 插入一行,将后面的行向下移动 _rows = m_Sheet.GetRow(row + rows) ?? m_Sheet.CreateRow(row + rows); _rows.HeightInPoints = 16; //设置当前行高度 } ICell cell = null; if (columnNameDic.ContainsKey("shift_index".ToUpper()) && !isIndex) { int _col = Convert.ToInt32(ReflectTool .GetInstanceValue(columnNameDic["shift_index".ToUpper()], "CellIndex").ToString()); isIndex = true; cell = _rows.GetCell(_col) ?? _rows.CreateCell(_col); cell.SetCellValue(Convert.ToDouble(rows + 1)); if (cellStyleList.Count > 0) { cell.CellStyle = SetCellFormat("Int32", cellStyleList[0]); } } string input = dr[column.ColumnName].ToString(); cell = _rows.GetCell(col) ?? _rows.CreateCell(col); switch (column.DataType.Name) { case "DateTime": cell.SetCellValue(input); break; case "Int32": case "Int64": cell.SetCellValue(Convert.ToInt64(input)); break; case "Decimal": case "Double": case "Single": cell.SetCellValue(double.Parse(input)); break; default: cell.SetCellValue(input); break; } if (cellStyleList.Count > 0) { ICellStyle style = SetCellFormat(column.DataType.Name, cellStyleList[0]); cell.CellStyle = style; } } rows++; } m_Sheet.ShiftRows(rows + row + 1, m_Sheet.LastRowNum, -1); //移除自定义的属性列 return rows + row; } #endregion #region excel 公用方法 private static void JoinStr(ISheet m_Sheet, object columnNameList) { foreach (var item in columnNameList as List<object>) { string key = item.GetType().GetProperty("Key").GetValue(item, null).ToString(); object data = item.GetType().GetProperty("Value").GetValue(item, null); int row = Convert.ToInt32(ReflectTool.GetInstanceValue(data, "RowIndex").ToString()); int col = Convert.ToInt32(ReflectTool.GetInstanceValue(data, "CellIndex").ToString()); if ("date".Equals(key, StringComparison.OrdinalIgnoreCase)) { string val = ReflectTool.GetInstanceValue(data, "CellValue").ToString(); m_Sheet.GetRow(row).GetCell(col) .SetCellValue(val.Replace($"[{key}]", $"{DateTime.Now:yyyy年MM月dd日}")); } else if ("month".Equals(key, StringComparison.OrdinalIgnoreCase)) { string val = ReflectTool.GetInstanceValue(data, "CellValue").ToString(); m_Sheet.GetRow(row).GetCell(col).SetCellValue(val.Replace($"[{key}]", $"{DateTime.Now:yyyy年MM月}")); } else if ("year".Equals(key, StringComparison.OrdinalIgnoreCase)) { string val = ReflectTool.GetInstanceValue(data, "CellValue").ToString(); m_Sheet.GetRow(row).GetCell(col).SetCellValue(val.Replace($"[{key}]", $"{DateTime.Now:yyyy年}")); } else { string val = ReflectTool.GetInstanceValue(data, "CellValue").ToString(); m_Sheet.GetRow(row).GetCell(col).SetCellValue(val.Replace($"[{key}]", val)); } } } /// <summary> /// 设置单元格样式 /// </summary> /// <param name="colType"></param> /// <param name="cellStyle"></param> /// <returns></returns> private static ICellStyle SetCellFormat(string colType, ICellStyle cellStyle) { switch (colType) { case "DateTime": // XSSFDataFormat cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("yyyy/MM/dd HH:mm:ss"); break; case "Int32": case "Int64": cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("#,##0"); break; case "Decimal": case "Double": case "Single": cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("#,##0.00"); break; default: cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@"); break; } return cellStyle; } /// <summary> /// 打开工作簿 /// </summary> /// <param name="fileNameCombinationType"></param> /// <param name="fileName"></param> /// <param name="filePath"></param> /// <param name="m_Book"></param> /// <exception cref="Exception"></exception> /// <returns>文件路径,文件后缀,工作簿</returns> private static Tuple<string, string, IWorkbook> OpenWorkbook(int fileNameCombinationType, string fileName, string filePath, bool repeatWrite = false, DateTime? reportDate = null) { #region 判断是新增还是打开修改 IWorkbook m_Book = null; string fileExt = string.Empty; if (filePath == null) { SavePath(fileNameCombinationType, fileName, reportDate, out filePath); if (AlphaFS.File.Exists(filePath) && !repeatWrite) { AlphaFS.File.Delete(filePath); } } else { SavePath(fileNameCombinationType, fileName, reportDate, out filePath, sourceFile: filePath); if (!AlphaFS.File.Exists(filePath)) throw new Exception("報表模板不存在"); } //获取后缀名称 fileExt = AlphaFS.Path.GetExtension(filePath)?.ToLower(); using (FileStream file = AlphaFS.File.Open(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { switch (fileExt) { //如果是XLSX格式选择XSSFWorkbook ,如果是XLS格式选择HSSFWorkbook case ".xlsx": m_Book = new XSSFWorkbook(file); break; case ".xls": m_Book = new HSSFWorkbook(file); break; } } return new Tuple<string, string, IWorkbook>(filePath, fileExt, m_Book); #endregion } /// <summary> /// 获取工作表 /// </summary> /// <param name="m_Book"></param> /// <param name="sheetName"></param> /// <param name="copyIndex"></param> /// <param name="sheetIndex"></param> /// <param name="m_Sheet"></param> /// <exception cref="Exception"></exception> private static ISheet GetSheet(IWorkbook m_Book, string sheetName, int copyIndex, int sheetIndex) { #region 判断是否需要创建新的sheet ISheet m_Sheet = null; if (sheetIndex == -1) { // 处理路径中的非法字符 string invalid = new string(AlphaFS.Path.GetInvalidFileNameChars()) + new string(AlphaFS.Path.GetInvalidPathChars()); sheetName = invalid.Aggregate(sheetName, (current, c) => current.Replace(c.ToString(), "")); if (copyIndex != -1) { m_Book?.GetSheetAt(copyIndex).CopyTo(m_Book, sheetName, true, true); //按指定下标读写数据 // m_Sheet = m_Book?.GetSheetAt(copyIndex).CopySheet(sheetName, true); int? index = m_Book?.GetSheetIndex(sheetName); m_Book.SetSelectedTab(Convert.ToInt32(index)); m_Book.SetActiveSheet(Convert.ToInt32(index)); //设置当前操作的sheet m_Sheet = m_Book?.GetSheetAt(Convert.ToInt32(index)); } else { // sheet表名也是要处理的,但在存储过程拿到数据后已经处理,dt.TableName这里就不再处理 m_Sheet = m_Book?.CreateSheet(sheetName); //创建工作表 m_Sheet.SetMargin(MarginType.RightMargin, (double)10); //设置页右边距 m_Sheet.SetMargin(MarginType.TopMargin, (double)10); //设置页头边距 m_Sheet.SetMargin(MarginType.LeftMargin, (double)10); //设置页左边距 m_Sheet.SetMargin(MarginType.BottomMargin, (double)10); //设置页底边距 m_Sheet.PrintSetup.Copies = 1; //打印份数 m_Sheet.PrintSetup.NoColor = true; //是否打印黑白 m_Sheet.PrintSetup.Landscape = true; //页面方向横向 m_Sheet.PrintSetup.PaperSize = (short)PaperSize.A4; //设置纸张大小 m_Sheet.PrintSetup.FitHeight = 2; //设置打印纸张大小 m_Sheet.PrintSetup.FitWidth = 3; //设置打印纸张大小 m_Sheet.IsPrintGridlines = true; //打印网格线 //是否自适应界面 m_Sheet.FitToPage = false; m_Sheet.VerticallyCenter = true; //单元格文字垂直居中 } } else if (sheetIndex >= 0) { //按指定下标读写数据 m_Book.SetActiveSheet(sheetIndex); //设置当前操作的sheet m_Book.SetSelectedTab(sheetIndex); m_Sheet = m_Book?.GetSheetAt(sheetIndex); if (!string.IsNullOrWhiteSpace(sheetName)) { m_Book.SetSheetName(sheetIndex, sheetName); } } else { throw new Exception("没有指定的处理"); } return m_Sheet; #endregion } /// <summary> /// 获取单元格注释 /// </summary> /// <param name="m_Book"></param> /// <param name="m_Sheet"></param> /// <param name="copyIndex"></param> /// <param name="row"></param> /// <param name="column"></param> /// <returns>列规则,行规则,填充颜色</returns> private static Tuple<Dictionary<string, CountRuleInfo>, Dictionary<string, CountRuleInfo>, Dictionary<string, ICellStyle>> GetCellComment(IWorkbook m_Book, ISheet m_Sheet, int copyIndex, int row = 0, int column = 0) { #region 获取excel0,0单元格注释 var cellcomment = copyIndex != -1 ? m_Book.GetSheetAt(copyIndex).GetCellComment(row, column) : m_Sheet.GetCellComment(0, 0); // 获取(0,0)单元格注释 Dictionary<string, CountRuleInfo> countColumnRuleDic = null; //列规则 Dictionary<string, CountRuleInfo> countRowRuleDic = null; //行规则 Dictionary<string, ICellStyle> colorFillDic = null; //填充颜色 string filterProperties = ""; if (cellcomment == null) { return new Tuple<Dictionary<string, CountRuleInfo>, Dictionary<string, CountRuleInfo>, Dictionary<string, ICellStyle>>(countColumnRuleDic, countRowRuleDic, colorFillDic); } countRowRuleDic = new Dictionary<string, CountRuleInfo>(); countColumnRuleDic = new Dictionary<string, CountRuleInfo>(); colorFillDic = new Dictionary<string, ICellStyle>(); ExcelCellComment comment = JsonConvert.DeserializeObject<ExcelCellComment>(cellcomment.String .ToString()); // 序列化注释 List<CountRuleInfo> countRuleInfo = comment.CountRule; // 获取注释中的统计规则 filterProperties = comment.FilterProperties; //m_Sheet.IsColumnHidden(6); // 判断列是否隐藏 foreach (var item in countRuleInfo.Where(item => !"?".Equals(item.ColumnIndex) || !"?".Equals(item.RowIndex))) { //行规则 是指这行的数据是由哪些列的数据计算出来的 if (!countRowRuleDic.ContainsKey(item.ColumnIndex) && !string.IsNullOrWhiteSpace(item.RowRule)) { countRowRuleDic.Add(item.ColumnIndex, item); } if (string.IsNullOrWhiteSpace(item.ColumnRule) && string.IsNullOrWhiteSpace(item.FillForegroundColor)) continue; // 获取列的统计规则 列规则 是指这列的数据是由哪些行的数据计算出来的 if (!string.IsNullOrWhiteSpace(item.ColumnRule) && !countColumnRuleDic.ContainsKey(item.ColumnIndex)) { countColumnRuleDic.Add(item.ColumnIndex, item); } if (string.IsNullOrWhiteSpace(item.FillForegroundColor) || "?".Equals(item.FillForegroundColor)) continue; IFont contentFont = m_Book.CreateFont(); contentFont.FontName = "新細明體"; //設置數據行字體 contentFont.FontHeightInPoints = 11; //設置數據行字體大小 contentFont.Boldweight = (short)FontBoldWeight.Normal; ICellStyle fillCellStyle = SetCellStyle(mBook: m_Book, fillForegroundColor: item.FillForegroundColor, contentFont: contentFont); colorFillDic.Add(item.ColumnIndex, fillCellStyle); } #endregion return new Tuple<Dictionary<string, CountRuleInfo>, Dictionary<string, CountRuleInfo>, Dictionary<string, ICellStyle>>(countColumnRuleDic, countRowRuleDic, colorFillDic); } /// <summary> /// 设置单元格样式 /// </summary> /// <param name="m_Book"></param> /// <param name="fillForegroundColor"></param> /// <param name="contentFont"></param> /// <returns></returns> private static ICellStyle SetCellStyle(IWorkbook mBook, string fillForegroundColor = null, IFont contentFont = null) { ICellStyle cellStyle = mBook.CreateCellStyle(); cellStyle.BorderBottom = BorderStyle.Thin; // 设置单元格底部边框线 cellStyle.BorderLeft = BorderStyle.Thin; // 设置单元格左部边框线 cellStyle.BorderRight = BorderStyle.Thin; // 设置单元格右部边框线 cellStyle.BorderTop = BorderStyle.Thin; // 设置单元格头部边框线 cellStyle.WrapText = true; if (contentFont != null) cellStyle.SetFont(contentFont); if (fillForegroundColor == null) return cellStyle; // cellStyle.FillForegroundColor = HexToShort(fillForegroundColor); Color color = ColorTranslator.FromHtml(fillForegroundColor); if (mBook is XSSFWorkbook) { cellStyle.FillForegroundColor = 0; ((XSSFColor)cellStyle.FillForegroundColorColor).SetRgb(new byte[] { color.R, color.G, color.B }); } else { cellStyle.FillForegroundColor = (((HSSFWorkbook)mBook).GetCustomPalette().FindSimilarColor(color.R, color.G, color.B)).Indexed; } cellStyle.FillPattern = FillPattern.SolidForeground; return cellStyle; } /// <summary> /// 每列的规则统计 /// </summary> /// <param name="m_Sheet"></param> /// <param name="countColumnRuleDic"></param> /// <param name="startDataRow"></param> /// <returns></returns> private static int WriteCurrentValueColumnRule(ISheet m_Sheet, IDictionary<string, CountRuleInfo> countColumnRuleDic, int startDataRow) { IRow currRow = m_Sheet.CreateRow(startDataRow); currRow.HeightInPoints = 16; //设置当前行高度 foreach (var item in countColumnRuleDic) { CountRuleInfo countRuleInfo = item.Value; if (string.IsNullOrWhiteSpace(countRuleInfo.ColumnRule)) continue; int index = ExcelToNum(countRuleInfo.Letter); // 获取列号 ICell cell = currRow.CreateCell(index); MatchCollection regBrace = _rgxBrace.Matches(countRuleInfo.ColumnRule); // 提取大括号的内容出来 if (regBrace.Count <= 0) { cell.CellFormula = $"{countRuleInfo.ColumnRule}"; cell.SetCellType(CellType.Formula); XSSFFormulaEvaluator evaluator = new XSSFFormulaEvaluator(currRow.Sheet.Workbook); evaluator.EvaluateFormulaCell(cell); continue; } string formula = string.Empty; foreach (Match regItem in regBrace) { string value = regItem.Value; //替换成当前列 MatchCollection matches_bracket = new Regex(@"(?i)(\[)(.*)(\])").Matches(value); // 提取中括号的内容出来转换成字母 //没有中括号直接替换行、列写入公式 if (matches_bracket.Count <= 0) { formula = countRuleInfo.ColumnRule.Replace("row", startDataRow.ToString()) .Replace("{", "") .Replace("}", ""); continue; } //处理中括号的内容 foreach (Match bracketItem in matches_bracket) { string rowValue = _rgxBracket.Match(bracketItem.Value).Value; // int col = rgx.IsMatch(rowValue) ? CalcByDataTable(rowValue) : Convert.ToInt32(rowValue); //计算公式 // string letter = ExcelIndexToLetter(col); //列号转字母 string cellValue = String.Empty; if (rowValue.IndexOf("column") < 0) { cellValue = rowValue.Replace("row", startDataRow.ToString()); int row = _rgx.IsMatch(rowValue) ? CalcByDataTable(cellValue) : Convert.ToInt32(cellValue); //计算公式 value = value.Replace(bracketItem.Value, $"{countRuleInfo.Letter}{row}"); //替换成字母 continue; } cellValue = rowValue.Replace("column", index.ToString()); int col = _rgx.IsMatch(rowValue) ? CalcByDataTable(cellValue) : Convert.ToInt32(cellValue); //计算公式 string letter = ExcelIndexToLetter(col); //列号转字母 value = value.Replace(bracketItem.Value, $"{letter}{startDataRow}"); //替换成字母 } formula = countRuleInfo.ColumnRule.Replace(regItem.Value, value) .Replace("{", "") .Replace("}", ""); } cell.CellFormula = $"{formula}"; cell.SetCellType(CellType.Formula); XSSFFormulaEvaluator evaluator1 = new XSSFFormulaEvaluator(currRow.Sheet.Workbook); evaluator1.EvaluateFormulaCell(cell); } startDataRow++; return startDataRow; } /// <summary> /// 对单元格进行公式计算 /// </summary> /// <param name="m_Sheet"></param> /// <param name="columnNameList"></param> private static void FormulaColumn(ISheet m_Sheet, int dataCout, object columnNameList, object dataStartRow = null) { if (dataCout == 0) { m_Sheet.Workbook.GetCreationHelper().CreateFormulaEvaluator().EvaluateAll(); return; } var startRow = dataStartRow ?? 0; foreach (var item in columnNameList as List<object>) { int col = Convert.ToInt32(ReflectTool.GetInstanceValue(item, "CellIndex").ToString()); int row = Convert.ToInt32(ReflectTool.GetInstanceValue(item, "RowIndex").ToString()); string cellValue = ReflectTool.GetInstanceValue(item, "CellValue").ToString(); // var cell = m_Sheet.GetRow(dataCout+Convert.ToInt32(dataStartRow)+row-1).GetCell(col); var cell = m_Sheet.GetRow(Convert.ToInt32(dataStartRow) > row ? row : dataCout + row - 1)?.GetCell(col); CellType isCellType = cell.CellType; if (isCellType != CellType.Blank && cell.CellFormula.Contains("#REF!")) { cellValue = cellValue.Replace("#REF!", $"{ExcelIndexToLetter(col)}{Convert.ToInt32(startRow) + 1}"); cell.CellFormula = cellValue; } if (isCellType == CellType.Blank) { cell.CellFormula = cellValue; } XSSFFormulaEvaluator evaluator = new XSSFFormulaEvaluator(m_Sheet.Workbook); evaluator.EvaluateFormulaCell(cell); } } /// <summary> /// 由DataTable计算公式 /// </summary> /// <param name="expression">表达式</param> public static int CalcByDataTable(string expression) { object result = new DataTable().Compute(expression, ""); return int.Parse(result + ""); } /// <summary> /// 删除execl 里面的sheet /// </summary> /// <param name="filePath"></param> /// <param name="sheetName"></param> public static void DeleteSheet(string filePath, string sheetName) { IWorkbook m_Book = null; ISheet m_Sheet; FileStream fs = null; string tableName = string.Empty; try { Logger.Debug($"NPOIGenerateReport - 工作表名称:{tableName}"); string fileExt = string.Empty; //文件后缀 #region 判断是新增还是打开修改 if (!AlphaFS.File.Exists(filePath)) throw new Exception("報表模板不存在"); //获取后缀名称 fileExt = AlphaFS.Path.GetExtension(filePath)?.ToLower(); using (FileStream file = AlphaFS.File.Open(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { switch (fileExt) { //如果是XLSX格式选择XSSFWorkbook ,如果是XLS格式选择HSSFWorkbook case ".xlsx": m_Book = new XSSFWorkbook(file); break; case ".xls": m_Book = new HSSFWorkbook(file); break; } } int sheetIndex = m_Book.GetSheetIndex(sheetName); m_Book.RemoveSheetAt(sheetIndex); using (MemoryStream stream = new MemoryStream()) { m_Book.Write(stream); var buf = stream.ToArray(); //保存为Excel文件 using (fs = new FileStream(filePath, FileMode.Create, FileAccess.Write)) { fs.Write(buf, 0, buf.Length); fs.Flush(); } } #endregion } catch (Exception e) { Logger.Error($"NPOIGenerateReport error - 删除sheet出错---{filePath}"); } finally { #region 释放Excel资源 m_Sheet = null; m_Book = null; if (fs != null) { fs.Close(); } GC.WaitForPendingFinalizers(); GC.Collect(); #endregion } } /// <summary> /// 获取sheet索引 /// </summary> /// <param name="filePath"></param> /// <param name="sheetName"></param> public static int GetSheetIndex(string filePath, string sheetName) { IWorkbook m_Book = null; ISheet m_Sheet; FileStream fs = null; string tableName = string.Empty; try { Logger.Debug($"NPOIGenerateReport - 工作表名称:{tableName}"); string fileExt = string.Empty; //文件后缀 #region 判断是新增还是打开修改 if (!AlphaFS.File.Exists(filePath)) throw new Exception("報表模板不存在"); //获取后缀名称 fileExt = AlphaFS.Path.GetExtension(filePath)?.ToLower(); using (FileStream file = AlphaFS.File.Open(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { switch (fileExt) { //如果是XLSX格式选择XSSFWorkbook ,如果是XLS格式选择HSSFWorkbook case ".xlsx": m_Book = new XSSFWorkbook(file); break; case ".xls": m_Book = new HSSFWorkbook(file); break; } } int sheetIndex = m_Book.GetSheetIndex(sheetName); #endregion return sheetIndex; } catch (Exception e) { Logger.Error($"NPOIGenerateReport error - 删除sheet出错"); throw; } finally { #region 释放Excel资源 m_Sheet = null; m_Book = null; if (fs != null) { fs.Close(); } GC.WaitForPendingFinalizers(); GC.Collect(); #endregion } } /// <summary> /// 删除execl 里面的sheet /// </summary> /// <param name="filePath"></param> /// <param name="sheetIndex"></param> public static void DeleteSheet(string filePath, int sheetIndex) { IWorkbook m_Book = null; ISheet m_Sheet; FileStream fs = null; string tableName = string.Empty; try { Logger.Debug($"NPOIGenerateReport - 工作表名称:{tableName}"); string fileExt = string.Empty; //文件后缀 #region 判断是新增还是打开修改 if (!AlphaFS.File.Exists(filePath)) { return; } //获取后缀名称 fileExt = AlphaFS.Path.GetExtension(filePath)?.ToLower(); using (FileStream file = AlphaFS.File.Open(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { switch (fileExt) { //如果是XLSX格式选择XSSFWorkbook ,如果是XLS格式选择HSSFWorkbook case ".xlsx": m_Book = new XSSFWorkbook(file); break; case ".xls": m_Book = new HSSFWorkbook(file); break; } } m_Book.RemoveSheetAt(sheetIndex); using (MemoryStream stream = new MemoryStream()) { m_Book.Write(stream); var buf = stream.ToArray(); //保存为Excel文件 using (fs = new FileStream(filePath, FileMode.Create, FileAccess.Write)) { fs.Write(buf, 0, buf.Length); fs.Flush(); } } #endregion } catch (Exception e) { Logger.Error($"NPOIGenerateReport error - 删除sheet出错"); throw; } finally { #region 释放Excel资源 m_Sheet = null; m_Book = null; if (fs != null) { fs.Close(); } GC.WaitForPendingFinalizers(); GC.Collect(); #endregion } } /// <summary> /// 保存路径 /// </summary> /// <param name="fileNameCombinationType">报表类型:1-日报;2-月报; 3-年报;</param> /// <param name="fileName"></param> /// <param name="filePath"></param> /// <param name="sourceFile"></param> public static void SavePath(int fileNameCombinationType, string fileName, DateTime? reportDate, out string filePath, string sourceFile = "") { string destFileName = GetFile(fileNameCombinationType, fileName, reportDate); string pathTo = GetDirectoryNameLongPath(destFileName).Replace("\0", ""); if (!AlphaFS.Directory.Exists(pathTo)) { AlphaFS.Directory.CreateDirectory(pathTo); } if (!string.IsNullOrWhiteSpace(sourceFile) && !AlphaFS.File.Exists(destFileName)) { AlphaFS.File.Copy(sourceFile, destFileName, true); // File.Copy(sourceFile, destFileName, true); } filePath = destFileName; } /// <summary> /// 删除文件 /// </summary> /// <param name="fileNameCombinationType"></param> /// <param name="fileName"></param> public static void DeleteFile(int fileNameCombinationType, string fileName) { string destFileName = GetFile(fileNameCombinationType, fileName); if (AlphaFS.File.Exists(destFileName)) { AlphaFS.File.Delete(destFileName); } } /// <summary> /// 删除文件 /// </summary> /// <param name="fileNameCombinationType"></param> public static void DeleteFile(string fileName) { string destFileName = fileName; if (AlphaFS.File.Exists(destFileName)) { AlphaFS.File.Delete(destFileName); } } /// <summary> /// 获取文件 /// </summary> /// <param name="fileNameCombinationType"></param> /// <param name="fileName"></param> /// <returns></returns> public static string GetFile(int fileNameCombinationType, string alias) { var pathTo = ""; string path = AlphaFS.Path.GetDirectoryName(alias); string fileName = string.Empty; alias = AlphaFS.Path.GetFileNameWithoutExtension(alias); pathTo = AlphaFS.Path.Combine(AlphaFS.Path.Combine(ReportSavePath)); switch (fileNameCombinationType) { case 1: fileName = string.Format($"{alias}_{DateTime.Now:yyyyMMdd}"); pathTo = path != "" ? AlphaFS.Path.Combine(pathTo, path) : pathTo; break; case 2: fileName = string.Format($"{alias}_{DateTime.Now:yyyyMM}"); pathTo = path != "" ? AlphaFS.Path.Combine(pathTo, path) : pathTo; break; case 3: fileName = string.Format($"{alias}_{DateTime.Now.ToString("yyyy")}"); pathTo = path != "" ? AlphaFS.Path.Combine(pathTo, path) : pathTo; break; case 4: fileName = string.Format($"({DateTime.Now:yyyy}){DateTime.Now:MMdd}{alias}"); pathTo = path != "" ? AlphaFS.Path.Combine(pathTo, path) : pathTo; break; case 5: fileName = string.Format($"({DateTime.Now:yyyyMMdd})"); pathTo = path != "" ? AlphaFS.Path.Combine(pathTo, path) : pathTo; break; case 6: fileName = string.Format($"{alias}({DateTime.Now:yyyy}){DateTime.Now:MMdd}"); pathTo = path != "" ? AlphaFS.Path.Combine(pathTo, path) : pathTo; break; case 7: fileName = string.Format($"{alias}{DateTime.Now:yyyyMMdd}"); pathTo = path != "" ? AlphaFS.Path.Combine(pathTo, path) : pathTo; break; case 8: fileName = string.Format($"{DateTime.Now:yyyy年MM月}{alias}"); pathTo = path != "" ? AlphaFS.Path.Combine(pathTo, path) : pathTo; break; case 9: fileName = string.Format($"{alias}"); pathTo = path != "" ? AlphaFS.Path.Combine(pathTo, path) : pathTo; break; case 10: fileName = string.Format($"{DateTime.Now:yyyy年}{alias}"); pathTo = path != "" ? AlphaFS.Path.Combine(pathTo, path) : pathTo; break; } string destFileName = AlphaFS.Path.Combine(pathTo, fileName + ".xlsx"); return destFileName; } /// <summary> /// 获取文件 /// </summary> /// <param name="fileNameCombinationType"></param> /// <param name="fileName"></param> /// <returns></returns> public static string GetFile(int fileNameCombinationType, string alias, DateTime? reportDate) { var pathTo = ""; string path = AlphaFS.Path.GetDirectoryName(alias); string fileName = string.Empty; alias = AlphaFS.Path.GetFileNameWithoutExtension(alias); if (reportDate == null) { reportDate = DateTime.Now; } pathTo = AlphaFS.Path.Combine(AlphaFS.Path.Combine(ReportSavePath)); switch (fileNameCombinationType) { case 1: fileName = string.Format($"{alias}_{reportDate.Value:yyyyMMdd}"); pathTo = path != "" ? AlphaFS.Path.Combine(pathTo, path) : pathTo; break; case 2: fileName = string.Format($"{alias}_{reportDate.Value:yyyyMM}"); pathTo = path != "" ? AlphaFS.Path.Combine(pathTo, path) : pathTo; break; case 3: fileName = string.Format($"{alias}_{reportDate.Value.ToString("yyyy")}"); pathTo = path != "" ? AlphaFS.Path.Combine(pathTo, path) : pathTo; break; case 4: fileName = string.Format($"({reportDate.Value:yyyy}){reportDate.Value:MMdd}{alias}"); pathTo = path != "" ? AlphaFS.Path.Combine(pathTo, path) : pathTo; break; case 5: fileName = string.Format($"({reportDate.Value:yyyyMMdd})"); pathTo = path != "" ? AlphaFS.Path.Combine(pathTo, path) : pathTo; break; case 6: fileName = string.Format($"{alias}({reportDate.Value:yyyy}){reportDate.Value:MMdd}"); pathTo = path != "" ? AlphaFS.Path.Combine(pathTo, path) : pathTo; break; case 7: fileName = string.Format($"{alias}{reportDate.Value:yyyyMMdd}"); pathTo = path != "" ? AlphaFS.Path.Combine(pathTo, path) : pathTo; break; case 8: fileName = string.Format($"{reportDate.Value:yyyy年MM月}{alias}"); pathTo = path != "" ? AlphaFS.Path.Combine(pathTo, path) : pathTo; break; case 9: fileName = string.Format($"{alias}"); pathTo = path != "" ? AlphaFS.Path.Combine(pathTo, path) : pathTo; break; case 10: fileName = string.Format($"{reportDate.Value:yyyy年}{alias}"); pathTo = path != "" ? AlphaFS.Path.Combine(pathTo, path) : pathTo; break; } string destFileName = AlphaFS.Path.Combine(pathTo, fileName + ".xlsx"); return destFileName; } /// <summary> /// 判断是否有占位符 /// </summary> /// <param name="value"></param> /// <returns></returns> public static bool IsSegnaposto(string value) { return new Regex("\\{.+?\\}|\\[(.*)\\]").IsMatch(value); } /// <summary> /// 生成Excel列字母 /// </summary> /// <param name="intCol"></param> /// <returns></returns> public static string ExcelIndexToLetter(int intCol) { int intFirstLetter = ((intCol) / 676) + 64; int intSecondLetter = ((intCol % 676) / 26) + 64; int intThirdLetter = (intCol % 26) + 65; char FirstLetter = (intFirstLetter > 64) ? (char)intFirstLetter : ' '; char SecondLetter = (intSecondLetter > 64) ? (char)intSecondLetter : ' '; char ThirdLetter = (char)intThirdLetter; return string.Concat(FirstLetter, SecondLetter, ThirdLetter).Trim(); } /// <summary> /// 列转数字下标 /// </summary> /// <param name="col"></param> /// <returns></returns> public static int ExcelToNum(string col) { if (col == null) return -1; char[] chrs = col.ToUpper().ToCharArray(); // 转为大写字母组成的 char数组 int length = chrs.Length; double index = -1; for (int i = 0; i < length; i++) { index += (chrs[i] - 'A' + 1) * Math.Pow(26, length - i - 1); // 当做26进制来算 AAA=111 26^2+26^1+26^0 } return Convert.ToInt32(index); // 从0开始的下标 } /// <summary> /// 获取npoi支持的颜色 /// </summary> /// <param name="hex"></param> /// <returns></returns> public static short HexToShort(string hex) { HSSFWorkbook book = null; try { Color color = ColorTranslator.FromHtml(hex); book = new HSSFWorkbook(); //调色板实例 HSSFPalette palette = book.GetCustomPalette(); return palette.FindColor(color.R, color.G, color.B).Indexed; } finally { book?.Close(); book?.Clear(); } } /// <summary> /// 获取超长路径的目录名 /// </summary> /// <param name="filePath"></param> /// <returns></returns> static string GetDirectoryNameLongPath(string filePath) { const string longPathPrefix = @"\\?\"; if (filePath.Length <= 260) return AlphaFS.Path.GetDirectoryName(filePath); string directory = filePath.Substring(0, filePath.LastIndexOf('\\')); return directory; } /// <summary> /// 判断是否是数字 /// </summary> /// <param name="value"></param> /// <returns></returns> static bool IsNumeric(string value) { return Regex.IsMatch(value, @"^[+-]?\d*[.]?\d*$"); } /// <summary> /// 复制列和样式 /// </summary> /// <param name="templateSheet">模版 </param> /// <param name="targetSheet">目标</param> /// <param name="sourceColumnIndexes">复制那些列(下标)</param> /// <param name="startColumnIndex">从那行开始插入复制的列</param> /// <param name="copyRowIndex">从模版中的那一行开始复制</param> static int CopyColumnsAndStyles(ISheet templateSheet, ISheet targetSheet, int[] sourceColumnIndexes, int startColumnIndex, ExcelParam excelParam, int copyRowIndex = 0) { int endColumnIndex = 0; for (int rowIndex = copyRowIndex; rowIndex <= templateSheet.LastRowNum; rowIndex++) { IRow templateRow = templateSheet.GetRow(rowIndex); if (templateRow == null) continue; IRow newRow = targetSheet.GetRow(rowIndex) ?? targetSheet.CreateRow(rowIndex); int columnIndex = 0; int nextMerge = 0; ICell sourceDefaultvalue = templateRow.GetCell(0); //获取第一列的默认值,规则规定合并单元格值放在第一个元素 foreach (int sourceColumnIndex in sourceColumnIndexes) { ICell sourceCell = templateRow.GetCell(sourceColumnIndex); if (sourceCell == null) continue; int targetColumnIndex = startColumnIndex + columnIndex; ICell targetCell = newRow.GetCell(targetColumnIndex) ?? newRow.CreateCell(targetColumnIndex); if (targetCell == null) continue; string sourceValue = sourceCell.StringCellValue; if (columnIndex == 0 && !string.IsNullOrWhiteSpace(sourceDefaultvalue.StringCellValue) && string.IsNullOrWhiteSpace(sourceValue)) { sourceValue = sourceDefaultvalue.StringCellValue; } string key = Regex.Match(sourceValue, @"\{(.*)\}", RegexOptions.Singleline).Groups[1].Value; targetCell.SetCellValue(!string.IsNullOrWhiteSpace(key) ? excelParam.Data.ContainsKey(key) ? excelParam.Data[key] : "" : sourceValue); targetCell.CellStyle = sourceCell.CellStyle; if (sourceCell.IsMergedCell && targetCell.IsMergedCell) { columnIndex++; continue; } int currentRow = sourceCell.RowIndex; int currentColumn = sourceCell.ColumnIndex; CellRangeAddress mergedRegion = null; for (int i = 0; i < templateSheet.NumMergedRegions; i++) { CellRangeAddress region = templateSheet.GetMergedRegion(i); if (!region.IsInRange(currentRow, currentColumn)) continue; mergedRegion = region; break; } if (mergedRegion == null) { columnIndex++; continue; } int numRows = mergedRegion.LastRow - mergedRegion.FirstRow + 1; int numColumns = mergedRegion.LastColumn - mergedRegion.FirstColumn + 1; if (nextMerge == 0 && (targetCell.ColumnIndex + 1) == (startColumnIndex + sourceColumnIndexes.Length)) { nextMerge = targetCell.ColumnIndex + 1; CellRangeAddress mergeRange = new CellRangeAddress(targetCell.RowIndex, targetCell.RowIndex - 1 + numRows, startColumnIndex, targetCell.ColumnIndex); targetSheet.AddMergedRegion(mergeRange); } else if (nextMerge == 0 && (targetCell.ColumnIndex + 1) == startColumnIndex + numColumns) { nextMerge = targetCell.ColumnIndex + 1; CellRangeAddress mergeRange = new CellRangeAddress(targetCell.RowIndex, targetCell.RowIndex - 1 + numRows, startColumnIndex, targetCell.ColumnIndex); targetSheet.AddMergedRegion(mergeRange); } else if (nextMerge > 0 && (targetCell.ColumnIndex + 1) == (nextMerge + numColumns)) { CellRangeAddress mergeRange = new CellRangeAddress(targetCell.RowIndex, targetCell.RowIndex - 1 + numRows, nextMerge, targetCell.ColumnIndex); targetSheet.AddMergedRegion(mergeRange); nextMerge += numColumns; } columnIndex++; } endColumnIndex = columnIndex + startColumnIndex; } return endColumnIndex; } /// <summary> /// 将模版行列及样式复制到目标行列 /// </summary> /// <param name="templateSheet">模版</param> /// <param name="targetSheet">目标</param> /// <param name="sourceRowIndexes">复制哪些行(下标) int 数组</param> /// <param name="sourceColumnIndexes">复制哪些列(下标) int 数组</param> /// <param name="startRowIndex">在目标那行开始插入 默认值:0</param> /// <param name="startColumnIndex">在目标那列开始插入 默认值:0</param> /// <param name="copyStartRowIndex">从那行开始复制(下标)默认值:0</param> /// <param name="copyStartColumnIndex">从那列开始复制(下标)默认值:0</param> /// <param name="intervalRow">用来隔行 默认值:0</param> /// <param name="intervalColumn">用来隔列 默认值:0</param> /// <param name="excelParam">ExcelParam 对象</param> /// <returns>endColumnIndex,endRowIndex</returns> static Tuple<int, int> CopyTemplateRowsColumnsAndStyles(ISheet templateSheet, ISheet targetSheet, int[] sourceRowIndexes, int[] sourceColumnIndexes, int startRowIndex = 0, int startColumnIndex = 0, int copyStartRowIndex = 0, int copyStartColumnIndex = 0, int intervalRow = 0, int intervalColumn = 0, ExcelParam excelParam = null, List<string> filter = null) { int endColumnIndex = 0; int endRowIndex = 0; if (sourceRowIndexes.Length <= 0 && sourceColumnIndexes.Length <= 0) { for (int rowIndex = copyStartRowIndex; rowIndex <= templateSheet.LastRowNum; rowIndex++) { IRow templateRow = templateSheet.GetRow(rowIndex); if (templateRow == null) continue; IRow newRow = targetSheet.GetRow(rowIndex + startRowIndex) ?? targetSheet.CreateRow(rowIndex + startRowIndex); int index = 0; int nextMerge = 0; string sourceDefaultvalue = templateRow.GetCell(0)?.StringCellValue; //获取第一列的默认值,规则规定合并单元格值放在第一个元素 for (int i = copyStartColumnIndex; i < templateRow.LastCellNum; i++) { Tuple<ICell, ICell> itemTuple = ProcessTemplateRow(templateRow, newRow, startColumnIndex, sourceDefaultvalue, i, index, excelParam, filter: filter); ICell sourceCell = itemTuple.Item1; ICell targetCell = itemTuple.Item2; if (sourceCell == null) { continue; } if (sourceCell.IsMergedCell && targetCell.IsMergedCell) { index++; continue; } int currentRow = sourceCell.RowIndex; int currentColumn = sourceCell.ColumnIndex; CellRangeAddress mergedRegion = null; for (int j = 0; j < templateSheet.NumMergedRegions; j++) { CellRangeAddress region = templateSheet.GetMergedRegion(j); if (!region.IsInRange(currentRow, currentColumn)) continue; mergedRegion = region; break; } if (mergedRegion == null) { index++; continue; } //获取合并单元格的行数和列数 int numRows = mergedRegion.LastRow - mergedRegion.FirstRow + 1; int numColumns = mergedRegion.LastColumn - mergedRegion.FirstColumn + 1; //判断合并的单元格(nextMerge)是否为0 ,如果为0则表示是第一次合并,否则表示已经合并过了 if (nextMerge == 0 && (targetCell.ColumnIndex + 1) == (startColumnIndex + sourceColumnIndexes.Length)) { nextMerge = targetCell.ColumnIndex + 1; CellRangeAddress mergeRange = new CellRangeAddress(targetCell.RowIndex, targetCell.RowIndex - 1 + numRows, startColumnIndex, targetCell.ColumnIndex); targetSheet.AddMergedRegion(mergeRange); } else if (nextMerge == 0 && (targetCell.ColumnIndex + 1) == startColumnIndex + numColumns) { nextMerge = targetCell.ColumnIndex + 1; CellRangeAddress mergeRange = new CellRangeAddress(targetCell.RowIndex, targetCell.RowIndex - 1 + numRows, startColumnIndex, targetCell.ColumnIndex); targetSheet.AddMergedRegion(mergeRange); } else if (nextMerge > 0 && (targetCell.ColumnIndex + 1) == (nextMerge + numColumns)) { CellRangeAddress mergeRange = new CellRangeAddress(targetCell.RowIndex, targetCell.RowIndex - 1 + numRows, nextMerge, targetCell.ColumnIndex); targetSheet.AddMergedRegion(mergeRange); nextMerge += numColumns; } index++; } endColumnIndex = index + startColumnIndex; endRowIndex = targetSheet.LastRowNum; } } else if (sourceRowIndexes.Length <= 0 && sourceColumnIndexes.Length > 0) { int _rowIndex = 0; for (int rowIndex = copyStartRowIndex; rowIndex <= templateSheet.LastRowNum; rowIndex++) { IRow templateRow = templateSheet.GetRow(rowIndex); if (templateRow == null) continue; IRow newRow = targetSheet.GetRow(startRowIndex + _rowIndex) ?? targetSheet.CreateRow(startRowIndex + _rowIndex); _rowIndex++; int index = 0; int nextMerge = 0; string sourceDefaultvalue = templateRow.GetCell(0)?.StringCellValue; //获取第一列的默认值,规则规定合并单元格值放在第一个元素 foreach (int columnIndex in sourceColumnIndexes) { Tuple<ICell, ICell> itemTuple = ProcessTemplateRow(templateRow, newRow, startColumnIndex, sourceDefaultvalue, columnIndex, index, excelParam, filter: filter); ICell sourceCell = itemTuple.Item1; ICell targetCell = itemTuple.Item2; if (sourceCell == null) { continue; } if (sourceCell.IsMergedCell && targetCell.IsMergedCell) { index++; continue; } int currentRow = sourceCell.RowIndex; int currentColumn = sourceCell.ColumnIndex; CellRangeAddress mergedRegion = null; for (int i = 0; i < templateSheet.NumMergedRegions; i++) { CellRangeAddress region = templateSheet.GetMergedRegion(i); if (!region.IsInRange(currentRow, currentColumn)) continue; mergedRegion = region; break; } if (mergedRegion == null) { index++; continue; } //获取合并单元格的行数和列数 int numRows = mergedRegion.LastRow - mergedRegion.FirstRow + 1; int numColumns = mergedRegion.LastColumn - mergedRegion.FirstColumn + 1; //判断合并的单元格(nextMerge)是否为0 ,如果为0则表示是第一次合并,否则表示已经合并过了 if (nextMerge == 0 && (targetCell.ColumnIndex + 1) == (startColumnIndex + sourceColumnIndexes.Length)) { nextMerge = targetCell.ColumnIndex + 1; CellRangeAddress mergeRange = new CellRangeAddress(targetCell.RowIndex, targetCell.RowIndex - 1 + numRows, startColumnIndex, targetCell.ColumnIndex); targetSheet.AddMergedRegion(mergeRange); } else if (nextMerge == 0 && (targetCell.ColumnIndex + 1) == startColumnIndex + numColumns) { nextMerge = targetCell.ColumnIndex + 1; CellRangeAddress mergeRange = new CellRangeAddress(targetCell.RowIndex, targetCell.RowIndex - 1 + numRows, startColumnIndex, targetCell.ColumnIndex); targetSheet.AddMergedRegion(mergeRange); } else if (nextMerge > 0 && (targetCell.ColumnIndex + 1) == (nextMerge + numColumns)) { CellRangeAddress mergeRange = new CellRangeAddress(targetCell.RowIndex, targetCell.RowIndex - 1 + numRows, nextMerge, targetCell.ColumnIndex); targetSheet.AddMergedRegion(mergeRange); nextMerge += numColumns; } index++; } endColumnIndex = index + startColumnIndex; endRowIndex = targetSheet.LastRowNum; } } else if (sourceRowIndexes.Length > 0 && sourceColumnIndexes.Length <= 0) { int _rowIndex = 0; foreach (int rowIndex in sourceRowIndexes) { IRow templateRow = templateSheet.GetRow(rowIndex); if (templateRow == null) continue; IRow newRow = targetSheet.GetRow(_rowIndex + startRowIndex) ?? targetSheet.CreateRow(_rowIndex + startRowIndex); _rowIndex++; int index = 0; int nextMerge = 0; string sourceDefaultvalue = templateRow.GetCell(0)?.StringCellValue; //获取第一列的默认值,规则规定合并单元格值放在第一个元素 for (int i = copyStartColumnIndex; i < templateRow.LastCellNum; i++) { Tuple<ICell, ICell> itemTuple = ProcessTemplateRow(templateRow, newRow, startColumnIndex, sourceDefaultvalue, i, index, excelParam, filter: filter); ICell sourceCell = itemTuple.Item1; ICell targetCell = itemTuple.Item2; if (sourceCell == null) { continue; } if (sourceCell.IsMergedCell && targetCell.IsMergedCell) { index++; continue; } int currentRow = sourceCell.RowIndex; int currentColumn = sourceCell.ColumnIndex; CellRangeAddress mergedRegion = null; for (int j = 0; j < templateSheet.NumMergedRegions; j++) { CellRangeAddress region = templateSheet.GetMergedRegion(j); if (!region.IsInRange(currentRow, currentColumn)) continue; mergedRegion = region; break; } if (mergedRegion == null) { index++; continue; } //获取合并单元格的行数和列数 int numRows = mergedRegion.LastRow - mergedRegion.FirstRow + 1; int numColumns = mergedRegion.LastColumn - mergedRegion.FirstColumn + 1; //判断合并的单元格(nextMerge)是否为0 ,如果为0则表示是第一次合并,否则表示已经合并过了 if (nextMerge == 0 && (targetCell.ColumnIndex + 1) == (startColumnIndex + sourceColumnIndexes.Length)) { nextMerge = targetCell.ColumnIndex + 1; CellRangeAddress mergeRange = new CellRangeAddress(targetCell.RowIndex, targetCell.RowIndex - 1 + numRows, startColumnIndex, targetCell.ColumnIndex); targetSheet.AddMergedRegion(mergeRange); } else if (nextMerge == 0 && (targetCell.ColumnIndex + 1) == startColumnIndex + numColumns) { nextMerge = targetCell.ColumnIndex + 1; CellRangeAddress mergeRange = new CellRangeAddress(targetCell.RowIndex, targetCell.RowIndex - 1 + numRows, startColumnIndex, targetCell.ColumnIndex); targetSheet.AddMergedRegion(mergeRange); } else if (nextMerge > 0 && (targetCell.ColumnIndex + 1) == (nextMerge + numColumns)) { CellRangeAddress mergeRange = new CellRangeAddress(targetCell.RowIndex, targetCell.RowIndex - 1 + numRows, nextMerge, targetCell.ColumnIndex); targetSheet.AddMergedRegion(mergeRange); nextMerge += numColumns; } index++; } endColumnIndex = index + startColumnIndex; endRowIndex = targetSheet.LastRowNum; } } else { int _rowIndex = 0; foreach (int rowIndex in sourceRowIndexes) { IRow templateRow = templateSheet.GetRow(rowIndex); if (templateRow == null) continue; IRow newRow = targetSheet.GetRow(_rowIndex + startRowIndex) ?? targetSheet.CreateRow(_rowIndex + startRowIndex); _rowIndex++; int index = 0; int nextMerge = 0; string sourceDefaultvalue = templateRow.GetCell(0)?.StringCellValue; //获取第一列的默认值,规则规定合并单元格值放在第一个元素 foreach (int columnIndex in sourceColumnIndexes) { Tuple<ICell, ICell> itemTuple = ProcessTemplateRow(templateRow, newRow, startColumnIndex, sourceDefaultvalue, columnIndex, index, excelParam, filter); ICell sourceCell = itemTuple.Item1; ICell targetCell = itemTuple.Item2; if (sourceCell == null) { continue; } int currentRow = sourceCell.RowIndex; int currentColumn = sourceCell.ColumnIndex; CellRangeAddress mergedRegion = null; for (int j = 0; j < templateSheet.NumMergedRegions; j++) { CellRangeAddress region = templateSheet.GetMergedRegion(j); if (!region.IsInRange(currentRow, currentColumn)) continue; mergedRegion = region; break; } if (mergedRegion == null) { index++; continue; } // 获取合并单元格的行数和列数 int numRows = mergedRegion.LastRow - mergedRegion.FirstRow + 1; int numColumns = mergedRegion.LastColumn - mergedRegion.FirstColumn + 1; // 判断合并的单元格(nextMerge)是否为0, 如果为0则表示是第一次合并,否则表示已经合并过了 if (nextMerge == 0 && (targetCell.ColumnIndex + 1) == (startColumnIndex + sourceColumnIndexes.Length)) { nextMerge = targetCell.ColumnIndex + 1; CellRangeAddress mergeRange = new CellRangeAddress(targetCell.RowIndex, targetCell.RowIndex - 1 + numRows, startColumnIndex, targetCell.ColumnIndex); // Check if the merge range overlaps with any existing merged regions bool overlaps = targetSheet.MergedRegions.Any(existingMergeRange => mergeRange.Intersects(existingMergeRange)); if (!overlaps) { targetSheet.AddMergedRegion(mergeRange); } } else if (nextMerge == 0 && (targetCell.ColumnIndex + 1) == startColumnIndex + numColumns) { nextMerge = targetCell.ColumnIndex + 1; CellRangeAddress mergeRange = new CellRangeAddress(targetCell.RowIndex, targetCell.RowIndex - 1 + numRows, startColumnIndex, targetCell.ColumnIndex); // Check if the merge range overlaps with any existing merged regions bool overlaps = targetSheet.MergedRegions.Any(existingMergeRange => mergeRange.Intersects(existingMergeRange)); if (!overlaps) { targetSheet.AddMergedRegion(mergeRange); } } else if (nextMerge > 0 && (targetCell.ColumnIndex + 1) == (nextMerge + numColumns)) { CellRangeAddress mergeRange = new CellRangeAddress(targetCell.RowIndex, targetCell.RowIndex - 1 + numRows, nextMerge, targetCell.ColumnIndex); // Check if the merge range overlaps with any existing merged regions bool overlaps = targetSheet.MergedRegions.Any(existingMergeRange => mergeRange.Intersects(existingMergeRange)); if (!overlaps) { targetSheet.AddMergedRegion(mergeRange); nextMerge += numColumns; } } index++; } endColumnIndex = index + startColumnIndex; endRowIndex = targetSheet.LastRowNum; } } return new Tuple<int, int>(endColumnIndex + intervalColumn, endRowIndex + intervalRow); } /// <summary> /// /// </summary> /// <param name="templateRow"></param> /// <param name="newRow"></param> /// <param name="startColumnIndex"></param> /// <param name="sourceDefaultvalue"></param> /// <param name="columnIndex"></param> /// <param name="index"></param> /// <param name="excelParam"></param> /// <returns>sourceCell, targetCell</returns> private static Tuple<ICell, ICell> ProcessTemplateRow(IRow templateRow, IRow newRow, int startColumnIndex, string sourceDefaultvalue, int columnIndex, int index, ExcelParam excelParam = null, List<string> filter = null) { ICell sourceCell = templateRow.GetCell(columnIndex); if (sourceCell == null) return new Tuple<ICell, ICell>(null, null); int targetColumnIndex = startColumnIndex + index; ICell targetCell = newRow.GetCell(targetColumnIndex) ?? newRow.CreateCell(targetColumnIndex); string sourceValue = sourceCell.StringCellValue; if (index == 0 && !string.IsNullOrWhiteSpace(sourceDefaultvalue) && string.IsNullOrWhiteSpace(sourceValue)) { sourceValue = sourceDefaultvalue; } string key = Regex.Match(sourceValue, @"\{(.*)\}", RegexOptions.Singleline).Groups[1].Value; string key1 = Regex.Match(sourceValue, @"\{{(.*)\}}", RegexOptions.Singleline).Groups[1].Value; string value = (excelParam?.Data != null && excelParam.Data.ContainsKey(key) ? excelParam.Data[key] : ""); Func<string> setTargetCellValue = () => { filter?.Add(key1); // 设置 targetCell 的值 return key; }; targetCell.SetCellValue( !string.IsNullOrWhiteSpace(key) && !string.IsNullOrWhiteSpace(key1) ? (filter?.Count > 0 && filter.Contains(key1)) ? "" // 设置 targetCell 的值为空字符串 : setTargetCellValue() : (!string.IsNullOrWhiteSpace(key) ? value // 设置 targetCell 的值为 value : sourceValue // 设置 targetCell 的值为 sourceValue ) ); targetCell.CellStyle = sourceCell.CellStyle; if (key.StartsWith("formula", StringComparison.OrdinalIgnoreCase)) { targetCell.CellFormula = value.Replace("=", ""); } return new Tuple<ICell, ICell>(sourceCell, targetCell); } /// <summary> /// 获取复制列的下标 /// </summary> /// <param name="months">月份数组</param> /// <param name="sourceColumnIndexes">目标列</param> /// <param name="subarraySize">将目标拆分二维数组</param> /// <returns></returns> public static int[] GetCopyColumnIndexes(int[] months, int[] sourceColumnIndexes, int subarraySize = 2) { int[][] result = new int[sourceColumnIndexes.Length / subarraySize][]; int index = 0; for (int i = 0; i < sourceColumnIndexes.Length; i += subarraySize) { result[index] = new int[subarraySize]; Array.Copy(sourceColumnIndexes, i, result[index], 0, subarraySize); index++; } List<int> list = new List<int>(); foreach (var currentMonth in months) { int[] columnIndexes = result[currentMonth - 1]; list.AddRange(columnIndexes); } return list.ToArray(); } #endregion #region 通过 MFT 检索硬盘中的文件 [StructLayout(LayoutKind.Sequential, CharSet = CharSet.Unicode)] struct FILE_DIRECTORY_INFO { public long FileIndex; public long CreationTime; public long LastAccessTime; public long LastWriteTime; public long ChangeTime; public long EndOfFile; public long AllocationSize; public uint FileAttributes; public uint FileNameLength; [MarshalAs(UnmanagedType.ByValTStr, SizeConst = 256)] public string FileName; } class NativeMethods { const string KERNEL32 = "kernel32.dll"; [DllImport(KERNEL32, CharSet = CharSet.Unicode)] public static extern IntPtr FindFirstFileEx( string lpFileName, int fInfoLevelId, out FILE_DIRECTORY_INFO lpFindFileData, int fSearchOp, IntPtr lpSearchFilter, int dwAdditionalFlags); [DllImport(KERNEL32, CharSet = CharSet.Unicode)] public static extern bool FindNextFile( IntPtr hFindFile, out FILE_DIRECTORY_INFO lpFindFileData); [DllImport(KERNEL32)] public static extern bool FindClose(IntPtr hFindFile); } static void RecursiveSearch(string path) { FILE_DIRECTORY_INFO fileInfo; IntPtr hFindFile = NativeMethods.FindFirstFileEx( path + "*", 1, out fileInfo, 1, IntPtr.Zero, 0); if (hFindFile == IntPtr.Zero) return; do { if (fileInfo.FileName == "." || fileInfo.FileName == "..") continue; Console.WriteLine($"文件名: {fileInfo.FileName}"); Console.WriteLine($"文件大小: {fileInfo.EndOfFile} bytes"); Console.WriteLine(); if ((fileInfo.FileAttributes & 0x10) == 0) continue; // 目录 string subDirPath = path + fileInfo.FileName + "\\"; RecursiveSearch(subDirPath); } while (NativeMethods.FindNextFile(hFindFile, out fileInfo)); NativeMethods.FindClose(hFindFile); } #endregion }
模版:
效果
发表评论