同志们,怎样把Excel里的数据导到oracle里 用 PB powerBuilder !!!功能超级强大, 去 sybase 下一个测试版的! 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 打开excel文件,取数据,再用sql insert 到oracle try{ //Workbook book0= Workbook.getWorkbook(你的excel文件); //Sheet sheet0 = book0.getSheet(0); //获得第一个工作表对象 //Cell cell0 = sheet0.getCell(0, 0); //得到第一列第一行的单元格 //String content00 = cell0.getContents(); Workbook wkb = Workbook.getWorkbook(你的excel文件); Sheet[] sheets = wkb.getSheets(); String[] contentsArr = new String[9]; //得到各页 for(int i=0; i<sheets.length; i++) { //得到各行 进行处理 每页第一行为抬头 不做处理 for(int j=1; j<sheets[i].getRows(); j++) { //得到每格的内窜 csheets[i].getCell(k, j).getContents(); 用你的sql处理内容 } } wkb.close(); }catch(Exception ex){ log.error(ex.getMessage()); outmap.put("error", "处理excel文件出错 请联系管理员"); } 用的是jxl的控件,没有网上搜一下 原始的方法工具 1 plsql或者类似的工具 2 excel(废话)步骤 1 在要插入的execel数据前面加一空白列 连空白列一起复制 2 到plsql里面 select * from you_table or update 3 把锁打开,贴进数据,提交就可以了 import java.io.*;import jxl.Cell;import jxl.Sheet;import jxl.Workbook;public class Bean { private String tableNmae = ""; public void setTableName(String tableNmae){ this.tableNmae = tableNmae; } public String getTableName(){ return tableNmae; } public void take(InputStream is) { String[] colValues = new String[getColumnsTypes(tableNmae).length]; String sql = ""; String value = ""; Cell[] cell = new Cell[500]; jxl.Workbook wk = null; try { wk = Workbook.getWorkbook(is); }catch (Exception e) { } for (int i = 0; i < wk.getNumberOfSheets(); i++) { Sheet st = wk.getSheet(i); for (int y = 0; y < st.getRows(); y++) { for (int x = 0; x < colValues.length; x++) { cell[i] = st.getCell(x, y); value = (String) cell[i].getContents(); colValues[x] = value; } sql = getSQL(colValues); System.out.println("sql-->> " + sql ); } } try{ wk.close(); is.close(); }catch (Exception e) { } } String getSQL(String[] colValues) { String value = ""; String sql = "insert into " + tableNmae + " values("; for (int i = 0; i < colValues.length - 1; i++) { value = colValues[i].replaceAll("'", "''"); if (value.equals("")) { value = null; sql = sql+ value + ","; }else sql = sql + "'" + value + "',"; } value = colValues[colValues.length - 1].replaceAll("'", "''"); sql = sql + " '" + value + "')"; return sql; }} public ServletConfig servletconfig = null; public void init() throws ServletException { } //Process the HTTP Get request public void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType(CONTENT_TYPE); PrintWriter out = response.getWriter(); response.setContentType(CONTENT_TYPE); javax.servlet.http.HttpSession session = request.getSession(true); javax.servlet.ServletContext sc = getServletContext(); javax.servlet.RequestDispatcher rd = null; java.util.Enumeration en = request.getParameterNames(); Bean bean = new Bean(); try { SmartUpload mySmartUpload = new SmartUpload(); mySmartUpload.initialize(servletconfig, request, response); mySmartUpload.upload(); com.jspsmart.upload.File myFile = mySmartUpload.getFiles().getFile(0); DataInputStream dis = new DataInputStream(new ByteArrayInputStream(myFile. m_parent.m_binArray, myFile.m_startData, myFile.m_size)); String tableName = mySmartUpload.getRequest().getParameter("tableName"); bean.setTableName(tableName); bean.take(dis); }catch (Exception e){ } rd = sc.getRequestDispatcher("/a.jsp"); rd.forward(request, response); } //Clean up resources public void destroy() { } public void init(ServletConfig servletconfig) throws ServletException { this.servletconfig = servletconfig; super.init(servletconfig); } 最简单的方法是用微软SQL Server2000带的“导入和导出数据”工具操作 或者使用Access链接表功能,连接到Oracle中的表,然后复制Excel中的内容,粘贴到Access表格中即可。 我来了,贴出代码,对应的excelSheet导入到一个对应的oracleTableExcelToOracle.java:import java.sql.*;public class ExcelToOracle{ private Connection excelCon,oracleCon; private Statement excelStmt,oracleStmt; private ResultSet excelRs; private String excelSQL,oracleSQL; public static void main(String[] args) throws Exception{ new ExcelToOracle(); } public ExcelToOracle() throws Exception{ ConExcel(); WriteToOracle("CURRENCY_CODES"); CloseExcelCon(); } private void ConExcel() throws Exception{ Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); //DataSheet为odbc名 excelCon = DriverManager.getConnection("jdbc:odbc:DataSheet","",""); excelStmt = excelCon.createStatement(); String excelSQL = "select * from [Sheet1$]"; excelRs = excelStmt.executeQuery(excelSQL); } private void CloseExcelCon() throws Exception{ excelStmt.close(); excelCon.close(); } private void ReadToExcel() throws Exception{ } private void WriteToOracle(String tableName) throws Exception{ int count; String tmp=""; Class.forName("oracle.jdbc.driver.OracleDriver").newInstance(); String url="jdbc:oracle:thin:@10.10.254.5:1521:Leap"; //Leap为数据库的SID String user="system"; String password="manager"; oracleCon= DriverManager.getConnection(url,user,password); oracleStmt=oracleCon.createStatement(); count=excelRs.getMetaData().getColumnCount();//得到列数 oracleSQL="insert into "+tableName+" values ('"; while(excelRs.next()){ for(int i=1;i<=count;i++){ if(i<count) tmp=tmp+excelRs.getString(i).trim()+"','"; else tmp=tmp+excelRs.getString(i).trim(); } oracleSQL=oracleSQL+tmp+"')"; System.out.println (oracleSQL); oracleStmt.execute(oracleSQL); tmp=""; oracleSQL="insert into "+tableName+" values ('"; } oracleStmt.close(); oracleCon.close(); }} 具体实现步骤如下:利用jxl读取excel的数据,然后把数据写入到oracle上面几位兄第已经给出的代码,修改一下就可以了 用SQLServer的“导入和导出数据” medusagjf(郭婉仪) :你的ConExcel() 是要用odbc连接excel?问题是我要上传很多个excel表,不可能每次都给它建立一个odbc连接啊 SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0') 查出来,表名就是工作表名,其他的就不要我说了把insert into table() (SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')) 使用TOAD直接在菜单选择一步一步操作就可以把数据倒进去,不用写程序。 tomcat设置开机启动.后怎么设置内存 程序如何国际化 这一段简单的JSP输出代码错在哪儿了? 急啊. struts+hibernate删除时出现错误,求助! poi控制EXCEL输出,页眉页脚乱码的问题,2.5.1 请问这是怎么产生的? 如何在网页中显示自己的flash 大家有没有碰到过表单的二次提交的问题?有没有好的办法解决这种问题? 为什么用javamail收不到邮件?? jsp的中文问题 JAVA 连接SQL 2000 在JSP中又遇到了麻烦,新建一个对象后提示Conne conn=new Conne();错误,帮帮看一下.谢谢!
try{ //Workbook book0= Workbook.getWorkbook(你的excel文件); //Sheet sheet0 = book0.getSheet(0); //获得第一个工作表对象
//Cell cell0 = sheet0.getCell(0, 0); //得到第一列第一行的单元格
//String content00 = cell0.getContents();
Workbook wkb = Workbook.getWorkbook(你的excel文件);
Sheet[] sheets = wkb.getSheets();
String[] contentsArr = new String[9];
//得到各页
for(int i=0; i<sheets.length; i++)
{
//得到各行 进行处理 每页第一行为抬头 不做处理
for(int j=1; j<sheets[i].getRows(); j++)
{
//得到每格的内窜
csheets[i].getCell(k, j).getContents(); 用你的sql处理内容
}
} wkb.close();
}catch(Exception ex){
log.error(ex.getMessage());
outmap.put("error", "处理excel文件出错 请联系管理员");
}
工具 1 plsql或者类似的工具
2 excel(废话)
步骤
1 在要插入的execel数据前面加一空白列 连空白列一起复制
2 到plsql里面 select * from you_table or update
3 把锁打开,贴进数据,提交就可以了
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
public class Bean {
private String tableNmae = "";
public void setTableName(String tableNmae){
this.tableNmae = tableNmae;
}
public String getTableName(){
return tableNmae;
}
public void take(InputStream is) {
String[] colValues = new String[getColumnsTypes(tableNmae).length];
String sql = "";
String value = "";
Cell[] cell = new Cell[500];
jxl.Workbook wk = null;
try {
wk = Workbook.getWorkbook(is);
}catch (Exception e) { }
for (int i = 0; i < wk.getNumberOfSheets(); i++) {
Sheet st = wk.getSheet(i);
for (int y = 0; y < st.getRows(); y++) {
for (int x = 0; x < colValues.length; x++) {
cell[i] = st.getCell(x, y);
value = (String) cell[i].getContents();
colValues[x] = value;
}
sql = getSQL(colValues);
System.out.println("sql-->> " + sql );
}
}
try{
wk.close();
is.close();
}catch (Exception e) { }
} String getSQL(String[] colValues) {
String value = "";
String sql = "insert into " + tableNmae + " values(";
for (int i = 0; i < colValues.length - 1; i++) {
value = colValues[i].replaceAll("'", "''");
if (value.equals("")) {
value = null;
sql = sql+ value + ",";
}else
sql = sql + "'" + value + "',";
}
value = colValues[colValues.length - 1].replaceAll("'", "''");
sql = sql + " '" + value + "')";
return sql;
}
}
public void init() throws ServletException {
}
//Process the HTTP Get request
public void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setContentType(CONTENT_TYPE);
PrintWriter out = response.getWriter();
response.setContentType(CONTENT_TYPE);
javax.servlet.http.HttpSession session = request.getSession(true);
javax.servlet.ServletContext sc = getServletContext();
javax.servlet.RequestDispatcher rd = null;
java.util.Enumeration en = request.getParameterNames();
Bean bean = new Bean(); try {
SmartUpload mySmartUpload = new SmartUpload();
mySmartUpload.initialize(servletconfig, request, response);
mySmartUpload.upload();
com.jspsmart.upload.File myFile = mySmartUpload.getFiles().getFile(0);
DataInputStream dis = new DataInputStream(new ByteArrayInputStream(myFile.
m_parent.m_binArray, myFile.m_startData, myFile.m_size));
String tableName = mySmartUpload.getRequest().getParameter("tableName");
bean.setTableName(tableName);
bean.take(dis);
}catch (Exception e){
}
rd = sc.getRequestDispatcher("/a.jsp");
rd.forward(request, response); }
//Clean up resources
public void destroy() {
} public void init(ServletConfig servletconfig) throws ServletException {
this.servletconfig = servletconfig;
super.init(servletconfig);
}
ExcelToOracle.java:
import java.sql.*;public class ExcelToOracle{
private Connection excelCon,oracleCon;
private Statement excelStmt,oracleStmt;
private ResultSet excelRs;
private String excelSQL,oracleSQL;
public static void main(String[] args) throws Exception{
new ExcelToOracle();
}
public ExcelToOracle() throws Exception{
ConExcel();
WriteToOracle("CURRENCY_CODES");
CloseExcelCon();
}
private void ConExcel() throws Exception{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//DataSheet为odbc名
excelCon = DriverManager.getConnection("jdbc:odbc:DataSheet","","");
excelStmt = excelCon.createStatement();
String excelSQL = "select * from [Sheet1$]";
excelRs = excelStmt.executeQuery(excelSQL);
}
private void CloseExcelCon() throws Exception{
excelStmt.close();
excelCon.close();
}
private void ReadToExcel() throws Exception{
}
private void WriteToOracle(String tableName) throws Exception{
int count;
String tmp="";
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
String url="jdbc:oracle:thin:@10.10.254.5:1521:Leap";
//Leap为数据库的SID
String user="system";
String password="manager";
oracleCon= DriverManager.getConnection(url,user,password);
oracleStmt=oracleCon.createStatement();
count=excelRs.getMetaData().getColumnCount();//得到列数
oracleSQL="insert into "+tableName+" values ('";
while(excelRs.next()){
for(int i=1;i<=count;i++){
if(i<count)
tmp=tmp+excelRs.getString(i).trim()+"','";
else
tmp=tmp+excelRs.getString(i).trim();
}
oracleSQL=oracleSQL+tmp+"')";
System.out.println (oracleSQL);
oracleStmt.execute(oracleSQL);
tmp="";
oracleSQL="insert into "+tableName+" values ('";
}
oracleStmt.close();
oracleCon.close();
}
}
利用jxl读取excel的数据,然后把数据写入到oracle上面几位兄第已经给出的代码,修改一下就可以了
你的ConExcel() 是要用odbc连接excel?
问题是我要上传很多个excel表,不可能每次都给它建立一个odbc连接啊
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test.xls";User ID=Admin;Password=;Extended
properties=Excel 5.0') 查出来,表名就是工作表名,其他的就不要我说了把insert into table() (SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test.xls";User ID=Admin;Password=;Extended
properties=Excel 5.0'))