java - XSSF。NullPointerException

Question

我正在为下面的代码获取NullPointerException。有人能帮个忙吗？我正在尝试将数据库值添加到现有的excel工作表中。

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");

Answer 1

您需要创建行和单元格对象：

  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");

如果您要打开现有的笔记本，并且行/单元格/工作表可能存在，则需要如下内容：

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");

当然，您可能应该创建一些助手方法，如下所示：

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;
}

然后上面的代码就变得更加整洁了：

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