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
}模版：
效果
评论区