Java高效实现批处理Ecel数据导入数据库代码

1、(SQLServer)库入数据处理Excel敌据导批Java实现注：这是我Z前写的Java实现批处理Excel数据导入数据库代码绝对原创(掀 初发表于百度帕客)可以实现批址处理.效率很高.便用时里面有些(尤其那些红色部分)地方需耍改为自己的数 据信息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秒(系数6000)*/public class ExcelReadPre public static void main(StringQ args) ExcelReadPre cr = new ExcelReadPre();long start = System.currentTimeMillis();/ 记录开始时间cr.testRun();/ 开始执行long 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();*执行指定的Sql*/private String url = jdbc:sqlserver:/:1433;database=moble_db;/fl(2的数据库名称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,烫屋？猫?);/这里数据库的用户名和密码 catch (Exception e) IITODO Auto-generated catch blocke.printStackTrace();严*导入一指定文件数据*批处理SQL* para

6、m filePath*/public void writeToDb(String filePath) /这里是我口己的表结构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/防止一次数址过大.导致内存泄漏 pst.executeBatch();con .commit();pst.clearBatch();i=0;continue;SQL命令加入命令列表把一个/ 执行批 更新 pst.executeBatch();语句执行完毕.提交本爭务/mitO;pst.clearBatch();pst.close();/ System.gcO; catch (SQLException e) / TODO Auto-generated catch blocke.printStackTrace();严readEx

9、cel*方法名：*参数:filePath为Execl文件的绝对略径放在数组中返回数据.用途：读取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.