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