public static DataTable ExcelStreamToDataTable(Stream stream, string type, string sheetName)
{
using (stream)
{
int temp = 0;
try
{
MemoryStream outstream = new MemoryStream();
if (type == "xls")
{
const int bufferLen = 1000000;
byte[] buffer = new byte[bufferLen];
int count = 0;
while ((count = stream.Read(buffer, 0, bufferLen)) > 0)
{
temp++;
outstream.Write(buffer, 0, count);
}
}
DataTable dt = new DataTable();
IWorkbook workbook;
//xls使用HSSFWorkbook类实现，xlsx使用XSSFWorkbook类实现
switch (type)
{
case "xlsx":
workbook = new XSSFWorkbook(stream);
break;
default:
workbook = new HSSFWorkbook(outstream);
break;
}
ISheet sheet = null;
//获取工作表 默认取第一张
if (string.IsNullOrWhiteSpace(sheetName))
sheet = workbook.GetSheetAt(0);
else
sheet = workbook.GetSheet(sheetName);
if (sheet == null)
return null;
IEnumerator rows = sheet.GetRowEnumerator();
#region 获取表头
IRow headerRow = sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
ICell cell = headerRow.GetCell(j);
if (cell != null)
{
dt.Columns.Add(cell.ToString());
}
else
{
dt.Columns.Add("");
}
}
#endregion
#region 获取内容
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
//判断单元格是否为日期格式
if (row.GetCell(j).CellType == NPOI.SS.UserModel.CellType.Numeric && HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))
{
if (row.GetCell(j).DateCellValue.Year >= 1970)
{
dataRow[j] = row.GetCell(j).DateCellValue.ToString();
}
else
{
dataRow[j] = row.GetCell(j).ToString();
}
}
else
{
dataRow[j] = row.GetCell(j).ToString();
}
}
}
dt.Rows.Add(dataRow);
}
#endregion
return dt;
}
catch (Exception ex)
{
throw;
}
}
}
当while循环到某次后，运行到while ((count = stream.Read(buffer, 0, Convert.ToInt32(bufferLen))) > 0)，就报出 “无法从传输连接中读取数据: 连接已关闭。”异常
当传进来的流文件大时，就会报错，小的话就不会报错