首页 > 代码库 > Java + Excel 接口自动化
Java + Excel 接口自动化
最近项目比较悠闲，想找点事干，写了个 Excel 接口测试的 "框架" 以前用 python 写过一个，这次用 java, 应该说框架都不算，反正就是写了，能帮我解决问题就行。
当然咯，也许会问干嘛那么麻烦直接用 feed4testng, 或者 testng 就行了，没事找事干还专门写个这玩意... 呵呵，就闲的蛋疼！
文笔有限不知道怎么写，直接上代码：
欢迎各位指定，或提出好的意见，总觉得还有很多不好的地方。
结构就这破样了， E 文也不好， 随便捣鼓，开心就好。 哈哈
ExcelUtil.java 类：
1 package com.hozhu.excel; 2 3 import java.io.BufferedInputStream; 4 import java.io.File; 5 import java.io.FileInputStream; 6 import java.io.FileOutputStream; 7 import java.io.IOException; 8 import java.util.ArrayList; 9 import java.util.HashMap; 10 import java.util.List; 11 import java.util.Map; 12 13 import org.apache.log4j.Logger; 14 import org.apache.poi.ss.usermodel.CellStyle; 15 import org.apache.poi.ss.usermodel.IndexedColors; 16 import org.apache.poi.xssf.usermodel.XSSFCell; 17 import org.apache.poi.xssf.usermodel.XSSFCellStyle; 18 import org.apache.poi.xssf.usermodel.XSSFRow; 19 import org.apache.poi.xssf.usermodel.XSSFSheet; 20 import org.apache.poi.xssf.usermodel.XSSFWorkbook; 21 22 /** 23 * 只支持读取 .xlsx 所有方法读取数据时, 未对 Excel 格式类型进行判断处理 如 Excel 中有特殊数据类型, 需在 Excel 24 * 中标注为文本类型, 程序才能正常处理 25 * 26 * @author Roger 27 * @version 1.0 28 */ 29 public class ExcelUtil { 30 private static Logger logger = Logger.getLogger(ExcelUtil.class); 31 32 private String filePath = null; 33 private String sheetName = null; 34 35 public String getSheetName() { 36 return sheetName; 37 } 38 39 public void setSheetName(String sheetName) { 40 this.sheetName = sheetName; 41 } 42 43 public String getFilePath() { 44 return filePath; 45 } 46 47 public void setFilePath(String filePath) { 48 this.filePath = filePath; 49 } 50 51 /** 52 * 判断成员变量 filePath 是否为空 53 * 54 * @return 55 */ 56 private boolean isFilePathEmpty() { 57 boolean flag = false; 58 if ((null != filePath && (!filePath.equals("")))) { 59 flag = true; 60 } 61 return flag; 62 } 63 64 private boolean isSheetNameEmpty() { 65 boolean flag = false; 66 if ((null != sheetName && (!sheetName.equals("")))) { 67 flag = true; 68 } 69 return flag; 70 } 71 72 // 使用静态内部类创建外部类对象 73 private static class Excel { 74 private static ExcelUtil excelUtil = new ExcelUtil(); 75 } 76 77 private ExcelUtil() { 78 } 79 80 // 获取 ExcelUtil 实例 81 public static ExcelUtil getInstance() { 82 return Excel.excelUtil; 83 } 84 85 /** 86 * 检查传入的文件后缀是否为 xlsx 87 * 88 * @param filePacht 89 * @return 90 */ 91 public boolean checkXlsx(File file) { 92 boolean flag = false; 93 94 if (file.exists()) { 95 String str = file.getName(); 96 if (str.substring(str.lastIndexOf(".") + 1).equals("xlsx")) 97 flag = true; 98 } else { 99 logger.info(file.getName() + ", 文件不存在...");100 // System.out.println("文件不存在...");101 }102 103 return flag;104 }105 106 /**107 * 108 * @param file109 * @return true: 文件未被操作; false: 文件正在被操作.110 */111 public boolean isFile(File file) {112 return file.renameTo(file);113 }114 115 public File createFile() {116 File file = null;117 if (isFilePathEmpty()) {118 file = new File(filePath);119 } else {120 logger.error("filePath 不能为: "121 + filePath122 + ", 请先使用 ‘ExcelUtil.getInstance().setFilePath(filePath)‘ 设置!");123 // System.out.println("filePath 不能为: " + filePath +124 // ", 请先使用 ‘ExcelUtil.getInstance().setFilePath(filePath)‘ 设置!");125 System.exit(-1);126 }127 return file;128 }129 130 /**131 * 创建 XSSFWorkbook132 * 133 * @param 文件路径134 * @return135 */136 public XSSFWorkbook createExcelWorkBook() {137 File file = createFile();138 139 BufferedInputStream in = null;140 XSSFWorkbook book = null;141 142 if (checkXlsx(file)) {143 try {144 in = new BufferedInputStream(new FileInputStream(file));145 book = new XSSFWorkbook(in);146 } catch (IOException e) {147 e.printStackTrace();148 }149 }150 return book;151 }152 153 /**154 * 创建 XSSFSheet155 * 156 * @param sheetName157 * @return158 */159 public XSSFSheet createExcelSheet(String sheetName) {160 XSSFSheet sheet = null;161 if (isSheetNameEmpty()) {162 XSSFWorkbook book = createExcelWorkBook();163 if (book != null) {164 int sheetCount = book.getNumberOfSheets();165 for (int i = 0; i < sheetCount; i++) {166 if (sheetName.equals(book.getSheetName(i))) {167 sheet = book.getSheet(sheetName);168 break;169 }170 }171 }172 } else {173 logger.error("sheetName 不能为: "174 + sheetName175 + ", 请先使用 ‘ExcelUtil.getInstance().setSheetName(SheetName)‘ 设置!");176 // System.out.println("sheetName 不能为: " + sheetName +177 // ", 请先使用 ‘ExcelUtil.getInstance().setSheetName(SheetName)‘ 设置!");178 System.exit(-1);179 }180 181 return sheet;182 }183 184 /**185 * 获取指定行186 * 187 * @param sheetName188 * @param line189 * 行索引, 从 0 开始190 * @return191 */192 public XSSFRow getExcelRow(int line) {193 XSSFRow row = null;194 if (line <= getSheetMaxRow())195 row = createExcelSheet(sheetName).getRow(line);196 return row;197 }198 199 /**200 * 获取指定 sheet 中最大行数201 * 202 * @param sheetName203 * @return204 */205 public int getSheetMaxRow() {206 int maxRow = -1;207 if (createExcelSheet(sheetName) != null)208 maxRow = createExcelSheet(sheetName).getLastRowNum();209 return maxRow;210 }211 212 /**213 * 使用正则表达式去掉多余的.与0214 * 215 * @param s216 * @return217 */218 private String subZeroAndDot(String s) {219 if (s.indexOf(".") > 0) {220 // 去掉多余的 0221 s = s.replaceAll("0+?$", "");222 // 如果最后一位是.则去掉223 s = s.replaceAll("[.]$", "");224 }225 return s;226 }227 228 /**229 * 获取 Excel 指定行数据230 * 231 * @param sheetName232 * @param line233 * @return 返回一个一维数组234 */235 public String[] readExcelRows(int line) {236 String[] result = null;237 XSSFRow row = getExcelRow(line);238 int maxRow = getSheetMaxRow();239 if (row != null && maxRow > -1) {240 int columnNum = row.getLastCellNum();241 result = new String[columnNum];242 for (int i = 0; i < columnNum; i++) {243 // 判断单元格是否为空, 不进行判断时, 如遇到空白单元格时, 抛出空指针异常244 if (null != row.getCell(i))245 result[i] = subZeroAndDot(row.getCell(i).toString().trim());246 }247 }248 return result;249 }250 251 /**252 * 获取指定单元格中内容253 * 254 * @param sheetName255 * @param line256 * 行257 * @param column258 * 列259 * @return260 */261 public String readExcelCell(int line, int column) {262 String[] value = http://www.mamicode.com/null;263 String result = "";264 value =http://www.mamicode.com/ readExcelRows(line);265 if (value != null) {266 result = value[column];267 }268 return result;269 }270 271 /**272 * 从指定行开始读取 Excel 中所有数据273 * 274 * @param sheetName275 * sheet 名称276 * @param line277 * 表标题所在行278 * @return 返回一个包含 HashMap<String, String> 的 ArrayList; 格式：[{第一行数据},{第二行数据}]279 * {列 1 标题 = 列 1 值, 列 2 标题 = 列 2 值}280 */281 public List<Map<String, String>> readExcelAllData(int line) {282 List<Map<String, String>> result = new ArrayList<Map<String, String>>();283 // 读取标题行284 String[] titleRow = readExcelRows(line);285 int maxRow = getSheetMaxRow();286 XSSFRow row;287 if (null != titleRow && maxRow != -1) {288 // i = line + 1; 标题的下一行开始取数据289 for (int i = line + 1; i <= maxRow; i++) {290 row = getExcelRow(i);291 Map<String, String> map = new HashMap<String, String>();292 for (int j = 0; j < row.getLastCellNum(); j++) {293 if (null != row.getCell(j)) {294 String value =http://www.mamicode.com/ subZeroAndDot(row.getCell(j).toString()295 .trim());296 map.put(titleRow[j], value);297 }298 }299 result.add(map);300 }301 }302 return result;303 }304 305 /**306 * 从指定数据行开始读取 Excel 所有数据307 * 308 * @param sheetName309 * sheet 名称310 * @param line311 * 数据读取开始行312 * @return313 */314 public String[][] readDataArrayAll(int line) {315 ArrayList<Object> list = new ArrayList<Object>();316 int maxRow = getSheetMaxRow();317 XSSFRow row = null;318 int columnNum = 0;319 if (maxRow != -1) {320 for (int i = line; i <= maxRow; i++) {321 row = getExcelRow(i);322 columnNum = row.getLastCellNum();323 String[] values = new String[columnNum];324 325 for (int j = 0; j < columnNum; j++) {326 if (null != row.getCell(j)) {327 String tempStr = subZeroAndDot(row.getCell(j)328 .toString().trim());329 values[j] = tempStr;330 }331 }332 list.add(values);333 }334 }335 336 String[][] result = new String[list.size()][columnNum];337 338 for (int i = 0; i < result.length; i++) {339 result[i] = (String[]) list.get(i);340 }341 return result;342 }343 344 /**345 * 从指定数据行开始读取 Excel 所有数据346 * 347 * @param sheetName348 * sheet 名称349 * @param line350 * 数据读取开始行351 * @return 返回一个包含 list 的 list; 格式 [[第一行数据],[第二行数据]]352 */353 public List<List<String>> readDataListAll(int line) {354 List<List<String>> result = new ArrayList<List<String>>();355 356 int maxRow = getSheetMaxRow();357 XSSFRow row = null;358 359 if (maxRow != -1) {360 for (int i = line; i <= maxRow; i++) {361 row = getExcelRow(i);362 List<String> list = new ArrayList<String>();363 for (int j = 0; j < row.getLastCellNum(); j++) {364 if (null != row.getCell(j)) {365 String value =http://www.mamicode.com/ subZeroAndDot(row.getCell(j).toString()366 .trim());367 list.add(value);368 }369 }370 result.add(list);371 }372 }373 return result;374 }375 376 /**377 * 将数据写入指定单元格378 * 379 * @param sheetName380 * sheet 名称381 * @param line382 * 需写入的行383 * @param column384 * 需写入的列385 * @param content386 * 需写入的内容387 * @throws IOException388 */389 public void writeExcelCell(int line, int column, String content)390 throws IOException {391 XSSFWorkbook book = createExcelWorkBook();392 XSSFSheet sheet = book.getSheet(sheetName);393 394 if (line <= sheet.getLastRowNum() && line >= 0) {395 XSSFRow row = sheet.getRow(line);396 397 if (column < row.getLastCellNum() && column >= 0) {398 if (isFile(createFile())) {399 FileOutputStream out = new FileOutputStream(createFile());400 XSSFCell cell = row.getCell(column);401 if (null == cell) {402 cell = row.createCell(column);403 }404 cell.setCellValue(content);405 book.write(out);406 out.close();407 } else {408 logger.error("文件: " + filePath + ", 正被使用, 请关闭! 程序执行终止...");409 // System.out.println("文件: " + filePath410 // + ", 正被使用, 请关闭! 程序执行终止...");411 System.exit(-1);412 }413 } else414 logger.error("列索引越界...");415 // System.out.println("列索引越界...");416 } else417 logger.error("行索引越界...");418 // System.out.println("行索引越界...");419 }420 421 /**422 * 从指定行的下一行开始读取某列的所有数据423 * @param titleLineIndex 指定行读取424 * @param columnName 列名425 * @return426 */427 public String[] readExcelColumnData(int titleLineIndex, String columnName) {428 String[] result = null;429 int columnIndex = getColumnIndex(titleLineIndex, columnName);430 int maxRow = getSheetMaxRow();431 432 if (columnIndex != -1 && maxRow != -1) {433 result = new String[maxRow - titleLineIndex];434 435 for (int i = 0; i < maxRow - titleLineIndex; i++) {436 result[i] = readExcelCell(titleLineIndex + 1 + i, columnIndex);437 }438 }439 return result;440 }441 442 443 /**444 * 获取列的索引445 * 446 * @param sheetName447 * sheet 名称448 * @param line449 * 需要获取的行450 * @param columnName451 * 列名452 * @return453 */454 public int getColumnIndex(int line, String columnName) {455 int index = -1;456 String[] title = readExcelRows(line);457 458 if (null != title) {459 for (int i = 0; i < title.length; i++) {460 if (columnName.equals(title[i]))461 index = i;462 }463 }464 return index;465 }466 467 /**468 * 设置单元格背景色469 * 470 * @param sheetName471 * sheet 名称472 * @param titleRow473 * 列标题所在行号, 索引 0 开始474 * @param columnName475 * 需要获取列索引的列名称476 * @param line477 * 需要设置颜色单元格所在行478 * @param color479 * 需设置的颜色; 0: 红色; 1: 绿色; 2: 灰色; 3: 黄色; 4: 白色.480 * @throws IOException481 */482 public void setCellBackgroundColor(int titleRow, String columnName,483 int line, int color) throws IOException {484 XSSFWorkbook book = createExcelWorkBook();485 XSSFSheet sheet = book.getSheet(sheetName);486 487 int columnIndex = getColumnIndex(titleRow, columnName);488 XSSFRow row = sheet.getRow(line);489 XSSFCell cell = row.getCell(columnIndex);490 XSSFCellStyle old = cell.getCellStyle();491 XSSFCellStyle temp = book.createCellStyle();492 temp.cloneStyleFrom(old); // 拷贝旧的样式493 494 switch (color) {495 case 0:496 // 红色497 temp.setFillForegroundColor(IndexedColors.RED.getIndex());498 break;499 case 1:500 // 绿色501 temp.setFillForegroundColor(IndexedColors.BRIGHT_GREEN.getIndex());502 break;503 case 2:504 // 灰色505 temp.setFillForegroundColor(IndexedColors.GREY_50_PERCENT506 .getIndex());507 break;508 case 3:509 // 黄色510 temp.setFillForegroundColor(IndexedColors.YELLOW.getIndex());511 break;512 case 4:513 // 白色514 temp.setFillForegroundColor(IndexedColors.WHITE.getIndex());515 break;516 default:517 System.out.println("设定颜色参数 (color) 错误...");518 break;519 }520 521 temp.setFillPattern(CellStyle.SOLID_FOREGROUND);522 // XSSFFont font = book.createFont();523 // font.setFontHeightInPoints((short)9); // 字体大小524 // font.setFontName("宋体");525 // temp.setFont(font);526 cell.setCellStyle(temp);527 if (isFile(createFile())) {528 FileOutputStream out = new FileOutputStream(createFile());529 book.write(out);530 out.close();531 } else {532 logger.error("文件: " + filePath + ", 正被使用, 请关闭! 程序执行终止...");533 // System.out.println("文件: " + filePath + ", 正被使用, 请关闭! 程序执行终止...");534 System.exit(-1);535 }536 }537 538 }
HttpRequester.java类：
1 package com.hozhu.api; 2 3 import java.io.BufferedReader; 4 import java.io.IOException; 5 import java.io.InputStreamReader; 6 import java.io.PrintWriter; 7 import java.net.HttpURLConnection; 8 import java.net.URL; 9 import java.util.HashMap; 10 import java.util.Map; 11 12 public class HttpRequester { 13 /** 14 * 向指定 URL 发送POST方法的请求 15 * 16 * @param method 17 * 指定请求方法：GET, POST 等 18 * @param url 19 * 发送请求的 URL 20 * @param param 21 * 请求参数，请求参数是 name1=value1&name2=value2 的形式。 22 * @return result 返回结果 23 */ 24 public static Map<String, String> sendPost(String method, String url, 25 String param) { 26 PrintWriter out = null; 27 BufferedReader br = null; 28 String result = ""; 29 int responseCode = 0; 30 Map<String, String> map = new HashMap<String, String>(); 31 try { 32 // 打开和URL之间的连接 33 HttpURLConnection httpConn = (HttpURLConnection) new URL(url) 34 .openConnection(); 35 36 // 发送POST请求必须设置如下两行 37 // 设置可输入、 可输出 38 httpConn.setDoInput(true); 39 httpConn.setDoOutput(true); 40 41 httpConn.setReadTimeout(150000); 42 httpConn.setConnectTimeout(15000); 43 44 // 连接后不自动跳转 45 httpConn.setInstanceFollowRedirects(false); 46 47 // 设置通用的请求属性 48 httpConn.setRequestProperty("Accept-Charset", "utf-8"); 49 httpConn.setRequestProperty("User-Agent", "systempatch"); 50 httpConn.setRequestProperty("Accpet-Encoding", "gzip"); 51 52 // 设置提交方式 53 httpConn.setRequestMethod(method); 54 55 // httpConn.connect(); 56 57 // 获取HttpURLConnection对象对应的输出流 58 out = new PrintWriter(httpConn.getOutputStream()); 59 60 // 发送请求参数 61 out.print(param); 62 out.flush(); 63 responseCode = httpConn.getResponseCode(); 64 map.put("code", String.valueOf(responseCode)); 65 // 打印 http 状态码 66 // System.out.println("responseCode: " + responseCode); 67 68 if (HttpURLConnection.HTTP_OK == responseCode) { 69 // 定义BufferedReader输入流来读取URL的响应 70 br = new BufferedReader(new InputStreamReader( 71 httpConn.getInputStream(), "utf-8")); 72 String strLine; 73 StringBuffer responseBuf = new StringBuffer(); 74 75 while ((strLine = br.readLine()) != null) { 76 responseBuf.append(strLine); 77 } 78 79 result = responseBuf.toString(); 80 map.put("result", result); 81 } 82 83 } catch (Exception e) { 84 System.out.println("发送 POST 请求出现异常！" + e); 85 e.printStackTrace(); 86 } 87 // 使用finally块来关闭输出流、输入流 88 finally { 89 try { 90 if (out != null) { 91 out.close(); 92 } 93 if (br != null) { 94 br.close(); 95 } 96 } catch (IOException ex) { 97 ex.printStackTrace(); 98 } 99 }100 return map;101 }102 }
MobileApiTools.java 类
1 package com.hozhu.api; 2 3 import java.io.IOException; 4 import java.text.SimpleDateFormat; 5 import java.util.Date; 6 7 import com.hozhu.excel.ExcelUtil; 8 9 public class MobileApiTools { 10 private MobileApiTools() { 11 } 12 13 /** 14 * 获取当前系统时间 15 * 16 * @return 17 */ 18 public static String getDate() { 19 SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 20 return df.format(new Date()); 21 } 22 23 /** 24 * 期望结果与实际结果比较 25 * 26 * @param expectedResult 27 * @param actualResult 28 * @return 29 */ 30 public static String assertResult(String expectedResult, String actualResult) { 31 String result; 32 if (expectedResult.equals(actualResult)) 33 result = "OK"; 34 else 35 result = "NG"; 36 return result; 37 38 } 39 40 /** 41 * 初始化 Excel 中指定列数据 42 * 43 * @param filePath 44 * 文件路径 45 * @param sheetName 46 * sheet 名称 47 * @param titleLineIndex 48 * 表标题所在行索引 49 * @param columnName 50 * 列名称 51 * @param content 52 * 需写入的内容 53 * @param color 54 * 需设置的单元格颜色 55 * @throws IOException 56 */ 57 public static void initializeData(int titleLineIndex, String columnName, 58 String content, int color) throws IOException { 59 int maxRow = ExcelUtil.getInstance().getSheetMaxRow(); 60 int columnIndex = ExcelUtil.getInstance().getColumnIndex( 61 titleLineIndex, columnName); 62 if (maxRow != -1 && columnIndex != -1) { 63 for (int i = titleLineIndex + 1; i <= maxRow; i++) { 64 // 初始化单元格内容 65 ExcelUtil.getInstance().writeExcelCell(i, columnIndex, content); 66 // 设置单元格颜色 67 ExcelUtil.getInstance().setCellBackgroundColor(titleLineIndex, 68 columnName, i, color); 69 } 70 } 71 } 72 73 /** 74 * 将执行结果写入 Excel 中 75 * 76 * @param filePath 77 * 文件路径 78 * @param sheetName 79 * sheet 名称 80 * @param titleLineIndex 81 * 标题所在索引行 82 * @param writeStartRow 83 * 写入起始行 84 * @param columnName 85 * 列名称 86 * @param content 87 * 写入内容 88 */ 89 public static void writeResult(int titleLineIndex, int writeStartRow, 90 String columnName, String content) { 91 92 int columnIndex = ExcelUtil.getInstance().getColumnIndex( 93 titleLineIndex, columnName); 94 if (columnIndex != -1) { 95 try { 96 ExcelUtil.getInstance().writeExcelCell(writeStartRow, 97 columnIndex, content); 98 } catch (IOException e) { 99 e.printStackTrace();100 }101 }102 }103 104 /**105 * 表标题行是否包含指定参数106 * @param argLineIndex 参数名称所在单元格行索引107 * @param argColumnIndex 参数名称所在单元格列索引108 * @param titleLineIndex 表标题所在行索引109 * @return110 */111 public static boolean isArgEquals(int argLineIndex, int argColumnIndex,112 int titleLineIndex) {113 String[] argArray = null;114 boolean flag = false;115 116 // 获取所在单元格的参数列表117 String args = ExcelUtil.getInstance().readExcelCell(argLineIndex, argColumnIndex);118 if (!args.equals("")) {119 argArray = args.split("\\|");120 } else {121 System.out.println("文件: " + ExcelUtil.getInstance().getFilePath()122 + ", sheetName: " + ExcelUtil.getInstance().getSheetName()123 + ", 获取参数失败...");124 }125 126 if (argArray != null) {127 for (int i = 0; i < argArray.length; i++) {128 int tempIndex = ExcelUtil.getInstance().getColumnIndex(129 titleLineIndex, argArray[i]);130 if (tempIndex >= 0)131 flag = true;132 else133 System.out.println("参数: " + argArray[i]134 + ", 不存在表标题行中, 请检查...");135 }136 }137 return flag;138 }139 140 public void info() {141 142 }143 }
LoginAPI.java 类
1 package com.hozhu.cases; 2 3 import java.io.IOException; 4 import java.util.List; 5 import java.util.Map; 6 7 import org.apache.log4j.Logger; 8 import org.json.JSONException; 9 import org.json.JSONObject; 10 11 import com.hozhu.api.HttpRequester; 12 import com.hozhu.api.MobileApiTools; 13 import com.hozhu.excel.ExcelUtil; 14 15 public class LoginAPI { 16 private static Logger logger = Logger.getLogger(LoginAPI.class); 17 18 private final String FILE_PATH = "mobileApiCase.xlsx"; 19 private final String SHEET_NAME = "Login"; 20 private final int TITLE_LINE_INDEX = 4; 21 22 private final String RESULT_CODE = "ResultCode"; 23 private final String TEST_RESULT = "TestResult"; 24 private final String RUNNING_TIME = "RunningTime"; 25 private final String ACTUAL_RESULT = "ActualResult"; 26 private final String RUN = "Run"; 27 28 // 需要的参数常量 29 private final String MAIL = "mail"; 30 private final String MOBILE = "mobile"; 31 private final String PASS_WORD = "password"; 32 33 public LoginAPI() { 34 try { 35 logger.info(LoginAPI.class); 36 37 ExcelUtil.getInstance().setFilePath(FILE_PATH); 38 ExcelUtil.getInstance().setSheetName(SHEET_NAME); 39 40 logger.info("初始化: " + ExcelUtil.getInstance().getFilePath() + ", " + ExcelUtil.getInstance().getSheetName()); 41 42 MobileApiTools.initializeData(TITLE_LINE_INDEX, RUN, "N", 4); 43 MobileApiTools.initializeData(TITLE_LINE_INDEX, ACTUAL_RESULT, "", 44 4); 45 MobileApiTools.initializeData(TITLE_LINE_INDEX, RESULT_CODE, "", 4); 46 MobileApiTools.initializeData(TITLE_LINE_INDEX, TEST_RESULT, "NT", 47 2); 48 MobileApiTools 49 .initializeData(TITLE_LINE_INDEX, RUNNING_TIME, "", 4); 50 51 logger.info(ExcelUtil.getInstance().getFilePath() + ", " + ExcelUtil.getInstance().getSheetName() + "初始化完成"); 52 } catch (IOException e) { 53 e.printStackTrace(); 54 } 55 } 56 57 public void login() throws JSONException, IOException { 58 String url = ""; 59 String act = ""; 60 String method = ""; 61 List<Map<String, String>> data = http://www.mamicode.com/null; 62 boolean flag = false; 63 64 url = ExcelUtil.getInstance().readExcelCell(0, 1); 65 act = ExcelUtil.getInstance().readExcelCell(1, 1); 66 method = ExcelUtil.getInstance().readExcelCell(2, 1); 67 flag = MobileApiTools.isArgEquals(3, 1, TITLE_LINE_INDEX); 68 69 if (url.equals("") || act.equals("") || method.equals("") || !flag) { 70 logger.error("请检查 Excel 中 Interface、Act、Method、ArgName 是否设置正确..."); 71 //System.out 72 // .println("请检查 Excel 中 Interface、Act、Method、ArgName 是否设置正确..."); 73 System.exit(-1); 74 } 75 76 data = http://www.mamicode.com/ExcelUtil.getInstance().readExcelAllData(4); 77 78 if (data != null) { 79 for (int i = 0; i < data.size(); i++) { 80 Map<String, String> map = data.get(i); 81 String mail = map.get(MAIL); 82 String mobile = map.get(MOBILE); 83 String password = map.get(PASS_WORD); 84 String state = map.get("State"); 85 String expectedResult = map.get("ExpectedResult"); 86 String loginName; 87 88 // 如果 state == 0 则用 邮箱登录, 否则使用手机号码登录 89 if (Integer.parseInt(state) == 0) 90 loginName = mail; 91 else 92 loginName = mobile; 93 94 String param = "Act=" + act + "&" + "LoginName=" + loginName 95 + "&" + "Pwd=" + password; 96 97 Map<String, String> result = HttpRequester.sendPost(method, 98 url, param); 99 String code = result.get("code");100 String rsTmp = result.get("result");101 102 // 将字符串转换为 JSON103 JSONObject object = new JSONObject(rsTmp);104 String actualResult = object.getString("msg");105 106 String testResult = MobileApiTools.assertResult(expectedResult,107 actualResult);108 109 // 写入 Run 列, 执行纪录110 MobileApiTools.writeResult(TITLE_LINE_INDEX, TITLE_LINE_INDEX111 + 1 + i, RUN, "Y");112 113 // 写入 http code114 MobileApiTools.writeResult(TITLE_LINE_INDEX, TITLE_LINE_INDEX115 + 1 + i, RESULT_CODE, code);116 117 // 设置单元格颜色118 if (Integer.parseInt(code) == 200)119 ExcelUtil.getInstance().setCellBackgroundColor(120 TITLE_LINE_INDEX, RESULT_CODE,121 TITLE_LINE_INDEX + 1 + i, 1);122 else123 ExcelUtil.getInstance().setCellBackgroundColor(124 TITLE_LINE_INDEX, RESULT_CODE,125 TITLE_LINE_INDEX + 1 + i, 1);126 127 // 写入实际结果128 MobileApiTools.writeResult(TITLE_LINE_INDEX, TITLE_LINE_INDEX129 + 1 + i, ACTUAL_RESULT, actualResult);130 131 // 写入测试通过与否132 MobileApiTools.writeResult(TITLE_LINE_INDEX, TITLE_LINE_INDEX133 + 1 + i, TEST_RESULT, testResult);134 135 if (testResult.equals("OK"))136 ExcelUtil.getInstance().setCellBackgroundColor(137 TITLE_LINE_INDEX, TEST_RESULT,138 TITLE_LINE_INDEX + 1 + i, 1);139 else140 ExcelUtil.getInstance().setCellBackgroundColor(141 TITLE_LINE_INDEX, TEST_RESULT,142 TITLE_LINE_INDEX + 1 + i, 0);143 144 // 写入执行时间145 MobileApiTools.writeResult(TITLE_LINE_INDEX, TITLE_LINE_INDEX146 + 1 + i, RUNNING_TIME, MobileApiTools.getDate());147 148 logger.info("CaseID: " + map.get("CaseID") + ", CaseName: " + map.get("CaseName") + ", ExpectedResult: " +149 map.get("ExpectedResult") + ", ActualResult: " + actualResult + ", ResultCode: " + code +150 ", TestResult: " + testResult);151 }152 }153 154 }155 156 public static void main(String[] args) throws JSONException, IOException {157 LoginAPI loginAPI = new LoginAPI();158 loginAPI.login();159 160 }161 162 }
RunCase.java : 所有的 case 都放这里运行
1 package com.hozhu.cases; 2 3 import java.io.IOException; 4 5 import org.json.JSONException; 6 import org.testng.Assert; 7 import org.testng.annotations.Test; 8 9 import com.hozhu.excel.ExcelUtil;10 11 public class RunCase {12 @Test13 public void testLogin() throws JSONException, IOException {14 LoginAPI login = new LoginAPI();15 login.login();16 String [] result = ExcelUtil.getInstance().readExcelColumnData(4, "TestResult");17 for (int i = 0; i < result.length; i++) {18 Assert.assertEquals(result[i], "OK");19 }20 }21 }
好了， 上传完毕，等等， 还有那 excel 也上截图吧，呵呵
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。