Bug 24207 - [INFO NEEDED] Call HSSFName.getReference() throwing java.lang.IndexOutOfBoundsException
Summary: [INFO NEEDED] Call HSSFName.getReference() throwing java.lang.IndexOutOfBound...
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: unspecified
Hardware: PC other
: P3 critical (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2003-10-29 06:48 UTC by Ambrose Wong
Modified: 2008-05-17 03:45 UTC (History)
1 user (show)


Note You need to log in before you can comment on or make changes to this bug.
Description Ambrose Wong 2003-10-29 06:48:46 UTC
I am using the poi-1.8.0-dev package.

The following is a part of my program.

POIFSFileSystem fs      =
					new POIFSFileSystem(new FileInputStream
("C:\\Projects\\KPM\\POI Test\\xls\\SimpleForm.xls"));
			HSSFWorkbook wb = new HSSFWorkbook(fs);
			HSSFSheet sheet = wb.getSheet("DataCollectionForm");
			HSSFRow row = null;
			HSSFCell cell = null;
			HSSFName name = null;
			CellReference ref = null;
			int index = 0;
			/* Read a locked cell */
			index = wb.getNameIndex("Form_4.1_01");
			System.out.println("index = " + index);
			System.out.println("count = " + wb.getNumberOfNames());
			name = wb.getNameAt(index);
			System.out.println("name = " + name.toString());
			System.out.println("name's name = " + name.getNameName
			System.out.println("name's sheet = " + name.getSheetName
			System.out.println("ref = " + name.getReference());
			ref = new CellReference(name.getReference().substring
			row = sheet.getRow(ref.getRow());
			cell = row.getCell((short)ref.getCol());
			System.out.println("The value of Form_4.1_01 is  " + 

When calling name.getReference() function, the IndexOutOfBoundsException was 
thrown as the following:
java.lang.IndexOutOfBoundsException: Index: 8, Size: 6
	at java.util.ArrayList.RangeCheck(Unknown Source)
	at java.util.ArrayList.get(Unknown Source)
	at org.apache.poi.hssf.model.Workbook.getSheetName(Workbook.java:460)
	at org.apache.poi.hssf.model.Workbook.findSheetNameFromExternSheet
	at org.apache.poi.hssf.model.Workbook.getSheetReferences
	at org.apache.poi.hssf.usermodel.HSSFName.getReference
	at TestSimpleForm.main(TestSimpleForm.java:49)

I also found the error caused by field_2_index_to_first_supbook_sheet, inside 
in ExternSheetSubRecord, which is larger than arraylist's size.
Comment 1 Avik Sengupta 2003-10-29 07:11:55 UTC
Could you try 2.0pre3. Many changes to named ranges were made in that build. If
that does not work, you will need to attach the culprit workbook, otherwise
debugging is impossible. 
Comment 2 Dave Combs 2005-01-26 00:37:53 UTC
I've got exactly the same problem as the original report,
and am using version 2.5.1-final.  I've attached a test
case.  Does anyone have a workaround for this in the

Dave Combs
Comment 3 Dave Combs 2005-01-26 00:39:37 UTC
Created attachment 14103
Comment 4 Dave Combs 2005-01-26 22:03:22 UTC
Comment on attachment 14103

this needed to be removed.
Comment 5 Sander Temme 2005-01-27 07:13:24 UTC
Attachment removed by administrator per request by Dave Combs.
Comment 6 Jianmang Li 2005-03-12 02:33:51 UTC
I'm not sure I have encoutered the same problem but it looks to me the root
causes are the same

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.*;
import org.apache.poi.hssf.util.*;
import java.io.*;

public class TestPOI
  public static void main(String[] argv){
      POIFSFileSystem fs=new POIFSFileSystem(new FileInputStream(argv[0]));
      HSSFWorkbook wb = new HSSFWorkbook(fs);
      for(int i=0;i<wb.getNumberOfNames();i++){
        HSSFName nm=wb.getNameAt(i);
          AreaReference areaRef=new AreaReference(nm.getReference());
    }catch(Exception e){

create a fresh new Excel book and put two names in it say a at A1 and b at A2.
run the program against the book and it will be fine.

delete the second row so the a at A2 will disapepar. save the book and run the
program against the modified book. you will get something like

[WARNING] Unknown Ptg 3c (60)
java.lang.ArrayIndexOutOfBoundsException: 5
        at org.apache.poi.hssf.util.CellReference.<init>(CellReference.java:35)
        at org.apache.poi.hssf.util.AreaReference.<init>(AreaReference.java:33)
        at TestPOI.main(TestPOI.java:15)
Comment 7 Jason Height 2006-01-17 10:24:34 UTC

Dont know if you have a change to lok at this one. I dont have much experience
with the CellReference side of things. Basically what is happening is that the
delete of the second row causes the name to point to a cell that no longer
exists, hence the cell reference becomes something like:


Now it appears to me at least that the AreaReference and the CellReference
classes do not handle invalid cell references such as this.

Comment 8 Avik Sengupta 2006-01-17 11:22:57 UTC
Indeed, CellReference etc does not handle invalid refs very well. I'll take a 
Comment 9 Yegor Kozlov 2008-05-17 03:45:47 UTC
We still have the described behaviour in poi-3.1-beta1. 
If a named range points to a deleted cell then an attempt to contruct AreaReference results in ArrayIndexOutOfBoundsException.
Excel does not delete names when a cell is deleted and workbook can contain such "invalid" named ranges. 

I added HSSFName.isDeleted() which checks if the name points to a cell that no longer exists. 
When iterating over the names user should check if the name is valid:

  //named range points to a deleted cell. 
} else {
  AreaReference ref1 = new AreaReference(hssfName.getReference());