ASP.NET实现上传Excel功能

 

 $(document).ready(function () {

  $("#Import").click(function () {

  var filename = $("#FileUpload1").val();

  if (filename == '') {

   alert('请选择上传的EXCEL文件');

   return false;

  }

  else {

   var exec = (/[.]/.exec(filename)) ? /[^.]+$/.exec(filename.toLowerCase()) : '';

   if (!(exec == "xlsx" || exec == "xls")) {

   alert("文件格式不对，请上传Excel文件!");

   return false;

   }

  }

  return true;

  });

 });

protectedvoidImpClick(objectsender, EventArgs e)

 {

  try

  {

  #region 校验

  var fileName =this.FileUpload1.FileName;

  if(string.IsNullOrWhiteSpace(fileName))

  {

   //提示信息

   ShowMsgHelper.Alert("请选择上传Excel文件");

   return;

  }

  //获取上传文件扩展名称

  if(!(fileName.IndexOf(".xlsx") > 0 || fileName.IndexOf(".xls") > 0))

  {

   ShowMsgHelper.Alert("上传文件格式不正确，请核对！");

   return;

  }

  #endregion

  #region 将Excel文件上传到服务器上临时文件夹中

  //临时文件夹，根目录下/Upload/tmp/，根据自己配置选择

  stringpath = Server.MapPath("~/") +"Upload\tmp\";

  stringretStr=UploadHelper.FileUpload(path,this.FileUpload1);

  if(!retStr.Equals("上传成功")) {

   ShowMsgHelper.Alert(retStr);

   return;

  }

  #endregion

  #region 读取Excel文件第一个表获取内容并转换成DataTable,删除临时文件，也可以自己加时间戳，维护处理

  DataTable dt =this.ExcelToDataTable(path +this.FileUpload1.FileName,true);

  if(dt ==null) {

   ShowMsgHelper.Alert_Error("获取失败");

   return;

  }

  //示例：获取dt中的值

  stringtest = dt.Rows[0]["name"].ToString();

  stringtest2 = dt.Rows[1]["class"].ToString();

  //删除临时文件

  DirFileHelper.DeleteFile("Upload\tmp\"+ fileName);

  #endregion

  }

  catch(Exception ex) {

  throwex;

  }

 }

 ///

 /// excel路径

 /// 第一行是否是列名

 /// 返回datatable

 publicDataTable ExcelToDataTable(stringfilePath,boolisColumnName)

 {

  DataTable dataTable =null;

  FileStream fs =null;

  DataColumn column =null;

  DataRow dataRow =null;

  IWorkbook workbook =null;

  ISheet sheet =null;

  IRow row =null;

  ICell cell =null;

  intstartRow = 0;

  try

  {

  using(fs = File.OpenRead(filePath))

  {

   // 2007版本

   if(filePath.IndexOf(".xlsx") > 0)

   workbook =newXSSFWorkbook(fs);

   // 2003版本

   elseif(filePath.IndexOf(".xls") > 0)

   workbook =newHSSFWorkbook(fs);

   if(workbook !=null)

   {

   sheet = workbook.GetSheetAt(0);//读取第一个sheet，当然也可以循环读取每个sheet

   dataTable =newDataTable();

   if(sheet !=null)

   {

    introwCount = sheet.LastRowNum;//总行数

    if(rowCount > 0)

    {

    IRow firstRow = sheet.GetRow(0);//第一行

    intcellCount = firstRow.LastCellNum;//列数

    //构建datatable的列

    if(isColumnName)

    {

     startRow = 1;//如果第一行是列名，则从第二行开始读取

     for(inti = firstRow.FirstCellNum; i < cellCount; ++i)

     {

     cell = firstRow.GetCell(i);

     if(cell !=null)

     {

      if(cell.StringCellValue !=null)

      {

      column =newDataColumn(cell.StringCellValue);

      dataTable.Columns.Add(column);

      }

     }

     }

    }

    else

    {

     for(inti = firstRow.FirstCellNum; i < cellCount; ++i)

     {

     column =newDataColumn("column"+ (i + 1));

     dataTable.Columns.Add(column);

     }

    }

    //填充行

    for(inti = startRow; i <= rowCount; ++i)

    {

     row = sheet.GetRow(i);

     if(row ==null)continue;

     dataRow = dataTable.NewRow();

     for(intj = row.FirstCellNum; j < cellCount; ++j)

     {

     cell = row.GetCell(j);

     if(cell ==null)

     {

      dataRow[j] ="";

     }

     else

     {

      //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)

      switch(cell.CellType)

      {

      caseCellType.Blank:

       dataRow[j] ="";

       break;

      caseCellType.Numeric:

       shortformat = cell.CellStyle.DataFormat;

       //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理

       if(format == 14 || format == 31 || format == 57 || format == 58)

dataRow[j] = cell.DateCellValue;

else      dataRow[j] = cell.NumericCellValue;

break;

      caseCellType.String:

dataRow[j] = cell.StringCellValue;

break;

      }

     }

     }

     dataTable.Rows.Add(dataRow);

    }

    }

   }

   }

  }

  returndataTable;

  }

  catch(Exception)

  {

  if(fs !=null)

  {

   fs.Close();

  }

  returnnull;

  }

 }