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}");
View Code
  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 }
View Code

 

posted @ 2019-02-10 20:52  DaisyLinux  阅读(789)  评论(0)  编辑  收藏  举报