java excel上传

对于上传和下载excel文件，程序开发里经常用到，这里只做简要总结：

 

  boolean isMultipart = ServletFileUpload.isMultipartContent(request);
  if(isMultipart == false) {
   throw new IOException("HTTP request does not contains multipart content!");
  }
  
  DiskFileItemFactory factory = new DiskFileItemFactory();

  factory.setSizeThreshold(10240);
  ServletFileUpload upload = new ServletFileUpload(factory);

  
  try {
   List<FileItem> items = upload.parseRequest(request);
   
   Iterator<FileItem> iter = items.iterator();
   while (iter.hasNext()) {
       FileItem item = iter.next();

       if (item.isFormField() == false) {
        //String fieldName = item.getFieldName();
        //boolean isInMemory = item.isInMemory();
        //item may return the absolute full path name. we only need the last name
           String fileName = item.getName();
           InputStream uploadedStream = item.getInputStream();
           request.setAttribute("fileName", fileName);
           request.setAttribute("inputStream", uploadedStream);
       }
   }
  } catch (FileUploadException e) {
   throw new IOException(e);
  }

 

注意：这里的文件名是包含路径的，需要进行解析

int flag = fileName.lastIndexOf('/');

if(flag<0)flag = fileName.lastIndexOf('\\');

 

if(flag>=0)

return fileName.substring(flag+1)

else

return fileName;

 

有了文件名后，可以区分2003和2007及以后的版本了。

对于jxl，只能解析2003的版本。对于poi，两个版本都支持。但是对于非官方的excel文档，解析是有问题的

需要重新下载 poi-bin-3.8-20120326.zip文件，下载之后解压，拷贝相关的jar到lib目录即可。

 

 

