Excel读写引用库替换为Easy POI
原因: Easy POI更简单(具体可对比下方代码跟2.5中Excel读取代码)
用例Excel分层设计: excel封装成对象
1) 导入easypoi的坐标
2) 加载excel的流对象
3) 导入参数对象ImportParams
4) 工具ExcelImportUtil解析excel封装成List的一项
5) 添加java类API和excel中列的映射关系
Easy POI学习地址: http://easypoi.mydoc.io/
一、 Easy POI 引入
1. 引入库如下:
2. Maven 坐标
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.0.0</version>
</dependency>
3. Excel导入参数介绍
可在:http://easypoi.mydoc.io/#text_226078 查看
二、用例设计 & 代码实现
1. Excel用例如下
1) 一个表单专门用来管理(表单API):
存储信息包含: 接口编号(每个接口的唯一编号) + 该接口的URL(接口请求地址)
+Content-Type(接口请求类型) + Method(接口请求方法)
API_No | API_Name | Url | Method | ContentType |
1 | register | /member/register | POST | application/json |
2 | login | /member/login | POST | application/json |
3 | recharge | /member/recharge | POST | application/json |
4 | withdraw | /member/withdraw | POST | application/json |
5 | update | /member/update | PATCH | application/json |
7 | info | /member/{member_id}/info | GET | application/json |
8 | add | /loan/add | POST | application/json |
9 | audit | /loan/audit | PATCH | application/json |
10 | generateRepayments | /loans | GET | application/json |
2) 其他表单用来进行每个接口的接口用例的编写(表单:register)
如下图,表格中API_No与1)中API表单中API_No对应
Case_id | Params | Desc | API_No |
register_001 | {"mobile_phone":"159349158","pwd":"12345678"} | 手机号长度不满11位 | 1 |
register_002 | {"mobile_phone":"1593491582999","pwd":"12345678"} | 手机号长度大于11位 | 1 |
register_003 | {"mobile_phone":"11111111111","pwd":"12345678"} | 手机号不符合规则 | 1 |
register_004 | {"mobile_phone":"13221000001","pwd":"12345678"} | 手机号已被注册 | 1 |
register_005 | {"pwd":"12345678"} | 手机号为空 | 1 |
register_006 | {"mobile_phone":"13221099614","pwd":"123456"} | 密码长度不满8位 | 1 |
register_007 | {"mobile_phone":"13221099614","pwd":"123456789101112131415"} | 手机号长度大于16位 | 1 |
register_008 | {"mobile_phone":"13221099614"} | 密码为空 | 1 |
register_009 | {"mobile_phone":"13221099614","pwd":"12345678"} | 正常注册 | 1 |
2. 添加java类API和Case类
package com.lujier.pojo;
public class API {
// 必须私有属性,空参构造,get/set方法
// 接口编号 接口名称 接口请求方式 接口地址 参数类型
@Excel(name="API_No") // Excel中: API_No列与实体类的成员变量映射关系(name可汉字可字母)
@NotNull // NotNull,过滤值=null的行,只获取id不为null的行
private String id;
@Excel(name="API_Name")
private String name;
@Excel(name="Method")
private String method;
@Excel(name="Url")
// @URL(protocol="http",host="api.lemonban.com") // 协议:http,域名api.lemonban.com
private String url;
@Excel(name="ContentType")
private String contentType;
public API() {
super();
}
public API(String id ,String name,String method,String url,String contentType) {
super();
this.id=id;
this.name=name;
this.method=method;
this.url = url;
this.contentType=contentType;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getMethod() {
return method;
}
public void setMethod(String method) {
this.method = method;
}
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public String getContentType() {
return contentType;
}
public void setContentType(String contentType) {
this.contentType = contentType;
}
@Override
public String toString() {
// return "API [id=%s,name=%s,method=%s,url=%s,contentType=%s]"(id,name,method,url,contentType;
return "API [id="+id+",name="+name+",method="+method+",url=" + url + ",contentType="+contentType+"]\n";
}
}
package com.lujier.pojo;
public class Case {
// 用例编号,用例描述,参数,接口编号
@Excel(name="Case_id")
private String id;
@Excel(name="Desc")
private String desc;
@Excel(name="Params")
private String params;
@Excel(name="API_No")
private String apiId;
public Case() {
super();
}
public Case(String caseId,String caseDesc,String caseParams,String apiId) {
super();
this.id=caseId;
this.desc =caseDesc;
this.params = caseParams;
this.apiId = apiId;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getDesc() {
return desc;
}
public void setDesc(String desc) {
this.desc = desc;
}
public String getParams() {
return params;
}
public void setParams(String params) {
this.params = params;
}
public String getApiId() {
return apiId;
}
public void setApiId(String apiId) {
this.apiId = apiId;
}
@Override
public String toString() {
return "case [id="+id+",desc="+desc+",params="+params+",apiId=" + apiId+"]\n";
}
}
3. Excel读写
1) 读取API
package com.lujier.utils;
public class ExcelUtilsEasyPoi {
public static String file_path="src/test/resources/cases_V2.xlsx";
public static void main(String[] args) {
try {
FileInputStream inputs = new FileInputStream(file_path);
// 导入参数设置类(默认有一系列参数)
ImportParams params = new ImportParams();
// importExcel需3个参数,第一个:打开指定文件的输入文件流对象,
// 第二个参数,被映射的类对象是哪个 --- 字节码对象
// 第三个参数:导入参数设置对象
List<API> importExcel = ExcelImportUtil.importExcel(inputs, API.class, params);
for(API api:importExcel) {
System.out.print(api);
}
inputs.close();
}catch(Exception e) {
e.printStackTrace();
}
}
结果如下:
2) 读取register用例数据
public class ExcelUtilsEasyPoi {
// 文件路径
public static String file_path="src/test/resources/cases_V2.xlsx";
public static void main(String[] args) {
// getAPI();
getCase();
}
private static void getCase() {
try {
FileInputStream inputs = new FileInputStream(file_path);
// 导入参数设置类(默认有一系列参数)
ImportParams params = new ImportParams();
params.setStartSheetIndex(1); // 0). 设置从第2个表格开始读取
// params.setHeadRows(1); // 1). 设置表头行数,默认1,也建议设置为1不要修改,避免影响映射关系
// params.setStartRows(2); // 2). 设置从第3行后开始读取(不算标题行)
// params.setStartRows(3); // 3). 设置从第3行后开始读取(不算标题行)
// params.setNeedVerify(true); // 4) 校验上传的Excel: 默认false,不校验,设置为true则表示需要校验,需要导入库(可查看pox.xml说明)
List<Case> importExcel = ExcelImportUtil.importExcel(inputs,Case.class, params);
for(Case caseone:importExcel) {
System.out.print(caseone);
}
inputs.close();
}catch(Exception e) {
e.printStackTrace();
}
}
结果运行: