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

使用工具类的脚本：

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、在执行脚本之前，在目录…\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工具类中的参数即可。