从Excel中读取数据导入到数据库中

package com.nciic.ImportData; import java.io.BufferedWriter; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Calendar; import java.util.HashMap; import java.util.Iterator; import java.util.TimeZone; import java.util.Map.Entry; import jxl.Range; import jxl.Sheet; import jxl.Workbook; import com.nciic.log.util.DBUtil; public class OperExcel { public static void InsertData(String sjly, File excelFile, String tableName) { try { Workbook rwb = null; //创建输入流 InputStream stream = new FileInputStream(excelFile); //获取Excel文件对象 rwb = Workbook.getWorkbook(stream); //选择第一个工作表 Sheet sheet1 = rwb.getSheet(0); ArrayList<GsxxImpVO> gsxximplist = new ArrayList<GsxxImpVO>(); GsxxImpVO gsxximp = null; String str = null; Connection conn = DBUtil.getOracleConnection(); TimeZone tz = TimeZone.getTimeZone("Asia/Shanghai"); TimeZone.setDefault(tz); for (int j = 1; j < sheet1.getRows(); j++) { gsxximp = new GsxxImpVO(); for (int k = 0; k < sheet1.getColumns(); k++) { str = sheet1.getCell(k, j).getContents(); Range[] ranges = sheet1.getMergedCells(); for (Range r : ranges) { if (j > r.getTopLeft().getRow() && j <= r.getBottomRight().getRow() && k == r.getTopLeft().getColumn()) { str = sheet1.getCell(r.getTopLeft().getColumn(), r.getTopLeft().getRow()).getContents(); } } if (k == 0) { gsxximp.setXM(str); } else if (k == 1) { gsxximp.setGMSFHM(str); } else if (k == 2) { gsxximp.setGSXX(str); } else if (k == 3) { gsxximp.setBT(str); } else if (k == 4) { gsxximp.setWZ(str); } else if (k == 5) { gsxximp.setWZID(str); } else if (k == 6) { gsxximp.setKWMC(str); } else if (k == 7) { gsxximp.setKDSJ(str); } } gsxximplist.add(gsxximp); } doImportBatch(gsxximplist, tableName, sjly,conn); // if (gsxximplist != null && gsxximplist.size() > 0) { // for (int i = 0; i < gsxximplist.size(); i++) { // doImport(gsxximplist.get(i), tableName, sjly); // System.out.println("成功导入" + (i + 1) + "条————————"); // } // } } catch (Exception e2) { e2.printStackTrace(); } } public static void doImportBatch(ArrayList<GsxxImpVO> impvo, String tableName, String sjly,Connection conn) { doImportFromfzsjBatch(conn, impvo, tableName, sjly); } /** * 数据来源：方正提供 * @param conn * @param impvo * @param tableName * @param sjly */ public static void doImportFromfzsjBatch(Connection conn, ArrayList<GsxxImpVO> impvo, String tableName, String sjly) { String Nowtime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss") .format(Calendar.getInstance().getTime()); String id = ""; ResultSet rs; Statement stmt = null; String savesql=" INSERT INTO " + tableName+ "(id,XM,GMSFHM,GSXX,BT,WZ,WZID,KWMC,KDSJ,SJLY,RKSJ) "+" VALUES(?,?,?,EMPTY_CLOB(),?,EMPTY_CLOB(),?,?,?,'00',?)"; PreparedStatement pstmt; HashMap<String,String> mapgsxx = new HashMap<String, String>(); HashMap<String, String> mapwz = new HashMap<String,String>(); try { pstmt = conn.prepareStatement(savesql); for(int i=0;i< impvo.size();i++){ String sqlid = "select ZX_RES.SEQ_tablename_ID.NEXTVAL from dual"; try { stmt = conn.createStatement(); rs = stmt.executeQuery(sqlid); while (rs.next()) { id = rs.getString(1); } rs.close(); stmt.close(); } catch (SQLException e2) { e2.printStackTrace(); } pstmt.setString(1, id); pstmt.setString(2, impvo.get(i).getXM()); pstmt.setString(3, impvo.get(i).getGMSFHM()); pstmt.setString(4, impvo.get(i).getBT()); pstmt.setString(5, impvo.get(i).getWZID()); pstmt.setString(6, impvo.get(i).getKWMC()); pstmt.setString(7, impvo.get(i).getKDSJ()); pstmt.setString(8, Nowtime); pstmt.addBatch(); mapgsxx.put(id,impvo.get(i).getGSXX()); mapwz.put(id,impvo.get(i).getWZ()); if (i % 20 == 0 || i == (impvo.size()-1)) {// 每20条数据执行一次、到最后必须执行一次 pstmt.executeBatch();//执行批 System.out.println("success import "+(i+1)+"条记录,sjly is :00"); try{//这里一定要捕捉异常 conn.commit();// 提交事务 }catch(SQLException exc){ conn.rollback();// 在批处理命令中，如果有一个命令出现了错误，则回滚 } } } pstmt.close(); //直接for update Iterator it = mapgsxx.entrySet().iterator(); int j=0; while(it.hasNext()){ ++j; Entry entry = (Entry) it.next(); String key = (String) entry.getKey() ; String update_sql = "select GSXX from " + tableName + " where ID='" + key + "' for update"; try { conn.setAutoCommit(false); stmt = conn.createStatement(); rs = stmt.executeQuery(update_sql); if (rs.next()) { /* 取出此CLOB对象 */ oracle.sql.CLOB clob = null; clob = (oracle.sql.CLOB) rs.getClob("GSXX"); /* 向CLOB对象中写入数据 */ BufferedWriter out = new BufferedWriter(clob .getCharacterOutputStream()); try { out.write(entry.getValue().toString()); out.flush(); out.close(); conn.commit(); conn.setAutoCommit(true); System.out.println("update success j:"+j); } catch (IOException e) { e.printStackTrace(); } } rs.close(); stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } Iterator itwz = mapwz.entrySet().iterator(); int n=0; while(itwz.hasNext()){ ++n; Entry entry = (Entry) itwz.next(); String key = (String) entry.getKey() ; String update_sql = "select WZ from " + tableName + " where ID='" + key + "' for update"; try { conn.setAutoCommit(false); stmt = conn.createStatement(); rs = stmt.executeQuery(update_sql); if (rs.next()) { /* 取出此CLOB对象 */ oracle.sql.CLOB clob = null; clob = (oracle.sql.CLOB) rs.getClob("WZ"); /* 向CLOB对象中写入数据 */ BufferedWriter out = new BufferedWriter(clob .getCharacterOutputStream()); try { out.write(entry.getValue().toString()); out.flush(); out.close(); conn.commit(); conn.setAutoCommit(true); System.out.println("update success n:"+n); } catch (IOException e) { e.printStackTrace(); } } rs.close(); stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } conn.close(); } catch (SQLException e) { e.printStackTrace(); } finally { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } /** * 单条处理 * @param impvo * @param tableName * @param sjly */ // public static void doImport(GsxxImpVO impvo, String tableName, String sjly) { // //导入数据 // Connection conn = DBUtil.getOracleConnection(); // ResultSet rs; // Statement stmt = null; // TimeZone tz = TimeZone.getTimeZone("Asia/Shanghai"); // TimeZone.setDefault(tz); // String Nowtime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss") // .format(Calendar.getInstance().getTime()); // String id = ""; // String sqlid = "select ZX_RES.SEQ_tablename_ID.NEXTVAL from dual"; // try { // stmt = conn.createStatement(); // rs = stmt.executeQuery(sqlid); // while (rs.next()) { // id = rs.getString(1); // } // rs.close(); // stmt.close(); // } catch (SQLException e2) { // e2.printStackTrace(); // }