POI操作Excel

在自动化测试过程中，避免硬编码，经常会用到数据驱动测试模式、关键字驱动测试模式等，这就少不了操作excel。以下是我在测试过程中因为场景需要用poi来实现测试数据的一些操作，后续有其他场景，再持续增加

代码如下

package com.utils;import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.IOException;import java.text.DecimalFormat;import java.util.ArrayList;import java.util.List;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;public class ExcelOperation {/** * 获取Excel 某sheet  * @param filePath fileName sheetName * @return Sheet */public static Sheet sheetExcel(String filePath, String fileName,String sheetName) {try {// 创建文件对象File file = new File(filePath + "\\" + fileName);// 创建文件流FileInputStream inputStream = new FileInputStream(file);Workbook wb = null;// 判断excel 2007/2010if (fileName.endsWith(".xlsx")) {wb = new XSSFWorkbook(inputStream);}// 判断excel 2003else if (fileName.endsWith(".xls")) {wb = new HSSFWorkbook(inputStream);}// Workbook获取sheetSheet sheet = wb.getSheet(sheetName);return sheet;} catch (Exception e) {e.printStackTrace();return null;}}/** * 获取Excel已知title列号 * @param filePath fileName sheetName titleName * @return int j 列号 */public static Integer getExcelTitleColumn(String filePath, String fileName,String sheetName, String titleName) {try {// 获取要操作的sheetSheet sheet = sheetExcel(filePath, fileName, sheetName);// 获取列数Row row0 = sheet.getRow(0);for (int j = 0; j < row0.getLastCellNum(); j++) {if (getValue(row0.getCell(j)).equals(titleName)) {System.out.println("返回的title的列号：" + j);return j;}}} catch (Exception e) {e.printStackTrace();return -1;}return -1;}/** * 获取Excel已知列的行号 * @param filePath fileName sheetName titleName * @return int i 行号 */public static Integer getExcelRow(String filePath, String fileName,String sheetName, String cellName) {try {// 获取要操作的sheetSheet sheet = sheetExcel(filePath, fileName, sheetName);Row row0 = sheet.getRow(0);// 通过列遍历行int rowNum = sheet.getLastRowNum() - sheet.getFirstRowNum();for (int j = 0; j < row0.getLastCellNum(); j++) {for (int i = 0; i < rowNum + 1; i++) {if (getValue(sheet.getRow(i).getCell(j)).equals(cellName)) {System.out.println("返回的单元格的行号：" + i);return i;}}}} catch (Exception e) {e.printStackTrace();return -1;}return -1;}/** * 获取Excel cell行列号 * @param filePath fileName sheetName cellValue * @return return List<Integer> 行号、列号 */public static List<Integer> getCellNO(String filePath, String fileName,String sheetName, String cellValue) {try {// 获取要操作的sheetSheet sheet = sheetExcel(filePath, fileName, sheetName);// 获取列数Row row0 = sheet.getRow(0);int rowNum = sheet.getLastRowNum() - sheet.getFirstRowNum();List<Integer> cellNum = new ArrayList<Integer>();for (int i = 0; i < rowNum + 1; i++) {for (int j = 0; j < row0.getLastCellNum(); j++) {if (getValue(row0.getCell(j)).equals(cellValue)) {cellNum.add(i);cellNum.add(j);System.out.println("返回的单元格的行、列号：" + cellNum);return cellNum;}}}} catch (Exception e) {e.printStackTrace();return null;}return null;}/** * 获取Excel已知行列号的cell值 行、列都从0开始  * @param filePath  fileName sheetName cellValue * @return return String cell value */public static String getCellValue(String filePath, String fileName,String sheetName, int row, int column) {// 获取要操作的sheetSheet sheet = sheetExcel(filePath, fileName, sheetName);Row rowObj = sheet.getRow(row);try {// String cellValue = rowObj.getCell(column).getStringCellValue();// Double cellValue=rowObj.getCell(column).getNumericCellValue();return getValue(rowObj.getCell(column));} catch (Exception e) {e.printStackTrace();return null;}}/** * 处理单元格类型，使其转化成字符串 *  * @return return String cellValue */public static String getValue(Cell hssfCell) {if (hssfCell.getCellType() == 4) {// 布尔类型的值return String.valueOf(hssfCell.getBooleanCellValue());} else if (hssfCell.getCellType() == 0) {// 数值类型的值Double cellValue = hssfCell.getNumericCellValue();// 格式化为四位小数String res = new DecimalFormat("#.####").format(cellValue);return res; // 返回String类型} else {// 字符串类型的值return hssfCell.getStringCellValue();}}/** * 清除某一列单元格内容 *  * @return return String cellValue */public static void clearExcelColumn(String filePath, String fileName,String sheetName, int column) {try {// 创建文件对象File file = new File(filePath + "\\" + fileName);// 创建文件流FileInputStream is = new FileInputStream(file);Workbook wb = null;// 判断excel 2007/2010if (fileName.endsWith(".xlsx")) {wb = new XSSFWorkbook(is);}// 判断excel 2003else if (fileName.endsWith(".xls")) {wb = new HSSFWorkbook(is);}// Workbook获取sheetSheet sheet = wb.getSheet(sheetName);int rowNum = sheet.getLastRowNum() - sheet.getFirstRowNum();for (int i = 1; i < rowNum + 1; i++) {sheet.getRow(i).getCell(column).setCellValue("");}FileOutputStream os = new FileOutputStream(filePath + "\\"+ fileName);wb.write(os);is.close();os.close();} catch (Exception e) {e.printStackTrace();}}/** * 写入某一单元格 *  * @return return String cellValue */public static void writeExcelCell(String filePath, String fileName,String sheetName, int row, int column, String cellValue) {try {// 创建文件对象File file = new File(filePath + "\\" + fileName);// 创建文件流FileInputStream is = new FileInputStream(file);Workbook wb = null;// 判断excel 2007/2010if (fileName.endsWith(".xlsx")) {wb = new XSSFWorkbook(is);}// 判断excel 2003else if (fileName.endsWith(".xls")) {wb = new HSSFWorkbook(is);}// Workbook获取sheetSheet sheet = wb.getSheet(sheetName);sheet.getRow(row).getCell(column).setCellValue(cellValue);FileOutputStream os = new FileOutputStream(filePath + "\\"+ fileName);wb.write(os);is.close();os.close();} catch (Exception e) {e.printStackTrace();}}}