Struts2 Excel导入导出数据

package com.abin.struts2.excel; import java.io.BufferedInputStream; import java.io.BufferedOutputStream; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.InputStream; import java.io.OutputStream; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.Calendar; import java.util.List; import javax.servlet.http.HttpServletRequest; import jxl.Cell; import jxl.Sheet; import jxl.Workbook; import org.apache.struts2.ServletActionContext; import com.abin.connect.oracle.DBClassConnection; import com.opensymphony.xwork2.ActionSupport; public class CreateExcel extends ActionSupport{ private static final int BUFFER_SIZE=16*1024; private File file; private String fileFileName; private String fileContentType; private String savePath; private String remarks; public String FileUpload(){ Connection conn=null; CallableStatement cs=null; PreparedStatement ps=null; ResultSet rs=null; Calendar cal=Calendar.getInstance(); String suffix=fileFileName.substring(fileFileName.lastIndexOf(".")); long systime=cal.getTimeInMillis(); String filename=systime+suffix; String path=ServletActionContext.getServletContext().getRealPath(getSavePath())+"\\"+filename; File outpath=new File(path); java.sql.Timestamp time=new java.sql.Timestamp(new java.util.Date().getTime()); int returnid=0; HttpServletRequest request=ServletActionContext.getRequest(); if(fileFileName!=null){ try{ copyFile(file,outpath); conn=DBClassConnection.getOracle(); cs=conn.prepareCall("{call filepath(?,?,?,?,?,?)}"); cs.setInt(1, 1); cs.setTimestamp(2, time); cs.setString(3, path); cs.setString(4, filename); cs.setString(5, remarks); cs.registerOutParameter(6, java.sql.Types.INTEGER); cs.execute(); returnid=cs.getInt(6); System.out.println("returnid="+returnid); }catch(Exception e){ e.printStackTrace(); }finally{ try{ if(cs!=null){ cs.close(); } if(conn!=null){ conn.close(); } }catch(Exception e){ e.printStackTrace(); } } String sql="insert into FILEEXCEL (id,createtime,filepath,filename,remarks) values (?,?,?,?,?)"; try{ conn=DBClassConnection.getOracle(); ps=conn.prepareStatement(sql); Workbook book=Workbook.getWorkbook(new File(path)); Sheet st=book.getSheet(0); int row=st.getRows(); int col=st.getColumns(); for(int i=2;i<row-2;i++){ for(int j=0;j<col;j++){ Cell cell=st.getCell(j,i); String content=cell.getContents(); ps.setString(j+1, content); } ps.addBatch(); if(i==10000){ ps.executeBatch(); } } ps.executeBatch(); if(book!=null){ book.close(); } }catch(Exception e){ e.printStackTrace(); }finally{ try{ if(conn!=null){ conn.close(); } if(ps!=null){ ps.close(); } }catch(Exception e){ e.printStackTrace(); } } fileBean bean=new fileBean(); List<fileBean> list=new ArrayList<fileBean>(); try{ conn=DBClassConnection.getOracle(); cs=conn.prepareCall("{call fileout(?,?)}"); cs.setInt(1, returnid); cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR); cs.execute(); rs=(ResultSet)cs.getObject(2); while(rs.next()){ bean.setId(rs.getInt("id")); bean.setCreatetime(rs.getTime("createtime")); bean.setFilepath(rs.getString("filepath")); bean.setFilename(rs.getString("filename")); bean.setRemarks(rs.getString("remarks")); list.add(bean); } }catch(Exception e){ e.printStackTrace(); } request.getSession().setAttribute("list", list); return SUCCESS; }else{ return ERROR; } } public void copyFile(File from,File to){ InputStream in=null; OutputStream out=null; try{ in=new BufferedInputStream(new FileInputStream(from),BUFFER_SIZE); out=new BufferedOutputStream(new FileOutputStream(to),BUFFER_SIZE); byte[] buffer=new byte[BUFFER_SIZE]; while(in.read(buffer)>0){ out.write(buffer); out.flush(); } }catch(Exception e){ e.printStackTrace(); } } public File getFile() { return file; } public void setFile(File file) { this.file = file; } public String getFileFileName() { return fileFileName; } public void setFileFileName(String fileFileName) { this.fileFileName = fileFileName; } public String getFileContentType() { return fileContentType; } public void setFileContentType(String fileContentType) { this.fileContentType = fileContentType; } public String getSavePath() { return savePath; } public void setSavePath(String savePath) { this.savePath = savePath; } public String getRemarks() { return remarks; } public void setRemarks(String remarks) { this.remarks = remarks; } }