Java代码实现excel数据导入到Oracle

1.首先需要两个jar包jxl.jar,ojdbc.jar（注意版本，版本不合适会报版本错误）

2.代码：

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

}

 

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

}

 

 

评论

1 楼 天辰2013 2015-02-04