JXL生成excel实战

需求：

       完成一个简单excel的生成和导出

    

       前台代码：

       var flightStart = $("#flightDate1").val();
            var flightEnd = $("#flightDate2").val();
            var segment = $("#segment").val();
            var flightNo = $("#flightNo").val();
            var isAuto = $("#isAuto").val();
            var isEffect = $("#isEffect").val();
            var autocap = $("#autocap").val();
            var  requestPath =   "<%=path%>/pwr/assist/exportThreshold.action?speType.isEffect="+isEffect+"&speType.isAuto="+isAuto ;
            if(flightStart!="" && flightStart!= null){
             requestPath = requestPath +"&speType.flightStart="+flightStart ;
            }
            if(flightEnd!="" && flightEnd!= null){
             requestPath = requestPath +"&speType.flightEnd="+flightEnd ;
            }
            if(segment!="" && segment!= null){
             requestPath = requestPath +"&speType.segment="+segment ;
            }
            if(flightNo!="" && flightNo!= null){
             requestPath = requestPath +"&speType.flightNo="+flightNo ;
            }
            if(autocap!="" && autocap!= null){
             requestPath = requestPath +"&speType.autocap="+autocap ;
            }   

            form1.action= requestPath ;
        $("#form1").submit();              
       //window.location.href=requestPath;

 

       后台代码：

 
 public String exportThreshold() throws Exception{
  try
  {  
   List<SpeFlightType> resultlist = thresholdService.getSpeFlightTypeList(speType);
  
   OutputStream os = response.getOutputStream();// 取得输出流  
         response.reset();// 清空输出流  
         response.setHeader("Content-disposition", "attachment; filename=result.xls");// 设定输出文件头  
         response.setContentType("application/msexcel");// 定义输出类型         
         WritableWorkbook wbook = Workbook.createWorkbook(os); // 建立excel文件  
       
         WritableSheet wsheet = wbook.createSheet("子阀值数据", 0); // sheet名称 
        
         //表头列名
      String [] titles  ={"开始日期" , "结束日期" , "航班号" , "航段" , "自动收放舱" ,"是否生效" ,
       "舱位序列" , "阀值类型"  ,  "是否加座位" ,"操作时间", "优先级"  ,"对比航班号" ,"对比时刻区间" ,
       "对比航空公司","比对方式" ,"客座率差值区间"  ,"折扣率差值区间" ,"周期"  ,"提前天数" ,"状态" } ;
   
    for(int i = 0 ; i < titles.length ; i++){
        Label label = new Label(i, 0,titles[i] ); 
        wsheet.addCell(label); 
      }
   
    int i = 1 ;//行数控制   
    if(resultlist.size()>0){

    SpeFlightType speFlightType = null ;
     for (int j = 0 ; j < resultlist.size() ; j ++){          
       speFlightType = resultlist.get(j);
    TurnListToSet getList = new TurnListToSet();
    List kzList = getList.getKZlist(speFlightType.getKeZuoList(),true);
    List tbList = getList.getTBlist(speFlightType.getTongBiList(),true);
    //竟比子阀值写入EXCEL
    if(kzList.size()>0){
                    for(int m  = 0 ; m < kzList.size() ; m++){
                      ThreshKeZuoEntity threshKeZuoEntity = (ThreshKeZuoEntity)kzList.get(m);
                      int k = createNewLine(wsheet, i, speFlightType, "1") ;
                      wsheet.addCell(new Label(k++, i, ""+threshKeZuoEntity.getPriority()));
                      wsheet.addCell(new Label(k++, i, threshKeZuoEntity.getFlightNo()));
                      String compareDate = "" ;
                      if(threshKeZuoEntity.getTimeSpan1()!=null){
                       compareDate = threshKeZuoEntity.getTimeSpan1();
                      }
                      if(threshKeZuoEntity.getTimeSpan2()!=null){
                       compareDate = compareDate+"-"+ threshKeZuoEntity.getTimeSpan2();
                      }                    
                      wsheet.addCell(new Label(k++, i, compareDate));
                      wsheet.addCell(new Label(k++, i, threshKeZuoEntity.getIncludeAirline()));
                      if("1".equals(threshKeZuoEntity.getRateType()) ){
                       wsheet.addCell(new Label(k++, i, "最低"));
                      }else if("2".equals(threshKeZuoEntity.getRateType()) ){
                       wsheet.addCell(new Label(k++, i, "平均"));
                      }else if("3".equals(threshKeZuoEntity.getRateType()) ){
                       wsheet.addCell(new Label(k++, i, "最高"));
                      }
                      wsheet.addCell(new Label(k++, i,threshKeZuoEntity.getDiscountRange() ));
                      wsheet.addCell(new Label(k++, i,threshKeZuoEntity.getDiscount() ));
                      wsheet.addCell(new Label(k++, i,threshKeZuoEntity.getWeek() ));
                      wsheet.addCell(new Label(k++, i,threshKeZuoEntity.getAheadDate() ));
                      wsheet.addCell(new Label(k++, i, "1".equals(threshKeZuoEntity.getIsEffect()) ? "生效":"未生效")); 
                      i++;
                    }
    }
    
    //同比子阀值写入EXCEL
    if(tbList.size()>0){
                    for(int m  = 0 ; m < kzList.size() ; m++){
                      ThreshTongBiEntity threshTongBiEntity = (ThreshTongBiEntity)tbList.get(m);
                      int k = createNewLine(wsheet, i, speFlightType, "2") ;
                     
                      wsheet.addCell(new Label(k++, i, ""+threshTongBiEntity.getPriority()));
                      wsheet.addCell(new Label(k++, i, ""));        
                      wsheet.addCell(new Label(k++, i, ""));
                      wsheet.addCell(new Label(k++, i, ""));
                      wsheet.addCell(new Label(k++, i, ""));                     
                      wsheet.addCell(new Label(k++, i,threshTongBiEntity.getKezuolv() ));
                      wsheet.addCell(new Label(k++, i,threshTongBiEntity.getDiscount() ));
                      wsheet.addCell(new Label(k++, i,threshTongBiEntity.getWeek() ));
                      wsheet.addCell(new Label(k++, i,threshTongBiEntity.getAheadDate() ));
                      wsheet.addCell(new Label(k++, i, "1".equals(threshTongBiEntity.getIsEffect()) ? "生效":"未生效")); 
                      i++;
                    }
    }
    
    if(tbList.size() == 0 && kzList.size()==0){
      createNewLine(wsheet, i, speFlightType ,"3") ;
      i++;
    }
     
     }
        speFlightType = null;
        LOG.info("export into excel success  and has "+ i +" row export. ");
     }
   
  // 主体内容生成结束          
  wbook.write(); // 写入文件  
  wbook.close(); 
  os.close(); // 关闭流
  return null;
  }
  catch(Exception ex){
    ex.printStackTrace();
    throw new Exception();
  
  }
}

 /**
  *
  * @param wsheet 
  * @param i 传入写到第几行
  * @param speFlightType
  * @param type
  * @return 返回已经写到excel的第几列
  * @throws RowsExceededException
  * @throws WriteException
  *
  */
 private int createNewLine(WritableSheet wsheet , int i , SpeFlightType speFlightType , String type) throws RowsExceededException, WriteException{
   int j = 0 ;
   wsheet.addCell(new Label(j++, i, speFlightType.getFlightStart())); 
   wsheet.addCell(new Label(j++, i, speFlightType.getFlightEnd()));
   wsheet.addCell(new Label(j++, i, speFlightType.getFlightNo()));
   wsheet.addCell(new Label(j++, i, speFlightType.getSegment()));
   wsheet.addCell(new Label(j++, i, "1".equals(speFlightType.getIsAuto()) ? "是":"否"));
   wsheet.addCell(new Label(j++, i, "1".equals(speFlightType.getIsEffect()) ? "生效":"作废"));
   wsheet.addCell(new Label(j++, i, speFlightType.getClsSqe()));  
   wsheet.addCell(new Label(j++, i, "1".equals(type) ? "竞比阀值": "2".equals(type) ? "进度阀值" : "没有子阀值"));
   wsheet.addCell(new Label(j++, i, "1".equals(speFlightType.getAutocap()) ? speFlightType.getCapnum():"0"));
   wsheet.addCell(new Label(j++, i, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(speFlightType.getCreateDate()))) ;
      return j ;
 }