用java代码读取excel文档插入SQL Server数据库
用java代码读取excel文档插入SQL Server数据库，好像有个JXL.jar包是吧，具体如何有知道的大侠告知告知，谢谢！！
[解决办法]
读过CSV
BUT
http://blog.csdn.net/manytao/article/details/5819853 这个也许能帮你的忙。
[解决办法]
给你个例子吧
private void importYdjksj(HttpServletRequest req, HttpServletResponse resp){
String status="0";
StringBuffer msg_err=new StringBuffer();
StringBuffer msg=new StringBuffer();
try {
YdjksjDao dao = new YdjksjDao();
String fileName = null; //导入文件名
int rowNum=0; //导入文件记录数
int rowNum_pre=3;//从前开始需要减的行数
int rowNum_last=1;//从后开始需要减的行数
int rowNum_start=4;//开始行数索引
int errNum=0;
String filePath="E:/tmp/import";
String filePath_new="";
filePath_new=generateFile(req, filePath, fileName);
Workbook wb = null;
try{
File file = new File(filePath_new);//(this.getHttpServletRequest().getParameter("sjwj"));
wb=Workbook.getWorkbook(file);
} catch (BiffException e){
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
if(wb==null)
throw new Exception("文件对象为空!");
try {
Sheet[] sheet = wb.getSheets();
if(sheet!=null&&sheet.length>0){
rowNum = sheet[0].getRows();
String jkyh="";
String yhzh="";
String bsrq="";
jkyh=getSpeciBySplit(sheet[0].getRow(1), "dsyh:");
yhzh=getSpeciBySplit(sheet[0].getRow(1), "ylshdm:");
bsrq=getSpeciBySplit(sheet[0].getRow(2), "bbfsrq:");
System.out.println("jkyh:"+jkyh+", yhzh:"+yhzh+", bsrq:"+bsrq);
if (rowNum > rowNum_pre){
//删除当天原有记录
dao.deleteBySql("delete WFJKSJ_YD t where to_char(t.gxsj,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')", null);
//导入记录
for(int j=rowNum_start;j<rowNum-rowNum_last;j++){
try{
YdjksjValueObject vo=new YdjksjValueObject();
Cell[] cells = sheet[0].getRow(j);
if(cells!=null&&cells.length>0){
vo.setUnitname(cells[0].getContents());
vo.setUnitno(cells[1].getContents());
vo.setPnno(cells[2].getContents());
vo.setFkje(cells[3].getContents());
vo.setZnj(cells[4].getContents());
vo.setHtje(cells[5].getContents());
vo.setPjlx(cells[6].getContents());
vo.setPjhm(cells[7].getContents());
vo.setJksj(cells[8].getContents());
System.out.println("j:"+j+"pnno:"+vo.getPnno()+", len:"+vo.getPnno().length());
vo.setJdslb("1");
vo.setZt("0");
vo.setJkyh(jkyh);
vo.setYhzh(yhzh);
vo.setBsrq(bsrq);
vo.setOrderindex(Integer.toString(j));
String msg_tmp="";
msg_tmp=dao.saveYdjksj(vo);
if(msg_tmp!=null && !"".equals(msg_tmp)){
throw new Exception(msg_tmp);
}
}
} catch (Exception e){
errNum++;
msg_err.append("第"+j+"行数据异常："+e.getMessage()+";\r\n");
}
}
msg.append("开始导入数据：\r\n");
int rowCount=rowNum-rowNum_start-rowNum_last;
msg.append("总共"+rowCount+"条数据,成功"+(rowCount-errNum)+"条,失败"+errNum+"条。\r\n");
if(StringUtils.strNotNull(msg_err.toString(), true)){
msg.append("下面是失败数据:\r\n");
msg.append(msg_err.toString());
}
if(rowCount>errNum){
status="1";
//获取文件合计笔数和合计金额
String tmp_hjbs="0";
String tmp_hjje="0";
String tmp_msg="";
try {
Cell[] cells = sheet[0].getRow(rowNum-1);
if(cells!=null&&cells.length>0){
if(cells[2].getContents()!=null && !"".equals(cells[2].getContents())){
tmp_hjbs=cells[2].getContents();
}
if(cells[5].getContents()!=null && !"".equals(cells[5].getContents())){
tmp_hjje=cells[5].getContents();
}
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
tmp_msg="获取合计笔数或合计金额出错!";
}
try {
Sys_userValueobject user = applicationContext.getCurrentSys_userValueobject();
YdjksjRzValueObject rzVo=new YdjksjRzValueObject();
rzVo.setBsrq(bsrq);
rzVo.setDsyhmc(jkyh);
rzVo.setYlshdm(yhzh);
rzVo.setWjhjbs(tmp_hjbs);
rzVo.setWjhjje(tmp_hjje);
rzVo.setDrzs(Integer.toString(rowCount));
rzVo.setDrcgzs(Integer.toString(rowCount-errNum));
rzVo.setDrsbzs(Integer.toString(errNum));
rzVo.setGxr(user.getUsername());
// rzVo.setNote(msg.toString());
rzVo.setDrzt((rowCount==rowCount-errNum)?"1":"0");
String msg_tmp=dao.saveYdjksjRz(rzVo);
if(msg_tmp!=null && !"".equals(msg_tmp)){
throw new Exception(msg_tmp);
}
} catch (Exception e) {
e.printStackTrace();
msg.append("\r\n保存日志信息失败:"+e.getMessage());
}
}
}
}
} catch (Exception e) {
e.printStackTrace();
msg.append("数据文件不规范，数据导入失败！");
}
} catch (Exception e) {
e.printStackTrace();
msg.append("数据导入失败:"+e.getMessage());
}
log.debug("===msg:"+msg.toString());
req.setAttribute("msg", msg.toString());
req.setAttribute("status", status);
}
/**
* 初始化文件路径
* @param req
* @param filePath
* @param fileName
* @return
* @throws Exception
*/
private String generateFile(HttpServletRequest req,String filePath,String fileName) throws Exception{
String returnFilePath="";
DiskFileUpload fu = new DiskFileUpload();
fu.setSizeMax(1024*1024*1024*10);
fu.setSizeThreshold(4096);
fu.setRepositoryPath(filePath);
List fileItems = fu.parseRequest(req);
Iterator it = fileItems.iterator();
while(it.hasNext()) {
System.out.println("i====="+String.valueOf(it));
FileItem fi = (FileItem)it.next();
if (!fi.isFormField()){
fileName = fi.getName();
String ss[] = fileName.split("\\\");
String fileName1 = ss[ss.length-1];
if(fileName1!=null){
if(fileName1.toLowerCase().lastIndexOf(".xls")<=0){
throw new Exception("文件格式不为xls");
}
}else{
throw new Exception("文件格式不为xls");
}
if(fileName!=null&&!fileName.equals("")){
returnFilePath=filePath+"/temptyfp.xls";
fi.write(new File(returnFilePath));
}
}
}
return returnFilePath;
}
