Bug 51998 - XSSF error when removing and re-creating worksheets
Summary: XSSF error when removing and re-creating worksheets
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.8-dev
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2011-10-08 19:55 UTC by Matt Rogghe
Modified: 2015-09-29 13:10 UTC (History)
0 users

Java class and workbook to re-create error (7.48 KB, application/zip)
2011-10-08 19:55 UTC, Matt Rogghe

Note You need to log in before you can comment on or make changes to this bug.
Description Matt Rogghe 2011-10-08 19:55:34 UTC
Created attachment 27741 [details]
Java class and workbook to re-create error

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.
Comment 1 Dominik Stadler 2015-09-29 13:10:51 UTC
I verified that this works in the latest version of POI (3.13), it likely was fixed as part of other fixes. A unit test showing that it works now is added via r1705855.