excel 导入导出

else if (type.equalsIgnoreCase("import")){
// 需要插入的表名

String tableName = LxForm.getString("table");
DBConn dbconn = new DBConn();
// 获取流
response.setContentType("text/html;charset=gbk");
PrintWriter out = response.getWriter();
if (!DiskFileUpload.isMultipartContent(request)) {
out.println("只能处理multipart/form-data类型的数据!");
return mapping.findForward("failure");
}
MultipartRequestHandler mrh = LxForm.getMultipartRequestHandler();
Hashtable elements = mrh.getFileElements();  
Collection files = elements.values();
// 处理每个表单字段
Iterator i = files.iterator();
while (i.hasNext()) {
try {
FormFile fi = (org.apache.struts.upload.FormFile) i.next();
InputStream is = fi.getInputStream();// 得到文件流
Workbook wb = Workbook.getWorkbook(is);
int sheet_size = wb.getNumberOfSheets();//得到excel文件的sheet个数
int sheetRows=0;//初始化读取的行数
System.out.println("开始读取EXCEL");
Vector v = new Vector();
if ("LxDepFileReg".equalsIgnoreCase(tableName)) {

                       for(int index=0;index<sheet_size;index++){

Sheet sheet =wb.getSheet(index);
int rowNum=sheet.getRows();
if(rowNum==0||rowNum==1){
break;
}else{
//取标题前两列验证
Cell cellflag1 =sheet.getCell(0, 0);
String flagValue1= cellflag1.getContents();
Cell cellflag2 = sheet.getCell(1, 0);

String flagValue2 =  cellflag2.getContents();




if(flagValue1.trim().indexOf("来文时间")==-1||flagValue2.trim().indexOf("来文单位")==-1){

request.setAttribute("tableName", tableName);
request.setAttribute("message","成员单位来文管理表前两列标题 ：(来文时间,来文单位)顺序不匹配 或者表头信息不对");
return mapping.findForward("importLxDepFileReg");
}

for (int m = 0; m < rowNum - 1; m++) {
LxDepFileReg ldf= new LxDepFileReg();
// 开始读取EXCEL了
// 列 、行
Cell c0 = sheet.getCell(0, m + 1);
String strc0 = c0.getContents();



Cell c1 = sheet.getCell(1, m + 1);
String strc1 = c1.getContents();
Cell c2 = sheet.getCell(2, m + 1);
String strc2 = c2.getContents();


if(c0.getType()==CellType.DATE)
{
DateCell   datec00   =   (DateCell)c0;
Date   dt   =   datec00.getDate();
TimeZone gmt = TimeZone.getTimeZone("GMT");
       DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd",Locale.getDefault());
       dateFormat.setTimeZone(gmt);
       String str = dateFormat.format(dt);
       ldf.setRegTime(str);

} else {

ldf.setRegTime(strc0);

}


AccountActionForm acctForm = (AccountActionForm) request.getSession().getAttribute("accountForm");
String userName=acctForm.getAccount().getUname();
ldf.setUserName(userName);

ldf.setDepName(strc1);
ldf.setRegTitle(strc2);


v.add(ldf);
}
System.out.println("读取结束");
List<LxDepFileReg> list = v;
System.out.println("总共：" + list.size() + "行数据");
for (int j = 0; j < list.size(); j++) {
LxDepFileReg temp = list.get(j);
String depid="";
if(temp.getDepName()!=""){
String sql="select dep_mid from lx_dep_memberinfo where trim(dep_mname)='"+temp.getDepName()+"' ";
ResultSet rs=dbconn.executeQuery(sql);
if (!rs.next()){

//如果不存在此单位，则加入此单位到单位信息表

String insql = "insert into lx_dep_memberinfo(dep_mid,dep_mname) values (seq_lx_dep_memberinfo.nextval,'"+temp.getDepName()+"')";
dbconn.execute(insql);
String sqlString = "commit";
dbconn.execute(sqlString);
}
}
String insertSql =

"insert into lx_depfile_reg  (reg_Id,dep_Name, reg_Time,  reg_Title, add_Date,upd_Note)" +
                            "select seq_lx_depfile_reg.nextval,c.dep_mid,'"+temp.getRegTime()+"','"+temp.getRegTitle()+"',to_char(sysdate,'YYYY-MM-DD hh:mm:ss'),'"+temp.getUserName()+"'||to_char(sysdate,'YYYY-MM-DD hh:mm:ss')  from dual  , lx_dep_memberinfo c where c.dep_MName='"+temp.getDepName()+"' ";
dbconn.execute(insertSql);
System.out.println(insertSql);

String sqlString = "commit";
dbconn.execute(sqlString);
}
}
sheetRows+=v.size();
v = new Vector(); //清空每次读取的sheet容器
                       }
                        request.setAttribute("tableName", tableName);
request.setAttribute("message","成功导入" + sheetRows + "条信息");
return mapping.findForward("importLxDepFileReg");