Bug 54399 - Sheet index (-1) is out of range when rename sheet
Summary: Sheet index (-1) is out of range when rename sheet
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.9-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2013-01-10 05:04 UTC by Paul van Cingel
Modified: 2016-03-17 12:06 UTC (History)
1 user (show)

XLSX file that fails sheet rename. (17.34 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2013-01-10 05:04 UTC, Paul van Cingel

Note You need to log in before you can comment on or make changes to this bug.
Description Paul van Cingel 2013-01-10 05:04:18 UTC
Created attachment 29838 [details]
XLSX file that fails sheet rename.

For one particular spreadsheet, renaming a sheet returns 
java.lang.IllegalArgumentException: Sheet index (-1) is out of range (0..1)

	at org.apache.poi.xssf.usermodel.XSSFWorkbook.validateSheetIndex(XSSFWorkbook.java:1043)
	at org.apache.poi.xssf.usermodel.XSSFWorkbook.getSheetName(XSSFWorkbook.java:876)
	at org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook.getSheetNameByExternSheet(XSSFEvaluationWorkbook.java:135)
	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.Area3DPtg.toFormulaString(Area3DPtg.java:100)
	at org.apache.poi.ss.formula.FormulaRenderer.toFormulaString(FormulaRenderer.java:92)
	at org.apache.poi.xssf.usermodel.helpers.XSSFFormulaUtils.updateName(XSSFFormulaUtils.java:143)
	at org.apache.poi.xssf.usermodel.helpers.XSSFFormulaUtils.updateSheetName(XSSFFormulaUtils.java:97)
	at org.apache.poi.xssf.usermodel.XSSFWorkbook.setSheetName(XSSFWorkbook.java:1230)

Code is:

import org.apache.poi.ss.usermodel.*;
import java.io.*;
FileInputStream fileInputStream = new FileInputStream(filePath);  
Workbook workbook = WorkbookFactory.create(fileInputStream);  

System.out.println("sheets:" + workbook.getNumberOfSheets());

for (int i = 0; i < workbook.getNumberOfSheets(); i++) {  
  System.out.println("i:" + i);
  workbook.setSheetName(i, "Sheet" + (i + 1));

FileOutputStream fileOutputStream = new FileOutputStream(filePath); 

This outputs 

Tested on POI versions 3.8 and 3.9.
Comment 1 Dominik Stadler 2013-08-22 20:38:52 UTC
The error happens at a point where the external references are resolved in order to adjust all formulas/references for the new sheet-name. FormularParser.createAreaRefParseNode() tries to read the external sheet index, but does not find the referenced external sheets and thus ends up using -1. 

I still lack details of how it is supposed to work, though, to say if this is somehow expected here or an invalid .xlsx or really a bug in POI...
Comment 2 Dominik Stadler 2015-09-05 19:55:06 UTC
This seems to work with the current trunk version of POI, some other fix seems to have fixed this too.
Comment 3 Jafar Aliyev 2016-03-17 12:06:59 UTC
I downloaded the latest version 3.14 from 2016-03-07 and checked.
It was fixed in XSSFWorkbook class, but the problem remains on HSSFWorkbook.