Search code examples
excelapache-poinamed-ranges

Apache POI: how to set cell formula to a name of another sheet?

Am I doing it wrong, or is this a bug / limitation of POI?

I'm getting FormulaParseException: Cell reference expected after sheet name at index 8.

It works without the sheet qualifier, but then the formula will not work if copy pasted to another sheet.

And I need sheet-scoped names because multiple sheets defined the same names.

  File file = new File(System.getProperty("user.home"), "Desktop/Test.xlsx");
  Workbook workbook = new XSSFWorkbook();
  Sheet sheet = workbook.createSheet("Sheet1");
  Name name = workbook.createName();
  name.setNameName("NameForA1");
  name.setSheetIndex(workbook.getSheetIndex(sheet));
  name.setRefersToFormula("'Sheet1'!$A$1:$A$1");
  Row row = sheet.createRow(0);
  row.createCell(0).setCellValue("This is A1");
  Cell b1 = row.createCell(1);
  //      b1.setCellFormula("'Sheet1'!NameForA1"); // throws exception
  b1.setCellFormula("Sheet1!NameForA1"); // throws exception
  //      b1.setCellFormula("NameForA1"); // works
  workbook.write(new FileOutputStream(file));
  Desktop.getDesktop().open(file);

screenshot

But I need:

screenshot2

Solution

  • Sorry, was a bug in 3.10.x. Works in 3.17.

    Leaving the question. Maybe the example is useful to someone.