JAVA EXCEL 数据 导入 ORACLE

public class ImportExcelToOracle {

private static final Logger logger = Logger.getLogger(ImportExcelToOracle.class);

private static String tableName="EMP_BIRTHDAY_REMIND_RELATION";

public static void main(String[] args) {

Connection con = null;

DatabaseMetaData dmd = null ; // 数据库元数据

ResultSet result = null;

PreparedStatement pst = null;

ResultSetMetaData rsmd = null;

Statement smt = null;

try{

logger.debug("start load file-------------------------");

String separator = File.separator ;

InputStream in = null; 

in = new FileInputStream("C:" +separator + "Users"

+ separator +"temp"+ separator +"Desktop"

+ separator +"员工生日提醒"+ separator 

+"人员情况-生日发送.xls");//创建输入

jxl.Workbook rwb = Workbook.getWorkbook(in);

Sheet[] sheet = rwb.getSheets();

Sheet rs = rwb.getSheet(0); //读取第一个sheet

int colNum = rs.getColumns();//列数

int rowNum = rs.getRows();//行数

logger.debug("colNum: "+ colNum + " ,rowNum: " + rowNum);

logger.debug("JDBC strat loading. ...... ");

String url = "jdbc:oracle:thin:@114.251.247.78:11101:DEV"; 

String user = "apps";

String password = "apps_dev";

Class.forName("oracle.jdbc.driver.OracleDriver");

con = DriverManager.getConnection(url,user, password);

con.setAutoCommit(false);  // 关闭事务自动提交

logger.debug("Connection : " + con);

dmd = con.getMetaData() ; // 实例化对象

smt = con.createStatement();

pst = con.prepareStatement("select * from " + tableName);

pst.execute();  //这点特别要注意:如果是Oracle而对于mysql可以不用加.

result = pst.executeQuery();

logger.debug("result : " + result);

if(result != null || "".equals(result)){

logger.debug("delete " + tableName + " data ......");

smt.execute("DELETE FROM  " + tableName + "");

con.commit();

}

        rsmd = pst.getMetaData();

        int colunmCount = rsmd.getColumnCount(); //统计列数

        for(int i = 1; i<colunmCount; i++){

         logger.debug("Table colunm name : " + rsmd.getColumnName(i) + "  ,colunm type : " + rsmd.getColumnTypeName(i));

        }

        

String  sql = insertData(rowNum,colNum,rs);

String strValue = "";

pst = (PreparedStatement) con.prepareStatement(sql.toString());

for(int i=0;i<rowNum;i++){

logger.debug("########################## index row : " + i + "##########################");

if(i == 0 || i == 1){

continue;

}

strValue="";

for(int j=0; j<colNum; j++){

Cell cc = rs.getCell(j, 1);

String name = cc.getContents();

Cell c = rs.getCell(j, i);

strValue = c.getContents();

strValue = strValue.trim().replace("#N/A", "");

logger.debug("index: " + j+1 + "  ," + name + ": " + strValue);

pst.setString(j+1, strValue.trim().toString());

}

pst.addBatch();

}

int[] count = pst.executeBatch();

logger.debug("#############  success insert: " + count.length + "  article record  ##################");

con.commit();

if(pst!=null){

pst.close();

}

con.close();

}catch(Exception e){

logger.error(e);

}

}

//当然也可以做出从 TABLE 里拿字段的

private static String insertData(int rowNum, int colNum,Sheet rs) {

logger.debug("|---->insertData(int rowNum: " + rowNum + " , int colNum: " + colNum + ")");

//可以做成可配置文件

String colSql="";

String colValue="";

String insertData = "";

for(int i=0; i<colNum; i++){

Cell c = rs.getCell(i, 1);

String strValue = c.getContents();

strValue = strValue.trim().replace("#N/A", "");

colSql += "" + strValue +"";

colValue += ""+"?";

if(i < colNum -1){

colSql +=",";

colValue += ",";

}

};

logger.debug("colSql:" + colSql);

insertData = "insert into " + tableName +" ("+colSql+") values("+colValue+")";

logger.debug("return : " + insertData);

return insertData;

}

}