Bug 56325

Summary: IndexOutOfBoundsException in removeSheetAt(0)
Product: POI Reporter: Aleksey <frolovskij>
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: normal CC: bboyz269, dolphin.in.the.sky.51
Priority: P2    
Version: 3.10-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: All   
Attachments: An example of XLS file to help you reproduce a bug
Here is a file can verify this problem

Description Aleksey 2014-03-27 17:02:17 UTC
Created attachment 31448 [details]
An example of XLS file to help you reproduce a bug

Steps to reproduce:
1. Create an empty XLS file with 3 sheets (they're there by default)
2. On the 3rd sheet create 6 named ranges. The scope of those names must be that sheet, not the workbook (it is important!), so do it with Name Manager.
3. Run the code:

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import java.io.FileInputStream;
import java.io.IOException;

public class Test {

  public static void main(String[] args) throws IOException {
    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("empty.xls"));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    wb.removeSheetAt(0);
  }
}

Expected result is:
- the first sheet is being removed from the sheet.

The actual result is:
Exception in thread "main" java.lang.IndexOutOfBoundsException: Index: 3, Size: 3
	at java.util.ArrayList.RangeCheck(ArrayList.java:547)
	at java.util.ArrayList.get(ArrayList.java:322)
	at org.apache.poi.hssf.record.ExternSheetRecord.getRef(ExternSheetRecord.java:164)
	at org.apache.poi.hssf.record.ExternSheetRecord.adjustIndex(ExternSheetRecord.java:168)
	at org.apache.poi.hssf.model.LinkTable.updateIndexToInternalSheet(LinkTable.java:419)
	at org.apache.poi.hssf.model.InternalWorkbook.removeSheet(InternalWorkbook.java:721)
	at org.apache.poi.hssf.usermodel.HSSFWorkbook.removeSheetAt(HSSFWorkbook.java:899)
	at Test.main(Test.java:12)
Comment 1 Dominik Stadler 2014-05-15 19:51:16 UTC
This seems to have been introduced by the fix for Bug 54400, the linkTable-code is a bit complicated and hard to get right, we obviously need another go at it.
Comment 2 Dominik Stadler 2014-05-15 21:16:06 UTC
I tried to fix this in r1595048 so that both the previous problem and this one are fixed and removing sheets correctly updates the references to external books in all cases.
Comment 3 bboyz269 2014-08-23 06:58:53 UTC
Hi, I encountered the possible the same problems with poi_3.10.1 and poi_3.11.beta2.

The workbook contains: 
* sheet1: is protected; contains formulas with referred cells from the other sheets
* sheet2, 3, ...: nothing special
* all sheets: contains named ranges for printing (print area, repeated page header)

My code: just open and try to delete any sheet using wb.removeSheetAt(x).

Error: the same exception as Aleksey. (IndexOutOfBoundsException at ExternSheetRecord#getRef).

I wonder if this bug is fixed and withc version of poi containning the fix since i'm not familliar with bugzilla.

Sorry for my bad English.
Comment 4 Dominik Stadler 2014-08-23 09:56:21 UTC
Can you attach the Excel file that triggers the problem so we can reproduce it? The original file in this bug-report works fine now via a unit-test so it seems there are more cases where it fails with similar error message.
Comment 5 Stanley Ni 2014-10-24 17:26:43 UTC
I believe that I have the same problem here. After setting the print area or set the repeat rows. I can't use the method removeSheetAt(int).

And in my code, if I remove the sheet first, and then set the print area or repeat rows, everything works well.
Comment 6 Stanley Ni 2014-10-24 17:30:08 UTC
Sorry, I forgot the mention the POI version I'm using.
It is poi-3.10.1-2014-0818. 

And here is the exception I got:
Caused by: java.lang.IndexOutOfBoundsException: Index: 12, Size: 6
        at java.util.ArrayList.rangeCheck(ArrayList.java:571)
        at java.util.ArrayList.get(ArrayList.java:349)
        at org.apache.poi.hssf.record.ExternSheetRecord.getRef(ExternSheetRecord.java:164)
        at org.apache.poi.hssf.record.ExternSheetRecord.adjustIndex(ExternSheetRecord.java:168)
        at org.apache.poi.hssf.model.LinkTable.updateIndexToInternalSheet(LinkTable.java:419)
        at org.apache.poi.hssf.model.InternalWorkbook.removeSheet(InternalWorkbook.java:721)
        at org.apache.poi.hssf.usermodel.HSSFWorkbook.removeSheetAt(HSSFWorkbook.java:899)


In my code, I called removeSheetAt(3). And there are 6 sheets in the file. I set print area and repeat rows on the last two sheets.
Comment 7 Stanley Ni 2014-10-24 17:46:57 UTC
Created attachment 32143 [details]
Here is a file can verify this problem

Here is the code will cause this problem
public class PoiBugTesting {

  public PoiBugTesting() {
    // TODO Auto-generated constructor stub
  }

  public static void main(String[] args) {
    try {
      HSSFWorkbook wBook = (HSSFWorkbook) WorkbookFactory.create(new FileInputStream("C:\\Users\\xxx\\Desktop\\Bug Test Book.xls"));
      HSSFSheet sheet = wBook.cloneSheet(2);
      wBook.setSheetName(3, "Clone 1");
      sheet.setRepeatingRows(CellRangeAddress.valueOf("2:3"));
      wBook.setPrintArea(3, "$A$4:$C$10");
      
      sheet = wBook.cloneSheet(2);
      wBook.setSheetName(4, "Clone 2");
      sheet.setRepeatingRows(CellRangeAddress.valueOf("2:3"));
      wBook.setPrintArea(4, "$A$4:$C$10");
      
      wBook.removeSheetAt(2);
      
      FileOutputStream fOut = new FileOutputStream("C:\\Users\\xxx\\Desktop\\test.xls");
      wBook.write(fOut);
      fOut.close();
      
    } catch (InvalidFormatException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (FileNotFoundException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (IOException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }

  }

}
Comment 8 Dominik Stadler 2015-03-14 14:57:32 UTC
I now added the latest test that was provided here, it works fine with latest versions of POI. 

If you still see this problem, please check with the latest version of POI, at least 3.12-beta1 and reopen this bug with an updated test-file/unit-test or more information how the problem can be reproduced.