本文共 5367 字,大约阅读时间需要 17 分钟。
POI
读取Excel
测试用例POI
依赖jar注意:poi.jar
和poi-ooxml.jar
版本要一致
org.apache.poi poi 4.1.2 org.apache.poi poi-ooxml 4.1.2
Excel
数据Excel
文件String fileName = "Excel文件";InputStream is = new FileInputStream(new File(fileName));
Workbook wb = null;//根据后缀创建不同类型的工作簿对象if (fileName.endsWith("xlsx")) { wb = new XSSFWorkbook(is);//Excel 2007} else if (fileName.endsWith("xls")) { wb = new HSSFWorkbook(is);//Excel2003}
//根据工作表名称读取工作表Sheet sh = wb.getSheet("工作表名");---------------------------------------------//获取工作表总数int sheets = wb.getNumberOfSheets();//通过索引值读取工作表Sheet sheetAt = wb.getSheetAt(0);
//行数int rowNum = sh.getLastRowNum();//根据第一行数据为基准,读取列数int colNum = sh.getRow(0).getLastCellNum();
//获取第m行数据Row row = sh.getRow(m);//读取第n列的值Cell cell = row.getCell(n);//数值类型cell.getNumericCellValue();//字符串类型cell.getStringCellValue();//布尔类型cell.getBooleanCellValue();//错误类型cell.getErrorCellValue();//公式类型cell.getCellFormula();
List<Map<String, Object>>
中//定义存放读取数据的ListList
excel
获取的List
转换为Object[][]
///根据excel获取的list转换为 Object[][]public static Object[][] getObjArrByList(List
Object[][]
中(这样做的好处:可以直接在testng
中进行参数化)//定义存放标题的ListListtitle = new ArrayList<>();//定义存放读取数据的二维数组Object[][] datas = new Object[rowNum][1];//存放每一条用例的map集合Map data;//获取第一行的标题行Row titleRow = sh.getRow(0);//列数int colNum = titleRow.getLastCellNum();//循环添加标题到存放标题的List中for (int i = 0; i < colNum; i++) { title.add(titleRow.getCell(i).getStringCellValue());}//读取用例数据for (int i = 1; i <= rowNum; i++) { Row row = sh.getRow(i); data = new HashMap<>(); //循环每一列的数据,然后添加到集合中 for (int j = 0; j < colNum; j++) { //获取列对象 Cell cell = row.getCell(j); if (cell == null) { //判断如果列对象为空,则在map集合中写去null data.put(title.get(j), null); continue; } //获取列的类型 CellType cellType = cell.getCellType(); //通过类型转换对应的数据类型 switch (cellType) { case NUMERIC: //数值类型 data.put(title.get(j), (int) row.getCell(j).getNumericCellValue()); break; case STRING: //字符串类型 data.put(title.get(j), row.getCell(j).getStringCellValue()); break; default: data.put(title.get(j), ""); break; } } //把第i行的map数据添加到二维数组中 datas[i - 1][0] = data;}
is.close();
//定义输入流对象InputStream is = new FileInputStream(new File(fileName));// 拿到文件转化为JavaPoi可操纵类型Workbook wb = WorkbookFactory.create(is);//获取到Workbook对象后,就可以关闭输入流了is.close();//获取工作表对象Sheet sh = wb.getSheet("Sheet1");
Row row = sh.getRow(rowNum);Cell cell = row.getCell(colNum);if (cell == null) { row.createCell(colNum).setCellValue(value);} else { cell.setCellValue(value);}
String coordinate = "H5";//获取单元格的row和cellCellAddress address = new CellAddress(coordinate);// 获取行int rowNum = address.getRow();Row row = sh.getRow(rowNum);// 获取列int colNum = address.getColumn();Cell cell = row.getCell(colNum);if (cell == null) { row.createCell(colNum).setCellValue(value);} else { cell.setCellValue(value);}
//写入数据FileOutputStream os = new FileOutputStream(new File(fileName));wb.write(os);os.flush();os.close();
//创建单元格样式对象CellStyle cellStyle = wb.createCellStyle();//指定填充的颜色(17:GREEN绿色;10:RED红色)//0-64都可选cellStyle.setFillForegroundColor((short) 17);//指定填充模式cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
Row row = sh.getRow(rowNum);Cell cell = row.getCell(colNum);cell.setCellStyle(cellStyle);
转载地址:http://modfi.baihongyu.com/