c# 读取上传excel转换为DataTable

var files = Request.Files;

if (files == null || files.Count == 0)
{
    return Json(new { code = 1, msg = "未获取到文件" });
}



var file = files[0];

var dt = ReadExcelToDataTable(file.InputStream);

return Json(new { code = 1, msg = "已读取Excel到DataTable" });

        static DataTable ReadExcelToDataTable(Stream stream)
        {
            DataTable dataTable = new DataTable();

            // 读取Excel文件
            XSSFWorkbook workbook = new XSSFWorkbook(stream);
            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 = 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}";
            }
        }