java如何将Excel表格中内容写入oracle数据库

首先我们需要一个jxl.jar文件，下载地址：http://www.andykhan.com/jexcelapi/download.html

数据库连接jar包根据你安装的oracle数据库版本选择jar包，class12.jar,ojdbc4.jar或者ojdbc5.jar应该都可以的。

 例如，我们想将如下excel表中的数据导入本机数据库中t_excel表中

将jxl.jar和class12.jar导入项目中

整个代码主要分如下三部分

1.TablePostCode类，是省市与邮政编码

class TablePostCode{String Area;String PostCode;public String getArea(){return Area;}public void setArea(String Area){this.Area = Area;}public String getPostCode(){return PostCode;}public void setPostCode(String PostCode){this.PostCode = PostCode;}}

2.ConnectionOrcl类，连接数据库执行SQL语句

class ConnectionOrcl{private Connection conn;private Statement stat;private String driver = "oracle.jdbc.driver.OracleDriver";public void connToTable(List<TablePostCode> LTPC){try{//数据库连接Class.forName(driver);conn = java.sql.DriverManager.getConnection("jdbc:oracle:thin:@172.17.219.210:1521:ora11g", "syz123", "syz123");stat = conn.createStatement();//遍历list，插入数据库for(TablePostCode TPC : LTPC){StringBuffer sql = new StringBuffer();//构造sql语句sql.append("insert into t_excel (area,postcode) values ('");String name = TPC.getArea();String pc = TPC.getPostCode();sql.append(name).append("',");sql.append("'").append(pc).append("')");//执行sql语句stat.execute(sql.toString());}}catch(ClassNotFoundException e){e.printStackTrace();}catch(SQLException e){e.printStackTrace();}finally{if(null != stat){try {stat.close();} catch (SQLException e){e.printStackTrace();}}if(null != conn){try {conn.close();} catch (SQLException e) {e.printStackTrace();}}}}}

3.读取excel表格中内容

public class ExcelToTable {/** * @param args */public static void main(String[] args){ExcelToTable ETT = new ExcelToTable();List<TablePostCode> LTPC = ETT.taPCL();ConnectionOrcl corcl = new ConnectionOrcl();corcl.connToTable(LTPC);}public List<TablePostCode> taPCL(){List<TablePostCode> LTPC = new ArrayList<TablePostCode>();Workbook book = null;try{//选取excel文件book = Workbook.getWorkbook(new File("c:\\1.xls"));//选取excel文件中的第一个工作薄Sheet sheet = book.getSheet(0);//得到excel表行数int RowNum = sheet.getRows();for(int i=1;i<RowNum;i++){//获得excel表中第1列第i行单元格Cell cell0 = sheet.getCell(0, i);//获得excel表中第2列第i行单元格Cell cell1 = sheet.getCell(1, i);//获得cell0单元格内容String Area = cell0.getContents();//获得cell1单元格内容String PostCode = cell1.getContents();TablePostCode TPC = new TablePostCode();TPC.setArea(Area);TPC.setPostCode(PostCode);LTPC.add(TPC);}}catch(Exception e){e.printStackTrace();}finally{if(null != book){book.close();}}return LTPC;}}

复制excel表格到C盘目录下，重命名为1.xls,执行程序，即可将exce表格中数据插入oracle表t_excel中。