I posted this as a bug but figured the workaround may help others.
POI-3.8-beta4.
XSSF usermodel using SS usermodel interfaces.
Original motivation is to clear the contents of worksheets and then repopulate
the worksheet with other data.
1. After some other experiments, method I opted for was to delete the sheet
and then recreate it (same name and same index). This resulted in POI errors
indicating the sheet index it was trying to use was out of range. Stack trace:
Exception in thread "main" java.lang.IllegalArgumentException: Sheet index (-1)
is out of range (0..8)
at
org.apache.poi.xssf.usermodel.XSSFWorkbook.validateSheetIndex(XSSFWorkbook.java:945)
at
org.apache.poi.xssf.usermodel.XSSFWorkbook.getSheetName(XSSFWorkbook.java:792)
at
org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook.getSheetNameByExternSheet(XSSFEvaluationWorkbook.java:137)
at
org.apache.poi.xssf.usermodel.helpers.XSSFFormulaUtils$1.getSheetNameByExternSheet(XSSFFormulaUtils.java:81)
at
org.apache.poi.ss.formula.ptg.ExternSheetNameResolver.prependSheetName(ExternSheetNameResolver.java:42)
at
org.apache.poi.ss.formula.ptg.Ref3DPtg.toFormulaString(Ref3DPtg.java:91)
at
org.apache.poi.ss.formula.FormulaRenderer.toFormulaString(FormulaRenderer.java:92)
at
org.apache.poi.xssf.usermodel.helpers.XSSFFormulaUtils.updateFormula(XSSFFormulaUtils.java:126)
at
org.apache.poi.xssf.usermodel.helpers.XSSFFormulaUtils.updateSheetName(XSSFFormulaUtils.java:106)
at
org.apache.poi.xssf.usermodel.XSSFWorkbook.setSheetName(XSSFWorkbook.java:1177)
at TestCreateSheet.main(TestCreateSheet.java:33)
Workaround: At first I was using Workbook.createSheet() without passing a
sheet name argument to it to create my new sheets. When I switched that to the
form passing in a sheet name this method works. I recreated this error with
the attached code and by creating a workbook with formulas that span sheets and
are linked. Sheet2 has a formula that depends on a formula from Sheet7 which
in turn depends on a value on Sheet8. This error did not occur when I had the
same linked formula situation but on consecutive sheets (Sheet1 to Sheet2 to
Sheet3). This seems to be similar to Bug 46028.
Regards,
Matt
