/// <summary>
/// 使用NPOI从Stream中读取DataSet
/// </summary>
/// <param name="stream"></param>
/// <returns></returns>
public static DataSet LoadExcel(Stream stream)
{
try
{
IWorkbook book = new HSSFWorkbook(stream);
DataSet ds = new DataSet();
foreach (ISheet sheet in book)
{
if (sheet.LastRowNum < 1)
continue;
DataTable dt = new DataTable(sheet.SheetName);
IRow headerRow = sheet.GetRow(0);//第一行当你是表头
int cellCount = headerRow.LastCellNum;
int rowCount = sheet.LastRowNum;
//处理表头
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
if (headerRow.GetCell(i) != null)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).ToString());
dt.Columns.Add(column);
}
}
//处理表数据
for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
{
IRow row = sheet.GetRow(i);
DataRow dataRow = dt.NewRow();
if (row != null)
{
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
dataRow[j] = row.GetCell(j).ToString();
}
}
dt.Rows.Add(dataRow);
}
ds.Tables.Add(dt);
}
return ds;
}
catch (Exception ex)
{
return new DataSet();
}
}