1 <?xml version="1.0" encoding="UTF-8"?> 2 <TestResults> 3 <TestResult> 4 <name>Dummy sample test case 0</name> 5 <id>dummy- TC 0000</id> 6 <result>CANCELLED</result> 7 <errinfo/> 8 <startTS>2015-02-12 14:20:31</startTS> 9 <finishTS>2015-02-12 14:20:37</finishTS> 10 </TestResult> 11 <TestResult> 12 <name>Dummy sample test case 1</name> 13 <id>dummy- TC 0001</id> 14 <result>FAIL</result> 15 <errinfo>PreCondition NOT match</errinfo> 16 <startTS>2015-02-12 14:20:39</startTS> 17 <finishTS/> 18 </TestResult> 19 <TestResult> 20 <name>Dummy sample test case 0</name> 21 <id>dummy- TC 0000</id> 22 <result>CANCELLED</result> 23 <errinfo/> 24 <startTS>2015-02-12 14:22:08</startTS> 25 <finishTS>2015-02-12 14:22:09</finishTS> 26 </TestResult> 27 <TestResult> 28 <name>Dummy sample test case 1</name> 29 <id>dummy- TC 0001</id> 30 <result>FAIL</result> 31 <errinfo>PreCondition NOT match</errinfo> 32 <startTS>2015-02-12 14:23:11</startTS> 33 <finishTS/> 34 </TestResult> 35 <TestResult> 36 <name>Dummy sample test case 1</name> 37 <id>dummy- TC 0001</id> 38 <result>FAIL</result> 39 <errinfo>PreCondition NOT match</errinfo> 40 <startTS>2015-02-12 15:50:33</startTS> 41 <finishTS/> 42 </TestResult> 43 <TestResult> 44 <name>Dummy sample test case 1</name> 45 <id>dummy- TC 0001</id> 46 <result>FAIL</result> 47 <errinfo>PreCondition NOT match</errinfo> 48 <startTS>2015-02-12 16:45:17</startTS> 49 <finishTS/> 50 </TestResult> 51 </TestResults>
上面即是XML文件,有许多TestResult的Element,Element里有许多相同的节点如name,id等,所以导出的结构也显而易见!!
将XML文件的内容用Excel导出,我的思路是分2步的,
第一步获得XML的数据,存在ArrayList对象链表里,
第二步将链表插进Excel里面。
第一步:
1 package com.wn.XML; 2 3 import java.io.FileInputStream; 4 import org.jdom.Document; 5 import org.jdom.input.SAXBuilder; 6 7 public class XMLtoExcel { 8 public static Document LoadXMLFile(String path){ 9 Document doc = null; 10 try { 11 //我这里使用是JDOM容器,也可以DOM4J等 12 //XML 的输入源放在SAXBuilder里解析 13 FileInputStream fi = new FileInputStream(path); 14 SAXBuilder sb = new SAXBuilder(); 15 doc = sb.build(fi); 16 fi.close(); 17 } catch (Exception e) { 18 e.printStackTrace(); 19 } 20 return doc; 21 } 22 23 24 25 }
一个工具类,解析XML文件的方法,参数即为XML文件的路径,为以后提供服务。
第二步:
1 package com.wn.XML; 2 3 import java.io.File; 4 import java.io.FileOutputStream; 5 import java.io.OutputStream; 6 import java.util.ArrayList; 7 import java.util.Iterator; 8 import java.util.List; 9 import org.jdom.Document; 10 import org.jdom.Element; 11 import jxl.Workbook; 12 import jxl.write.Label; 13 import jxl.write.WritableSheet; 14 import jxl.write.WritableWorkbook; 15 16 public class createExcel { 17 18 public static void main(String[] args) { 19 String path = "./conf/test.xml"; 20 // first step 21 List<Element> list = getDataFromXML(path); 22 try { 23 // second step 24 writeIntoxls(list); 25 } catch (Exception e) { 26 e.printStackTrace(); 27 } 28 } 29 30 // first step : get data from XML 31 @SuppressWarnings("unchecked") 32 public static List<Element> getDataFromXML(String path) { 33 // 工具类的静态方法,直接传参调用 34 Document doc = XMLtoExcel.LoadXMLFile(path); 35 List<Element> testList = new ArrayList<Element>(); 36 // 将<TestResult>里面的内容当做一个对象,保存在ArrayList里面 37 testList = doc.getRootElement().getChildren("TestResult"); 38 System.out.println("list的长度为:" + testList.size()); 39 System.out.println("++++++++++++++++++++++++++++++++++++"); 40 return testList; 41 } 42 43 // second step : write into Excel 44 // 将上面的ArrayList里面的data作为参数传入 45 public static void writeIntoxls(List<Element> list) throws Exception { 46 int count = 0; 47 String xlsRoot = "./data/test.xls"; 48 // jxl.jar里面的方法,不多提 49 OutputStream os = new FileOutputStream(new File(xlsRoot)); 50 WritableWorkbook wwb = Workbook.createWorkbook(os); 51 WritableSheet sheet = wwb.createSheet("myFirstSheet", 0); 52 53 sheet.addCell(new Label(0, count, "name")); 54 sheet.addCell(new Label(1, count, "id")); 55 sheet.addCell(new Label(2, count, "result")); 56 sheet.addCell(new Label(3, count, "errinfo")); 57 sheet.addCell(new Label(4, count, "startTS")); 58 sheet.addCell(new Label(5, count, "finishTS")); 59 60 // 迭代刚刚上面的ArrayList里面的data,插入到Excel 61 Iterator<Element> iterator = list.iterator(); 62 while (iterator.hasNext()) { 63 ++count; 64 Element ele = iterator.next(); 65 sheet.addCell(new Label(0, count, ele.getChildTextTrim("name"))); 66 System.out.println(ele.getChildTextTrim("name")); 67 68 sheet.addCell(new Label(1, count, ele.getChildTextTrim("id"))); 69 System.out.println(ele.getChildTextTrim("id")); 70 71 sheet.addCell(new Label(2, count, ele.getChildTextTrim("result"))); 72 System.out.println(ele.getChildTextTrim("result")); 73 74 sheet.addCell(new Label(3, count, ele.getChildTextTrim("errinfo"))); 75 System.out.println(ele.getChildTextTrim("errinfo")); 76 77 sheet.addCell(new Label(4, count, ele.getChildTextTrim("startTS"))); 78 System.out.println(ele.getChildTextTrim("startTS")); 79 80 sheet.addCell(new Label(5, count, ele.getChildTextTrim("finishTS"))); 81 System.out.println(ele.getChildTextTrim("finishTS")); 82 System.out.println("------------------------------------"); 83 } 84 wwb.write(); 85 wwb.close(); 86 } 87 88 }
运行的结果如下:
list的长度为:6 ++++++++++++++++++++++++++++++++++++ Dummy sample test case 0 dummy- TC 0000 CANCELLED 2015-02-12 14:20:31 2015-02-12 14:20:37 ------------------------------------ Dummy sample test case 1 dummy- TC 0001 FAIL PreCondition NOT match 2015-02-12 14:20:39 ------------------------------------ Dummy sample test case 0 dummy- TC 0000 CANCELLED 2015-02-12 14:22:08 2015-02-12 14:22:09 ------------------------------------ Dummy sample test case 1 dummy- TC 0001 FAIL PreCondition NOT match 2015-02-12 14:23:11 ------------------------------------ Dummy sample test case 1 dummy- TC 0001 FAIL PreCondition NOT match 2015-02-12 15:50:33 ------------------------------------ Dummy sample test case 1 dummy- TC 0001 FAIL PreCondition NOT match 2015-02-12 16:45:17 ------------------------------------
截图如下: