Excel导入数据(97--2003版本)的ExcelHelper

首先确定excel的版本为97~2003版本

其次创建一个帮助类——ExcelHelper

//单个sheet
public static DataTable AnalysisExcel(string fileName,string sheetName)
{
    using(Stream stream=new FileStream(fileName,FileMode.Open))
    {
        HSSFWorkbook work=new HSSFWorkbook(stream);
        int sheetCount=wook.Count;
        ISheet spusheet=work.GetSheet("spu");//spu为sheet名称
        DataTable sputable=new DataTable();
    }
    return new DataTable();  
}

//多个sheet
public static List<DataTable> AnalysisExcel(Stream fileStream,List<String> sheetNames)
{
    HSSFWorkbook work=new HSSFWorkboox(fileStream);
    int sheetCount=work.Count;
    List<DataTable> tables=new List<DataTable>();
    foreach(string sheetname in sheetNames)
    {
        tables.Add(GetDataTable(work,sheetName));
    }
    return tables;
}  

//获取到datatable
public static DataTale GetDataTable(HSSFWorkbook work,string sheetName)
{
  ISheet sheet=work.GetSheet(sheetName);
  DataTable dbTable=new DataTable();
  int firstRowNum =sheet.FirstRowNum;
  IRow titleRow=sheet.GetRow(firstRowNum);
  List<ICell> titleCell=titleRow.Cells;
  foreach(var item in titleCell)
  {
      string titleName=item.StringCellValue;
      DataColumn col=new DataColumn(titleName);
      dbTable.Columns.Add(col);    
  }              
  for(int i=1;i<=sheet.LastRowNum;i++)
  {
      DataRow dbRow=dbTable.NewRow();
      IRow row=sheet.GetRow(i);
      if(row!=null)
      {
           for(int j=1;j<titleCell.Count;j++)
           {
                ICell cell=row.GetCell(j);
                dbRow[j]=cell==null?"":cell.ToString();
           }
           dbTable.Rows.Add(dbRow);
      }
      else 
           continue; 
  }
  return dbTable;
}

 

引用方法如下：

Stream stream=Request.Files[0].InputStream;
List<string> sheetNames=new List<string>();
sheetNames.Add("spu");
sheetNames.Add("sku");
List<DataTable> tables=ExcelHelper.AnalysisExcel(stream,sheetName);
DataTable spuTable=table[0];
DataTable skuTable=table[1];
............此处省略