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

}