接口自动化测试：参数化封装(excel文件读取)

 

log4j.properties文件配置

log4j.rootLogger = DEBUG,stdout,F

log4j.appender.stdout = org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target = System.out
log4j.appender.stdout.layout = org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern = %d{ABSOLUTE} %5p %c{1}:%L - %m%n

log4j.appender.F = org.apache.log4j.DailyRollingFileAppender
log4j.appender.F.File = logs/debug.log
log4j.appender.F.Append = true
log4j.appender.F.Threshold = DEBUG
log4j.appender.F.layout = org.apache.log4j.PatternLayout
log4j.appender.F.layout.ConversionPattern = %-d{yyyy-MM-dd HH:mm:ss} [ %t:%r ] - [ %p ] %m%n

#log4j.appender.D = org.apache.log4j.DailyRollingFileAppender
#log4j.appender.D.File = logs/error.log
#log4j.appender.D.Append = true
#log4j.appender.D.Threshold = ERROR
#log4j.appender.D.layout = org.apache.log4j.PatternLayout
#log4j.appender.D.layout.ConversionPattern =%-d{yyyy-MM-dd HH\:mm\:ss} [ %t\:%r ] - [ %p ] %m%n

 

获取异常信息内容工具类

import java.io.PrintWriter;
import java.io.StringWriter;

public class ExceptionMessage {
	public static String getTrace(Throwable t) {
        StringWriter stringWriter= new StringWriter();
        PrintWriter writer= new PrintWriter(stringWriter);
        t.printStackTrace(writer);
        StringBuffer buffer= stringWriter.getBuffer();
        return buffer.toString();
    }
}

 

 

 

读取EXCEL文件

package com.mazhan3.fileReader;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

import org.apache.log4j.Logger;
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;

import com.mazhan3.utils.ExceptionMessage;

public class ExcelReader {
	//标记两种excel文档的后缀名
	private static final String EXTENSION_XLS = "xls";
	private static final String EXTENSION_XLSX = "xlsx";
	Logger log = Logger.getLogger(ExcelReader.class);
	private Workbook wb = null;
	/**
	 * 构造时加载excel文件
	 * @param filePath
	 */
	public ExcelReader(String filePath){
		File file = new File(filePath);
		String absolutePath = file.getAbsolutePath();
		FileInputStream in = null;
		try {
			in = new FileInputStream(file);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			log.info(ExceptionMessage.getTrace(e));
			log.error("文件没有发现");
		}
		getWorkBook(absolutePath, in);
	}

	/**
	 * 
	 * @param absolutePath
	 * @param in
	 */
	private void getWorkBook(String absolutePath, FileInputStream in) {
		if(absolutePath.endsWith(EXTENSION_XLS)){
			try {
				wb = new HSSFWorkbook(in);  
			} catch (IOException e) {
				log.info(ExceptionMessage.getTrace(e));
			}
		}else if(absolutePath.endsWith(EXTENSION_XLSX)){
			try {
				wb = new XSSFWorkbook(in);
			} catch (IOException e) {
				log.info(ExceptionMessage.getTrace(e));
			}
		}else{
			log.error("EXCEL文件格式错误");
		}
	}
	
	/**
	 * 默认读取sheet0页
	 * 传入的行号与列号直接传入excel表格中的即可，更符合使用习惯，方法中已进行了处理
	 * 即如果想要获取A1的值，那么行号传1，列号传1即可。
	 * @param rowNum
	 * @param cellNum
	 * @return String类型的excel表格中存储的值
	 */
	public String getValue(int rowNum,int cellNum){
		String value = getValue(rowNum,cellNum,0);	
		return value;
	}
	
	/**
	 * 传入的行号与列号直接传入excel表格中的即可，更符合使用习惯，方法中已进行了处理
	 * 即如果想要获取A1的值，那么行号传1，列号传1即可。
	 * @param rowNum
	 * @param cellNum
	 * @param sheetNum
	 * @return String类型的excel表格中存储的值
	 */
	public String getValue(int rowNum,int cellNum,int sheetNum){
		rowNum--;
		cellNum--;
		Row row = getRow(rowNum, sheetNum);
		Cell cell = getCell(cellNum, row);
		//return cell.getStringCellValue();
		//如果选择了上面一行的方法，当输出一个数字时则
		//会抛出java.lang.IllegalStateException: Cannot get a text value from a numeric cell

		return cell.toString();
	}

	/**
	 * 拿到sheet页中的行
	 * @param rowNum
	 * @param sheetNum
	 * @return Row
	 */
	private Row getRow(int rowNum, int sheetNum) {
		Sheet sheet = wb.getSheetAt(sheetNum);
		//注意这里的行号，如果你有1,2,3,4四行，那么第一行的行号为0，最后一行的行号为3
		int firstRowNum = sheet.getFirstRowNum();
		int lastRowNum = sheet.getLastRowNum();
		
		Row row = null;
		
		if(rowNum >= firstRowNum && rowNum <= lastRowNum){
			row = sheet.getRow(rowNum);
		}else{
			log.error("行号输入错误，在excel文件sheet页内容的范围之外");
			throw new RuntimeException("行号错误");
		}
		return row;
	}

	/**
	 * 拿到行中的列
	 * @param cellNum
	 * @param row
	 * @return Cell
	 */
	private Cell getCell(int cellNum, Row row) {
		Cell cell = null;
		//注意列号，如果你的excel文件中有A,B,C三列，那么第一列号为0，最后一列的列号为3
		int firstCellNum = row.getFirstCellNum();
		int lastCellNum = row.getLastCellNum();
		if(cellNum >= firstCellNum && cellNum < lastCellNum ){
			cell = row.getCell(cellNum);
		}else{
			log.error("列号错误，，在excel文件sheet页内容的范围之外");
			throw new RuntimeException("列号错误");
		}
		return cell;
	}
	
	
}