求一将Excel的数据导到数据库中的程序,紧急,在线等待中!
求一Excel的数据导到数据库中的程序,
Excel 中有几个sheet , 单元格中还可能含有图片等文档 ,
数据库为oracle, 其他数据库的也行,

我暂时用的是JXL ,
也有人用POI或其他的,都可以,只要达到要求就行了，因为比较紧,没时间挑三检四,功能第一先.

如上，给出程序代码的(连接的也可以) 立马 给分.

先谢谢各位了^^^^^^

[解决办法]
使用jxl.jar的api函数可以比较轻松的完成
[解决办法]
//Created by MyEclipse Struts
// XSL source (default): platform:/plugin/com.genuitec.eclipse.cross.easystruts.eclipse_4.1.0/xslt/JavaClass.xsl

package com.inventec.cmm.shopimport;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;

import javax.servlet.RequestDispatcher;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.swing.JOptionPane;
import java.io.File;

//import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.*;
import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.apache.struts.upload.FormFile;

import java.lang.*;
import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;


import java.util.*;
;
/**
* MyEclipse Struts
* Creation date: 02-18-2006
*
* XDoclet definition:
* @struts:action validate= "true "
*/
public class DoImportAction extends Action {

// --------------------- Instance Variables

// --------------------- Methods

/**
* Method execute
* @param mapping
* @param form
* @param request
* @param response
* @return ActionForward
*/
public ActionForward execute (
ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response) throws Exception{

// Logger logger =Logger.getLogger( "DoatProAction.class ");

Collection errors = new ArrayList();
String id= "-1 ";
if (request.getParameter( "id ")==null){
errors.add( "请登陆！ ");
return mapping.getInputForward();
}else
id=request.getParameter( "id ");

request.setAttribute( "id ",request.getParameter( "id "));

// int isnull =0;
int cnt=0;

String dir=servlet.getServletContext().getRealPath( "/upload ");
ShopImportForm hff=(ShopImportForm)form;
FormFile file =(FormFile) hff.getFile();

if((file==null)||(file.getFileSize()==0))

{
errors.add( "文件上传出错或者未指定文件！ ");
request.setAttribute( "errors ",errors);
return mapping.getInputForward();
}

String fname=file.getFileName();
String size=Integer.toString(file.getFileSize())+ "bytes ";
InputStream streamIn=file.getInputStream();

java.text.DateFormat d = new java.text.SimpleDateFormat( "yyyy-MM-dd-hh_mm_ss ");
String strd = d.format(new Date(System.currentTimeMillis()));

String sfname=id+ '- '+strd+ '- '+fname;

OutputStream streamOut =new FileOutputStream(dir+ "/ "+sfname);
int c;
while ((c = streamIn.read()) != -1)
{
streamOut.write(c);
}
streamOut.close();
streamIn.close();

hff.setFname(fname);
hff.setSize(size);
file.destroy();

String fileToBeRead=dir+ "\\ "+sfname;
FileInputStream fs =new FileInputStream(fileToBeRead);
Connection conn=null;
PreparedStatement stmt=null;

PreparedStatement stmt1=null;
PreparedStatement stmt2=null;
PreparedStatement st=null;

Statement statement=null;
ResultSet rs=null;
String shopId= " ";
String orgId= " ";
int oid=-1;
String prodId= " ";
String state= " ";
String shopLevel= " ";
String checkDate= " ";
String temp1= " ";
//String temp2= " ";
//double amount=0;
HSSFCell cell0 =null;
HSSFCell cell1 =null;
HSSFCell cell2 =null;
HSSFCell cell3=null;
HSSFCell cell4=null;
HSSFRow aRow=null;
List arr=new ArrayList();
// List shopArr=new ArrayList();
// List orgArr=new ArrayList();
HSSFWorkbook workbook=null;
int numOfSheets=0;
short k=5;
short colNum=5;


try
{
conn=DataBaseMgr.getSalesConnection();
statement=conn.createStatement();
rs=statement.executeQuery( "select max(checkdate) as checkdate from cmm_shop_import_checkdate ");
if (rs.next()){
checkDate=rs.getString( "checkdate ");
}else{
errors.add( "必须设置普查时间 ");
return mapping.getInputForward();
}




st=conn.prepareStatement( "select prod_id from V_PRODUCT_DIS where prod_name=? ");
workbook = new HSSFWorkbook(fs);
numOfSheets=workbook.getNumberOfSheets();
for(int numSheets = 0; numSheets < numOfSheets; numSheets++)
{

if(null != workbook.getSheetAt(numSheets))
{


HSSFSheet aSheet = workbook.getSheetAt(numSheets);
aRow=aSheet.getRow(0);
if (aRow==null)
continue;
colNum=5;
if (numSheets==0){
while((aRow.getCell(colNum)!=null)&&(!aRow.getCell(colNum).getStringCellValue().toString().trim().equals( " ")))
{


colNum++;


}
colNum--;

if (aRow!=null){

for( k=4;k <=colNum;k++){
prodId=aRow.getCell(k).getStringCellValue().toString();
st.setString(1,prodId);
rs=st.executeQuery();
if (rs.next()){
arr.add(rs.getString( "prod_id "));

}else{
errors.add( "第 "+(numSheets+1)+ "个sheet的第 " + (k + 1) + "列 " + "产品型号不存在！ ");
}
}
}
}
stmt=conn.prepareStatement( "select org_id,sh_id from cmm_v_shop_org where org_name=? and sh_name=? ");
//stmt1=conn.prepareStatement( "select sh_id from EB_SHOP where SH_NAME=? ");
stmt2=conn.prepareStatement( "select shopid as amount from cmm_shop_import where shopid=? and checkdate=? and orgid=? ");

for(int rowNumOfSheet = 1; rowNumOfSheet <=aSheet.getLastRowNum(); rowNumOfSheet++)

{
if(null != aSheet.getRow(rowNumOfSheet))
{
try{
aRow = aSheet.getRow(rowNumOfSheet);

short ps0=0,ps1=1,ps2=2,ps3=3,ps4=4;

cell0 =aRow.getCell(ps0);
cell1 =aRow.getCell(ps1);
cell2 =aRow.getCell(ps2);
cell3=aRow.getCell(ps3);
// cell4=aRow.getCell(ps4);
if ((cell0==null)&&(cell1==null)&&(cell2==null)&&(cell3==null)){
continue;
}
if ((cell0==null)||(cell1==null)||(cell2==null)||(cell3==null)){
errors.add( "第 "+(numSheets+1)+ "个sheet的第 " + (rowNumOfSheet + 1) + "行 " + "没有填写完全！ ");
continue;
}

[解决办法]




//checkDate=cell0.getStringCellValue().trim();
orgId=cell0.getStringCellValue().trim();
shopId=cell1.getStringCellValue().trim();
shopLevel=cell2.getStringCellValue().trim();
state=cell3.getStringCellValue().trim();
if (orgId.equals( " ")||shopId.equals( " ")){
errors.add( "第 "+(numSheets+1)+ "个sheet的第 " + (rowNumOfSheet + 1) + "行 " + "办事处或零售店没有填写！ ");
continue;

}else{
stmt.setString(1,orgId);
stmt.setString(2,shopId);
rs=stmt.executeQuery();
if (rs.next()){
oid=rs.getInt( "org_id ");
orgId=String.valueOf(oid);
temp1=rs.getString( "sh_id ");
}else
errors.add( "第 "+(numSheets+1)+ "个sheet的第 " + (rowNumOfSheet + 1) + "行 " + "办事处合零售店不存在！ ");

}

// if (shopId.equals( " ")){
// errors.add( "第 "+(numSheets+1)+ "个sheet的第 " + (rowNumOfSheet + 1) + "行 " + "零售店没有填写！ ");
// continue;

// }else{
// stmt1.setString(1,shopId);
// rs=stmt1.executeQuery();
// if (rs.next()){
// temp1=rs.getString( "sh_id ");
// }else
// errors.add( "第 "+(numSheets+1)+ "个sheet的第 " + (rowNumOfSheet + 1) + "行 " + "零售店不存在！ ");



// }
if (shopLevel.equals( " ")){

errors.add( "第 "+(numSheets+1)+ "个sheet的第 " + (rowNumOfSheet + 1) + "行 " + "店面级别没有填写！ ");

continue;
}else{
if ((!shopLevel.equals( "A "))&&(!shopLevel.equals( "B "))&&(!shopLevel.equals( "C "))&&(!shopLevel.equals( "D "))){
errors.add( "第 "+(numSheets+1)+ "个sheet的第 " + (rowNumOfSheet + 1) + "行 " + "店面级别填写不正确！ ");
}

}

if (state.equals( " ")){

errors.add( "第 "+(numSheets+1)+ "个sheet的第 " + (rowNumOfSheet + 1) + "行 " + "合作状态没有填写！ ");
continue;
}else{
if ((!state.equals( "Y "))&&(!state.equals( "N "))&&(!state.equals( "T "))&&(!state.equals( "T "))){
errors.add( "第 "+(numSheets+1)+ "个sheet的第 " + (rowNumOfSheet + 1) + "行 " + "合作状态填写不正确！ ");
}

}
// if (checkDate.equals( " ")){
// errors.add( "第 "+(numSheets+1)+ "个sheet的第 " + (rowNumOfSheet + 1) + "行 " + "时间没有填写！ ");
// continue;
// }else{
// if (!convertDateTime(checkDate)){
// errors.add( "第 "+(numSheets+1)+ "个sheet的第 " + (rowNumOfSheet + 1) + "行 " + "时间格式有误！ ");
// continue;
// }
// }

if ((!temp1.equals( " "))&&(!checkDate.equals( " "))){
stmt2.setString(1,temp1);
stmt2.setString(2,checkDate);
stmt2.setInt(3,oid);
rs=stmt2.executeQuery();
if (rs.next()){
errors.add( "店名为 "+shopId+ "，普查时间为 " + checkDate + "的纪录已经存在！请不要重复导入！ ");

}

}

}catch(Exception e){

e.printStackTrace();
errors.add( "第 "+(numSheets+1)+ "个sheet的第 " + (rowNumOfSheet + 1) + "行 " + "出！检查格式！ ");
errors.add(e.toString());
request.setAttribute( "errors ",errors);
return mapping.getInputForward();
}

}
}
}

}





}
catch(Exception e)

[解决办法]
{
fs.close();
fs=null;
errors.add( "exl文档格式有误 ");
System.out.print(e);
//logger.error(e.toString());

}finally{
if (rs!=null) rs.close();
if (stmt!=null) stmt.close();
if (st!=null) st.close();
if (stmt1!=null) stmt1.close();
//if (stmt2!=null) stmt1.close();
if (conn!=null) conn.close();

}
if (!errors.isEmpty()){
request.setAttribute( "errors ",errors);
fs.close();

fs=null;
File f=new File(fileToBeRead);
if (f.exists())
f.delete();
return new ActionForward(mapping.getInput());
}





try
{ int intOrg=-3;
SimpleDateFormat df=new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss ");
Date curDate=new Date(System.currentTimeMillis());
String currentDate=df.format(curDate);
conn=DataBaseMgr.getSalesConnection();
Statement s=null; ///////////
// s=conn.createStatement();//////////
HSSFCell tempCell=null;

st=conn.prepareStatement( "insert into CMM_SHOP_IMPORT (orgid,shopid,shoplevel,checkdate,state,prodid,value,createuserid,createdate) values(?,?,?,?,?,?,?,?,?) ");
stmt=conn.prepareStatement( "select org_ID from SYS_organization where org_name=? ");
stmt1=conn.prepareStatement( "select sh_id from EB_shop where sh_name=? ");


numOfSheets=workbook.getNumberOfSheets();
for(int numSheets = 0; numSheets < numOfSheets; numSheets++)
{
if(null != workbook.getSheetAt(numSheets))
{
HSSFSheet aSheet = workbook.getSheetAt(numSheets);
for(int rowNumOfSheet = 1; rowNumOfSheet <=aSheet.getLastRowNum(); rowNumOfSheet++)

{
if(null != aSheet.getRow(rowNumOfSheet))
{
try{
aRow = aSheet.getRow(rowNumOfSheet);

short ps0=0,ps1=1,ps2=2,ps3=3,ps4=4;

cell0 =aRow.getCell(ps0);
cell1 =aRow.getCell(ps1);
cell2 =aRow.getCell(ps2);
cell3=aRow.getCell(ps3);
// cell4=aRow.getCell(ps4);
if ((cell0==null)&&(cell1==null)&&(cell2==null)){
continue;
}
// if (cell0!=null)
// checkDate=cell0.getStringCellValue().trim();
// else
// checkDate=null;
if (cell0!=null)
orgId=cell0.getStringCellValue().trim();
else
orgId= "-1 ";
if (cell1!=null)
shopId=cell1.getStringCellValue().trim();
else
shopId= "-1 ";

if (cell2!=null)
shopLevel=cell2.getStringCellValue().trim();
else
shopLevel= " ";



if (cell3!=null)
state=cell3.getStringCellValue();
else
state= " ";


stmt.setString(1,orgId);
rs=stmt.executeQuery();
if (rs.next()){
intOrg= rs.getInt( "org_id ");
}else
continue;

stmt1.setString(1,shopId);
rs=stmt1.executeQuery();
if (rs.next()){

shopId= rs.getString( "sh_id ");
}else
continue;
String strNum= "0 ";
int num=0;
st.setInt(1,intOrg);
st.setString(2, shopId);
st.setString(3,shopLevel);
st.setString(4,checkDate);
st.setString(5,state);


for(k=4;k <=colNum;k++) {

st.setString(6,arr.get(k-4).toString());
tempCell=aRow.getCell(k);
// cell0.setCellType(1);
try{
num=(int)(tempCell.getNumericCellValue());
if ((num!=1)&&(num!=0))
num=0;
}catch(Exception e){
num=0;
}

st.setInt(7,num);
st.setString(8,id);
st.setString(9,currentDate);
st.execute();
}




}catch(Exception e){

e.printStackTrace();
errors.add( "插入错误：第 "+(numSheets+1)+ "个sheet的第 " + (rowNumOfSheet + 1) + "行 " + "出！检查格式！ ");
errors.add(e.toString());
request.setAttribute( "errors ",errors);
return mapping.getInputForward();
}

}
}
}

}



if (!errors.isEmpty()){
request.setAttribute( "errors ",errors);
return new ActionForward(mapping.getInput());
}

}
catch(Exception e)
{

errors.add( "exl文档格式有误 ");
//logger.error(e.toString());

}finally{

if (rs!=null) rs.close();
if (stmt!=null) stmt.close();
if (stmt1!=null) stmt.close();
if (st!=null) stmt.close();
if (conn!=null) conn.close();
if (fs!=null){
fs.close();
fs=null;
}
}





request.setAttribute( "psid ",errors);
request.setAttribute( "cnt ",String.valueOf(cnt));

if(errors.size()==0)
{
//JOptionPane jp=new JOptionPane();
//JOptionPane.showConfirmDialog(jp, "已成功导入! ", "信息 ",JOptionPane.CLOSED_OPTION,1);
// response.sendRedirect( "shopimport.jsp?id= "+id);
//request.setAttribute();
return mapping.findForward( "sucess ");

}
request.setAttribute( "errors ",errors);
return mapping.getInputForward();


}


private boolean convertDateTime(String date){
try{
SimpleDateFormat df = new SimpleDateFormat( "yyyy-MM-dd ");
df.parse(date);
return true;
}catch(Exception e){
return false;
}
}


}


[解决办法]
mark
[解决办法]
这么长，还不如说思想。其实就是一个格一个格的读，然后放到二位数组中，用各insert插到db里面去

[解决办法]
JXL我用过，感觉JAVA对OFFICE支持不是很好，注释、宏之类的都不能做，但如果数据排列简单，想要实现导入数据库应该是没什么问题的。楼主去找下JXL方面的资料应该可以很方便得写个程序，但要想找现成的估计比较困难。