Java JUnit 调用Excel表中数据 参数化测试 JUnit4

第一步：Excel数据表

 

共3列20行数据，其中第3列是第1列和第2列的和。

第3行和第20行会测试会报错。

 

11222433544855106612771488169918101020111122121224131326141428151530161632171734181836191938202041

 

第二步：被测试程序

 

package com.lance;

public class Target{
 public double add(double num1 , double num2){
  return num1 + num2 ;
 }
}

 

第三步：测试程序

 

package com.lance;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Collection;

import junit.framework.TestCase;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.junit.runners.Parameterized;
import org.junit.runners.Parameterized.Parameters;

@RunWith(Parameterized.class)
public class TargetTestPara extends TestCase{
 private double num1;
 private double num2;
 private double expResult;
 
 @Parameters
 public static Collection<Object> spreadsheetData() throws IOException {
  InputStream excelFile = new FileInputStream("src/data/TestData.xls");
  return new DataExtractTool(excelFile).getData();
 }
 
 public TargetTestPara(double column1, double column2, double column3) {
  super();
  this.num1 = column1;
  this.num2 = column2;
  this.expResult = column3;
 }

 @Test
 public void testAdd(){
  Target calculator = new Target();
  double result = calculator.add(num1, num2);
  assertEquals(expResult, result);
 }
}

 

第四步：读取Excel表数据的类

 

此处用到poi包：poi-3.7-20101029

 

package com.lance;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

public class DataExtractTool {
 private transient Collection<Object> data = null;

    public DataExtractTool(final InputStream excelFile) throws IOException {
        this.data = extractData(excelFile);
    }

    public Collection<Object> getData() {
        return data;
    }

    private Collection<Object> extractData(final InputStream excelFile)
            throws IOException {
        HSSFWorkbook workbook = new HSSFWorkbook(excelFile);

        data = new ArrayList<Object>();
        Sheet sheet = workbook.getSheetAt(0);

        int numberOfColumns = countNonEmptyColumns(sheet);
        List<Object> rows = new ArrayList<Object>();
        List<Object> rowData = new ArrayList<Object>();
       
        for (Row row : sheet) {       
         if (isEmpty(row)) {
          break;
         } else {
          rowData.clear();      
          for (int column = 0; column < numberOfColumns; column++) {          
           Cell cell = row.getCell(column);
           rowData.add(objectFrom(workbook, cell));
          }         
          rows.add(rowData.toArray()); 
         }
        }      
        return rows;       
    }
   
    private boolean isEmpty(final Row row) {
     Cell firstCell = row.getCell(0);   
     boolean rowIsEmpty = (firstCell == null) || (firstCell.getCellType() == Cell.CELL_TYPE_BLANK);    
     return rowIsEmpty; 
    }
   
    private int countNonEmptyColumns(final Sheet sheet) {   
     Row firstRow = sheet.getRow(0);
     return firstEmptyCellPosition(firstRow);    
    }
   
    private int firstEmptyCellPosition(final Row cells) {  
     int columnCount = 0;
     
     for (Cell cell : cells) {     
      if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
       break;
      }
      columnCount++;    
     }    
     return columnCount;    
    }
   
    private Object objectFrom(final HSSFWorkbook workbook, final Cell cell) {    
     Object cellValue = null;
     
     if (cell.getCellType() == Cell.CELL_TYPE_STRING) {    
      cellValue = cell.getRichStringCellValue().getString();     
     }    
     else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {    
      cellValue = getNumericCellValue(cell);  
     } 
     else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {   
      cellValue = cell.getBooleanCellValue();    
     }  
     else if (cell.getCellType() ==Cell.CELL_TYPE_FORMULA) { 
      cellValue = evaluateCellFormula(workbook, cell);
     } 
     return cellValue;   
    }
   
    private Object getNumericCellValue(final Cell cell) {   
     Object cellValue;
   
     if (DateUtil.isCellDateFormatted(cell)) { 
      cellValue = new Date(cell.getDateCellValue().getTime());   
     } else {
      cellValue = cell.getNumericCellValue();     
     }     
     return cellValue;
    }
   
    private Object evaluateCellFormula(final HSSFWorkbook workbook, final Cell cell) {
     FormulaEvaluator evaluator = workbook.getCreationHelper() .createFormulaEvaluator();
     CellValue cellValue = evaluator.evaluate(cell);   
     Object result = null;
     
     if (cellValue.getCellType() == Cell.CELL_TYPE_BOOLEAN) {    
      result = cellValue.getBooleanValue();  
     }  
     else if (cellValue.getCellType() == Cell.CELL_TYPE_NUMERIC) {   
      result = cellValue.getNumberValue();  
     }   
     else if (cellValue.getCellType() == Cell.CELL_TYPE_STRING) {  
      result = cellValue.getStringValue();      
     }     
     return result;     
    }
}