Apache POI Row iteration issue

Question

I am getting Null pointer while testing the below code

public static List<String> getColumnList(Row row) {
    List<String> columnList = new ArrayList<>();
    **for(Cell cell : row){**
        if(StringUtils.isEmpty(cell.getStringCellValue())){
            break;
        }else{
            columnList.add(cell.getStringCellValue());
        }
    }
    return columnList;
}

The test code is given below

    Workbook mockWorkbook = mock(Workbook.class);
    Sheet mockSheet = mock(Sheet.class);
    Row mockRow = mock(Row.class);
    Cell mockCell1 = mock(Cell.class);
    Cell mockCell2 = mock(Cell.class);
    Cell mockCell3 = mock(Cell.class);
    Cell mockCell4 = mock(Cell.class);
    when(mockWorkbook.createSheet("m_digi_svce_func_asset_cat")).thenReturn(mockSheet);
    when(mockWorkbook.getNumberOfSheets()).thenReturn(1);
    when(mockWorkbook.getSheetAt(0)).thenReturn(mockSheet);
    when(mockWorkbook.isSheetHidden(0)).thenReturn(false);
    when(mockSheet.getSheetName()).thenReturn("m_digi_svce_func_asset_cat");
    when(mockWorkbook.getSheet("m_digi_svce_func_asset_cat")).thenReturn(mockSheet);
    when(mockSheet.createRow(0)).thenReturn(mockRow);
    when(mockRow.createCell(0)).thenReturn(mockCell1);
    when(mockRow.createCell(1)).thenReturn(mockCell2);
    when(mockRow.createCell(2)).thenReturn(mockCell3);
    when(mockRow.createCell(3)).thenReturn(mockCell4);
    when(mockRow.getCell(0)).thenReturn(mockCell1);
    when(mockCell1.getStringCellValue()).thenReturn("test1");
    when(mockRow.getCell(1)).thenReturn(mockCell2);
    when(mockCell2.getStringCellValue()).thenReturn("test2");
    when(mockRow.getCell(2)).thenReturn(mockCell3);
    when(mockCell3.getStringCellValue()).thenReturn("test3");
    when(mockRow.getCell(3)).thenReturn(mockCell4);
    when(mockCell4.getStringCellValue()).thenReturn("test4");
    assertNotNull(mockRow.getCell(0).getStringCellValue());
    assertNotNull(getColumnList(mockRow));

The code is failing at for each loop of Row in getColumnList saying Row is null.

Any help/pointer would be highly appreciated.

As suggested by Lesiak, I added the below code and it is working fine now.

Iterator<Cell> cellIterator = mock(Iterator.class);
    when(mockRow.iterator()).thenReturn(cellIterator);
    when(cellIterator.hasNext()).thenReturn(true).thenReturn(true).thenReturn(true).thenReturn(true).thenReturn(false);
    when(cellIterator.next()).thenReturn(mockCell1).thenReturn(mockCell2).thenReturn(mockCell3).thenReturn(mockCell4);
    when(mockRow.getCell(0)).thenReturn(mockCell1);
    when(mockCell1.getStringCellValue()).thenReturn("test1");
    when(mockRow.getCell(1)).thenReturn(mockCell2);
    when(mockCell2.getStringCellValue()).thenReturn("test2");
    when(mockRow.getCell(2)).thenReturn(mockCell3);
    when(mockCell3.getStringCellValue()).thenReturn("test3");
    when(mockRow.getCell(3)).thenReturn(mockCell4);
    when(mockCell4.getStringCellValue()).thenReturn("test4");
    assertNotNull(mockRow.getCell(0).getStringCellValue());

Why not create a simple spreadsheet with the required test data in, and pass that into Apache POI for your testing? — Gagravarr, Oct 21 '21 at 07:35
After looking at the recent comment from Lesiak, your comment will be lot less work. I just wanted to check the feasibility of Mockito before using this simple idea. After learning my lesson and understanding the quantum of work needed for Mockito, I can proceed. Thank you very much. — sujikin, Oct 26 '21 at 05:58

Lesiak · Answer 1 · 2021-10-21T07:47:33.870

The foreach loop calls mockRow.iterator() method, which is not stubbed.

Please get familiar with How does the Java 'for each' loop work?

On top of that: It looks like you have a lot of interactions even when testing such a simple method. For more complex methods, the mocking setup will get even more nasty. This suggests that mocking is not an optimal solution. Consider creating a workbook in memory using real implementations, not mocks.

See https://www.codejava.net/coding/how-to-write-excel-files-in-java-using-apache-poi

I mean sth along the lines of:

public static Workbook buildTestWorkbook()  {
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("Java Books");

    Object[][] bookData = {
            {"Head First Java", "Kathy Serria", 79},
            {"Effective Java", "Joshua Bloch", 36},
            {"Clean Code", "Robert martin", 42},
            {"Thinking in Java", "Bruce Eckel", 35},
    };

    int rowCount = 0;

    for (Object[] aBook : bookData) {
        Row row = sheet.createRow(++rowCount);

        int columnCount = 0;

        for (Object field : aBook) {
            Cell cell = row.createCell(++columnCount);
            if (field instanceof String) {
                cell.setCellValue((String) field);
            } else if (field instanceof Integer) {
                cell.setCellValue((Integer) field);
            }
        }

    }
    return workbook;
}

Thanks Lesiak. It worked after adding the code for iterator stubbing. — sujikin, Oct 21 '21 at 04:48

Apache POI Row iteration issue

1 Answers1