Search code examples
javaexcelapache-poixlsm

Cannot invoke "org.apache.poi.ss.usermodel.Cell.setCellValue(String)" because "cell" is null for existing XLSM file

I use Apache POI to write data to a predefined XLSM file. I use this code to open existing file:

Cell cell;
File file = new File(XLSMPath);
FileInputStream inputStream = new FileInputStream(file);
XSSFWorkbook workbook = XSSFWorkbookFactory.createWorkbook(inputStream);
XSSFSheet sheet = workbook.getSheetAt(0);
XSSFRow row = sheet.getRow(recordcount+4);

Data is written in first iteration in the 5th row and so on. Code to set value of a given cell:

cell = row.getCell(CellReference.convertColStringToIndex("A"));
cell.setCellValue(myvalue);

It worked fine for the first 400 iterations, but after that I get following error message:

Cannot invoke "org.apache.poi.ss.usermodel.Cell.setCellValue(String)" because "cell" is null

Solution

  • You need to create the worksheet cells yourself. Just check if your Cell is null and create new Cell using createCell(int):

    cell = row.getCell(CellReference.convertColStringToIndex("A"));
if (cell == null) {
    // maybe in your case index should be taken in other way
    cell = row.createCell(CellReference.convertColStringToIndex("A")); 
}
cell.setCellValue(myvalue);