.net 通过npoi和excel模版标识实现复杂的报表导出

    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
    }


模版:

image.png

image.png


效果

image.png


image.png

本文作者:admin

本文链接:https://www.javalc.com/post/115.html

版权声明:本篇文章于2024-02-24,由admin发表,转载请注明出处:分享你我。如有疑问,请联系我们

C# 使用NPOI手搓excel转html
56 条评论
  • 游客  2024-07-07  回复
  • 收藏了,楼主加油!http://3lnza.06gk.cn
  • 游客  2024-07-07  回复
  • 楼主的帖子越来越有深度了!http://rovbar.0233l1b.cn
  • 游客  2024-07-07  回复
  • 投楼主一票,不用谢哦!http://7lrfy.bnsrqrz.cn
  • 游客  2024-07-07  回复
  • 雷锋做好事不留名,都写在帖子里!http://r88p.830x.cn
  • 游客  2024-07-07  回复
  • 今天过得很不爽!http://0tgizr.tpops.com.cn
  • 游客  2024-07-07  回复
  • 楼主很有经验啊!http://vxzsxa.xmona.cn
  • 游客  2024-07-07  回复
  • 收藏了,楼主加油!http://bxd0.zhijian.me
  • 游客  2024-07-08  回复
  • 楼主今年多大了?http://jkk.zhijian.me
  • 游客  2024-07-08  回复
  • 经典!http://n5bl.tiantianzhoupeng.com
  • 游客  2024-07-08  回复
  • 顶顶更健康!http://n1nq.xunmiai.com
  • 游客  2024-07-08  回复
  • 灌水不是我的目的!http://wqo.china-beili.com
  • 游客  2024-07-08  回复
  • 我裤子脱了,纸都准备好了,你就给我看这个?http://1fv.china-beili.com
  • 游客  2024-07-08  回复
  • 有内涵!http://o49tz9.xunmiai.com
  • 游客  2024-07-08  回复
  • 这么好的帖子,应该加精华!http://96i1.xunmiai.com
  • 游客  2024-07-08  回复
  • 我裤子脱了,纸都准备好了,你就给我看这个?http://3ei0.china-beili.com
  • 游客  2024-07-08  回复
  • 我裤子脱了,纸都准备好了,你就给我看这个?http://tfe.chaoyaoji.com
  • 游客  2024-07-08  回复
  • 突然觉得楼主说的很有道理,赞一个!http://90vzj.china-beili.com
  • 游客  2024-07-08  回复
  • 楼上的能详细介绍一下么?http://5mm.china-beili.com
  • 游客  2024-07-09  回复
  • 被楼主的逻辑打败了!https://www.ejy365.com/jygg_more
  • 游客  2024-07-09  回复
  • 楼上的心情不错啊!http://azxl2.sd-xbl.com/07/4.html
  • 游客  2024-07-09  回复
  • 听楼主一席话,省我十本书!http://qamo.adsofts.cn
  • 游客  2024-07-09  回复
  • 支持一个http://peczbd.mrs-jio.com
  • 游客  2024-07-09  回复
  • 楼上的心情不错啊!http://syq.adsofts.cn
  • 游客  2024-07-09  回复
  • 楼主发几张靓照啊!http://5hpuxt.whxf-pack.com
  • 游客  2024-07-09  回复
  • 楼主你想太多了!http://oe2.mrs-jio.com
  • 游客  2024-07-09  回复
  • 支持一个http://4ebd.mrs-jio.com
  • 游客  2024-07-09  回复
  • 这里的资源非常丰富,帮助我解决了很多问题。http://12bh9.healthreportz.com/20240709/5.html
  • 游客  2024-07-09  回复
  • 赞一个!http://6swnv.healthreportz.com/2/4.html
  • 游客  2024-07-10  回复
  • 投楼主一票,不用谢哦!http://clhwgb.mrs-jio.com
  • 游客  2024-07-10  回复
  • 禽兽不如应该不是说楼主的的吧?http://vh5v.mrs-jio.com
  • 游客  2024-07-10  回复
  • 看了这么多帖子,第一次看看到这么有内涵的!http://kckbd0.gz-chuangli.com
  • 游客  2024-07-10  回复
  • 网页的加载速度非常快,不会影响用户体验。http://xgb.zypottery.com
  • 游客  2024-07-10  回复
  • 看帖不回帖的人就是耍流氓,我回复了!http://h5co.adsofts.cn
  • 游客  2024-07-10  回复
  • 楼主是好人!http://ewist.whxf-pack.com
  • 游客  2024-07-10  回复
  • 以后要跟楼主好好学习学习!http://n3y967.mrs-jio.com
  • 游客  2024-07-10  回复
  • 我对楼主的敬仰犹如滔滔江水绵延不绝!http://flwl.zypottery.com
  • 游客  2024-07-10  回复
  • 经典,收藏了!http://p06u0.adsofts.cn
  • 游客  2024-07-10  回复
  • 论坛的人气越来越旺了!http://04y.shxclp.com
  • 游客  2024-07-10  回复
  • 顶顶更健康!http://rkq.adsofts.cn
  • 游客  2024-07-10  回复
  • 楼主的文笔不错!http://w9fj.zypottery.com
  • 游客  2024-07-10  回复
  • 在哪里跌倒,就在那里多爬一会儿!http://laf.0572lmj.com
  • 游客  2024-07-10  回复
  • 对牛弹琴的人越来越多了!https://www.ejy365.com/info/ejy88660
  • 游客  2024-07-10  回复
  • 收藏了,以后可能会用到!http://muebles-rusticos.com/html/89d099793.html
  • 游客  2024-07-10  回复
  • 文章写太挺好了,真的值得推荐http://hu3.b-autorental.com.cn
  • 游客  2024-07-10  回复
  • 投楼主一票,不用谢哦!http://qfv2.sz-xld.com
  • 游客  2024-07-10  回复
  • 楼主是好人!http://lw86ya.jindetai.cn
  • 游客  2024-07-11  回复
  • 楼主该去看心理医生了!http://2qdiz.29ppt.com/2024/3.html
  • 游客  2024-07-11  回复
  • 楼主是男的还是女的?http://g022f.shxclp.com
  • 游客  2024-07-11  回复
  • 感觉不错!http://22sa.eklaser.cn
  • 游客  2024-07-11  回复
  • 看帖回帖一条路!http://dpj1o.b-autorental.com.cn
  • 游客  2024-07-11  回复
  • 楼主是一个典型的文艺青年啊!http://2ko25.eklaser.cn
  • 游客  2024-07-11  回复
  • 楼主的文笔不错!http://74ee.0572lmj.com
  • 游客  2024-07-11  回复
  • 楼主加油,看好你哦!http://k4vkt.gz-chuangli.com
  • 游客  2024-07-11  回复
  • 东方不败外加灭绝师太啊!http://s2t9o.tjchaoke.com
  • 游客  2024-07-11  回复
  • 帖子好乱!http://vm0j3l.0572lmj.com
  • 游客  2024-07-11  回复
  • 有机会找楼主好好聊聊!http://pa32.eklaser.cn

发表评论

取消
扫码支持