JAVA实现Excel表数据导入ORACLE数据库

 在一个JAVA应用中，如果要把己知的一张Excel表数据导入一个己知的ORACLE表中，可以根据ODBC读取这张Excel表等操作来完成相关功能。具体代码如下：

public class InsertData { public static void main(String[] args) {  Connection myConnSDB = null;  PreparedStatement psInsertData = null;  int InsertData = 0;  try {   Class.forName("oracle.jdbc.driver.OracleDriver");   myConnSDB = DriverManager.getConnection(     "jdbc:oracle:thin:@192.168.1.202:1521:ORCL", "system",     "ORCL");   System.out.println(myConnSDB);  } catch (Exception e) {   e.printStackTrace();  }  try {   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");   Connection con = DriverManager.getConnection("jdbc:odbc:Book1");   Statement st = con.createStatement();   ResultSet rs = st.executeQuery("SELECT * FROM [Sheet1$]");   psInsertData = myConnSDB.prepareStatement("INSERT INTO test1"     + " (NAME,PASS)" + "  VALUES " + " (?,?) ");   while (rs.next()) {    psInsertData.clearParameters();    psInsertData.setString(1, rs.getString(1));    psInsertData.setString(2, rs.getString(2));    psInsertData.executeUpdate();   }   System.out.println("" + InsertData);   st.close();   con.close();  } catch (Exception ex) {   System.err.print("Exception: ");   System.err.println(ex.getMessage());   ex.printStackTrace();  } finally {   try {    if (psInsertData != null) {     psInsertData.close();    }    if (myConnSDB != null) {     myConnSDB.close();    }   } catch (SQLException e) {    e.printStackTrace();   }  } }}

在一个WEB应用中,根据一个Excel表，一个ORACLE表，导入ORACLE中，这里用到了连接池，jxl.jar和commons-dbutils-1.1.jar。

1.TOMCAT中连接池的代码如下：

<Context path="/ElsToOra" docBase="E:\workspace\ExcelToOraChang\WebRoot" debug="0"><Resource name="oracleds" auth="Container"  type="javax.sql.DataSource"   driverClassName="oracle.jdbc.driver.OracleDriver"    url="jdbc:oracle:thin:@192.168.1.202:1521:ORCL"    username="system"    password="ORCL"    maxActive="20"    maxIdle="10"    maxWait="-1"  /></Context>

2.业务连接代码如下：

package test;import java.io.File;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.util.List;import javax.naming.Context;import javax.naming.InitialContext;import javax.naming.NamingException;import javax.sql.DataSource;import jxl.Sheet;import jxl.Workbook;import org.apache.commons.dbutils.QueryRunner;public class XlsToOra {  private String xls; private String table; private DataSource ds; public XlsToOra() { } public XlsToOra(String xls, String table) {  this.xls = xls;  this.table = table; } public String getTable() {  return table; } public void setTable(String table) {  this.table = table; } public String getXls() {  return xls; } public void setXls(String xls) {  this.xls = xls; } public void init() {  Context initContext;  try {   initContext = new InitialContext();   ds = (DataSource) initContext.lookup("java:/comp/env/oracleds");   System.out.println(ds);  } catch (NamingException e) {   e.printStackTrace();  } } public boolean toOracle() throws Exception {  Workbook book = Workbook.getWorkbook(new File(xls));  Sheet sheet = book.getSheet(0);  int rows = sheet.getRows();  int cols = sheet.getColumns();  String sql = "INSERT INTO " + table + " VALUES (";  for (int i = 0; i < cols - 1; i++) {   sql += "?,";  }  sql += "?)";  init();  QueryRunner queryRun = new QueryRunner(ds);  for (int i = 1; i < rows; i++) {   String param[] = new String[cols];   for (int j = 0; j < cols; j++) {    param[j] = sheet.getCell(j, i).getContents();   }   queryRun.update(sql, param);  }  return false; } public List getAttribute() throws Exception {  List list = null;  String sql = "SELECT * FROM " + table;  PreparedStatement ps = getConnection().prepareStatement(sql);  ResultSet rs = ps.executeQuery();  ResultSetMetaData metaData = rs.getMetaData();  int len = metaData.getColumnCount();  for (int i = 1; i <= len; i++) {   System.out.print(metaData.getColumnName(i) + "\t");  }  while (rs.next()) {   System.out.println();   for (int i = 1; i <= len; i++) {    System.out.print(rs.getObject(i) + "\t\t");   }  }  return list; } public static Connection getConnection() throws Exception {  Connection con = null;  Class.forName("oracle.jdbc.driver.OracleDriver");  con = DriverManager.getConnection(    "jdbc:oracle:thin:@192.168.1.202:1521:ORCL", "system", "ORCL");  return con; }}

3. 编写JSP访问业务逻辑的代码：test.jsp

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%><%new test.XlsToOra("d:/test/test2.xls", "test2").toOracle(); %>

4.最后通过访问http://127.0.0.1:8088/ElsToOra/test.jsp，即把d:/test/test2.xls里的数据添加到了ORACLE中test2表中。

例子2：
下载 poi 包

public class TestPoiExcel { static Log log = LogFactory.getLog(TestPoiExcel.class); public static String fileToBeRead="e://test.xls"; public static void main(String argv[]) { try { // 创建对Excel工作簿文件的引用 HSSFWorkbook workbook= new HSSFWorkbook(new FileInputStream(fileToBeRead)); // 创建对工作表的引用。 // 　　本例是按名引用（让我们假定那张表有着缺省名"Sheet1"） // 也可用getSheetAt(int index)按索引引用， // 　　在Excel文档中，第一张工作表的缺省索引是0， // 　　其语句为：HSSFSheet sheet = workbook.getSheetAt(0); HSSFSheet sheet= workbook.getSheet("Sheet1"); int rows = sheet.getPhysicalNumberOfRows(); for (int r = 0; r < rows; r++) { // 　　读取左上端单元 HSSFRow row = sheet.getRow(r); if (row != null) { int cells = row.getPhysicalNumberOfCells(); String value = ""; // log.info(cells); for (short c = 0; c < cells; c++) { HSSFCell cell = row.getCell(c); if (cell != null){ switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA : // break; case HSSFCell.CELL_TYPE_NUMERIC: value += (long)cell.getNumericCellValue()+","; break; case HSSFCell.CELL_TYPE_STRING: value += cell.getStringCellValue()+","; break; default: value +="0,"; } } } //下面可以将查找到的行内容用SQL语句INSERT到oracle log.info(value); } } }catch(Exception e){ System.out.println(e); } } }