public String upload() throws Exception{
  
   
   XSSFWorkbook rwb = null;
  HSSFWorkbook hwb = null;
  InputStream io = (InputStream)request.getAttribute("inputStream");
  String fileName = (String)request.getAttribute("fileName");
  
  String fileExpName = fileName.substring(fileName.indexOf(".")+1);
  
  if(Constant.EXCEL2003_EXTENSION.equals(fileExpName)){
   versionFlag = Constant.EXCEL_VERSION_2003;
  }else if(Constant.EXCEL2007_EXTENSION.equals(fileExpName)){
   versionFlag=Constant.EXCEL_VERSION_2007;
  }else{
   request.setAttribute("resMsg","上传的文件格式应该是excel文件，请重新选择！");
   return "success";
   //throw new Exception("上传的文件格式不正确，请重新选择！");
  }
  List<PotentialCustomerInfoVO> potentialCustomerInfoList = new ArrayList<PotentialCustomerInfoVO>();
  
  try{
   
   if(Constant.EXCEL_VERSION_2007.equals(versionFlag)){
    
    rwb = new XSSFWorkbook(io);
    XSSFSheet sheet = rwb.getSheetAt(0);
    //得到所有的行
    int rows = sheet.getLastRowNum();
    
    if(rows>10000){
     request.setAttribute("resMsg","导入的数据不能超过一万条！！！");
     return "success";
    }
    //得到excel数据
    for(int i=2;i<=rows;i++){
     
     XSSFRow row = sheet.getRow(i);
     
     if(row !=null){
      
      PotentialCustomerInfoVO pcv = new PotentialCustomerInfoVO();
      
      int cols = row.getLastCellNum();
      
      if(cols!=8){
                      request.setAttribute("resMsg","上传的excel文件第"+(i+1)+"行不能有空字段！");
                   return "success";
                         
      }
      //读取列数
      for (int k = 0; k < cols; k++) {
       
                         XSSFCell cell = row.getCell((short)k);  
                         if (null != cell) {
                          String ret = changeToString(cell.getCellType(), cell);
                          switch(k){
                           case 0:
                            //车主姓名
                   pcv.setCustomerName(ret);
                            break;
                           case 1:
                            //证件类型
                            String tempValue = "";
                            if(Constant.CERTITYPE_TEXT_SFZ.equals(ret)){
                             tempValue = Constant.CERTITYPE_VALUE_SFZ;
                            }else if(Constant.CERTITYPE_TEXT_JGZ.equals(ret)){
                             tempValue = Constant.CERTITYPE_VALUE_JGZ;
                            }else if(Constant.CERTITYPE_TEXT_HZ.equals(ret)){
                             tempValue = Constant.CERTITYPE_VALUE_HZ;
                            }else if(Constant.CERTITYPE_TEXT_QT.equals(ret)){
                             tempValue = Constant.CERTITYPE_VALUE_QT;
                            }
                   pcv.setCertiType(tempValue);
                            break;
                           case 2:
                                            pcv.setCertiCode(ret);
                            break;
                           case 3:
                                             pcv.setLicenceNumber(ret);
                            break;
                           case 4:
                                               pcv.setVin(ret);
                            break;
                           case 5:
                                             pcv.setEngineNumber(ret);
                            break;
                           case 6:
                                           pcv.setDriverNumber(ret);
                            break;
                           case 7:
                                        pcv.setF_1(ret);
                            break;
                           default:
                             break;
                          }
                         }else{
           //行内有空字段
                       request.setAttribute("resMsg","上传的excel文件第"+(i+1)+"行不能有空字段！");
                    return "success";
          }
      }
      potentialCustomerInfoList.add(pcv);
     }
    }// for rows
   }else if(Constant.EXCEL_VERSION_2003.equals(versionFlag)){
    
    hwb = new HSSFWorkbook(io);
    
    HSSFSheet sheet = hwb.getSheetAt(0);
    //得到所有的行
    int rows = sheet.getLastRowNum();
    
    if(rows>10000){
     request.setAttribute("resMsg","导入的数据不能超过一万条！！！");
     return "success";
    }
    //得到excel数据
    for(int i=2;i<=rows;i++){
     
     HSSFRow row = sheet.getRow(i);
     
     
     if(row !=null){
      
      int cols = row.getLastCellNum();
      
      if(cols!=8){
                   request.setAttribute("resMsg","上传的excel文件第"+(i+1)+"行不能有空字段！");
                return "success";
                      
      }
      
      PotentialCustomerInfoVO pcv = new PotentialCustomerInfoVO();
      
      //读取列数
      for (int k = 0; k < cols; k++) {
       
                         HSSFCell cell = row.getCell((short)k);  
                         if (null != cell) {
                          String ret = changeToString(cell.getCellType(), cell);
                          switch(k){
                           case 0:
                            //车主姓名
                   pcv.setCustomerName(ret);
                            break;
                           case 1:
                            //证件类型
                            String tempValue = "";
                            if(Constant.CERTITYPE_TEXT_SFZ.equals(ret)){
                             tempValue = Constant.CERTITYPE_VALUE_SFZ;
                            }else if(Constant.CERTITYPE_TEXT_JGZ.equals(ret)){
                             tempValue = Constant.CERTITYPE_VALUE_JGZ;
                            }else if(Constant.CERTITYPE_TEXT_HZ.equals(ret)){
                             tempValue = Constant.CERTITYPE_VALUE_HZ;
                            }else if(Constant.CERTITYPE_TEXT_QT.equals(ret)){
                             tempValue = Constant.CERTITYPE_VALUE_QT;
                            }
                   pcv.setCertiType(tempValue);
                            break;
                           case 2:
                                              pcv.setCertiCode(ret);
                            break;
                           case 3:
                                               pcv.setLicenceNumber(ret);
                            break;
                           case 4:
                                           pcv.setVin(ret);
                            break;
                           case 5:
                                              pcv.setEngineNumber(ret);
                            break;
                           case 6:
                                             pcv.setDriverNumber(ret);
                            break;
                           case 7:
                                            pcv.setF_1(ret);
                           default:
                             break;
                          }
                         }else{
                       request.setAttribute("resMsg","上传的excel文件第"+(i+1)+"行不能有空字段！");
                    return "success";
          }
      }
      potentialCustomerInfoList.add(pcv);
     }
    }
   }
     
   //对组装的list数据进行导入，也就是保存
      
   
   
  }catch(Exception e){
   e.printStackTrace();
   request.setAttribute("resMsg","上传出错，请联系系统管理员！");
   throw new Exception(e.getMessage());
  }
  
  return "success";
 }