java oracle excel_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中连接池的代码如下：

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

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

}

}

}