java实现 excel 中数据导入 oracle

ORACLE是有一个叫ADI的解决方案

所需的额外包：commons-io-1.4、poi-bin-3.0.2

思路：用户选择要导入的EXCEL文件，上传至WEB服务器。然后将文件存放目录传给POI类。通过对row循环取到cell的值，最后insert到ORACLE中。

public boolean saleDeptToDB(String spreadSheet)throws HekException{

boolean flag = false;
IDBConn db = DBConn.getInstance();
IDBOperate dbOp = DBOperate.getInstance();

Connection conn = db.getConn();
PreparedStatement pstmt = null;
try{
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(spreadSheet));
HSSFSheet sheet = workbook.getSheetAt(0);

String sql = "insert into tableName(DEPT_ID,DEPT_CODE,DEPT_DESC,ITEM_NO,ITEM_CATE,BUDGET_QTY,BUDGET_AMOUNT,TIME_ID)";
sql +="values(?,?,?,?,?,?,?,?)";

pstmt = conn.prepareStatement(sql);

for(int rowNumOfSheet=1;rowNumOfSheet
HSSFRow rowOfSheet = sheet.getRow(rowNumOfSheet);
HekSaleDeptManual hek = new HekSaleDeptManual();

HSSFCell cell0 = rowOfSheet.getCell((short)0);
if(cell0 != null)hek.setDeptId((int)cell0.getNumericCellValue());

HSSFCell cell1 = rowOfSheet.getCell((short)1);
if(cell1 != null)hek.setDeptCode(cell1.getRichStringCellValue().toString());

HSSFCell cell2 = rowOfSheet.getCell((short)2);
if(cell2 != null)hek.setDeptDesc(cell2.getRichStringCellValue().toString());

HSSFCell cell3 = rowOfSheet.getCell((short)3);
if(cell3 != null)hek.setItemNo(cell3.getRichStringCellValue().toString());

HSSFCell cell4 = rowOfSheet.getCell((short)4);
if(cell4 != null)hek.setItemCate(cell4.getRichStringCellValue().toString());

HSSFCell cell5 = rowOfSheet.getCell((short)5);
if(cell5 != null)hek.setBudgetQty(cell5.getNumericCellValue());

HSSFCell cell6 = rowOfSheet.getCell((short)6);
if(cell6 != null)hek.setBudgetQty(cell6.getNumericCellValue());

HSSFCell cell7 = rowOfSheet.getCell((short)7);
if(cell7 != null)hek.setTimeID(cell7.getRichStringCellValue().toString());

dbOp.insertBathHekDept(pstmt, hek);
}
pstmt.executeBatch();
flag = true;
conn.commit();
}catch(SQLException ex){
db.rollbackTransaction(conn);
System.out.println("recordToDB Error: "+ex);
}catch(IOException ioex){
System.out.println("saleDeptToDB read file Error: "+ioex);
}finally{
db.closePstmt(pstmt);
db.closeConn(conn);
}
return flag;
}