jxl 各数据类型格式配置

package com.tht.common.xls;

import java.io.File;
import java.text.SimpleDateFormat;
import java.util.List;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormat;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import com.tht.common.date.util.ThtCalendar;
import com.tht.common.db.base.BaseDao;
import com.tht.sendmaill.weak.dao.SBCCDRDAO;
import com.tht.sendmaill.weak.vo.DayOfWeekVO;
import com.tht.sendmaill.weak.vo.SBCCDRVO;

public class WeekXLS extends BaseDao<WeekXLS>{
 
 private static  int final_columnNum=0;
 private static  int total_columnNum=0;
 private static  int final_titleRow=5;
 
 public Integer addColumn(Integer columnValue,int difference){
  columnValue=columnValue+difference;
        System.out.println(columnValue);
        return columnValue;
 }
 
 /**
  *
  * @param filePath  生成xls的文件路径
  * @param sqlQuality  是小时，还是一天的   Hour(小时)  Day(天)
  * @param sqlPath  执行sql 的路径
  * @param strTitle  xls文件中的表格标题
  * @throws Exception
  */
  public boolean writeXLS(String filePath,String sqlQuality,String sqlPath,String strTitle,String strBigTitle,String trunkSmallTitle,String vc2groupinfo,int final_titleRow_) throws Exception{

   jxl.format.Colour excelColour=Colour.GREY_25_PERCENT;
   WritableWorkbook workbook=null;
   WritableSheet sheet=null;
  
   if("1".equals(sqlQuality)){
    final_columnNum=0;
    final_titleRow=final_titleRow_;
    workbook = Workbook.createWorkbook(new File(filePath));
    sheet =workbook.createSheet(new ThtCalendar().getSimpleDate(), 0);
   }else{
    final_columnNum=0;
    final_titleRow=final_titleRow_;
    Workbook wb=Workbook.getWorkbook(new File(filePath));
        workbook = Workbook.createWorkbook(new File(filePath),wb);
        sheet=workbook.getSheet(0);
   }
         //标题 columnNum  行列号  titleRow标题行
    //初使值
   int columnNum=final_columnNum,titleRow=final_titleRow;//第5行开始
  
 
  
  

   sheet.setColumnView(columnNum, 25);//第一列的宽度
   sheet.setColumnView(++columnNum, 25);//第一列的宽度
   sheet.setColumnView(++columnNum, 25);//第一列的宽度
   sheet.setColumnView(++columnNum, 25);//第一列的宽度
   sheet.setColumnView(++columnNum, 25);//第一列的宽度
   sheet.setColumnView(++columnNum, 25);//第一列的宽度
   sheet.setColumnView(++columnNum, 25);//第一列的宽度
   sheet.setColumnView(++columnNum, 25);//第一列的宽度
   sheet.setColumnView(++columnNum, 25);//第一列的宽度
   sheet.setColumnView(++columnNum, 25);//第一列的宽度
   sheet.setColumnView(++columnNum, 25);//第一列的宽度
  
      
          //格式
          //标题格式  start
          WritableFont arial18ptBoldItalicUnderline = new WritableFont
          (WritableFont.ARIAL,
           9,
           WritableFont.BOLD,
           false,
           UnderlineStyle.NO_UNDERLINE);
         
          WritableCellFormat greyBackground = new WritableCellFormat(arial18ptBoldItalicUnderline);
          WritableCellFormat noBackground = new WritableCellFormat(arial18ptBoldItalicUnderline);
       
          noBackground.setWrap(false);
         // noBackground.setBackground(Colour.GRAY_50);
          //noBackground.setBorder(Border.ALL, BorderLineStyle.THIN);
          noBackground.setAlignment(Alignment.CENTRE);
         
         
          WritableCellFormat noRightBackground = new WritableCellFormat(arial18ptBoldItalicUnderline);
        
          noRightBackground.setWrap(false);
         // noBackground.setBackground(Colour.GRAY_50);
          noRightBackground.setBorder(Border.ALL, BorderLineStyle.THIN);
          noRightBackground.setAlignment(Alignment.RIGHT);
         
          greyBackground.setWrap(false);
          greyBackground.setBackground(excelColour);
          greyBackground.setBorder(Border.ALL, BorderLineStyle.THIN);
          greyBackground.setAlignment(Alignment.CENTRE);
         
         
          //大标题样式
          WritableFont bigTitleFont = new WritableFont
          (WritableFont.ARIAL,
           12,
           WritableFont.BOLD,
           false,
           UnderlineStyle.NO_UNDERLINE);
         
          WritableFont smallBackground = new WritableFont
          (WritableFont.ARIAL,
           9,
           WritableFont.BOLD,
           false,
           UnderlineStyle.NO_UNDERLINE);
         
         
         
          WritableCellFormat bigGreyBackground = new WritableCellFormat(bigTitleFont);
          bigGreyBackground.setWrap(false);
          bigGreyBackground.setBackground(excelColour);
          bigGreyBackground.setBorder(Border.ALL, BorderLineStyle.THIN);
          bigGreyBackground.setAlignment(Alignment.CENTRE);
         
         
         
         
          WritableCellFormat smallTitleBackground = new WritableCellFormat(smallBackground);
          smallTitleBackground.setWrap(false);
          smallTitleBackground.setBackground(excelColour);
          smallTitleBackground.setBorder(Border.ALL, BorderLineStyle.THIN);
          smallTitleBackground.setAlignment(Alignment.CENTRE);
         
         
          WritableCellFormat bigNoBackground = new WritableCellFormat(bigTitleFont);
          bigNoBackground.setWrap(false);
          //bigGreyBackground.setBackground(excelColour);
         // bigGreyBackground.setBorder(Border.ALL, BorderLineStyle.THIN);
          bigNoBackground.setAlignment(Alignment.CENTRE);
         
          //日期格式
          jxl.write.DateFormat dfsss = new jxl.write.DateFormat("yyyy-MM-dd hh:mm");
          SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd hh:mm");
          WritableCellFormat dataFormat = new WritableCellFormat(dfsss);
          dataFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
          DateTime dt=null;
         
          //文本样式
          WritableFont contentFont = new WritableFont
          (WritableFont.ARIAL,
           9,
           WritableFont.NO_BOLD,
           false,
           UnderlineStyle.NO_UNDERLINE);
          WritableCellFormat contentFormat = new WritableCellFormat (contentFont); 
          contentFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
         
          //红色字体样式
          WritableFont red = new WritableFont(WritableFont.ARIAL,
                   9,
                   WritableFont.BOLD,
                   false,
                   UnderlineStyle.NO_UNDERLINE,
                   Colour.RED);
         
          //红色字体样式
          WritableFont blue = new WritableFont(WritableFont.ARIAL,
                   9,
                   WritableFont.BOLD,
                   false,
                   UnderlineStyle.NO_UNDERLINE,
                   Colour.BLUE);
           //WritableCellFormat redContentFormat = new WritableCellFormat(red); 
           //redContentFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
    
         
            //数字格式  0.00
             NumberFormat doubleFormat=new NumberFormat("0.00");
             WritableCellFormat doublewcf=new WritableCellFormat(contentFont,doubleFormat);
             doublewcf.setBorder(Border.ALL, BorderLineStyle.THIN);

             //红色字体的数字格式  0.00
             NumberFormat redDoubleFormat=new NumberFormat("+0.00");
             WritableCellFormat redDoublewcf=new WritableCellFormat(red,redDoubleFormat);
             redDoublewcf.setBorder(Border.ALL, BorderLineStyle.THIN);

             //蓝色字体的数字格式  0.00
             NumberFormat blueDoubleFormat=new NumberFormat("0.00");
             WritableCellFormat blueDoublewcf=new WritableCellFormat(blue,blueDoubleFormat);
             blueDoublewcf.setBorder(Border.ALL, BorderLineStyle.THIN);
            
           //数字格式  0.0000
             NumberFormat doubleFormat2=new NumberFormat("0.0000");
             WritableCellFormat doublewcf2=new WritableCellFormat(contentFont,doubleFormat2);
             doublewcf2.setBorder(Border.ALL, BorderLineStyle.THIN);
            
            
             //红色字体的数字格式  0.0000
             NumberFormat redDoubleFormat2=new NumberFormat("+0.0000");
             WritableCellFormat redDoublewcf2=new WritableCellFormat(red,redDoubleFormat2);
             redDoublewcf2.setBorder(Border.ALL, BorderLineStyle.THIN);

             //蓝色字体的数字格式  0.0000
             NumberFormat blueDoubleFormat2=new NumberFormat("0.0000");
             WritableCellFormat blueDoublewcf2=new WritableCellFormat(blue,blueDoubleFormat2);
             blueDoublewcf2.setBorder(Border.ALL, BorderLineStyle.THIN);

         
            
            
             //数字格式  0%
             NumberFormat doubleFormat3=new NumberFormat("0%");
             WritableCellFormat doublewcf3=new WritableCellFormat(contentFont,doubleFormat3);
             doublewcf3.setBorder(Border.ALL, BorderLineStyle.THIN);

             //红色字体的数字格式  0.00 %
             NumberFormat redDoubleFormat3=new NumberFormat("+0.00%");
             WritableCellFormat redDoublewcf3=new WritableCellFormat(red,redDoubleFormat3);
             redDoublewcf3.setBorder(Border.ALL, BorderLineStyle.THIN);

             //蓝色字体的数字格式  0.00
             NumberFormat blueDoubleFormat3=new NumberFormat("0.00%");
             WritableCellFormat blueDoublewcf3=new WritableCellFormat(blue,blueDoubleFormat3);
             blueDoublewcf3.setBorder(Border.ALL, BorderLineStyle.THIN);
         
         
        
 
             columnNum=final_columnNum;
            
           Label titleLab=new Label(columnNum,titleRow,"FlOW_OUT_SRC_IP",greyBackground);
           sheet.addCell(titleLab);
          
           titleLab=new Label(++columnNum,titleRow,"FlOW_OUT_DEST_IP",greyBackground);
           sheet.addCell(titleLab);
          
           titleLab=new Label(++columnNum,titleRow,"CALLED_RTP_PACKETS",greyBackground);
           sheet.addCell(titleLab);
           titleLab=new Label(++columnNum,titleRow,"CALLED_PACKETS",greyBackground);
           sheet.addCell(titleLab);
           titleLab=new Label(++columnNum,titleRow,"PACK_LOSS",greyBackground);
           sheet.addCell(titleLab);
           titleLab=new Label(++columnNum,titleRow,"CALLED_RTP_AVG_JITTER",greyBackground);
           sheet.addCell(titleLab);
           titleLab=new Label(++columnNum,titleRow,"CALLED_R_FACTOR",greyBackground);
           sheet.addCell(titleLab);
           titleLab=new Label(++columnNum,titleRow,"CALLED_MOS",greyBackground);
           sheet.addCell(titleLab);
          
          
           SBCCDRDAO sbcCDRDAO=new SBCCDRDAO();
           List<SBCCDRVO> list=sbcCDRDAO.getList(sqlPath);
          

           columnNum=final_columnNum+1;

         
       
          //其它

         
          titleLab=new Label(final_columnNum,(final_titleRow-2),strBigTitle,bigGreyBackground);
          sheet.addCell(titleLab);
         
          titleLab=new Label(final_columnNum,(final_titleRow-1),trunkSmallTitle,smallTitleBackground);
          sheet.addCell(titleLab);
         
      
        
         
         
         
         
          //整数
          WritableCellFormat integerFormat = new WritableCellFormat (NumberFormats.INTEGER);
          integerFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
          Number number =null;
          
          //字符串
          Label label=null;
         
         

         
            int conColumn=final_columnNum;
            int conRow=final_titleRow+2;
           // WeekDao weekDao=new WeekDao();
           

  
     for(int i=0;i<list.size();i++){
     
     
                 SBCCDRVO vo=list.get(i);
                if(vo.getF32_flow_out_src_ip()==null){
        titleLab=new Label((conColumn++),conRow,"N/A",contentFormat);
     
       }else{
        titleLab=new Label((conColumn++),conRow,vo.getF32_flow_out_src_ip(),contentFormat);
            
       }
               sheet.addCell(titleLab);
               
                if(vo.getF34_flow_out_dest_ip()==null){
        titleLab=new Label((conColumn++),conRow,"N/A",contentFormat);
     
       }else{
        titleLab=new Label((conColumn++),conRow,vo.getF34_flow_out_dest_ip(),contentFormat);
            
       }
               
                sheet.addCell(titleLab);
               
               
               
                if(vo.getCalled_rtp_packets()==null){
        titleLab=new Label((conColumn++),conRow,"N/A",noRightBackground);
        sheet.addCell(titleLab);
       }else{
        number=new Number((conColumn++),conRow,vo.getCalled_rtp_packets(),integerFormat);
        sheet.addCell(number);
       }
               
               
                if(vo.getCalled_packets()==null){
        titleLab=new Label((conColumn++),conRow,"N/A",noRightBackground);
        sheet.addCell(titleLab);
       }else{
        number=new Number((conColumn++),conRow,vo.getCalled_packets(),integerFormat);
        sheet.addCell(number);
       }
               
               
               

               
                //%
                if(vo.getPack_loss()==null){
        titleLab=new Label((conColumn++),conRow,"N/A",noRightBackground);
        sheet.addCell(titleLab);
       }else{
        number=new Number((conColumn++),conRow,vo.getPack_loss()/100.00,doublewcf3);
        sheet.addCell(number);
       }
               
               
              if(vo.getCalled_rtp_avg_jitter()==null){
        titleLab=new Label((conColumn++),conRow,"N/A",noRightBackground);
        sheet.addCell(titleLab);
       }else{
        number=new Number((conColumn++),conRow,vo.getCalled_rtp_avg_jitter(),integerFormat);
        sheet.addCell(number);
       }
             
             
              if(vo.getCalled_r_factor()==null){
        titleLab=new Label((conColumn++),conRow,"N/A",noRightBackground);
        sheet.addCell(titleLab);
       }else{
        number=new Number((conColumn++),conRow,vo.getCalled_r_factor(),integerFormat);
        sheet.addCell(number);
       }
             
              if(vo.getCalled_mos()==null){
        titleLab=new Label((conColumn++),conRow,"N/A",noRightBackground);
        sheet.addCell(titleLab);
       }else{
        number=new Number((conColumn++),conRow,vo.getCalled_mos(),integerFormat);
        sheet.addCell(number);
       }
               
               
               
              total_columnNum= conColumn-1;
               conColumn=final_columnNum;
               conRow++;

            }
          
  
    
       
    
          sheet.mergeCells(final_columnNum,(final_titleRow-1),total_columnNum,(final_titleRow-1));//合并单元格  先往合并的第一个单元格写数据，再合并
          sheet.mergeCells(final_columnNum,(final_titleRow-2),total_columnNum,(final_titleRow-2));//合并单元格  先往合并的第一个单元格写数据，再合并

          workbook.write();
        
          if(workbook!=null){
           workbook.close();
          }
          return true;
  }

}