一、自动化测试中经常用到参数化,需要循环读取两行Excel的单元格值,这里返回的是Map
package com.test; import java.io.IOException; import java.util.HashMap; import java.util.Iterator; import java.util.Map; import org.apache.log4j.Logger; import org.apache.log4j.PropertyConfigurator; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.xssf.usermodel.*; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; public class Readxlsx { private static Logger logger = Logger.getLogger(ReadXls.class); private static String path = "D://TestCase.xlsx"; static { PropertyConfigurator.configure("src\\main\\resources\\log4j.properties"); } public static void main(String args[]){ ReadXls readXls = new ReadXls(); try { readXls.readXls(path); } catch (FileNotFoundException e){ logger.error(e.getMessage()); } } public Map<String,String> readXls (String path) throws FileNotFoundException{ File file = new File(path); if(!file.exists()){ throw new FileNotFoundException("文件仅支持后缀为xlsx的文件,该路径找不到TestCase.xlsx文件,请先创建"); } Map<String, String> map = new HashMap(); FileInputStream fileInputStream; XSSFWorkbook workbook = null; try { fileInputStream = new FileInputStream(file); workbook = new XSSFWorkbook(fileInputStream); } catch (FileNotFoundException e) { logger.error("找不到文件"); } catch (IOException e) { logger.error("IO错误"); } //获取工作簿的sheet XSSFSheet hssfSheet = workbook.getSheetAt(0); //iterator循环获取row的值 Iterator iterator = hssfSheet.rowIterator(); int rownum = 0; while (iterator.hasNext()) { if (iterator.next() != null) { rownum++; } } //对中间存在空行的情况进行处理,如果存在一个空行则rownum加1 if(hssfSheet.getLastRowNum()<=0){ System.err.println("该excel无内容"); return map; } for (int i = 0; i < rownum; i++) { if (hssfSheet.getRow(i) == null && i < hssfSheet.getLastRowNum()) { rownum += 1; logger.info("第" + (i + 1) + "行为空"); continue; } //定义两个String类型变量用于存储map的键值对 String parm1 = null; String parm2 = null; for (int j = 0; j < 2; j++) { try { //对单元格的CellType进行变更 hssfSheet.getRow(i).getCell(j).setCellType(CellType.STRING); if (j == 0) { /*把第一列数据存储在parm1中,第二列存储在parm2中 这里要对单元格为空的情况进行空指针异常捕获 */ parm1 = hssfSheet.getRow(i).getCell(j).getStringCellValue(); } else { parm2 = hssfSheet.getRow(i).getCell(j).getStringCellValue(); } } catch (NullPointerException e) { logger.error("第" + (i + 1) + "行第" + (j + 1) + "个单元格为空"); continue; } } //对键值对为null的进行处理 if (parm1 != null && parm2 != null) { map.put(parm1, parm2); } } // 循环map集合 for (Map.Entry entry : map.entrySet()) { System.out.println("key= " + entry.getKey() + " and value= " + entry.getValue()); } return map; } }