原地址忘了
需引用NPOI，引用方法：项目引用那儿右键 => 管理NuGet程序包 => 游览 =>输入NPOI =>选中NPOI后安装（一般是第一个）
/// <summary>
/// Excel 转换为 Datatable
/// </summary>
/// <param name="sheetNum">工作表索引</param>
/// <param name="isFirstRowColumn">首行为列</param>
/// <param name="fileName">Excel文件路径</param>
/// <returns></returns>
public DataTable ExcelToDataTable(int sheetNum, bool isFirstRowColumn, string fileName)
{
IWorkbook workbook = null;
ISheet sheet = null;
DataTable myTable = new DataTable();
try
{
var fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
if (fileName.IndexOf(".xlsx") > )
workbook = new XSSFWorkbook(fs);
else if (fileName.IndexOf(".xls") > )
workbook = new HSSFWorkbook(fs);
sheet = workbook.GetSheetAt(sheetNum); //工作表不能为空
if (sheet == null)
{
string str = "";
for (int i = ; i < workbook.NumberOfSheets; i++)
{
str += workbook.GetSheetAt(i).SheetName + ",";
}
str = workbook.NumberOfSheets + str;
throw new Exception($"sheet不能为空！参数：{sheetNum} 工作簿信息：{str}");
} //Excel最大列数
int MaxColumnNum = ;
for (int i = ; i < sheet.LastRowNum; i++)
{
var row = sheet.GetRow(i);
if (row.LastCellNum > MaxColumnNum)
{
MaxColumnNum = row.LastCellNum;
}
}
//Excel行数
int MaxRowNum = sheet.LastRowNum; //table新增列
for (int i = ; i < MaxColumnNum; ++i)
{
//首行为列
if (isFirstRowColumn)
{
bool addEmptyCell = true;//是否添加空列
ICell cell = sheet.GetRow().GetCell(i);
if (cell != null)
{
//table列赋值
string cellValue = "";//列名
if (cell.CellType == CellType.Numeric)
{
cellValue = cell.NumericCellValue.ToString();
}
else
{
cellValue = cell.StringCellValue;
}
if (!string.IsNullOrWhiteSpace(cellValue))
{
//列数据为Excel的数据
addEmptyCell = false;
myTable.Columns.Add(new DataColumn(cellValue));
}
}
if (addEmptyCell)
{
myTable.Columns.Add(new DataColumn(""));//列数据为空
}
}
else
{
myTable.Columns.Add(new DataColumn(i + ""));
}
} //起始行
int startRow = ;
if (isFirstRowColumn)
{
startRow = ;
} //DataTable赋值
for (int i = startRow; i <= MaxRowNum; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null) continue; DataRow NewRow = myTable.NewRow();
for (int j = row.FirstCellNum; j < row.LastCellNum; ++j)
{
ICell cell = row.GetCell(j);
string value = "";
if (cell != null)
{
//table行赋值
if (cell.CellType == CellType.Numeric)
{
value = cell.NumericCellValue.ToString();
if ((j == ) && ((i == ) || (i == )))
{
//特殊的几个单元格 转换为 日期格式
value = ToDateTimeValue(cell.NumericCellValue.ToString());
}
}
else
{
//row.GetCell(j).SetCellType(CellType.String);
value = cell.StringCellValue;
}
}
NewRow[j] = value;
}
myTable.Rows.Add(NewRow);
}
return myTable;
}
catch (Exception ex)
{
throw ex;
}
}