DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
What's in store for DevOps in 2023? Find out today at 11 am ET in our "DZone 2023 Preview: DevOps Edition!"
Last chance to join
  1. DZone
  2. Testing, Deployment, and Maintenance
  3. Testing, Tools, and Frameworks
  4. Data-driven tests With JUnit 4 and Excel

Data-driven tests With JUnit 4 and Excel

John Ferguson Smart user avatar by
John Ferguson Smart
·
Nov. 30, 09 · Interview
Like (1)
Save
Tweet
Share
40.25K Views

Join the DZone community and get the full member experience.

Join For Free

One nice feature in JUnit 4 is that of Parameterized Tests, which let you do data-driven testing in JUnit with a minimum of fuss. It's easy enough, and very useful, to set up basic data-driven tests by defining your test data directly in your Java class. But what if you want to get your test data from somewhere else? In this article, we look at how to obtain test data from an Excel spreadsheet.

Parameterized tests allow data-driven tests in JUnit. That is, rather than having different of test cases that explore various aspects of your class's (or your application's) behavior, you define sets of input parameters and expected results, and test how your application (or, more often, one particular component) behaves. Data-driven tests are great for applications involving calculations, for testing ranges, boundary conditions and corner cases.

In JUnit, a typical parameterized test might look like this:

@RunWith(Parameterized.class)
public class PremiumTweetsServiceTest {

private int numberOfTweets;
private double expectedFee;

@Parameters
public static Collection data() {
return Arrays.asList(new Object[][] { { 0, 0.00 }, { 50, 5.00 },
{ 99, 9.90 }, { 100, 10.00 }, { 101, 10.08 }, { 200, 18},
{ 499, 41.92 }, { 500, 42 }, { 501, 42.05 }, { 1000, 67 },
{ 10000, 517 }, });
}

public PremiumTweetsServiceTest(int numberOfTweets, double expectedFee) {
super();
this.numberOfTweets = numberOfTweets;
this.expectedFee = expectedFee;
}

@Test
public void shouldCalculateCorrectFee() {
PremiumTweetsService premiumTweetsService = new PremiumTweetsService();
double calculatedFees = premiumTweetsService.calculateFeesDue(numberOfTweets);
assertThat(calculatedFees, is(expectedFee));
}
}

The test class has member variables that correspond to input values (numberOfTweets) and expected results (expectedFee). The @RunWith(Parameterzed.class) annotation gets JUnit to inject your test data into instances of your test class, via the constructor.

The test data is provided by a method with the @Parameters annotation. This method needs to return a collection of arrays, but beyond that you can implement it however you want. In the above example, we just create an embedded array in the Java code. However, you can also get it from other sources. To illustrate this point, I wrote a simple class that reads in an Excel spreadsheet and provides the data in it in this form:

@RunWith(Parameterized.class)
public class DataDrivenTestsWithSpreadsheetTest { 

    private double a;
    private double b;
    private double aTimesB;
   
    @Parameters
    public static Collection spreadsheetData() throws IOException {
        InputStream spreadsheet = new FileInputStream("src/test/resources/aTimesB.xls");
        return new SpreadsheetData(spreadsheet).getData();
    }

    public DataDrivenTestsWithSpreadsheetTest(double a, double b, double aTimesB) {
        super();
        this.a = a;
        this.b = b;
        this.aTimesB = aTimesB;
    }

    @Test
    public void shouldCalculateATimesB() {
        double calculatedValue = a * b;
        assertThat(calculatedValue, is(aTimesB));
    }
}

The Excel spreadsheet contains multiplication tables in three columns:

The SpreadsheetData class uses the Apache POI project to load data from an Excel spreadsheet and transform it into a list of Object arrays compatible with the @Parameters annotation. I've placed the source code, complete with unit-test examples on BitBucket. For the curious, the SpreadsheetData class is shown here:

public class SpreadsheetData {

    private transient Collection data = null;

    public SpreadsheetData(final InputStream excelInputStream) throws IOException {
        this.data = loadFromSpreadsheet(excelInputStream);
    }

    public Collection getData() {
        return data;
    }

    private Collection loadFromSpreadsheet(final InputStream excelFile)
            throws IOException {
        HSSFWorkbook workbook = new HSSFWorkbook(excelFile);

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

        int numberOfColumns = countNonEmptyColumns(sheet);
        List rows = new ArrayList();
        List rowData = new ArrayList();

        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;
    }

    /**
     * Count the number of columns, using the number of non-empty cells in the
     * first row.
     */
    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;
    }
}

Data-driven testing is a great way to test calculation-based applications more thoroughly. In a real-world application, this Excel spreadsheet could be provided by the client or the end-user with the business logic encoded within the spreadsheet. (The POI library handles numerical calculations just fine, though it seems to have a bit of trouble with calculations using dates). In this scenario, the Excel spreadsheet becomes part of your acceptance tests, and helps to define your requirements, allows effective test-driven development of the code itself, and also acts as part of your acceptance tests.

From http://weblogs.java.net/blog/johnsmart

unit test JUnit Test data application Data (computing) Database Test-driven development End user Apache POI Business logic

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • PostgreSQL: Bulk Loading Data With Node.js and Sequelize
  • Public Cloud-to-Cloud Repatriation Trend
  • Exploring the Benefits of Cloud Computing: From IaaS, PaaS, SaaS to Google Cloud, AWS, and Microsoft
  • How To Use Terraform to Provision an AWS EC2 Instance

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: