一.NuGet 包管理器安装 NPOI
![](https://i-blog.csdnimg.cn/blog_migrate/2055c836dc102ef0ebfcb2260329461e.png)
二.主要代码
/// <summary>
/// 获取excel内容(.xlsx .xls)
/// 备注:这个方法只读取了第一个sheet的数据
/// </summary>
/// <param name="filePath">excel文件路径 eg:D:\A.xlsx</param>
/// <param name="dt">excel内数据(无标题)</param>
/// <param name="excelTitles">excel内标题</param>
/// <param name="startIndex">数据开始读取的行数位置(值为1默认有标题,excelTitles为空,值为0有标题,且赋值excelTitles)</param>
public static bool ReadExcelToDataTable(string filePath, out DataTable dt, out ArrayList excelTitles, int startIndex = 1)
{
//excel内数据(无标题)
dt = new DataTable();
//excel内标题
excelTitles = new ArrayList();
try
{
using (FileStream fsRead = File.OpenRead(filePath))
{
IWorkbook wk = null;
//获取后缀名
string extension = filePath.Substring(filePath.LastIndexOf(".")).ToString().ToLower();
//判断是否是excel文件
if (extension == ".xlsx" || extension == ".xls")
{
try
{
wk = new XSSFWorkbook(fsRead);
}
catch (Exception error)
{
wk = new HSSFWorkbook(fsRead);
}
//获取第一个sheet TODO 若有多个Sheet这里需要循环
ISheet sheet = wk.GetSheetAt(0);
//获取第一行
IRow headrow = sheet.GetRow(0);
for (int i = headrow.FirstCellNum; i < headrow.Cells.Count; i++)
{
ICell cell = headrow.GetCell(i);
var tempV = GetCellValue(cell);
//创建列
DataColumn datacolum = new DataColumn(tempV);
dt.Columns.Add(datacolum);
//读取标题
if ((!string.IsNullOrEmpty(tempV))&& startIndex != 0)
{
excelTitles.Add(tempV);
}
}
//读取每行
for (int r = startIndex; r <= sheet.LastRowNum; r++)
{
bool result = false;
DataRow dr = dt.NewRow();
//获取当前行
IRow row = sheet.GetRow(r);
//读取每列 采用headrow行数原因为,row.Cells.Count遇空会跳过
for (int j = 0; j < headrow.Cells.Count; j++)
{
//获取单元格的值
dr[j] = GetCellValue(row.GetCell(j));
//全为空则不取
if (dr[j].ToString() != "") result = true;
}
if (result == true)
{
dt.Rows.Add(dr); //把每行追加到DataTable
}
}
}
}
return true;
}
catch (Exception ex)
{
return false;
}
}
/// <summary>
/// 读取Cell数据
/// </summary>
private static string GetCellValue(ICell cell)
{
if (cell == null) return string.Empty;
switch (cell.CellType)
{
case CellType.Blank: //空数据类型 这里类型注意一下,不同版本NPOI大小写可能不一样,有的版本是Blank(首字母大写)
return string.Empty;
case CellType.Boolean: //bool类型
return cell.BooleanCellValue.ToString();
case CellType.Error:
return cell.ErrorCellValue.ToString();
case CellType.Numeric: //数字类型
//日期类型 其它数字
return (DateUtil.IsCellDateFormatted(cell) ? cell.DateCellValue.ToString() : cell.NumericCellValue.ToString());
case CellType.String: //string 类型
return cell.StringCellValue;
case CellType.Formula: //带公式类型
try
{
HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
e.EvaluateInCell(cell);
return cell.ToString();
}
catch
{
return cell.NumericCellValue.ToString();
}
case CellType.Unknown: //无法识别类型
default: //默认类型
return cell.ToString();//
}
}