接口自动化-testNG+poi（表格读写实例）

一、导入的坐标（pom.xml文件）

（这里使用的是easypoi）

<dependency>

<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.0.0</version>
</dependency>

<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.0.0</version>
</dependency>

 

二、ExcelUtils（封装表格工具类）

//读取表格内容
public static <E> List<E> read(int startSheetIndex,Class<E> clazz){
    //加载excel文件
    FileInputStream fis = null;
    try{
        fis = new FileInputStream(Constants.EXCEL_PATH);
        //导入配置
        ImportParams params = new ImportParams();
        params.setStartSheetIndex(startSheetIndex);
        //验证数据
        params.setNeedVerify(true);
        //执行导入
        List<E> list = ExcelImportUtil.importExcel(fis, clazz, params);
        return list;
    }catch(Exception e){
        e.printStackTrace();
    }finally{
        close(fis);
    }
    return null;
}    

//批量回写内容
public static void batchWrite(){
    //加载excel
    FileInputStream fis = null;
    FileOutputStream fos = null;
    try{
        fis = new FileInputStream(Constants.EXCEL_PATH);
        Workbook workbook = WorkbookFactory.create(fis);
        Sheet sheet = workbook.getSheetAt(1);
        //遍历集合
        for (WriteBackData wbd : wbdList) {
            //获取行号
            int rowNum = wbd.getRowNum();
            Row row = sheet.getRow(rowNum);
            //获取列
            int cellNum = wbd.getCellNum();
            Cell cell = row.getCell(cellNum, MissingCellPolicy.CREATE_NULL_AS_BLANK);
            //获取回写内容
            cell.setCellType(CellType.STRING);
            String content = wbd.getContent();
            cell.setCellValue(content);                
        }
        //回写到文件
        fos = new FileOutputStream(Constants.EXCEL_PATH);
        workbook.write(fos);
    }catch(Exception e){
        e.printStackTrace();
    }finally{
                //关流
        close(fis);
        close(fos);
    }
}

/*
 * 关闭流
 */
public static void close(Closeable stream){
    if(stream != null){
        try {
            stream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}