Using Excel As Source For Unit Tests

Sophisticated business logic requires a high amount of high quality data for test purposes. Tabular data is easier maintainable in Excel then directly in Java code.

With Java 8 it is easy to provide a function which converts an Excel Row (actually a HSSF Row) into whatever POJO you like:

 Function pojoMapper() {
        return (row) -> {
            Iterator cells = row.cellIterator();
            return new Input(

A few additional lines of code convert a Stream<Row> into a Stream<POJO>:

public static <T> Stream<T> load(Function<Row, T> mapper,...) {
        int skipCount = 0;
        if (hasHeader) {
            skipCount = 1;
        String fileName = //...
        try (InputStream inp = new BufferedInputStream(new FileInputStream(fileName));) {
            try (XSSFWorkbook wb = new XSSFWorkbook(inp)) {
                XSSFSheet sheet = wb.getSheetAt(tab);
                Stream<Row> stream =, false);
                return stream.skip(skipCount).map(mapper);

        } catch (IOException ex) {
            throw new IllegalStateException("Problems processing file: " + fileName, ex);

Parameterized JUnit tests can be used to define the locations of excel files and load the sheets representing different use cases, test suites or regression tests.

Hence the code above is reusable, andit was extracted and released as: (the pronunciation is semi-accidental :-)). This one-class utility is also available directly from maven central:


Example is implemented as system test:

Also checkout or come to MUC:


Are you trying JUnitParams

Posted by m1k0 on August 08, 2016 at 09:23 AM CEST #

Post a Comment:
  • HTML Syntax: NOT allowed
...the last 150 posts
...the last 10 comments