首先我们需要一个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中。