介面自動化測試：引數化封裝(excel檔案讀取)

凌.風發表於2015-05-09
封裝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;
	}
	
	
}

 

相關文章