Apachi POI - Cell setCellValue thows NullPointerException

Question

When I am trying to update existing excel file I am encountering following error:

Exception in thread "main" java.lang.NullPointerException
    at xltest.main(xltest.java:28)

My code:

FileInputStream file = new FileInputStream(new File("C:\\Users\\onu\\test.xlsx"));

XSSFWorkbook workbook = new XSSFWorkbook(file);
XSSFSheet sheet = workbook.getSheetAt(0);

//Update the value of cell
Cell cell = sheet.getRow(0).getCell(3); // cell D1
cell.setCellValue("onu"); // line 28 which throws NPE

file.close();

FileOutputStream outFile =new FileOutputStream(new File("C:\\Users\\onu\\test.xlsx"));
workbook.write(outFile);
outFile.close();

Answer 1

The cell does not exist yet, so getCell returns null.

You must detect this and create the cell if it doesn't exist, with the createCell method:

if (cell == null)
{
    cell = sheet.getRow(0).createCell(3);
}
// Then set the value.
cell.setCellValue("onu");

Alternatively, there is an overload of getCell where you can specify a MissingCellPolicy so that a blank Cell is automatically created if it didn't already exist:

cell = sheet.getRow(0).getCell(3, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);