在jsp中实现excel导入oracle的过程 最简单的方法:用jxl读取excel,然后组织数据insert到表里 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 也可以用POI实现参考http://viwo.javaeye.com/blog/66049jxl的看这个http://www.javaeye.com/topic/63001 先将excel上传到服务器上服务器上在解析这个文件然后入库。用到的组件有apache的upload、和poi包! 在Google里搜一下 jxl读取excel,到处是例子 先将文件上传,然后读取上传的文件。分行读取,然后写入数据库。可操作EXCEL的组件 ,楼上已经说了。JXL,POI(推荐,对于一些大数据量的时候,有点用) JAVA文件:ReadExcelTest.java--------------------------------------------------------------package com.mypack;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFCell;import java.sql.*;import java.io.*;import java.util.ArrayList;import java.sql.Connection;public class ReadExcelTest{private Connection con;String DBDriver="oracle.jdbc.driver.OracleDriver"; String url="jdbc:oracle:thin:@192.168.0.1:1521:dbname";String USER="username";String PWD="password";private ArrayList listFirstColum = new ArrayList();private ArrayList listSecondColum = new ArrayList();//连接数据库public ReadExcelTest() throws Exception{try{Class.forName(DBDriver); con=DriverManager.getConnection(url,USER,PWD);}catch(Exception e) {e.printStackTrace();throw(new Exception("Sorry,database connecting failed!"));}}//从excel中读取数据存入ArrayList中public void readExcel(String sFileName){try{HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(sFileName));//读取excel文件HSSFSheet sheet = workbook.getSheetAt(0);//读取第一个sheetfor(int i=0;i<=sheet.getLastRowNum();i++)//循环取得所有行{if(sheet.getRow(i)!=null){HSSFRow aRow = sheet.getRow(i);//行数HSSFCell cellFirstColum = aRow.getCell((short)0);//第一列HSSFCell cellSecondColum = aRow.getCell((short)1);//第二列//把数据存入list里备用listFirstColum.add((int)cellFirstColum.getNumericCellValue());//假定第一列为数字型listSecondColum.add(cellSecondColum.getStringCellValue());//假定第二列为字符串型}}System.out.println("Excel读取成功!");}catch(Exception e){e.printStackTrace();}}//从ArrayList中把数据写入数据库public void insertData(String sFileName){//读取readExcel(sFileName);String sqlInsert = "insert into tbl_name"+"(first_colum,second_colum)values(?,?)";//插入try{for (int i=0;i{PreparedStatement pstmt=con.prepareStatement(sqlInsert);pstmt.setInt(1, listFirstColum.get(i));pstmt.setString(2, listSecondColum.get(i).toString());pstmt.execute();System.out.println("正在导入第 "+(i+1)+" 条记录");}System.out.println("导入完毕!");}catch(Exception e){e.printStackTrace();}}}JSP:test.jsp-----------------------------------------<%@ page pageEncoding="GB2312"%><%@ page import="com.mypack.ReadExcelTest" %><%ReadExcelTest read = new ReadExcelTest();read.insertData("D://test.xls");%>基本上改改数据库连接就能用了,请根据Excel文件的具体情况配置列的定义部分。 java实现 excel 中数据导入 oracleORACLE是有一个叫ADI的解决方案所需的额外包:commons-io-1.4、poi-bin-3.0.2思路:用户选择要导入的EXCEL文件,上传至WEB服务器。然后将文件存放目录传给POI类。通过对row循环取到cell的值,最后insert到ORACLE中。public boolean saleDeptToDB(String spreadSheet)throws HekException{boolean flag = false;IDBConn db = DBConn.getInstance();IDBOperate dbOp = DBOperate.getInstance();Connection conn = db.getConn();PreparedStatement pstmt = null;try{HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(spreadSheet));HSSFSheet sheet = workbook.getSheetAt(0);String sql = "insert into tableName(DEPT_ID,DEPT_CODE,DEPT_DESC,ITEM_NO,ITEM_CATE,BUDGET_QTY,BUDGET_AMOUNT,TIME_ID)";sql +="values(?,?,?,?,?,?,?,?)";pstmt = conn.prepareStatement(sql);for(int rowNumOfSheet=1;rowNumOfSheetHSSFRow rowOfSheet = sheet.getRow(rowNumOfSheet);HekSaleDeptManual hek = new HekSaleDeptManual();HSSFCell cell0 = rowOfSheet.getCell((short)0);if(cell0 != null)hek.setDeptId((int)cell0.getNumericCellValue());HSSFCell cell1 = rowOfSheet.getCell((short)1);if(cell1 != null)hek.setDeptCode(cell1.getRichStringCellValue().toString());HSSFCell cell2 = rowOfSheet.getCell((short)2);if(cell2 != null)hek.setDeptDesc(cell2.getRichStringCellValue().toString());HSSFCell cell3 = rowOfSheet.getCell((short)3);if(cell3 != null)hek.setItemNo(cell3.getRichStringCellValue().toString());HSSFCell cell4 = rowOfSheet.getCell((short)4);if(cell4 != null)hek.setItemCate(cell4.getRichStringCellValue().toString());HSSFCell cell5 = rowOfSheet.getCell((short)5);if(cell5 != null)hek.setBudgetQty(cell5.getNumericCellValue());HSSFCell cell6 = rowOfSheet.getCell((short)6);if(cell6 != null)hek.setBudgetQty(cell6.getNumericCellValue());HSSFCell cell7 = rowOfSheet.getCell((short)7);if(cell7 != null)hek.setTimeID(cell7.getRichStringCellValue().toString());dbOp.insertBathHekDept(pstmt, hek);}pstmt.executeBatch();flag = true;conn.commit();}catch(SQLException ex){db.rollbackTransaction(conn);System.out.println("recordToDB Error: "+ex);}catch(IOException ioex){System.out.println("saleDeptToDB read file Error: "+ioex);}finally{db.closePstmt(pstmt);db.closeConn(conn);}return flag;} for (i=0;i{PreparedStatement pstmt=con.prepareStatement(sqlInsert);pstmt.setInt(1, listFirstColum.get(i));pstmt.setString(2, listSecondColum.get(i).toString());pstmt.execute();System.out.println("正在导入第 "+(i+1)+" 条记录");}楼主:这能循环吗??有这样的写法吗? sns网站上传头像功能 XML中配置含义 myeclipse搭配tomcat5.5 的问题 spring高手请进,看看这个奇怪的问题 关于JSF的下拉列表 急啊 網頁繪圖applet小插件 tomcat中关于数据库的配置 SQL语名求助. sql 语句多个条件查询 的处理 ■■马上结账!求《Sun Certified Enterprise Architect for J2EE Technology Study Guide》,下载链接 dtree添加属性 如何将ireport生成的jasper 嵌入到jsp 中
参考http://viwo.javaeye.com/blog/66049jxl的看这个
http://www.javaeye.com/topic/63001
服务器上在解析这个文件然后入库。
用到的组件有apache的upload、和poi包!
--------------------------------------------------------------
package com.mypack;import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;import java.sql.*;
import java.io.*;
import java.util.ArrayList;
import java.sql.Connection;public class ReadExcelTest
{
private Connection con;
String DBDriver="oracle.jdbc.driver.OracleDriver";
String url="jdbc:oracle:thin:@192.168.0.1:1521:dbname";
String USER="username";
String PWD="password";private ArrayList listFirstColum = new ArrayList();
private ArrayList listSecondColum = new ArrayList();//连接数据库
public ReadExcelTest() throws Exception
{
try
{
Class.forName(DBDriver);
con=DriverManager.getConnection(url,USER,PWD);
}
catch(Exception e)
{
e.printStackTrace();
throw(new Exception("Sorry,database connecting failed!"));
}
}//从excel中读取数据存入ArrayList中
public void readExcel(String sFileName)
{
try
{
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(sFileName));//读取excel文件
HSSFSheet sheet = workbook.getSheetAt(0);//读取第一个sheet
for(int i=0;i<=sheet.getLastRowNum();i++)//循环取得所有行
{
if(sheet.getRow(i)!=null)
{
HSSFRow aRow = sheet.getRow(i);//行数
HSSFCell cellFirstColum = aRow.getCell((short)0);//第一列
HSSFCell cellSecondColum = aRow.getCell((short)1);//第二列
//把数据存入list里备用
listFirstColum.add((int)cellFirstColum.getNumericCellValue());//假定第一列为数字型
listSecondColum.add(cellSecondColum.getStringCellValue());//假定第二列为字符串型
}
}
System.out.println("Excel读取成功!");
}catch(Exception e)
{
e.printStackTrace();
}
}//从ArrayList中把数据写入数据库
public void insertData(String sFileName)
{
//读取
readExcel(sFileName);String sqlInsert = "insert into tbl_name"
+"(first_colum,second_colum)values(?,?)";
//插入
try
{
for (int i=0;i{
PreparedStatement pstmt=con.prepareStatement(sqlInsert);
pstmt.setInt(1, listFirstColum.get(i));
pstmt.setString(2, listSecondColum.get(i).toString());
pstmt.execute();
System.out.println("正在导入第 "+(i+1)+" 条记录");
}
System.out.println("导入完毕!");
}catch(Exception e)
{
e.printStackTrace();
}
}
}
JSP:test.jsp
-----------------------------------------
<%@ page pageEncoding="GB2312"%>
<%@ page import="com.mypack.ReadExcelTest" %>
<%
ReadExcelTest read = new ReadExcelTest();
read.insertData("D://test.xls");
%>
基本上改改数据库连接就能用了,请根据Excel文件的具体情况配置列的定义部分。
ORACLE是有一个叫ADI的解决方案所需的额外包:commons-io-1.4、poi-bin-3.0.2思路:用户选择要导入的EXCEL文件,上传至WEB服务器。然后将文件存放目录传给POI类。通过对row循环取到cell的值,最后insert到ORACLE中。public boolean saleDeptToDB(String spreadSheet)throws HekException{boolean flag = false;
IDBConn db = DBConn.getInstance();
IDBOperate dbOp = DBOperate.getInstance();Connection conn = db.getConn();
PreparedStatement pstmt = null;
try{
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(spreadSheet));
HSSFSheet sheet = workbook.getSheetAt(0);String sql = "insert into tableName(DEPT_ID,DEPT_CODE,DEPT_DESC,ITEM_NO,ITEM_CATE,BUDGET_QTY,BUDGET_AMOUNT,TIME_ID)";
sql +="values(?,?,?,?,?,?,?,?)";pstmt = conn.prepareStatement(sql);for(int rowNumOfSheet=1;rowNumOfSheet
HSSFRow rowOfSheet = sheet.getRow(rowNumOfSheet);
HekSaleDeptManual hek = new HekSaleDeptManual();HSSFCell cell0 = rowOfSheet.getCell((short)0);
if(cell0 != null)hek.setDeptId((int)cell0.getNumericCellValue());HSSFCell cell1 = rowOfSheet.getCell((short)1);
if(cell1 != null)hek.setDeptCode(cell1.getRichStringCellValue().toString());HSSFCell cell2 = rowOfSheet.getCell((short)2);
if(cell2 != null)hek.setDeptDesc(cell2.getRichStringCellValue().toString());HSSFCell cell3 = rowOfSheet.getCell((short)3);
if(cell3 != null)hek.setItemNo(cell3.getRichStringCellValue().toString());HSSFCell cell4 = rowOfSheet.getCell((short)4);
if(cell4 != null)hek.setItemCate(cell4.getRichStringCellValue().toString());HSSFCell cell5 = rowOfSheet.getCell((short)5);
if(cell5 != null)hek.setBudgetQty(cell5.getNumericCellValue());HSSFCell cell6 = rowOfSheet.getCell((short)6);
if(cell6 != null)hek.setBudgetQty(cell6.getNumericCellValue());HSSFCell cell7 = rowOfSheet.getCell((short)7);
if(cell7 != null)hek.setTimeID(cell7.getRichStringCellValue().toString());dbOp.insertBathHekDept(pstmt, hek);
}
pstmt.executeBatch();
flag = true;
conn.commit();
}catch(SQLException ex){
db.rollbackTransaction(conn);
System.out.println("recordToDB Error: "+ex);
}catch(IOException ioex){
System.out.println("saleDeptToDB read file Error: "+ioex);
}finally{
db.closePstmt(pstmt);
db.closeConn(conn);
}
return flag;
}
PreparedStatement pstmt=con.prepareStatement(sqlInsert);
pstmt.setInt(1, listFirstColum.get(i));
pstmt.setString(2, listSecondColum.get(i).toString());
pstmt.execute();
System.out.println("正在导入第 "+(i+1)+" 条记录");
}楼主:这能循环吗??有这样的写法吗?