C# NPOI 读取EXCEL数据后转化为DataTable

XSSF是用于.xlsx（2007以后版本）

HSSF是用于.xls（2007以前版本）

//也可以根据后缀名自动切换IWorkbook
IWorkbook workbook;
if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(fs); } else if (fileExt == ".xls" || fileExt == ".xlt") { workbook = new HSSFWorkbook(fs); } else { workbook = null; }
if (workbook == null) { return null; }

 

static DataTable ReadExcelToDataTable(string filePath)
        {
            DataTable dataTable = new DataTable();

            // 读取Excel文件
            using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                HSSFWorkbook workbook = new HSSFWorkbook(file);
                ISheet sheet = workbook.GetSheetAt(0); // 获取第一个工作表

                // 获取列数
                int columnsCount = sheet.GetRow(0).LastCellNum;

                // 用于存储列名和对应的重复次数
                Dictionary<string, int> columnNameCounts = new Dictionary<string, int>();

                // 创建表头，处理重复的列名
                for (int i = 0; i < columnsCount; i++)
                {
                    object obj = GetValueType(sheet.GetRow(0).GetCell(i));
                    if (obj == null || obj.ToString() == string.Empty)
                    {
                        dataTable.Columns.Add(new DataColumn("Columns" + i.ToString()));
                    }
                    else
                    {
                        //string columnName = sheet.GetRow(0).GetCell(i).StringCellValue;
                        string columnName = obj.ToString();
                        string uniqueColumnName = GetUniqueColumnName(columnName, columnNameCounts);
                        dataTable.Columns.Add(uniqueColumnName);
                    }
         
                }

                // 填充数据
                for (int i = 1; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dataRow = dataTable.NewRow();
                    for (int j = 0; j < columnsCount; j++)
                    {
                        if (row.GetCell(j) != null)
                        {
                            dataRow[j] = row.GetCell(j).ToString();
                        }
                    }
                    dataTable.Rows.Add(dataRow);
                }
            }

            return dataTable;
        }

        static string GetUniqueColumnName(string columnName, Dictionary<string, int> columnNameCounts)
        {
            if (!columnNameCounts.ContainsKey(columnName))
            {
                columnNameCounts[columnName] = 1;
                return columnName;
            }
            else
            {
                int count = columnNameCounts[columnName];
                columnNameCounts[columnName] = count + 1;
                return $"{columnName}{count + 1}";
            }
        }