Java实现把测试结果写入Excel表中

自动化测试脚本执行完以后，会有一个测试结果，无论是通过框架还是Jenkins平台生成的结果，可读性都不是太好，为了方便手工测试人员查看结果，测试完成后把结果写入Excel是一个不错的方法，但是脚本多了，通过人工来写，无疑加重了自动化测试人员的工作，为了解决这个问题，我就开发了一个把测试结果写入Excel的工具类，代码如下：

import java.io.File;
import java.util.ArrayList;
import java.util.List;

import jxl.Workbook;
import jxl.common.Logger;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class CreateExcel {

    private String[] title;
    private String sheetName;

    private static WritableWorkbook book = null;
    private static WritableSheet sheet = null;

    private static Logger logger = Logger.getLogger(CreateExcel.class);
    private String testRoot = GeneralMethods.getTestRoot().replace("target/test-classes/", "src/test/resources/data/")
            + "Performance/";
    private File file;

    /*
     * titleName[0]表示Excel文件的名字
     * titleName后面的值是Excel第一行要填入的值
     */
    public CreateExcel(String... titleName) {

        sheetName = GeneralMethods.getDate();
        file = new File(testRoot + titleName[0]);
        List<String> temp = new ArrayList<String>();

        for (String s : titleName) {
            temp.add(s);
        }

        temp.remove(0);

        titleName = (String[]) temp.toArray(new String[temp.size()]);

        create(titleName);
    }

    private void create(String[] titleNames) {

        title = titleNames;

        try {
            Workbook wb = Workbook.getWorkbook(file);
            book = Workbook.createWorkbook(file, wb);
            sheet = addTitle();

        } catch (Exception e) {
            logger.debug("CreateWorkbook failed !");
            e.printStackTrace();
        }
    }

    public void addDataToExcel(String testname, double... data) {

        try {
            Workbook wb = Workbook.getWorkbook(file);

            book = Workbook.createWorkbook(file, wb);
            sheet = book.getSheet(sheetName);

            int length = sheet.getRows();

            Label name = new Label(0, length, testname);
            sheet.addCell(name);
            for (int i = 1; i < data.length + 1; i++) {
                Label label = new Label(i, length, Double.toString(data[i - 1]));
                sheet.addCell(label);
            }
            book.write();
            book.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private WritableSheet addTitle() {

        String nameStrs = "";
        String[] sheetNames = book.getSheetNames();
        for (String sheetName : sheetNames) {
            nameStrs += sheetName;
        }
        try {
            if (nameStrs.contains(sheetName))
                sheet = book.getSheet(sheetName);
            else {
                sheet = book.createSheet(sheetName, 0);
                sheet.getSettings().setDefaultColumnWidth(25);

                for (int i = 0; i < title.length; i++) {
                    sheet.addCell(new Label(i, 0, title[i]));
                }

            }
            book.write();
            book.close();
        } catch (Exception e) {
        }
        return sheet;
    }

}

 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111

使用工具类的脚本：

import org.testng.annotations.BeforeClass;
import org.testng.annotations.Test;

import tools.CreateExcel;
import tools.WindowsInfoUtil;

public class testWinMonitor {

    private WindowsInfoUtil info;
    private CreateExcel create;

    @BeforeClass
    public void setUp() {

        create = new CreateExcel("Windows Monitor.xls", "测试名称", "CPU开始占用(%)", "CPU结束占用(%)", "内存开始占用(MB)", "内存结束占用(MB)");

    }

    @Test
    public void test010() throws InterruptedException {

        Thread.sleep(2000);
        double startMem = info.getMem();
        double startCpu = info.getCpu();

        Thread.sleep(2000);
        double endMem = info.getMem();
        double endCpu = info.getCpu();

        create.addDataToExcel("test1", startCpu, endCpu, startMem, endMem);
    }
}
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

注意 
1、在执行脚本之前，在目录…\src\test\resources\data\Performance下面新建一个Excel，名字为实例化CreateExcel类的时候传入的第一个参数，例子中为“Windows Monitor.xls”。 
2、对Excel进行读写的第三方jar包是jxl，这个包暂时只支持Excel97-2003. 
3、脚本执行完成后，点开查看Excel，新建的sheet名字为日期，如：“2016-01-29” 

回到最初的话题，把测试结果写入到Excel，如果测试框架选择的是testNG，直接解析testNG的XML报告，按照自己项目的需求选择需要传入到CreateExcel工具类中的参数即可。