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;
? }
}
