Need to test apache poi excel using mockito

Question

I have written a class to create workbook based on passed data. I am now writing mockito tests for testing my method. I am not allowed to use powermock. Following is the code snippet.

public class TalkDetailsToExcel extends AbstractXlsView {
    private static final ResourceBundle RESOURCE_BUNDLE = MessageResource.getResourceBundle();

    /**
     * Builds the {@link Workbook} containing {@link Talk} details and adds to the {@link Sheet}.
     *
     * @param model               the {@link Map} containing the {@link List} of {@link Talk}.
     * @param workbook            the Excel {@link Workbook} to which {@link Talk} needs to be added.
     * @param httpServletRequest  the {@link HttpServletRequest}
     * @param httpServletResponse the {@link HttpServletResponse}
     * @throws Exception          when adding talk details to workbook.
     */
    @Override
    protected void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest httpServletRequest,
                                      HttpServletResponse httpServletResponse) throws Exception {
        final String talkListName = "talkList";
        final String fileName = "Talk Details.xls";

        httpServletResponse.setHeader("Content-Disposition", "attachment; filename=" + fileName);
        List<Talk> talkList = (List<Talk>) model.get(talkListName);
        Sheet sheet = createAndReturnSheetFromWorkbook(workbook);
        addTalkDetailsToSheet(talkList, sheet);
    }

    /**
     * Creates data cells to the {@link Sheet}.
     *
     * @param talkList the {@link List} of {@link Talk} objects.
     * @param sheet    the {@link Sheet} to which {@link Talk} details need to add.
     */
    @VisibleForTesting
    void addTalkDetailsToSheet(List<Talk> talkList, Sheet sheet) {
        Verifier.verifyNotNull(talkList, RESOURCE_BUNDLE.getString(MessageResource.SHEET_NULL));
        Verifier.verifyNotEmpty(talkList, RESOURCE_BUNDLE.getString(MessageResource.WORKBOOK_NULL));
        Verifier.verifyNotNull(sheet, RESOURCE_BUNDLE.getString(MessageResource.WORKBOOK_NULL));

        int rowCount = 1;
        for (Talk talk : talkList) {
            Row talkRow = sheet.createRow(rowCount++);
            talkRow.createCell(0).setCellValue(talk.getConference().getName());
            talkRow.createCell(1).setCellValue(talk.getTitle());
            talkRow.createCell(2).setCellValue(talk.getSpeaker());
            talkRow.createCell(3).setCellValue(talk.getCernerId());
            talkRow.createCell(4).setCellValue(String.valueOf(talk.getDifficultyLevel()));
            talkRow.createCell(5).setCellValue(talk.getDateTimeStr());
        }
    }

    /**
     * Creates and returns an empty {@link Sheet} containing header {@link Row}.
     *
     * @param workbook       the {@link Workbook} for which {@link Sheet} needs to be created.
     * @return {@link Sheet} containing the header {@link Row}.
     */
    public Sheet createAndReturnSheetFromWorkbook(Workbook workbook) {
        Verifier.verifyNotNull(workbook, RESOURCE_BUNDLE.getString(MessageResource.WORKBOOK_NULL));

        final String sheetName = "Conference Details";
        final String conferenceName = "Conference";
        final String talkTitle = "Talk";
        final String speakerName = "Speaker";
        final String cernerIdOfSpeaker = "Cerner Id";
        final String difficultyLevel = "Difficulty";
        final String dateTimeOfTalk = "Date Time";

        Sheet sheet = workbook.createSheet(sheetName);
        Row header = sheet.createRow(0);
        header.createCell(0).setCellValue(conferenceName);
        header.createCell(1).setCellValue(talkTitle);
        header.createCell(2).setCellValue(speakerName);
        header.createCell(3).setCellValue(cernerIdOfSpeaker);
        header.createCell(4).setCellValue(difficultyLevel);
        header.createCell(5).setCellValue(dateTimeOfTalk);
        return sheet;
    }
}

Following is the code snippet I have written so far to write junit, I am not able to mock row using createRow. Problem is Row is always null and test throws null pointer exception. Any suggestions are highly appreciated.

   /**
   * Tests {@link TalkDetailsToExcel#createAndReturnSheetFromWorkbook(Workbook)} returns a {@link
   * Sheet}
   */
  @Test
  public void testCreateAndReturnSheetFromWorkbookReturnsSheet() {
    Workbook mockWorkbook = mock(Workbook.class);
    Sheet mockSheet = mock(Sheet.class);
    Row mockRow = mock(Row.class);
    doReturn(mockSheet).when(mockWorkbook).createSheet();
    doReturn(mockRow).when(mockSheet.createRow(anyInt()));

    Sheet sheet = mockTalkDetailsToExcel.createAndReturnSheetFromWorkbook(mockWorkbook);
  }

Solution

Using the code you supplied I reproduced a NPE at this line ...

talkRow.createCell(0).setCellValue(...)

... because although a mocked Row was specified this mock was not instructed to return a Cell on invocation of createCell.

This could be mocked as follows:

when(mockSheet.createRow(anyInt())).thenReturn(mockRow);
when(mockRow.createCell(anyInt())).thenReturn(mockCell);

So, the test could be rewritten to:

Workbook mockWorkbook = mock(Workbook.class);
Sheet mockSheet = mock(Sheet.class);
Row mockRow = mock(Row.class);
Cell mockCell = mock(Cell.class);
when(mockWorkbook.createSheet("Conference Details")).thenReturn(mockSheet);
when(mockSheet.createRow(0)).thenReturn(mockRow);
when(mockSheet.createRow(anyInt())).thenReturn(mockRow);
when(mockRow.createCell(anyInt())).thenReturn(mockCell);

Sheet sheet = mockTalkDetailsToExcel.createAndReturnSheetFromWorkbook(mockWorkbook);

This addresses the NPE.

Comments

[Dawood ibn Kareem]

It looks to me like your parentheses are in the wrong place. Do you still get the error if you write doReturn(mockRow).when(mockSheet).createRow(anyInt()); ?

[Dawood ibn Kareem]

Does this even compile? And how will it address the NullPointerException? It looks like all you've tried to do is replace the original stubbing with doReturn and when with the equivalent code using when and thenReturn. I can't see what improvement you've made.

[glytching]

Updated, the NPE was occurring in this line: talkRow.createCell(0).setCellValue(...) because although a mockRow was specified this mock was not instructed to return a cell on invocation of createCell.

[Dawood ibn Kareem]

I see. Yes, your update makes sense, and I think this is probably correct. It would be really good if you could move the explanation from your comment to the answer. The explanation itself is far more valuable than all the code in the world, so it should really be part of the answer.

[glytching]

@DawoodibnKareem I have updated the answer to bring that aspect to the foreground.

[StonecoldIM]

Very much appreciated your help. Thank you!!

[Prabhat Gaur]

Cell is final class