Using Excel As Source For Unit Tests

Datetime:2016-08-23 02:50:11         Topic: Unit Testing          Share        Original >>
Here to See The Original Article!!!

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:

NEW workshop:On demand workshops:

available for streaming.


Put your ads here, just $200 per month.