.net导入excel文件到dataset

1.需要引入：

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

//2007及以上版本
XSSFWorkbook xWorkbook=new XSSFWorkbook(fileStream);
//2003版本
HSSFWorkbook hWorkbook=new HSSFWorkbook(fileStream);

2.传入文件路径，返回dataset数据集合

public static DataSet ImportExcel(string filePath)
{
DataSet ds = null;
try
{
FileStream fileStream = new FileStream(filePath, FileMode.Open);
HSSFWorkbook workbook = new HSSFWorkbook(fileStream);
ISheet sheet = null;
IRow row = null;
ds = new DataSet();
DataTable dt = null;
for (int i = 0; i < workbook.Count; i++)
{
dt = new DataTable();
dt.TableName = "table" + i.ToString();
//获取sheet表
sheet = workbook.GetSheetAt(i);
//起始行索引
int rowIndex = sheet.FirstRowNum;
//获取行数
int rowCount = sheet.LastRowNum;
//获取第一行
IRow firstRow = sheet.GetRow(rowIndex);
//起始列索引
int colIndex = firstRow.FirstCellNum;
//获取列数
int colCount = firstRow.LastCellNum;
DataColumn dc = null;
//获取列数
for (int j = colIndex; j < colCount; j++)
{
dc = new DataColumn(firstRow.GetCell(j).StringCellValue);
dt.Columns.Add(dc);
}
//跳过第一行列名
rowIndex++;
for (int k = rowIndex; k <= rowCount; k++)
{
DataRow dr = dt.NewRow();
row = sheet.GetRow(k);
for (int l = colIndex; l < colCount; l++)
{
if (row.GetCell(l) == null)
{
continue;
}
else {
row.GetCell(l).SetCellType(CellType.String);
//stuUser.setPhone(row.getCell(0).getStringCellValue());
dr[l] = row.GetCell(l).StringCellValue;
}
//dr[l] = row.GetCell(l).StringCellValue;

}
dt.Rows.Add(dr);
}
ds.Tables.Add(dt);
}
sheet = null;
workbook = null;
fileStream.Close();
fileStream.Dispose();
}
catch (Exception ex)
{
throw;
}
return ds;
}

3.调用方法：

DataSet ds = ImportExcel(“excel文件路径“);