public class Excel {
static String xml_path="";
static String[] days;
static String server="";
//读取配置文件信息
static{
InputStream is=Excel.class.getResourceAsStream("config.properties");
Properties p=new Properties();
try {
p.load(is);
xml_path=p.getProperty("xml_path").trim();
server=p.getProperty("server").trim();
if(!xml_path.endsWith("/"))xml_path=xml_path+"/";
days=p.getProperty("day").trim().split(",");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* @param args
* @throws DocumentException
* @throws IOException
*/
public static void main(String[] args) throws IOException, DocumentException {
for (String day : days){
createExcel(day);
System.out.println("----------"+day.replace('-', '.')+"---------");
}
}
public static List paraXml(String day) throws IOException, DocumentException{
SAXReader reader = new SAXReader();
InputStream inputStream = new FileInputStream(new File(day+".xml"));
Document doc = reader.read(inputStream);
Element root = doc.getRootElement();
List _list = root.elements("time");
return _list;
}
public static void createExcel(String day){
System.out.println("开始");
try{
//打开文件
WritableWorkbook book=Workbook.createWorkbook(new File("vod+电视回看应用服务器数据监控表("+day.replace('-', '.')+"."+server+")"+".xls"));
WritableSheet sheet=book.createSheet("第一页",0);
//设置字体
WritableFont NormalFont = new WritableFont(WritableFont.createFont("宋体"), 12,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.WHITE);
WritableFont BoldFont = new WritableFont(WritableFont.createFont("宋体"), 20 ,WritableFont.BOLD);
WritableFont tableFont = new WritableFont(WritableFont.createFont("宋体"), 11 , WritableFont.NO_BOLD);
WritableFont baodanFont = new WritableFont(WritableFont.ARIAL, 10 , WritableFont.BOLD);
//设置标题
WritableCellFormat wcf_title = new WritableCellFormat(BoldFont);
wcf_title.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
wcf_title.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
wcf_title.setAlignment(Alignment.CENTRE); // 水平对齐
wcf_title.setWrap( true ); // 是否换行
sheet.mergeCells(0, 0, 8, 0); //合并第一行的8列
Label label=new Label(0,0,"VOD+电视回看应用服务器数据监控表("+day.replace('-', '.')+"."+server+")",wcf_title);
sheet.addCell(label);
//设置行高
sheet.setRowView( 0 , 900 , false );
for(int i=0;i<=8;i++){
if(i==2||i==6||i==8){
sheet.setColumnView(i, 21); //设置列宽
}else if(i==5||i==7){
sheet.setColumnView(i, 19);
}else{
sheet.setColumnView(i, 17);
}
}
for(int i=1;i<=25;i++){
sheet.setRowView( i , 500 , false );
}
//设置正文
WritableCellFormat wcf = new WritableCellFormat(NormalFont);
wcf.setBorder(Border.ALL, BorderLineStyle.THIN);
wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
wcf.setAlignment(Alignment.LEFT);
wcf.setBackground(Colour.GREEN);
wcf.setWrap( true );
//将定义好的单元格添加到工作表中
/*生成一个保存数字的单元格
必须使用Number的完整包路径,否则有语法歧义
单元格位置是第二列,第一行,值为789.123*/
//jxl.write.Number number=new jxl.write.Number(1,1,123);
//sheet.addCell(number);
//Label(colum,row,string,style);
Label label0=new Label(0,1,"时间/监控项",wcf);
Label label1=new Label(1,1,"毫秒级并发数",wcf);
Label label2=new Label(2,1,"服务器在线用户数",wcf);
Label label3=new Label(3,1,"服务器访问量",wcf);
Label label4=new Label(4,1,"服务器连接数",wcf);
Label label5=new Label(5,1,"最大并发用户数",wcf);
Label label6=new Label(6,1,"最大并发用户时间",wcf);
Label label7=new Label(7,1,"最大并发连接数",wcf);
Label label8=new Label(8,1,"最大并发连接时间",wcf);
sheet.addCell(label0);
sheet.addCell(label1);
sheet.addCell(label2);
sheet.addCell(label3);
sheet.addCell(label4);
sheet.addCell(label5);
sheet.addCell(label6);
sheet.addCell(label7);
sheet.addCell(label8);
WritableCellFormat wcf_time = new WritableCellFormat(tableFont);
wcf_time.setBorder(Border.ALL, BorderLineStyle.THIN);
wcf_time.setVerticalAlignment(VerticalAlignment.CENTRE);
wcf_time.setAlignment(Alignment.RIGHT);
wcf_time.setWrap( true );
//将xml里的数据放到excel里
for (int i = 0; i <paraXml(day).size(); i++) {
Element _item = (Element) paraXml(day).get(i);
String time=_item.attributeValue("value");
String millse_count = _item.element("millse_count").attributeValue("value");
String server_users = _item.element("server_users").attributeValue("value");
String server_visitors = _item.element("server_visitors").attributeValue("value");
String server_connctors = _item.element("server_connctors").attributeValue("value");
String max_users = _item.element("max_users").attributeValue("value");
String max_users_time = _item.element("max_users").attributeValue("time");
String max_connectors = _item.element("max_connectors").attributeValue("value");
String max_connectors_time = _item.element("max_connectors").attributeValue("time");
Label ltime=new Label(0,i+2,time,wcf_time);
jxl.write.Number lmillse_count=new jxl.write.Number(1,i+2,Integer.parseInt(millse_count),wcf_time);
jxl.write.Number lserver_users=new jxl.write.Number(2,i+2,Integer.parseInt(server_users),wcf_time);
jxl.write.Number lserver_visitors=new jxl.write.Number(3,i+2,Integer.parseInt(server_visitors),wcf_time);
jxl.write.Number lserver_connctors=new jxl.write.Number(4,i+2,Integer.parseInt(server_connctors),wcf_time);
jxl.write.Number lmax_users=new jxl.write.Number(5,i+2,Integer.parseInt(max_users),wcf_time);
Label lmax_users_time=new Label(6,i+2,max_users_time,wcf_time);
jxl.write.Number lmax_connectors=new jxl.write.Number(7,i+2,Integer.parseInt(max_connectors),wcf_time);
Label lmax_connectors_time=new Label(8,i+2,max_connectors_time,wcf_time);
sheet.addCell(ltime);
sheet.addCell(lmillse_count);
sheet.addCell(lserver_users);
sheet.addCell(lserver_visitors);
sheet.addCell(lserver_connctors);
sheet.addCell(lmax_users);
sheet.addCell(lmax_users_time);
sheet.addCell(lmax_connectors);
sheet.addCell(lmax_connectors_time);
System.out.println("------"+time+"-----------:"+millse_count);
}
Label lt=new Label(0,25,"总计:",wcf_time);
sheet.addCell(lt);
for(int i=1;i<=8;i++){
Label ll=new Label(i,25,"",wcf_time);
sheet.addCell(ll);
}
//写入数据并关闭文件
book.write();
book.close();
}catch(Exception e){
System.out.println(e);
}
System.out.println("结束");
}
}
config.properties配置文件里:
day=2012-03-01
path=F:/log
xml_path=F:
server=204