java excel 导入oracle_java代码实现excel输到导入oracle

import java.io.File;

import java.io.FileInputStream;

import java.io.IOException;

import java.io.InputStream;

import jxl.Cell;

import jxl.Sheet;

import jxl.Workbook;

import jxl.read.biff.BiffException;

/**

* excel数据导入到oracle

* @author sh

* 2010-05-11

*/

public class InsertData {

public static void main(String[] args) throws Exception {

InsertData in = new InsertData();

in.insert("F:/myJob/hah.xls","information");

}

/**

*

* @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

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();

}

}

Util类

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

/**

* Oracle数据库连接

*

* @author sh 2010-05-11

*/

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:XE";

/** Oracle数据库连接驱动 */

private final static String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";

/** 数据库用户名 */

private final static String DB_USERNAME = "test";

/** 数据库密码 */

private final static String DB_PASSWORD = "test";

/**

* 获取数据库连接

*

* @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();

}

}

}

分享到：

2012-07-31 13:28

浏览 1090

分类:数据库

评论