jxl将xml转换成excel

本文介绍了一个利用XML配置文件生成服务器数据监控表的过程,包括读取配置、解析XML数据以及创建Excel文件。重点展示了如何遍历XML元素,获取所需数据,并将其填充到Excel表中。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >


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
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值