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/2010
			if (fileName.endsWith(".xlsx")) {
				wb = new XSSFWorkbook(inputStream);
			}
			// 判断excel 2003
			else if (fileName.endsWith(".xls")) {
				wb = new HSSFWorkbook(inputStream);
			}
			// Workbook获取sheet
			Sheet 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 {
			// 获取要操作的sheet
			Sheet 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 {
			// 获取要操作的sheet
			Sheet 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 {
			// 获取要操作的sheet
			Sheet 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) {

		// 获取要操作的sheet
		Sheet 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/2010
			if (fileName.endsWith(".xlsx")) {
				wb = new XSSFWorkbook(is);
			}
			// 判断excel 2003
			else if (fileName.endsWith(".xls")) {
				wb = new HSSFWorkbook(is);
			}
			// Workbook获取sheet
			Sheet 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/2010
			if (fileName.endsWith(".xlsx")) {
				wb = new XSSFWorkbook(is);
			}
			// 判断excel 2003
			else if (fileName.endsWith(".xls")) {
				wb = new HSSFWorkbook(is);
			}
			// Workbook获取sheet
			Sheet 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();
		}
	}
}