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}";
}
}
标签:files,c#,excel,columnName,int,new,columnNameCounts,DataTable
From: https://www.cnblogs.com/duixue/p/18403883