| Summary: | [INFO NEEDED] Call HSSFName.getReference() throwing java.lang.IndexOutOfBoundsException | ||
|---|---|---|---|
| Product: | POI | Reporter: | Ambrose Wong <w_ambrose> |
| Component: | HSSF | Assignee: | POI Developers List <dev> |
| Status: | RESOLVED FIXED | ||
| Severity: | critical | CC: | combs |
| Priority: | P3 | ||
| Version: | unspecified | ||
| Target Milestone: | --- | ||
| Hardware: | PC | ||
| OS: | other | ||
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. 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 meantime? Thanks, Dave Combs Created attachment 14103 Comment on attachment 14103 this needed to be removed. Attachment removed by administrator per request by Dave Combs. 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){
try{
POIFSFileSystem fs=new POIFSFileSystem(new FileInputStream(argv[0]));
HSSFWorkbook wb = new HSSFWorkbook(fs);
System.out.println(wb.getNumberOfNames());
for(int i=0;i<wb.getNumberOfNames();i++){
HSSFName nm=wb.getNameAt(i);
AreaReference areaRef=new AreaReference(nm.getReference());
}
}catch(Exception e){
e.printStackTrace();
}
}
}
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)
2
java.lang.ArrayIndexOutOfBoundsException: 5
at
org.apache.poi.hssf.util.CellReference.separateRefParts(CellReference.java:111)
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)
Avik, 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: =Sheet1!#REF! Now it appears to me at least that the AreaReference and the CellReference classes do not handle invalid cell references such as this. Jason Indeed, CellReference etc does not handle invalid refs very well. I'll take a look. 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:
if(hssfName.isDeleted()){
//named range points to a deleted cell.
} else {
AreaReference ref1 = new AreaReference(hssfName.getReference());
}
Yegor
|
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 (name.getReference().indexOf("!")+1)); row = sheet.getRow(ref.getRow()); cell = row.getCell((short)ref.getCol()); System.out.println("The value of Form_4.1_01 is " + cell.getStringCellValue()); 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 (Workbook.java:1693) at org.apache.poi.hssf.model.Workbook.getSheetReferences (Workbook.java:1678) at org.apache.poi.hssf.usermodel.HSSFName.getReference (HSSFName.java:142) 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.