/**
* 生成固定格式的excel,表格都为文本,水平居左,垂直居中
*
* @param sheetName sheet名称,默认为sheet1
* @param content Map,要生成excel的数据来源
* @param os excel输出流
*/
public void exportFormatExcel(
Map<String, String[][]> content,
String[] salary_name_array,
String sheetName,
OutputStream os) {
if (VerifyUtil.isNullObject(content, os) || content.size() == 0) {
return;
}
// 默认名称
if (VerifyUtil.isNullObject(sheetName)) {
sheetName = "sheet1";
}
WritableWorkbook workbook = null;
try {
workbook = Workbook.createWorkbook(os);
WritableSheet sheet = workbook.createSheet(sheetName, 0);
int index = 0;
for (int k = 0; k < salary_name_array.length; k++) {
String[][] value = (String[][]) content.get(salary_name_array[k]);
if (value != null && value.length > 0) {
if (index != 0) {
index++;
}
WritableCellFormat format1 = new WritableCellFormat();
format1.setAlignment(jxl.format.Alignment.LEFT);
format1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
Label label1 = new Label(0, index, salary_name_array[k], format1);
sheet.addCell(label1);
for (int i = 0; i < value.length; i++) {
index++;
for (int j = 0; j < value[i].length; j++) {
if (value[i][j] == null) {
value[i][j] = "";
}
WritableCellFormat format = new WritableCellFormat();
format.setAlignment(jxl.format.Alignment.LEFT);
format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
Label label = new Label(j, index, value[i][j], format);
sheet.addCell(label);
}
}
}
}
workbook.write();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
workbook.close();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
/**
* 判断字符串是否由纯数字组成
*
* @param str 源字符串
* @return true是，false否
*/
private boolean isNumeric(String str) {
if (VerifyUtil.isNullObject(str)) {
return false;
}
Pattern pattern = Pattern.compile("[0-9]*");
return pattern.matcher(str).matches();
}
/**
* 合并表格
*
* @param sheet 工作表
* @param mergeInfo 要合并的表格的信息
* @throws RowsExceededException
* @throws NumberFormatException
* @throws WriteException
*/
private void merge(WritableSheet sheet, String[] mergeInfo)
throws RowsExceededException, NumberFormatException, WriteException {
if (VerifyUtil.isNullObject(sheet) || VerifyUtil.isNull1DArray(mergeInfo)) {
return;
} else if (!this.isMergeInfo(mergeInfo)) {
return;
} else {
for (String str : mergeInfo) {
String[] temp = str.split(",");
sheet.mergeCells(
Integer.parseInt(temp[1]),
Integer.parseInt(temp[0]),
Integer.parseInt(temp[3]),
Integer.parseInt(temp[2]));
}
}
}
/**
* 判断合并项内容是否合法
*
* @param mergeInfo 合并项 每一项的数据格式为0,1,0,2即把(0,1)和(0,2)合并
* @return true合法,false非法
*/
private boolean isMergeInfo(String[] mergeInfo) {
if (VerifyUtil.isNull1DArray(mergeInfo)) {
return false;
} else {
for (String str : mergeInfo) {
String[] temp = str.split(",");
if (VerifyUtil.isNull1DArray(temp) || temp.length != 4) {
return false;
} else {
for (String s : temp) {
if (!isNumeric(s)) {
return false;
}
}
}
}
}
return true;
}
/**
* 生成固定格式的excel,表格都为文本,水平居左,垂直居中
*
* @param sheetName sheet名称,默认为sheet1
* @param content 二维数组,要生成excel的数据来源
* @param os excel输出流
*/
public void exportFormatExcel(String[][] content, String sheetName, OutputStream os) {
if (VerifyUtil.isNullObject(content, os) || VerifyUtil.isNull2DArray(content)) {
return;
}
// 默认名称
if (VerifyUtil.isNullObject(sheetName)) {
sheetName = "sheet1";
}
WritableWorkbook workbook = null;
try {
workbook = Workbook.createWorkbook(os);
WritableSheet sheet = workbook.createSheet(sheetName, 0);
for (int i = 0; i < content.length; i++) {
for (int j = 0; j < content[i].length; j++) {
if (content[i][j] == null) {
content[i][j] = "";
}
WritableCellFormat format = new WritableCellFormat();
format.setAlignment(jxl.format.Alignment.LEFT);
format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
Label label = new Label(j, i, content[i][j], format);
sheet.addCell(label);
}
}
workbook.write();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
workbook.close();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
/**
* 处理要居中的行或列的数据
*
* @param indexes 行标或列标
* @return 行坐标或列坐标组成的集合
*/
private Set<Integer> getInfo(String indexes) {
Set<Integer> set = new HashSet<Integer>();
if (VerifyUtil.isNullObject(indexes)) {
return set;
}
String[] temp = indexes.split(",", 0);
for (String str : temp) {
if (isNumeric(str)) {
set.add(Integer.parseInt(str));
}
}
return set;
}
/**
* 判断字符串是否是数字
*
* @param str 源字符串
* @return true是，false否
*/
private boolean isNumber(String number) {
// 判断参数
if (VerifyUtil.isNullObject(number)) {
return false;
}
// 查看是否有小数点
int index = number.indexOf(".");
if (index < 0) {
return isNumeric(number);
} else {
// 如果有多个".",则不是数字
if (number.indexOf(".") != number.lastIndexOf(".")) {
return false;
}
String num1 = number.substring(0, index);
String num2 = number.substring(index + 1);
return isNumeric(num1) && isNumeric(num2);
}
}
public InterfaceSteps() throws IOException {
verifyList = new ArrayList<>();
Given(
"^设置接口名称 (.*)$",
(String name) -> {
getRequestData().setInterfaceName(name);
});
And(
"^设置请求url (.*)$",
(String url) -> {
if (Common.hasBrance(url)) {
try {
url = Common.regularBrace(url, InterfaceEnv.global);
url = url.replace("\"", "");
} catch (InterfaceException e) {
Assert.assertTrue(false, e.getMessage());
}
}
getRequestData().setUrl(url);
});
And(
"^设置method (.*)$",
(String method) -> {
getRequestData().setMethod(method);
});
Given(
"^设置DB:$",
(String json) -> {
Config.DBinit(json);
});
And(
"^设置请求数据:$",
(String testData) -> {
testData = VerifyUtil.pathOrText(testData);
getRequestData().setBody(testData);
});
And(
"^设置headers:$",
(String headers) -> {
headers = VerifyUtil.headers(headers, InterfaceEnv.global);
getRequestData().setHeaders(headers);
});
Given(
"^初始化脚本package (.*)$",
(List<String> paths) -> {
Python.setPaths(paths);
});
And(
"^执行脚本,路径 (.*) 方法 (.*) 参数 (.*)$",
(String path, String method, String args) -> {
Script.evalScript(path, method, args);
});
Given(
"^设置全局变量 (.*)$",
(String global) -> {
updateGlobal(global);
});
Given(
"^查看全局变量$",
() -> {
Config.getLogger().info("全局变量:\n" + jsonPrettyPrint(global));
});
When(
"^执行请求$",
() -> {
beginHttp();
});
Given(
"^数据库中获取数据设置到全局变量中, sql (.*),获取行数 (.*), 获取的参数 (.*)$",
(String sql, Integer index, String list) -> {
try {
mysql = new DbUtil(Config.env);
String json;
if (Common.hasBrance(sql)) {
sql = Common.regularBrace(sql, InterfaceEnv.global);
json = mysql.getDataBySql(sql, index, list);
} else {
json = mysql.getDataBySql(sql, index, list);
}
updateGlobal(json);
} catch (InterfaceException e) {
Assert.assertTrue(false, e.getMessage());
}
});
Then(
"^从全局变量中取出字段 (.*) 的值,是否等于 (.*)$",
(String index, String expected) -> {
Object actual = JsonPath.read(InterfaceEnv.global, index);
Assert.assertEquals(
actual.toString(), expected, "全局变量中的" + index + ": " + actual + ", 不等于: " + expected);
});
Then(
"^比较两个全局变量中的字段 (.*) 是否等于字段 (.*)$",
(String index1, String index2) -> {
Object actual1 = JsonPath.read(InterfaceEnv.global, index1);
Object actual2 = JsonPath.read(InterfaceEnv.global, index2);
Assert.assertEquals(
actual1.toString(),
actual2.toString(),
"全局变量中的" + index1 + ": " + actual1 + ", 不等于" + index2 + ": " + actual2);
});
Then(
"^最近一次请求响应状态是否是 (.*)$",
(Integer status) -> {
// Assert.assertEquals(getResponse().code(), (int)status, "接口: " +
// getRequestData().getInterfaceName() + " code是: " + getResponse().code());
getResponse().then().statusCode(status);
});
Then(
"^jsonSchema验证response:$",
(String testData) -> {
testData = VerifyUtil.pathOrText(testData);
getResponse().then().body(matchesJsonSchemaInClasspath(testData));
});
Then(
"^当前responseBody中的 (.*) 是否等于 (.*)$",
(String jsonPath, String content) -> {
getResponse().then().body(jsonPath, equalTo(content));
});
Then(
"^当前responseBody中header的Content-Type是否等于 (.*)$",
(String contentType) -> {
getResponse().then().header("Content-Type", contentType);
});
Then(
"^当前responseBody中header的 (.*) 是否等于 (.*)$",
(String key, String contentType) -> {
getResponse().then().header(key, contentType);
});
Then(
"^当前responseBody中的Cookie是否包含 (.*)$",
(String cookieKey) -> {
getResponse().then().cookie(cookieKey);
});
Then(
"^当前responseBody中Cookie的 (.*) 是否等于 (.*)$",
(String key, String value) -> {
getResponse().then().cookie(key, value);
});
}
/**
* 生成具有一定格式excel
*
* @param sheetName sheet名称,默认为sheet1
* @param nf 数字类型的格式 如:jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.##");默认无格式
* @param content 二维数组,要生成excel的数据来源
* @param 合并项 每一项的数据格式为0,1,0,2 即：把(0,1)和(0,2)合并--->第1列的第一、二个元素合并
* @param os excel输出流
* @param row 需要水平居中的行,默认居左。以逗号分隔的字符串
* @param col 需要水平居中的列,默认居左。以逗号分隔的字符串
*/
public void export(
String sheetName,
NumberFormat nf,
String[][] content,
String[] mergeInfo,
OutputStream os,
String row,
String col) {
if (VerifyUtil.isNullObject(content, os) || VerifyUtil.isNull2DArray(content)) {
return;
}
// 默认名称
if (VerifyUtil.isNullObject(sheetName)) {
sheetName = "sheet1";
}
Set<Integer> rows = this.getInfo(row);
Set<Integer> cols = this.getInfo(col);
WritableWorkbook workbook = null;
try {
workbook = Workbook.createWorkbook(os);
WritableSheet sheet = workbook.createSheet(sheetName, 0);
for (int i = 0; i < content.length; i++) {
for (int j = 0; j < content[i].length; j++) {
if (content[i][j] == null) {
content[i][j] = "";
}
if (isNumber(content[i][j]) && !rows.contains(i) && !cols.contains(j)) { // 处理数字
Number number = null;
if (VerifyUtil.isNullObject(nf)) { // 数字无格式
number = new Number(j, i, Double.valueOf(content[i][j]));
} else { // 如果有格式,按格式生成
jxl.write.WritableCellFormat wcfn = new jxl.write.WritableCellFormat(nf);
number = new Number(j, i, Double.valueOf(content[i][j]), wcfn);
}
sheet.addCell(number);
} else { // 处理非数字
WritableCellFormat format = new WritableCellFormat();
if (rows.contains(i) || cols.contains(j)) {
format.setAlignment(jxl.format.Alignment.CENTRE);
} else {
format.setAlignment(jxl.format.Alignment.LEFT);
}
format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
Label label = new Label(j, i, content[i][j], format);
sheet.addCell(label);
}
}
}
this.merge(sheet, mergeInfo);
workbook.write();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
workbook.close();
os.close();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
