Java高效實現(xiàn)批處理Ecel數(shù)據(jù)導入數(shù)據(jù)庫代碼

1、(SQLServer)庫入數(shù)據(jù)處理Excel敵據(jù)導批Java實現(xiàn)注：這是我Z前寫的Java實現(xiàn)批處理Excel數(shù)據(jù)導入數(shù)據(jù)庫代碼絕對原創(chuàng)(掀 初發(fā)表于百度帕客)可以實現(xiàn)批址處理.效率很高.便用時里面有些(尤其那些紅色部分)地方需耍改為自己的數(shù) 據(jù)信息package com.jxl;import jxl.Cell;import jxl.Sheet;import java.io.File;import java.i 0nputStream;import java.sql?;import java.util.Vector;import java.io.Filel nputStream;import

2、jxl .Workbook;/* 耗時 315.469 秒 jxl.jar excel 導入 SQL 包*耗時131.469秒*耗時125.448秒(系數(shù)6000)*/public class ExcelReadPre public static void main(StringQ args) ExcelReadPre cr = new ExcelReadPre();long start = System.currentTimeMillis();/ 記錄開始時間cr.testRun();/ 開始執(zhí)行l(wèi)ong end = System.currentTimeMillis();System.out

3、.println(=耗時：=+ (end - start) /1000.0);public void testRun() Wk0527;/自己存放excel文件的目錄球械柿jOD?F:QJtryFile fileDir = new File(path);File filesQ = fileDir.listFiles();for (int i = 0; i < files.length; i+) System.out.println(P 第？桶?個文件:=+ filesi.getAbsolutePath();this.writeToDb(filesi.getAbsolutePath();i

4、f (this.con != null && !con.isClosed() this.con.close); catch (SQLException e) / TODO Auto-generated catch blocke.printStackTrace();*執(zhí)行指定的Sql*/private String url = jdbc:sqlserver:/:1433;database=moble_db;/fl(2的數(shù)據(jù)庫名稱private Connection con 二 null;PreparedStateme nt pst = null;public v

5、oid getCOnn() / System.out.pri ntln(二二=二=二二=+sql);tryif (con = null | con.isClosedO) Class.forName(com.microsoft.sqlserverjdbc.SQLServerDriver);con = DriverManager.getConnection(url,燙屋？貓?);/這里數(shù)據(jù)庫的用戶名和密碼 catch (Exception e) IITODO Auto-generated catch blocke.printStackTrace();嚴*導入一指定文件數(shù)據(jù)*批處理SQL* para

6、m filePath*/public void writeToDb(String filePath) /這里是我口己的表結(jié)構String sql = insert into msg_mobile (spcode.spname,bizcode,biz name,flow,cost,dow nd ate)valuestrythis.getCOnn();關閉爭務自動捉交 con.setAutoCommit(false);pst = con.prepareStatement(sql);Vector<CellQ> v = readExcel(filePath);int i = 0;for (

7、Ce叩 cells : v) int j = 0;pst.setString(+j, cellsO.getContents();pst.setString(+j, cellsDJ.getContentsO);pst.setString(+j, cellsj.getContents();pst.setString(+j, cellsOJ.getContentsO);pst.setString(+j, cellsOl.getContentsO);pst.setString(+j, cellsUJ.getContentsO);pst.setString(+j, cellsj.getContents(

8、);pst.addBatch(); i+;if(i>6000X/防止一次數(shù)址過大.導致內(nèi)存泄漏 pst.executeBatch();con .commit();pst.clearBatch();i=0;continue;SQL命令加入命令列表把一個/ 執(zhí)行批 更新 pst.executeBatch();語句執(zhí)行完畢.提交本爭務/mitO;pst.clearBatch();pst.close();/ System.gcO; catch (SQLException e) / TODO Auto-generated catch blocke.printStackTrace();嚴readEx

9、cel*方法名：*參數(shù):filePath為Execl文件的絕對略徑放在數(shù)組中返回數(shù)據(jù).用途：讀取Execl*返回類型：Vector*/public static Vector<CellO> readExcel(String filePath) Vector<CellQ> v = new Vector<CellO>()；Workbook rwb = null;tryInputstream is = new FilelnputStream(filePath);rwb = Workbook.getWorkbook(is);/獲取第一張Sheet表Sheet rs = rwb.getSheet(O);fo(int i = 1; i < rs.getRows(); i+)/ for (int j = 0; j < rs.getColumns(); j+) H System.out.print( +(+rs.getCell(j,II k).getContents().trim();v.add(rs.getRow(i);/ 放入一行記錄/ System.out.