jmeter XLSX 读取
import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.ss.usermodel.Workbook; import java.io.*; //XSSFWorkbook hook = ""; Workbook hook = ""; // try { // book = new XSSFWorkbook(excelFile); // } catch (Exception ex) { // book = new HSSFWorkbook(new FileInputStream(excelFile)); // } try { InputStream inputStream = new FileInputStream(new File("/Users/iot/2.xlsx")); hook = new XSSFWorkbook(inputStream); print("========================"+hook); inputStream.close(); } catch (Exception e) { print("========================"+"Exception hook"); InputStream inputStream = new FileInputStream(new File("/Users/iot/2.xlsx")); hook = new HSSFWorkbook(inputStream); print("========================"+hook); log.info("!!!!!!!!" + e.getMessage() + "!!!!!!!!!!!!!!!"); } if (!hook.equals("")) { print("========================"+"create hook"); vars.putObject("hook", hook); } else { print("========================"+"stop thread hook"); // hook=hook.createSheet("TestSuite"); // Row = hook.createRow(0); // Row.createCell(0).setCellValue("DTC"); // vars.putObject("hook", hook); // SampleResult.setStopTestNow(true); } int i = Integer.parseInt(vars.get("calc")); print("calc ========================"+i); XSSFRow row = vars.getObject("hook").getSheet("诗歌服务").getRow(i); print("row ========================"+row); vars.putObject("row", row); for (int j = 1; j <= vars.getObject("hook").getSheet("诗歌服务").getRow(0).getLastCellNum(); j++) { if (row.getCell(j) == null) { row.createCell(j).setCellValue(""); } } String caseNo = row.getCell(0).toString(); vars.put("caseNo",caseNo); print(" caseNo---->>> "+"${caseNo}"); String input = row.getCell(1).toString(); vars.put("input",input); print(" input---->>> "+"${input}"); String caseIntent = row.getCell(2).toString(); vars.put("caseIntent",caseIntent); print(" caseIntent---->>> "+"${caseIntent}"); String caseDomain = row.getCell(3).toString(); vars.put("caseDomain",caseDomain); print(" caseDomain---->>> "+"${caseDomain}"); String exp = row.getCell(4).toString(); vars.put("exp",exp); print(" exp---->>> "+"${exp}");
1 package excel; 2 3 import java.io.File; 4 import java.io.FileInputStream; 5 import java.io.IOException; 6 import java.io.InputStream; 7 import java.lang.instrument.ClassFileTransformer; 8 import java.text.SimpleDateFormat; 9 import java.util.Date; 10 11 import com.alibaba.fastjson.JSON; 12 import com.alibaba.fastjson.JSONObject; 13 import com.alibaba.fastjson.JSONPath; 14 import excel.CWResultFile.CWOutputFile; 15 import jxl.Cell; 16 import jxl.Workbook; 17 import jxl.format.Alignment; 18 import jxl.format.Colour; 19 import jxl.format.VerticalAlignment; 20 import jxl.read.biff.BiffException; 21 import jxl.write.Label; 22 import jxl.write.WritableCellFormat; 23 import jxl.write.WritableFont; 24 import jxl.write.WritableSheet; 25 import jxl.write.WritableWorkbook; 26 import jxl.write.WriteException; 27 import jxl.write.biff.RowsExceededException; 28 29 public class CWResultFile { 30 31 /** 导入jxl.jar;*后续扩充功能,sheet2增加测试报告展现;------待实现; */ 32 public static class CWOutputFile { 33 public static void main(String[] args) throws RowsExceededException, WriteException, BiffException, IOException { 34 wOutputFile("/Users/iot/1.xls", "1", "“疑是地上霜”的下一句是什么", "GetNextPhrases", "诗歌服务", "举头望明月", "","", "{\"source\":\"third_chitchat\",\"asr\":{\"lang\":\"\",\"text\":\"\"},\"operation\":{},\"iscredible\":\"true\",\"confidence\":1,\"tts\":[{\"text\":\"举头望明月\\\"(唐代诗人李白《静夜思》)\",\"lang\":\"\",\"action\":{\"name\":\"\",\"param\":{\"duration\":0,\"url\":\"\",\"intent\":\"\",\"params\":null}}}],\"tags\":[\"\"],\"recomendation\":[],\"simqs\":null,\"gwdata\":{},\"tree\":{\"currentstate\":\"\",\"subtree\":null},\"expiration\":-79}", "", "", ""); 35 } 36 /* 37 * wOutputFile方法写结果文件 wOutputFile(文件路径,案例编号,测试验证点,预期结果,实际结果,错误码,状态码,响应结果) 38 */ 39 public static void wOutputFile(String filepath, String caseNo, String testInput,String intent,String domain,String preResult,String fieldValue,String fresult,String rsp, String description,String tester,String sheetName) 40 throws IOException, RowsExceededException, WriteException, BiffException { 41 File output = new File(filepath); 42 if (!output.isFile()) { 43 output.createNewFile(); // 如果指定文件不存在,则新建该文件 44 WritableWorkbook writeBook = Workbook.createWorkbook(output); 45 WritableSheet Sheet = writeBook.createSheet(sheetName, 0); // createSheet(sheet名称,第几个sheet) 46 WritableFont headfont = new WritableFont(WritableFont.createFont("宋体"), 11, WritableFont.BOLD); // 字体样式 47 WritableCellFormat headwcf = new WritableCellFormat(headfont); 48 headwcf.setBackground(Colour.GRAY_25); // 灰色颜色 49 Sheet.setColumnView(0, 8); // 设置列宽度setColumnView(列号,宽度) 50 Sheet.setColumnView(1, 25); 51 Sheet.setColumnView(2, 10); 52 Sheet.setColumnView(3, 10); 53 Sheet.setColumnView(4, 10); 54 Sheet.setColumnView(5, 60); 55 Sheet.setColumnView(6, 10); 56 Sheet.setColumnView(7, 10); 57 headwcf.setAlignment(Alignment.CENTRE); // 设置文字居中对齐方式; 58 headwcf.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置垂直居中; 59 //以下控制Label对应的位置 60 Label labe00 = new Label(0, 0, "No.", headwcf); // Label(列号,行号, 内容)//caseNo 61 Label labe10 = new Label(1, 0, "测试语句", headwcf);//testInput 62 Label labe20 = new Label(2, 0, "intent", headwcf); //intent 63 Label labe30 = new Label(3, 0, "domain", headwcf); //domain 64 Label labe40 = new Label(4, 0, "期望内容", headwcf); //preResult 65 Label labe60 = new Label(5, 0, "字段对应值", headwcf); //响应报文fieldValue 66 Label labe50 = new Label(6, 0, "测试结果", headwcf); //打印通过与否 67 Label labe70 = new Label(7, 0, "返回字符串", headwcf);//rsp 68 Label labe80 = new Label(8, 0, "说明", headwcf); 69 Label labe90 = new Label(9, 0, "测试人员", headwcf); 70 Sheet.addCell(labe00); 71 Sheet.addCell(labe10); 72 Sheet.addCell(labe20); 73 Sheet.addCell(labe30); 74 Sheet.addCell(labe40); 75 Sheet.addCell(labe50); 76 Sheet.addCell(labe60); 77 Sheet.addCell(labe70); 78 Sheet.addCell(labe80); 79 Sheet.addCell(labe90); 80 writeBook.write(); 81 writeBook.close(); 82 } 83 System.out.println(" %===================================================================================================% "); 84 String response=rsp; 85 JSONObject json = JSON.parseObject(response); 86 Object source = JSONPath.eval(json, "$.source"); 87 Object text = JSONPath.eval(json, "$.tts[0].text"); 88 Object intent_json = JSONPath.eval(json, "$['tts'][0]['action']['param']['intent']"); 89 Object url = JSONPath.eval(json, "$['tts'][0]['action']['param']['url']"); 90 91 String sou=JSONObject.toJSONString(source); 92 String tex=JSONObject.toJSONString(text); 93 String inte=JSONObject.toJSONString(intent_json); 94 String ur=JSONObject.toJSONString(url); 95 96 String fieldReturnValue="source="+sou+";"+"text="+tex+";"+"intent="+inte+";"+"url="+ur; 97 System.out.println(" $$$$$$$$$$$$$$$$$$$$ "+fieldReturnValue); 98 fieldValue=fieldReturnValue; 99 String result = ""; 100 InputStream instream = new FileInputStream(filepath); 101 Workbook readwb = Workbook.getWorkbook(instream); 102 WritableWorkbook wbook = Workbook.createWorkbook(output, readwb); // 根据文件创建一个操作对象 103 WritableSheet readsheet = wbook.getSheet(0); 104 // int rsColumns = readsheet.getColumns(); //获取Sheet表中所包含的总列数 105 int rsRows = readsheet.getRows(); // 获取Sheet表中所包含的总行数 106 /******************************** 字体样式设置 ****************************/ 107 WritableFont font = new WritableFont(WritableFont.createFont("宋体"), 10, WritableFont.NO_BOLD);// 字体样式 108 WritableCellFormat wcf = new WritableCellFormat(font); 109 /***********************************************************************/ 110 Cell cell1 = readsheet.getCell(0, rsRows); 111 if (cell1.getContents().equals("")) { 112 Label labetest1 = new Label(0, rsRows, caseNo); // 第1列--案例编号; 113 Label labetest2 = new Label(1, rsRows, testInput); // 第2列--测试语句验证测试点; 114 Label labetest3 = new Label(2, rsRows, intent); //intent 115 Label labetest4 = new Label(3, rsRows, domain);//domain 116 Label labetest5 = new Label(4, rsRows, preResult); //预期结果; 117 Label labetest6 = new Label(5, rsRows, fieldValue); //fieldValue返回字段约束值 118 System.out.println("*********************************响应结果************************************** "+rsp); 119 System.out.println("*********************************预期结果************************************** "+preResult); 120 System.out.println("*********************************实际结果************************************** "+fresult); 121 if (rsp.contains(preResult)) { 122 // if (preResult == fresult) { 123 result = "通过"; 124 System.out.println("*********************************通过************************************** "); 125 wcf.setBackground(Colour.BRIGHT_GREEN); // 通过案例标注绿色 126 } else { 127 result = "不通过"; 128 wcf.setBackground(Colour.RED); // 不通过案例标注红色 129 } 130 Label labetest7 = new Label(6, rsRows, result, wcf); // 第7列--通过与否 执行结果; 131 Label labetest8 = new Label(7, rsRows, rsp); // 第8列--响应结果 132 Label labetest9 = new Label(8, rsRows, description); // 第9列--描述说明 133 Label labetest10 = new Label(9, rsRows, tester); // 第10列--测试人员 134 135 readsheet.addCell(labetest1); 136 readsheet.addCell(labetest2); 137 readsheet.addCell(labetest3); 138 readsheet.addCell(labetest4); 139 readsheet.addCell(labetest5); 140 readsheet.addCell(labetest6); 141 readsheet.addCell(labetest7); 142 readsheet.addCell(labetest8); 143 readsheet.addCell(labetest9); 144 readsheet.addCell(labetest10); 145 } 146 wbook.write(); 147 wbook.close(); 148 } 149 } 150 151 }