Excel输出Helper

C# excel输出帮助类，根据excel空白模板，导入数据到模板再下载

using CoreWebAPI.Common.Helper;
using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.POIFS.Crypt.Dsig;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.Streaming;
using NPOI.XSSF.UserModel;
using SixLabors.ImageSharp.Formats.Png;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace ACH_DAMCapacity.Services.Helper
{
    public class SpecialExcelHelper
    {
        private readonly string fileName = null;// 文件名
        private static IWorkbook workbook = null/* TODO Change to default(_) if this is not a reference type */;
        private static FileStream fs = null;
        private bool disposed;
        private static readonly string ExcelDateFormat = Appsettings.App("ExcelFromat", "ExcelDateFormat").GetCString();
        private static readonly string ExcelIntFormat = Appsettings.App("ExcelFromat", "ExcelIntFormat").GetCString();
        private static readonly string ExcelDecFormat = Appsettings.App("ExcelFromat", "ExcelDecFormat").GetCString();
        private static readonly string ExcelBoolTrueFormat = Appsettings.App("ExcelFromat", "ExcelBoolTrueFormat").GetCString();
        private static readonly string ExcelBoolFalseFormat = Appsettings.App("ExcelFromat", "ExcelBoolFalseFormat").GetCString();
        // 月份列名正则（匹配YYYYMM格式202508）
        private static readonly Regex MonthColumnRegex = new Regex(@"^\d{6}$", RegexOptions.Compiled);
        // 固定颜色映射（目标颜色 → NPOI内置相近颜色）
        private static readonly Dictionary<string, IndexedColors> FixedColors = new Dictionary<string, IndexedColors>(StringComparer.OrdinalIgnoreCase)
        {
            {"#5bbd62", IndexedColors.Green },   // 接近的绿色
            {"#fde74f", IndexedColors.Yellow }, // 接近的黄色
            {"#ff4d4d", IndexedColors.Red }     // 接近的红色
        };
        public SpecialExcelHelper(string fileName ="")
        {
            if (fileName.GetIsNotEmptyOrNull())
            {
                this.fileName = fileName;
            }
            disposed = false;
        }

        /// <summary>
        /// 获取多个sheet 表数据
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="sheetNames"></param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
        /// <param name="errMsg"></param>
        /// <returns></returns>
        public static DataSet ExcelToDataSet(string filePath, List<string> sheetNames, bool isFirstRowColumn, out string errMsg)
        {
            errMsg = string.Empty;
            DataSet dataSet = new DataSet();
            try
            {
                fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
                if (filePath.IndexOf(".xlsx") > 0)
                    // 2007版本
                    workbook = new XSSFWorkbook(fs);
                else if (filePath.IndexOf(".xls") > 0)
                    // 2003版本
                    workbook = new HSSFWorkbook(fs);

                foreach (var sheetName in sheetNames)
                {
                    ISheet sheet = workbook.GetSheet(sheetName);
                    if (sheet == null)
                    {
                        errMsg = "excel中不存在该sheet页 " + sheetName;
                        return null;
                    }
                    DataTable data = new DataTable(sheetName);
                    IRow firstRow = sheet.GetRow(0);
                    int cellCount = firstRow.LastCellNum;
                    int startRow;
                    // 一行最后一个cell的编号 即总的列数
                    if (isFirstRowColumn)
                    {
                        for (int i = firstRow.FirstCellNum; i <= cellCount - 1; i++)
                        {
                            ICell cell = firstRow.GetCell(i);
                            if (cell != null)
                            {
                                string cellValue = cell.StringCellValue;
                                if (cellValue != null)
                                {
                                    DataColumn column = new DataColumn(cellValue);
                                    data.Columns.Add(column);
                                }
                            }
                        }
                        startRow = sheet.FirstRowNum + 1;
                    }
                    else
                        startRow = sheet.FirstRowNum;

                    // 最后一列的标号
                    int rowCount = sheet.LastRowNum;
                    for (int i = startRow; i <= rowCount; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null)
                            continue;
                        // 没有数据的行默认是null　　　　　　　
                        List<object> itemArray = new List<object>();

                        for (int j = 0; j <= cellCount - 1; j++)
                        {
                            itemArray.Add(GetCellValue(row.GetCell(j)));
                        }
                        data.Rows.Add(itemArray.ToArray());
                    }
                    dataSet.Tables.Add(data);
                }
            }
            catch (Exception ex)
            {
                errMsg = ex.Message;
            }
            return dataSet;
        }

        /// <summary>
        /// 生成excel
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="sheetNames"></param>
        /// <param name="tableHeaders"></param>
        /// <returns></returns>
        public static bool DataSetToExcel(string filePath, List<string> sheetNames, Dictionary<string, object> tableHeaders, out string errMsg)
        {
            try
            {
                errMsg = string.Empty;
                fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                if (filePath.IndexOf(".xlsx") > 0)
                    // 2007版本
                    workbook = new SXSSFWorkbook(); //需要定时清理缓存文件 C:\Users\..\AppData\Local\Temp\poifiles
                else if (filePath.IndexOf(".xls") > 0)
                    // 2003版本
                    workbook = new HSSFWorkbook();
                foreach (var sheetName in sheetNames)
                {
                    ISheet sheet = workbook.CreateSheet(sheetName);
                    // 创建列名
                    IRow row = sheet.CreateRow(0);
                    // 获取列名
                    var keyValue = tableHeaders[sheetName];
                    if (keyValue != null)
                    {
                        List<string> columns = JsonHelper.JsonToObject<List<string>>(JsonHelper.ObjectToJson(keyValue));
                        for (int i = 0; i < columns.Count; i++)
                        {
                            row.CreateCell(i).SetCellValue(columns[i]);
                        }
                    }
                }
                workbook.Write(fs);
                return true;
            }
            catch (Exception ex)
            {
                errMsg = ex.Message;
                return false;
            }
        }
        /// <summary>
        /// 将DataTable数据导入到excel中
        /// </summary>
        /// <param name="data">要导入的数据</param>
        /// <param name="sheetName">要导入的excel的sheet的名称，默认:sheet1</param>
        /// <param name="isColumnWritten">DataTable的列名是否要导入,默认true</param>
        /// <param name="dateFormat">日期格式，默认:yyyy/MM/dd</param>
        /// <param name="intFormat">整数值格式，默认:#,##0</param>
        /// <param name="decFormat">浮点数值格式，默认:#,##0.00</param>
        /// <param name="boolTrueFormat">布尔值“真”格式，默认:yes</param>
        /// <param name="boolFalseFormat">布尔值“假”格式，默认:no</param>
        /// <returns>导出数据行数(包含列名那一行)</returns>
        public static int DataTableToExcel(DataTable data, string fileName, string sheetName = "Sheet1", bool isColumnWritten = true, string dateFormat = "", string intFormat = "", string decFormat = "", string boolTrueFormat = "", string boolFalseFormat = "", List<string> hiddenColumns = null)
        {
            fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            if (fileName.IndexOf(".xlsx") > 0)
                // 2007版本
                //workbook = new XSSFWorkbook();
                workbook = new SXSSFWorkbook(); //需要定时清理缓存文件 C:\Users\..\AppData\Local\Temp\poifiles
            else if (fileName.IndexOf(".xls") > 0)
                // 2003版本
                workbook = new HSSFWorkbook();

            if (dateFormat == "")
                dateFormat = ExcelDateFormat != "" ? ExcelDateFormat : "yyyy/MM/dd";
            if (intFormat == "")
                intFormat = ExcelIntFormat != "" ? ExcelIntFormat : "#,##0";
            if (decFormat == "")
                decFormat = ExcelDecFormat != "" ? ExcelDecFormat : "#,##0.00";
            if (boolTrueFormat == "")
                boolTrueFormat = ExcelBoolTrueFormat != "" ? ExcelBoolTrueFormat : "yes";
            if (boolFalseFormat == "")
                boolFalseFormat = ExcelBoolFalseFormat != "" ? ExcelBoolFalseFormat : "no";

            XSSFDataFormat sFormat = (XSSFDataFormat)workbook.CreateDataFormat();
            ICellStyle icsInt = workbook.CreateCellStyle();
            icsInt.DataFormat = sFormat.GetFormat(intFormat);
            ICellStyle icsDec = workbook.CreateCellStyle();
            icsDec.DataFormat = sFormat.GetFormat(decFormat);

            // 创建一个单元格样式
            ICellStyle partCellStyle = workbook.CreateCellStyle();
            // 设置背景填充颜色
            partCellStyle.FillForegroundColor = IndexedColors.Yellow.Index;
            // 设置填充模式
            partCellStyle.FillPattern = FillPattern.SolidForeground;

            try
            {
                ISheet sheet;
                if (workbook != null)
                    sheet = workbook.CreateSheet(sheetName);
                else
                    return -1;

                int count = 0;
                int partColIndex = -1;
                if (isColumnWritten == true)
                {
                    // 写入DataTable的列名
                    IRow row = sheet.CreateRow(0);
                    for (int j = 0; j <= data.Columns.Count - 1; j++)
                    {
                        string columnName = data.Columns[j].ColumnName;
                        row.CreateCell(j).SetCellValue(columnName);
                        if (columnName.ToLower() == "零件号".ToLower())
                        {
                            partColIndex = j;
                        }
                        // 列隐藏
                        if (hiddenColumns != null && hiddenColumns.Contains(columnName.ToLower()))
                        {
                            sheet.SetColumnHidden(j, true);
                        }
                    }

                    count++;
                }

                for (int i = 0; i <= data.Rows.Count - 1; i++)
                {
                    IRow row = sheet.CreateRow(count++);
                    for (int j = 0; j <= data.Columns.Count - 1; j++)
                    {
                        var cell = row.CreateCell(j);
                        switch (data.Columns[j].DataType.Name.ToLower())
                        {
                            case "int":
                                cell.SetCellValue(data.Rows[i][j].GetCInt());
                                cell.CellStyle = icsInt;
                                break;
                            case "int16":
                                cell.SetCellValue(data.Rows[i][j].GetCInt());
                                cell.CellStyle = icsInt;
                                break;
                            case "int32":
                                cell.SetCellValue(data.Rows[i][j].GetCInt());
                                cell.CellStyle = icsInt;
                                break;
                            case "int64":
                                cell.SetCellValue(data.Rows[i][j].GetCInt());
                                cell.CellStyle = icsInt;
                                break;
                            case "decimal":
                                cell.SetCellValue(data.Rows[i][j].GetCDouble());
                                cell.CellStyle = icsDec;
                                break;
                            case "double":
                                cell.SetCellValue(data.Rows[i][j].GetCDouble());
                                cell.CellStyle = icsDec;
                                break;
                            case "date":
                                if (data.Rows[i][j].ToString() != "")
                                    cell.SetCellValue(data.Rows[i][j].GetCDate().ToString(dateFormat));
                                else
                                    cell.SetCellValue("");
                                break;
                            case "datetime":
                                if (data.Rows[i][j].ToString() != "")
                                    cell.SetCellValue(data.Rows[i][j].GetCDate().ToString(dateFormat));
                                else
                                    cell.SetCellValue("");
                                break;
                            case "boolean":
                                cell.SetCellValue(data.Rows[i][j].GetCBool() == true ? boolTrueFormat : boolFalseFormat);
                                break;
                            default:
                                cell.SetCellValue(data.Rows[i][j].ToString());
                                break;
                        }
                        if (j == partColIndex)
                        {
                            if (data.Rows[i]["ExcelCellColor"].GetCString().ToLower() == "true")
                            {
                                cell.CellStyle = partCellStyle;
                            }
                        }
                    }
                }
                workbook.Write(fs);
                // 写入到excel
                return count;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
                return -1;
            }
        }

        #region DataTable数据导入Excel 多个sheet页
        /// <summary>
        /// 输出excel的datatable结构体
        /// </summary>
        public struct DTlist
        {
            public DataTable Data;
            public string SheetName;
            public bool IsColumnWritten;
            public bool IsFirstRowColumn;
            public string str;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dtlistdata">数据集合</param>
        /// <param name="dateFormat">日期格式，默认:yyyy/MM/dd</param>
        /// <param name="intFormat">整数值格式，默认:#,##0</param>
        /// <param name="decFormat">浮点数值格式，默认:#,##0.00</param>
        /// <param name="boolTrueFormat">布尔值“真”格式，默认:yes</param>
        /// <param name="boolFalseFormat">布尔值“假”格式，默认:no</param>
        /// <returns></returns>
        public int DataTableListToExcel(List<DTlist> dtlistdata, string dateFormat = "", string intFormat = "", string decFormat = "", string boolTrueFormat = "", string boolFalseFormat = "")
        {
            int count = 0;
            fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            if (fileName.IndexOf(".xlsx") > 0)
                // 2007版本
                workbook = new SXSSFWorkbook();
            else if (fileName.IndexOf(".xls") > 0)
                // 2003版本
                workbook = new HSSFWorkbook();

            if (dateFormat == "")
                dateFormat = ExcelDateFormat != "" ? ExcelDateFormat : "yyyy/MM/dd";
            if (intFormat == "")
                intFormat = ExcelIntFormat != "" ? ExcelIntFormat : "#,##0";
            if (decFormat == "")
                decFormat = ExcelDecFormat != "" ? ExcelDecFormat : "#,##0.00";
            if (boolTrueFormat == "")
                boolTrueFormat = ExcelBoolTrueFormat != "" ? ExcelBoolTrueFormat : "yes";
            if (boolFalseFormat == "")
                boolFalseFormat = ExcelBoolFalseFormat != "" ? ExcelBoolFalseFormat : "no";
            // 创建基础样式（复用，减少内存占用）
            XSSFDataFormat sFormat = (XSSFDataFormat)workbook.CreateDataFormat();
            ICellStyle icsDate = workbook.CreateCellStyle();
            icsDate.DataFormat = sFormat.GetFormat(dateFormat);
            ICellStyle icsInt = workbook.CreateCellStyle();
            icsInt.DataFormat = sFormat.GetFormat(intFormat);
            ICellStyle icsDec = workbook.CreateCellStyle();
            icsDec.DataFormat = sFormat.GetFormat(decFormat);
            // 预创建3种颜色的样式（复用，提升性能）
            var colorStyles = new Dictionary<IndexedColors, ICellStyle>();
            foreach (var color in FixedColors.Values)
            {
                ICellStyle style = workbook.CreateCellStyle();
                style.FillForegroundColor = color.Index;
                style.FillPattern = FillPattern.SolidForeground;
                colorStyles[color] = style;
            }

            try
            {
                for (var v_i = 0; v_i <= dtlistdata.Count - 1; v_i++)
                {
                    DataTable dt = dtlistdata[v_i].Data;
                    if (dt == null || dt.Rows.Count == 0)
                        continue;

                    // 关键变更：识别颜色列，允许无颜色列（返回空而非报错）
                    var (monthColumns, colorColumns) = GetMonthAndColorColumns(dt);
                    bool hasColorColumns = monthColumns != null && monthColumns.Count > 0;

                    ISheet sheet;
                    if (workbook != null)
                    {
                        sheet = workbook.CreateSheet();
                        workbook.SetSheetName(v_i, dtlistdata[v_i].SheetName);
                    }
                    else
                        return -1;
                    
                    if (dtlistdata[v_i].IsColumnWritten == true)
                    {
                        // 写入DataTable的列名
                        IRow row = sheet.CreateRow(0);
                        int excelColIndex = 0;
                        foreach (DataColumn col in dt.Columns)
                        {
                            if (hasColorColumns && colorColumns.Contains(col.ColumnName))
                                continue; // 仅当有颜色列时才跳过
                            row.CreateCell(excelColIndex++).SetCellValue(col.ColumnName);
                        }
                        count = 1;
                    }
                    else
                        count = 0;

                    // 写入数据行
                    foreach (DataRow dataRow in dt.Rows)
                    {
                        IRow excelRow = sheet.CreateRow(count++);
                        int excelColIndex = 0;

                        foreach (DataColumn col in dt.Columns)
                        {
                            // 仅当存在颜色列时，才跳过颜色列
                            if (hasColorColumns && colorColumns.Contains(col.ColumnName))
                                continue;

                            ICell cell = excelRow.CreateCell(excelColIndex);
                            object cellValue = dataRow[col];

                            // 处理基础数据类型和样式
                            HandleCellValue(cell, cellValue, col.DataType, icsDate, icsInt, icsDec, boolTrueFormat, boolFalseFormat);

                            // 仅当存在颜色列时，才处理月份列高亮
                            // 处理月份列颜色（仅支持3种固定颜色）
                            if (hasColorColumns && monthColumns.TryGetValue(col.ColumnName, out string colorColName))
                            {
                                string colorValue = dataRow[colorColName]?.ToString()?.Trim();
                                if (!string.IsNullOrEmpty(colorValue) && FixedColors.TryGetValue(colorValue, out IndexedColors color))
                                {
                                    // 合并数据样式和颜色样式
                                    ICellStyle mergedStyle = workbook.CreateCellStyle();
                                    mergedStyle.CloneStyleFrom(cell.CellStyle ?? workbook.CreateCellStyle());
                                    mergedStyle.FillForegroundColor = colorStyles[color].FillForegroundColor;
                                    mergedStyle.FillPattern = colorStyles[color].FillPattern;
                                    cell.CellStyle = mergedStyle;
                                }
                            }

                            excelColIndex++;
                        }
                    }
                }
                workbook.Write(fs);
                // 写入到excel
                return count;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
                throw ex;
            }
            finally
            {
                if (fileName.IndexOf(".xlsx") > 0)
                    ((SXSSFWorkbook)workbook).Dispose();
            }
        }

        /// <summary>
        /// 识别月份列和颜色列（无颜色列时返回空字典，不报错）
        /// </summary>
        private static (Dictionary<string, string> monthColumns, HashSet<string> colorColumns) GetMonthAndColorColumns(DataTable dt)
        {
            var monthColumns = new Dictionary<string, string>();
            var colorColumns = new HashSet<string>();
            bool hasAnyColorColumn = false;

            // 收集颜色列和对应的月份列
            foreach (DataColumn col in dt.Columns)
            {
                if (col.ColumnName.Length == 11 && col.ColumnName.EndsWith("Color", StringComparison.OrdinalIgnoreCase))
                {
                    hasAnyColorColumn = true;
                    string monthColName = col.ColumnName.Substring(0, 6);
                    if (MonthColumnRegex.IsMatch(monthColName) && dt.Columns.Contains(monthColName))
                    {
                        colorColumns.Add(col.ColumnName);
                        monthColumns[monthColName] = col.ColumnName;
                    }
                }
            }

            // 无颜色列时，直接返回空（不处理颜色逻辑）
            if (!hasAnyColorColumn)
                return (new Dictionary<string, string>(), new HashSet<string>());

            //// 有颜色列时，校验月份列完整性
            //foreach (DataColumn col in dt.Columns)
            //{
            //    if (MonthColumnRegex.IsMatch(col.ColumnName) && !monthColumns.ContainsKey(col.ColumnName))
            //    {
            //        throw new Exception($"存在月份列[{col.ColumnName}]，但未找到对应颜色列[{col.ColumnName}Color]");
            //    }
            //}

            return (monthColumns, colorColumns);
        }

        private static void HandleCellValue(
            ICell cell, object value, Type dataType,
            ICellStyle icsDate, ICellStyle icsInt, ICellStyle icsDec,
            string boolTrueFormat, string boolFalseFormat)
        {
            if (value == DBNull.Value || value == null)
            {
                cell.SetCellValue("");
                return;
            }

            string typeName = dataType.Name.ToLower();
            switch (typeName)
            {
                case "int":
                case "int16":
                case "int32":
                case "int64":
                    cell.SetCellValue(Convert.ToInt32(value));
                    cell.CellStyle = icsInt;
                    break;
                case "decimal":
                case "double":
                    cell.SetCellValue(Convert.ToDouble(value));
                    cell.CellStyle = icsDec;
                    break;
                case "datetime":
                    if (!string.IsNullOrEmpty(value.ToString()))
                    {
                        cell.SetCellValue(Convert.ToDateTime(value));
                        cell.CellStyle = icsDate;
                        cell.SetCellType(CellType.Numeric);
                    }
                    else
                    {
                        cell.SetCellValue("");
                    }
                    break;
                case "boolean":
                    cell.SetCellValue(Convert.ToBoolean(value) ? boolTrueFormat : boolFalseFormat);
                    break;
                default:
                    cell.SetCellValue(value.ToString());
                    break;
            }
        }

        #endregion
        /// <summary>
        /// 将excel中的数据导入到DataTable中
        /// </summary>
        /// <param name="sheetName">excel工作薄sheet的名称</param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
        /// <param name="ifConvertLetter">列名是否转换为字母</param>
        /// <returns>返回的DataTable</returns>
        public static DataTable ExcelToDataTable(string fileName, string sheetName, bool isFirstRowColumn, out string ErrorMessage, out bool isEmptyFlag, List<string> columnList = null, bool ifConvertLetter = true)
        {
            DataTable data = new DataTable();
            ErrorMessage = string.Empty;
            isEmptyFlag = false;
            try
            {
                fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                if (fileName.IndexOf(".xlsx") > 0)
                    // 2007版本
                    workbook = new XSSFWorkbook(fs);
                else if (fileName.IndexOf(".xls") > 0)
                    // 2003版本
                    workbook = new HSSFWorkbook(fs);

                var evaluator = workbook.GetCreationHelper().CreateFormulaEvaluator();

                ISheet sheet;
                if (sheetName != null)
                {
                    sheet = workbook.GetSheet(sheetName);
                    if (sheet == null)
                    {
                        // 如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                        // sheet = workbook.GetSheetAt(0);
                        ErrorMessage = "excel中不存在该sheet页 " + sheetName;
                        return null;
                    }
                }
                else
                    sheet = workbook.GetSheetAt(0);
                if (sheet != null)
                {
                    IRow firstRow = sheet.GetRow(0);
                    int cellCount = firstRow.LastCellNum;
                    int startRow;
                    // 一行最后一个cell的编号 即总的列数
                    if (isFirstRowColumn)
                    {
                        for (int i = firstRow.FirstCellNum; i <= cellCount - 1; i++)
                        {
                            ICell cell = firstRow.GetCell(i);
                            if (cell != null)
                            {
                                string cellValue = string.Empty;
                                if (ifConvertLetter)
                                {
                                    cellValue = ConvertToColumn(cell.ColumnIndex + 1);
                                }
                                else
                                {
                                    cellValue = cell.StringCellValue;
                                }
                                if (cellValue != null)
                                {
                                    DataColumn column = new DataColumn(cellValue);
                                    data.Columns.Add(column);
                                }
                            }
                        }
                        startRow = sheet.FirstRowNum + 1;
                    }
                    else
                        startRow = sheet.FirstRowNum;

                    // 最后一列的标号
                    int rowCount = sheet.LastRowNum;
                    for (int i = startRow; i <= rowCount; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null)
                            continue;
                        // 没有数据的行默认是null　　　　　　　
                        List<object> itemArray = new List<object>();
                        for (int j = 0; j <= cellCount - 1; j++)
                        {
                            var columnName = data.Columns[j].ColumnName;
                            var cellValue = GetCellValue(row.GetCell(j));
                            if (!isEmptyFlag && columnList.Contains(columnName) && (cellValue == null || cellValue.GetCString().GetIsEmptyOrNull()))
                                isEmptyFlag = true;

                            itemArray.Add(cellValue);
                        }
                        data.Rows.Add(itemArray.ToArray());
                    }
                }

                return data;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 获取单元格类型
        /// </summary>
        /// <param name="cell">单元格</param>
        /// <param name="isFormula">单元格内容是否为公式</param>
        /// <returns></returns>
        private static object GetCellValue(ICell cell, bool isFormula = false)
        {
            if (cell == null)
                return "";
            CellType cellType = isFormula ? cell.CachedFormulaResultType : cell.CellType;
            switch (cellType)
            {
                case CellType.String: //STRING
                    return cell.StringCellValue;
                case CellType.Boolean: //BOOLEAN
                    return cell.BooleanCellValue;
                case CellType.Numeric: //NUMERIC
                    //NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
                    if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
                    {
                        return cell.DateCellValue;
                    }
                    else//其他数字类型
                    {
                        return cell.NumericCellValue;
                    }
                //short format = cell.CellStyle.DataFormat;
                //if (format == 14 || format == 31 || format == 57 || format == 58 || format == 20) { return Convert.ToDateTime(cell.DateCellValue).ToString("yyyy-MM-dd HH:mm:ss"); } else { return cell.NumericCellValue; }                
                case CellType.Formula: //FORMULA公式
                    // 递归处理公式结果
                    return GetCellValue(cell, true);
                case CellType.Error: //ERROR
                    return cell.ErrorCellValue;
                case CellType.Blank: //BLANK
                    return "";
                default:
                    return cell.CellFormula;
            }
        }

        /// <summary>
        /// excel 数字转 列字母位置
        /// </summary>
        /// <param name="index"></param>
        /// <returns></returns>
        /// <exception cref="ArgumentException"></exception>
        public static string ConvertToColumn(int index)
        {
            if (index <= 0)
            {
                throw new ArgumentException("Invalid parameter");
            }
            string column = string.Empty;
            while (index > 0)
            {
                index--;
                column = Convert.ToChar((index % 26 + 65)).ToString() + column;
                index = (index - index % 26) / 26;
            }
            return column.ToLower();
        }

        /// <summary>
        /// List转DataTable
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="varlist"></param>
        /// <returns></returns>
        public static DataTable ToDataTable<T>(IEnumerable<T> varlist)
        {
            DataTable dtReturn = new DataTable();
            // column names
            PropertyInfo[] oProps = null;
            // Could add a check to verify that there is an element 0
            foreach (T rec in varlist)
            {
                if (oProps == null)
                {
                    oProps = ((Type)rec.GetType()).GetProperties();

                    foreach (PropertyInfo pi in oProps)
                    {
                        Type colType = pi.PropertyType;
                        if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
                        {
                            colType = colType.GetGenericArguments()[0];
                        }
                        dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
                    }
                }

                DataRow dr = dtReturn.NewRow();
                foreach (PropertyInfo pi in oProps)
                {
                    dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue(rec, null);
                }
                dtReturn.Rows.Add(dr);
            }
            return (dtReturn);
        }

        #region 迁移模板excel并填充数据
        /// <summary>
        /// 复制Excel模板到目标目录并填充数据
        /// </summary>
        /// <param name="templatePath">模板文件路径</param>
        /// <param name="targetDirectory">目标目录</param>
        /// <param name="newFileName">新文件名称（不含扩展名）</param>
        /// <returns>生成的文件路径</returns>
        public string ProcessTemplate(string templatePath, string targetDirectory, string newFileName, DataTable dt)
        {
            try
            {
                // 验证模板文件是否存在
                if (!File.Exists(templatePath))
                {
                    throw new FileNotFoundException("模板文件不存在", templatePath);
                }

                // 确保目标目录存在
                if (!Directory.Exists(targetDirectory))
                {
                    Directory.CreateDirectory(targetDirectory);
                }

                // 获取文件扩展名以确定Excel版本
                string extension = Path.GetExtension(templatePath).ToLower();
                string targetPath = Path.Combine(targetDirectory, $"{newFileName}{extension}");

                // 读取模板并填充数据
                using (FileStream templaACH_DAMCapacityream = new FileStream(templatePath, FileMode.Open, FileAccess.Read))
                {
                    IWorkbook workbook = CreateWorkbook(templaACH_DAMCapacityream, extension);

                    // 填充数据
                    FillData(workbook, dt);

                    // 保存到目标文件
                    using (FileStream targetStream = new FileStream(targetPath, FileMode.Create, FileAccess.Write))
                    {
                        workbook.Write(targetStream);
                    }
                }
                return newFileName + extension;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"处理Excel模板时出错: {ex.Message}");
                throw;
            }
        }

        /// <summary>
        /// 根据文件扩展名创建对应的Workbook
        /// </summary>
        private IWorkbook CreateWorkbook(Stream stream, string extension)
        {
            if (extension == ".xlsx")
            {
                return new XSSFWorkbook(stream);
            }
            else if (extension == ".xls")
            {
                return new HSSFWorkbook(stream);
            }
            else
            {
                throw new NotSupportedException("不支持的Excel格式，仅支持.xls和.xlsx");
            }
        }

        /// <summary>
        /// 向Excel工作簿填充数据
        /// </summary>
        private void FillData(IWorkbook workbook,DataTable data, string dateFormat = "", string intFormat = "", string decFormat = "", string boolTrueFormat = "", string boolFalseFormat = "")
        {
            // 获取第一个工作表（假设数据要填充到第一个工作表）
            ISheet sheet = workbook.GetSheetAt(0);

            // 获取表头行
            IRow headerRow = sheet.GetRow(0);
            if (headerRow == null)
                throw new InvalidOperationException("未找到表头行，请检查headerRowIndex设置");
            // 建立Excel列索引与DataTable字段名的映射关系
            var columnMapping = GetColumnMapping(headerRow, data);

            if (dateFormat == "")
                dateFormat = ExcelDateFormat != "" ? ExcelDateFormat : "yyyy/MM/dd";
            if (intFormat == "")
                intFormat = ExcelIntFormat != "" ? ExcelIntFormat : "#,##0";
            if (decFormat == "")
                decFormat = ExcelDecFormat != "" ? ExcelDecFormat : "#,##0.00";
            if (boolTrueFormat == "")
                boolTrueFormat = ExcelBoolTrueFormat != "" ? ExcelBoolTrueFormat : "yes";
            if (boolFalseFormat == "")
                boolFalseFormat = ExcelBoolFalseFormat != "" ? ExcelBoolFalseFormat : "no";

            // 假设模板中有表头，从第2行开始填充数据（NPOI的行索引从0开始）
            int count = 1; // 第2行

            // 填充数据到工作表
            for (int i = 0; i <= data.Rows.Count - 1; i++)
            {
                IRow row = sheet.CreateRow(count++);
                DataRow dataRow = data.Rows[i];
                // 按映射关系填充数据
                foreach (var mapping in columnMapping)
                {
                    int excelColumnIndex = mapping.Key; // Excel列索引
                    string dataFieldName = mapping.Value; // DataTable字段名
                    var cell = row.CreateCell(excelColumnIndex);

                    // 使用字段名获取DataTable中的值，而不是使用Excel列索引
                    object cellValue = dataRow[dataFieldName];

                    // 处理DBNull值
                    if (cellValue == DBNull.Value)
                    {
                        cell.SetCellValue("");
                        continue;
                    }

                    // 根据DataTable字段的数据类型处理值
                    switch (data.Columns[dataFieldName].DataType.Name.ToLower())
                    {
                        case "int":
                        case "int16":
                        case "int32":
                        case "int64":
                            cell.SetCellValue(Convert.ToInt32(cellValue));
                            break;
                        case "decimal":
                            cell.SetCellValue(Convert.ToDouble(cellValue));
                            break;
                        case "double":
                            cell.SetCellValue(Convert.ToDouble(cellValue));
                            break;
                        case "date":
                        case "datetime":
                            if (cellValue != null && cellValue != DBNull.Value)
                            {
                                DateTime dateValue = Convert.ToDateTime(cellValue);
                                cell.SetCellValue(dateValue.ToString(dateFormat));
                            }
                            else
                            {
                                cell.SetCellValue("");
                            }
                            break;
                        case "boolean":
                            bool boolValue = Convert.ToBoolean(cellValue);
                            cell.SetCellValue(boolValue ? boolTrueFormat : boolFalseFormat);
                            break;
                        default:
                            cell.SetCellValue(cellValue.ToString());
                            break;
                    }
                }
            }
        }

        /// <summary>
        /// 建立Excel列索引与DataTable字段名的映射（通过表头文本匹配）
        /// </summary>
        private Dictionary<int, string> GetColumnMapping(IRow headerRow, DataTable dataTable)
        {
            var mapping = new Dictionary<int, string>();
            var columnNames = dataTable.Columns.Cast<DataColumn>();
            // 遍历Excel表头单元格
            for (int colIndex = 0; colIndex <= headerRow.LastCellNum; colIndex++)
            {
                ICell headerCell = headerRow.GetCell(colIndex);
                if (headerCell == null) continue;

                // 获取表头文本（统一转为小写用于匹配）
                string headerText = GetCellStringValue(headerCell).Trim().ToLower();
                if (string.IsNullOrEmpty(headerText)) continue;

                // 查找DataTable中是否存在匹配的字段名（不区分大小写）
                var matchedColumn = columnNames.FirstOrDefault(col => col.ColumnName.Trim().ToLower() == headerText);

                // 如果找到匹配的字段，记录映射关系
                if (matchedColumn != null)
                {
                    mapping[colIndex] = matchedColumn.ColumnName;
                }
            }

            return mapping;
        }

        /// <summary>
        /// 获取单元格的字符串值（处理不同类型的单元格）
        /// </summary>
        private string GetCellStringValue(ICell cell)
        {
            if (cell == null) return string.Empty;

            return cell.CellType switch
            {
                CellType.String => cell.StringCellValue,
                CellType.Numeric => cell.NumericCellValue.GetCString(),
                CellType.Boolean => cell.BooleanCellValue.GetCString(),
                CellType.Formula => cell.CachedFormulaResultType == CellType.String ? cell.StringCellValue : cell.NumericCellValue.ToString(),
                _ => string.Empty
            };
        }
        #endregion

        public void Dispose()
        {
            Dispose(true);
        }
        protected virtual void Dispose(bool disposing)
        {
            if (!this.disposed)
            {
                if (disposing)
                {
                    if (fs != null)
                        fs.Close();
                }

                fs = null;
                disposed = true;
            }
        }
    }
}