jsp使用POI 导入Excel到数据库

写一个excelInput.jsp页面（struts）
<html:form action="ExcelInputAction.do?action=addSave" enctype="multipart/form-data">
<tr bgcolor="#F0F0F0">
    <td width="30%" align="right">
     导入文件:
    </td>
    <td>
     <html:file property = "excel_file" size = "30" />
    </td>
</tr>
</html:form>
<tr>
<td><html:errors/></td>
</tr>

ExcelInputAction.java页面
if(action != null && action.equals("addSave")){
    String path = null;
    String myFileName = null;
    FormFile file = eForm.getExcel_file();//取得上传的文件
     System.out.println("11111111111111111111111111111111FormFile----111"+file);
        try {
         java.io.InputStream stream = file.getInputStream();//把文件读入
             String filePath = request.getRealPath("/");//取当前系统路径
             ByteArrayOutputStream baos = new ByteArrayOutputStream();
             OutputStream bos = new FileOutputStream(filePath + "/ExcelInput/" +
                                                     time);//建立一个上传文件的输出流
             myFileName = file.getFileName();//上传的文件名
             path = filePath+"/ReportInput/"+myFileName;//可以自己起一个文件名保存到指定的目录，现在用原文件名
           
             int bytesRead = 0;
             byte[] buffer = new byte[8192];
             while ( (bytesRead = stream.read(buffer, 0, 8192)) != -1) {
               bos.write(buffer, 0, bytesRead);//将文件写入服务器
             }
             bos.close();
             stream.close();
        }catch(Exception e){
             System.err.print(e.getMessage());
        }
     
       message = logic.readExcelToSql(path, myFileName, eForm, userId, userLevel, userName, ip);
}

ExcelInputLogic.java
public String readExcelToSql(String url, String myFileName, ExcelImportForm form, String userId, String userLevel, String userName, String ip){
  
    POIFSFileSystem fs = null;
    HSSFWorkbook wb = null;
    String message = "";
    FileInputStream stream = null;
    try {
     // str取得excel.xls路径
     stream = new FileInputStream(url);
     fs = new POIFSFileSystem(stream);
     wb = new HSSFWorkbook(fs);
    } catch (Exception e) {
     System.out.println("poi ExcelImportLogic---"+e.getMessage());
     if(fs == null) {
      return "<script language=javascript>alert('请下载最新模版，按照要求填写数据!!!');</script>";
     }
    } finally {
     try {
      stream.close();
     } catch (Exception ee) {
      ee.getMessage();
     }
    }

    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFRow row = null;
    HSSFCell cell = null;
    row = sheet.getRow(0);
    cell = row.getCell((short) 0);
       String type = cell.getStringCellValue();//根据文件头判断，文件上传是否正确
    if(type.equals("Excel导入")){
     message = readExcelToSql(sheet)
    }
    return message;
}
//调用ExcelInputLogic.java的另一个方法
public String readExcelToSql(HSSFSheet sheet){
    String message = null;
  
    DbConnection db = null;
    int err = 0;
    int rowNum = 0;
    int count = 0;
try{
        db = new DbConnection();
     HSSFRow row = null;
     HSSFCell cell = null;
     // 名称列。注意：文本列要定义变量为String类型，而数值列要定义变量为Double类型
     String name = "";
     // 数值列
     double qy_capital;

     for (i = 4; i <= rowNum; i++) {//从第5行读取数据
      System.out.println("i = "+i);
      row = sheet.getRow(i);
      if(row == null) {//过滤掉Excel的空行
       continue;     
      }
      //1
      cell = row.getCell((short) 0);
      if(cell == null || cell.getStringCellValue()).trim() == ""){//判断是否为空
          name = "";
      }else{
       name = cell.getStringCellValue();//不为空时取值
      }
      //2
      cell = row.getCell((short) 1);
      if(cell == null) {//数值列不能为空，如果为空赋值0
       numberColumn = 0;
      }else if(cell.getCellType() == HSSFCell.CELL_TYPE_STRING){
       message = "<script language=javascript>alert('第"+(i+1)+"行：＂此＂列输入错误!!!请输入合法数字.');</script>";
       db.rollup();
       return message;
      }else {
       qy_capital = cell.getNumericCellValue();
      }
       
      String sql = "insert into reports(name, numberColumn) values('"+name+"',"+numberColumn+")";
      db.executeUpdate(sql);
     }
     db.commit();
    } catch (Exception e) {
     e.printStackTrace();
    } finally {
     db.close();
    }
    return message;
}

对应的Excel表为aaa.xls

 

感谢：http://hi.baidu.com/yhyweb/blog/item/5473e9fcd465cf83b901a0ae.html