Bug 59214 - java.lang.IllegalArgumentException: Sheet index (1) is out of range (0..0)
Summary: java.lang.IllegalArgumentException: Sheet index (1) is out of range (0..0)
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.14-FINAL
Hardware: PC All
: P2 critical (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2016-03-22 15:34 UTC by Jafar Aliyev
Modified: 2016-03-23 02:44 UTC (History)
0 users

This is the XLS file to test (31.00 KB, application/vnd.ms-excel)
2016-03-22 15:34 UTC, Jafar Aliyev
PoiTest.java (918 bytes, text/plain)
2016-03-22 15:37 UTC, Jafar Aliyev

Note You need to log in before you can comment on or make changes to this bug.
Description Jafar Aliyev 2016-03-22 15:34:50 UTC
Created attachment 33692 [details]
This is the XLS file to test

I'm trying to get an active sheet of HSSFWorkbook, but always get java.lang.IllegalArgumentException. 
Here is the code and the xls file to reproduce the problem. 

package org.apache.poi.mytest;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Iterator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.junit.Test;

public class PoiTest {
    public PoiTest() {

     public void testIt() throws FileNotFoundException, IOException {
         FileInputStream fis = new FileInputStream("Fir.xls"); 
         Workbook wb = new HSSFWorkbook(fis);
        Iterator<Sheet> shIt = wb.sheetIterator();
        while (shIt.hasNext()) {  
            Sheet sheet = shIt.next();
            System.out.println("Sheet name = "+sheet.getSheetName());
         Sheet sheet = wb.getSheetAt(wb.getActiveSheetIndex());           

When you run, you'll get the following output.

------------- Standard Output ---------------
Sheet name = Module1
------------- ---------------- ---------------
Testcase: testIt(org.apache.poi.mytest.PoiTest):	Caused an ERROR
Sheet index (1) is out of range (0..0)
java.lang.IllegalArgumentException: Sheet index (1) is out of range (0..0)
	at org.apache.poi.hssf.usermodel.HSSFWorkbook.validateSheetIndex(HSSFWorkbook.java:522)
	at org.apache.poi.hssf.usermodel.HSSFWorkbook.getSheetAt(HSSFWorkbook.java:994)
	at org.apache.poi.hssf.usermodel.HSSFWorkbook.getSheetAt(HSSFWorkbook.java:109)
	at org.apache.poi.mytest.PoiTest.testIt(PoiTest.java:30)

As you see, the Module1 is recognized as sheet, but active sheet's index=1, although the workbook has only one sheet.
Comment 1 Jafar Aliyev 2016-03-22 15:37:17 UTC
Created attachment 33693 [details]
Comment 2 Javen O'Neal 2016-03-22 16:30:47 UTC
Seema like the problem is that active sheet index could potentially be out of range.

What software was used to save Fir.xls? This seems like a bug on the creator of the file.

We could band-aid this problem by changing getActiveSheetIndex to return 0 if out of range, though this would still break if there were no sheets in the workbook. A sheet index of -1 could be used to indicate no sheet is active, though that would always break an unchecked call to getSheetAt.

What does Excel identify as the active sheet index if all sheets are deleted or hidden?

Another solution to this problem is to provide a nullable getActiveSheet().
Comment 3 Jafar Aliyev 2016-03-23 02:44:33 UTC
I'm using Excel 2007 (12.0.4518.1014), and the file is saved from this version.
I have nearly hundred of such files. These files are created initially on Excel 2013, then resaved on Excel 2007. Every month these files are updated, saved. In legacy application I converted them into database using Microsoft COM object and there was no problem with files. But the new application written in Java and I use POI to handle these files.
I deleted contents and an extra sheets, leaving only one sheet in this example. I can send you the filled file too. Even with information inside of sheets, it cant find the last sheet.
When I delete macros entirely, POI finds the sheet. The problem is macro Module.
It sees Macro1 as a sheet.
If I get null in getActiveSheet() it will not help me.
Actually wb.getSheet("Fir") gives me null value in this situation. But the sheet exists with information and I can't reach the cells of this sheet.