java把excel数据写入Oracle数据库,此处需要引入两个文件:ojdbc14.jar,jxl-2.6.10.jar,首先在oracle建立表exceltest,excel文件内容与其对应,此处注意:(excel版本太高了 报错:Exception in thread "main" jxl.read.biff.BiffException: Unable to recognize,把文件保存成excel 97-2003即可)
1.建表
-- Create table create table EXCELTEST ( line1 VARCHAR2(20), line2 VARCHAR2(20), line3 VARCHAR2(20), line4 VARCHAR2(20) )
2.准备好的excel内容(excel 97-2003)
3.java文件:
package excel; import java.io.File; import java.io.IOException; import jxl.Cell; import jxl.Sheet; import jxl.Workbook; import jxl.read.biff.BiffException; /** * excel数据导入到oracle * @author * */ public class excelToOracle { public static void main(String[] args) throws Exception { excelToOracle in = new excelToOracle(); in.insert("F:/test.xls","exceltest"); } /** * * @param path * 要解析的excel文件路径 * @param dataTable * 要写入到数据库中的表名 * @throws BiffException * @throws IOException */ public void insert(String path,String dataTable) throws BiffException, IOException { File file = new File(path); // 创建新的Excel 工作簿 Workbook rwb = null; rwb = Workbook.getWorkbook(file); // 得到工作簿中的第一个表索引即为excel下的sheet1,sheet2,sheet3... Sheet sheet = rwb.getSheets()[0]; int rsColumns = sheet.getColumns();// 列数 int rsRows = sheet.getRows();// 行数 String simNumber = "" ;//每个单元格中的数据 DBUtils jdbc=new DBUtils(); String str="";//拼接要插入的列 for (int j = 0; j <rsColumns; j++) { Cell cell = sheet.getCell(j, 0); simNumber = cell.getContents(); if(j==rsColumns-1){ str += simNumber ; }else{ str += simNumber+","; } } for (int i = 1; i < rsRows; i++) { String sql = "insert into "+dataTable+"("+str+") values(";//拼接sql System.out.println(str); for (int j = 0; j < rsColumns; j++) { Cell cell = sheet.getCell(j, i); simNumber = cell.getContents(); if(j==rsColumns-1){ sql += "'"+ simNumber+"'" ; }else{ sql +="'"+ simNumber+"',"; } } sql += " )"; jdbc.executeUpdate(sql);//执行sql } jdbc.closeStmt(); jdbc.closeConnection(); } }
工具类:
package excel; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; /** * Oracle数据库连接 * * @author */ public class DBUtils { private Connection conn = null; private Statement stmt = null; private ResultSet rs = null; /** Oracle数据库连接 URL */ private final static String DB_URL = "jdbc:oracle:thin:@localhost:1521:orcl"; /** Oracle数据库连接驱动 */ private final static String DB_DRIVER = "oracle.jdbc.driver.OracleDriver"; /** 数据库用户名 */ private final static String DB_USERNAME = "caiyl"; /** 数据库密码 */ private final static String DB_PASSWORD = "123456"; /** * 获取数据库连接 * * @return */ public Connection getConnection() { /** 声明Connection连接对象 */ Connection conn = null; try { /** 使用 Class.forName()方法自动创建这个驱动程序的实例且自动调用DriverManager来注册它 */ Class.forName(DB_DRIVER); /** 通过 DriverManager的getConnection()方法获取数据库连接 */ conn = DriverManager .getConnection(DB_URL, DB_USERNAME, DB_PASSWORD); stmt = conn.createStatement(); } catch (Exception ex) { ex.printStackTrace(); } return conn; } /** * 查询数据部分 * * @return ResultSet */ public ResultSet executeQuery(String sqlStr) { if (sqlStr == null || sqlStr.length() == 0) return null; try { this.getConnection(); rs = stmt.executeQuery(sqlStr); return rs; } catch (SQLException ex) { ex.printStackTrace(); return null; } } /** * 更新数据部分 * * @return 更新是否成功 */ public boolean executeUpdate(String sqlStr) { if (sqlStr == null || sqlStr.length() == 0) return false; try { this.getConnection(); stmt.executeUpdate(sqlStr); return true; } catch (SQLException ex) { ex.printStackTrace(); return false; } finally { try { if (stmt != null) { stmt.close(); } } catch (SQLException e) { e.printStackTrace(); } try { if (conn != null) { conn.close(); } } catch (SQLException e) { e.printStackTrace(); } } } public void closeStmt() { try { if (stmt != null) { stmt.close(); } } catch (Exception e) { e.printStackTrace(); } } /** * 关闭数据库连接 * * @param connect */ public void closeConnection() { try { if (conn != null) { /** 判断当前连接连接对象如果没有被关闭就调用关闭方法 */ if (!conn.isClosed()) { conn.close(); } } } catch (Exception ex) { ex.printStackTrace(); } } }
4.执行效果,查看数据库表: