java - XSSF. NullPointerException

Question

I am getting NullPointerException for following code. Could some one please help on this? I am trying to get database values to existing excel sheet.

Statement statement = connect.createStatement();
  ResultSet resultSet = statement.executeQuery("select * from basicinfo");
  FileInputStream fis = new FileInputStream(new File("exceldatabase.xlsx"));
  XSSFWorkbook workbook = new XSSFWorkbook(fis); 
  XSSFSheet spreadsheet = workbook.getSheetAt(0);
  XSSFRow row=spreadsheet.getRow(2);
  XSSFCell cell;
  cell=row.getCell(2);
  cell.setCellValue("user_name");
  cell=row.getCell(3);
  cell.setCellValue("email");
  cell=row.getCell(3);
  cell.setCellValue("phonenum");
  cell=row.getCell(4);
  cell.setCellValue("address");

WIthout knowing which line is giving the NPE, and which object is null, there isn't a lot we can do here to help... — Gagravarr, Feb 27 '16 at 22:59

score 1 · Answer 1 · answered Feb 28 '16 at 17:09

You need to create the row and cell objects:

  XSSFRow row=spreadsheet.createRow(2);
  XSSFCell cell;
  cell=row.createCell(2);
  cell.setCellValue("user_name");
  cell=row.createCell(3);
  cell.setCellValue("email");
  cell=row.createCell(3);
  cell.setCellValue("phonenum");
  cell=row.createCell(4);
  cell.setCellValue("address");

If you are opening an existing notebook and the row/cells/sheets might exist, you need something like this:

Sheet sheet=workbook.getSheet("foo");
if (sheet==null){
  sheet=workbook.createSheet("foo");
}
Row row=sheet.getRow(2);
if (row==null){
  row=sheet.createRow(2);
}
Cell cell=row.getCell(12);
if (cell==null){
  cell=row.createCell(12);
}
cell.setValue("phonenum");

Of course, you probably should create some helper methods, like this:

private Sheet getOrCreateSheet(Workbook workbook, String sheetName){
  Sheet sheet=workbook.getSheet(sheetName);
  if (sheet==null){
    sheet=workbook.createSheet(sheetName);
  }
 return sheet;
}

private Row getOrCreateRow(Sheet sheet, int rowIndex){
  Row row=sheet.getRow(rowIndex);
  if (row==null){
    row=sheet.createRow(rowIndex);
  }
  return row;
}

private Cell getOrCreateCell(Row row, int colIndex){
  Cell cell=row.getCell(colIndex);
  if (cell==null){
    cell=row.createCell(colIndex);
  }
  return cell;
}

then the code above becomes much more tidy:

Sheet sheet=getOrCreateSheet(workbook, "foo");
Row row=getOrCreateRow(sheet, 2);
Cell cell=getOrCreateCell(row, 12);
cell.setValue("phonenum");

java - XSSF. NullPointerException

1 Answers1