错误原文
The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI
that deals with OLE2 Office Documents. You need to call a different part of POI to process
this data (eg XSSF instead of HSSF)
该错误是由于使用HSSFWorkbook 操作Excel2007引起的。
解决方法如下:
使用HSSFWorkbook操作Excel2003,
使用XSSFWorkbook操作Excel2007。
首先加入引用:
using XLS=NPOI.HSSF.UserModel;//读取Excel2003
using XLSX=NPOI.XSSF.UserModel;//读取Excel2007
读取方法:
public void ProcessRequest(HttpContext context)
{
HttpFileCollection files = context.Request.Files;
if (files.Count > 0)
{
//设置文件名
string fileNewName = guid + "_" + System.IO.Path.GetFileName(files[0].FileName);
//保存文件
string path = context.Server.MapPath("/upload/" + fileNewName);
string newPath = path;
files[0].SaveAs(path);
DataTable dt = new DataTable();
FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read);
if (fileNewName.EndsWith(".xls"))
{
dt = ExcelToTableForXLS(fs);//从XLS文件(excel2003)读取数据
}
if (fileNewName.EndsWith(".xlsx"))
{
dt = ExcelToTableForXLSX(fs);//从XLSX文件(excel2007)读取数据
}
if (dt != null && dt.Rows.Count > 0)
{
//其他操作
}
}
}
/// <summary>
/// 读取数据到XLSX文件(excel2007)
/// </summary>
/// <param name="fs"></param>
/// <returns></returns>
public DataTable ExcelToTableForXLSX(FileStream fs)
{
DataTable dt = new DataTable();
XLSX.XSSFWorkbook xssfworkbook = new XLSX.XSSFWorkbook(fs);
if (xssfworkbook!=null)
{
XLSX.XSSFSheet sheet = (XLSX.XSSFSheet)xssfworkbook.GetSheetAt(0);
if (sheet!=null)
{
XLSX.XSSFRow firstRow = (XLSX.XSSFRow)sheet.GetRow(0);
#region 取出列名
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
//取出列名
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
XLSX.XSSFCell cell = (XLSX.XSSFCell)firstRow.GetCell(i);
if (cell != null)
{
string cellValue = cell.StringCellValue;
if (cellValue != null)
{
DataColumn column = new DataColumn(cellValue);
dt.Columns.Add(column);
}
}
}
#endregion
#region 取出所有数据到DataTable
//取出所有数据到DataTable
int rowCount = sheet.LastRowNum;
for (int i = 1; i <= rowCount; ++i)
{
XLSX.XSSFRow row = (XLSX.XSSFRow)sheet.GetRow(i);
if (row == null) continue; //没有数据的行默认是null
DataRow dataRow = dt.NewRow();
int m = 0;
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
if (row.GetCell(j) != null && !string.IsNullOrWhiteSpace(row.GetCell(j).ToString()))
{
dataRow[j] = row.GetCell(j).ToString();
}
else
{
m++;
}
}
if (m < cellCount)
{
dt.Rows.Add(dataRow);
}
}
#endregion
}
}
return dt;
}
/// <summary>
/// 读取数据到XLS文件(excel2003)
/// </summary>
/// <param name="fs"></param>
/// <returns></returns>
public DataTable ExcelToTableForXLS(FileStream fs)
{
DataTable dt = new DataTable();
XLS.HSSFWorkbook workbook = new XLS.HSSFWorkbook(fs);
if (workbook != null)
{
XLS.HSSFSheet sheet = (XLS.HSSFSheet)workbook.GetSheetAt(0);
if (sheet != null)
{
XLS.HSSFRow firstRow = (XLS.HSSFRow)sheet.GetRow(0);
#region 取出列名
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
//取出列名
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
XLS.HSSFCell cell = (XLS.HSSFCell)firstRow.GetCell(i);
if (cell != null)
{
string cellValue = cell.StringCellValue;
if (cellValue != null)
{
DataColumn column = new DataColumn(cellValue);
dt.Columns.Add(column);
}
}
}
#endregion
#region 取出所有数据到DataTable
//取出所有数据到DataTable
int rowCount = sheet.LastRowNum;
for (int i = 1; i <= rowCount; ++i)
{
XLS.HSSFRow row = (XLS.HSSFRow)sheet.GetRow(i);
if (row == null) continue; //没有数据的行默认是null
DataRow dataRow = dt.NewRow();
int m = 0;
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
if (row.GetCell(j) != null && !string.IsNullOrWhiteSpace(row.GetCell(j).ToString()))
{
dataRow[j] = row.GetCell(j).ToString();
}
else
{
m++;
}
}
if (m < cellCount)
{
dt.Rows.Add(dataRow);
}
}
#endregion
}
}
return dt;
}