error : Sheet index is out of bounds

Mar 9, 2011 at 8:55 PM

Hi everybody,

I have a problem with the objet Reader. I would like to read a specific sheet on a workbook but I have this error :

exception 'Exception' with message 'Sheet index is out of bounds.' in [...]Classes\PHPExcel.php:259 Stack trace: #0 [...]\Classes\PHPExcel\Reader\Excel2007.php(1379): PHPExcel->getSheet('10') #1 PHPExcel_Reader_Excel2007->load('...') #2 {main}

What does it mean ? What could be the reason ?

I try to find out. I check the title and the size of the sheet. The sheet is also in the middle of the workbook and not at the end. I try with the other sheets of the workbook but everything works fine.

Versions :

Apache 2.2.11

PHP 5.3.0

My code using objet Reader, the name of the sheet is in a array called $namesheet[] :

require_once 'Classes/PHPExcel/IOFactory.php';
			
			class MyReadFilter implements PHPExcel_Reader_IReadFilter
			{
				public function readCell($column, $row, $worksheetName = '') {
					if ($column <= 11 && $row <= 65 && $row >= 4) {
						return true;
					}
					
					return false;
				}
			}

			$objReader= PHPExcel_IOFactory::createReader('Excel2007');
			$objReader->setLoadSheetsOnly($namesheet[$p]);
			$objReader->setReadFilter( new MyReadFilter() );
				
			try 
			{
				$objPHPExcel = $objReader->load($dir_fichier);
			} catch(Exception $e) {
				die('Erreur chargement du fichier'.$e);
			}

Thank you for your help.

May 3, 2012 at 3:46 AM
Edited May 3, 2012 at 3:50 AM

Sorry to bump such an old topic, but I have a similar/related problem, and searching the discussions has not netted me a solution. So...

For almost all of my applications, setLoadSheetsOnly works correctly. However, sometimes, even though the sheet name parameter that I am supplying to setLoadOnlySheets does in fact exist and does show up in listWorksheetNames, it gives me a "sheet index out of bounds" error. I haven't figured out the specifics, but sometimes I can avoid the error for that particular sheet by moving it earlier or later in the list of sheet tabs in my Excel file, but this is not reliable, I cannot predict it, and it is certainly no solution.

Thus my question is... Does anybody know why the "sheet index out of bounds" error occurs when using setLoadSheetsOnly for what should be a valid sheet name?

I am using PHPExcel 1.7.6 under PHP 5.3.

Example code...

$reader = PHPExcel_IOFactory::createReader('Excel2007');
							
$contained_sheets = $reader->listWorksheetNames($tempfile);
if (array_search($sheet, $contained_sheets)) {
	$reader->setLoadSheetsOnly($sheet); // this is where the error happens
	$reader = $reader->load($tempfile); // on one of these two lines
	/// and so on

Jan 20, 2013 at 10:08 AM
Edited Jan 20, 2013 at 10:10 AM

I am running into a similar Problem.

I get the following Message (Fatal error: Uncaught exception 'Exception' with message 'Sheet index is out of bounds. ...) when I try to run the code below:

$inputFileName = $name;
$objReader = new PHPExcel_Reader_Excel2007();
$objReader->setReadDataOnly(true);
$objReader->setLoadSheetsOnly("pricelist");

$objPHPExcel = $objReader->load($inputFileName); // this is the place where the error occurs

$objWorksheet = $objPHPExcel->getActiveSheet();

The error occurs when the worksheet I try to load is not the first sheet in the collection.

If I delete all sheets standing in front of my desired sheet everything works fine.

I am using PHPExcel 1.7.8 under PHP 5.3

Coordinator
Jan 21, 2013 at 12:48 PM
Edited Jan 21, 2013 at 12:54 PM

I've not been able to recreate this. There was a problem with this in the 1.7.7 release, but it should have been resolved in the 1.7.8 release.

 

Can you try dumping the value of $objPHPExcel->getSheetNames() and $objPHPExcel->getActiveSheetIndex();

 

I have tweaked the Develop branch to correctly set the correct active sheet if setReadDataOnly(true) and the active sheet isn't the first sheet you're loading (if you load multiple sheets) since then, but it should still return a 0 value for the sheet index in the 1.7.8 release.

Feb 6, 2013 at 2:35 PM
I'm having this issue as well with the 1.7.8 release.

This is my code:
$filename = "/home/rob/Desktop/protected.xlsx";
$objReader = PHPExcel_IOFactory::createReader("Excel2007");

$worksheetNames = $objReader->listWorksheetNames($filename);
print_r($worksheetNames);

$objReader->setLoadSheetsOnly( array("Sales Dashboard") );
$objPHPExcel = $objReader->load( $filename );
This is the output of worksheetNames
Array
(
    [0] => Reference
    [1] => BBNS
    [2] => Revisions
    [3] => Mini Transfer
    [4] => Customer Details
    [5] => StarRecovery Contract
    [6] => Basic Configuration
    [7] => New Calcs
    [8] => Dashboard Calcs
    [9] => Sales Dashboard
    [10] => Comparison
    [11] => Quote
    [12] => Beta Contract Rider
    [13] => Order Detail
    [14] => All S2S Choices
    [15] => New Tables
    [16] => Dlr Comp Disbur
    [17] => Dealer Net Inv
    [18] => Reseller Summary
    [19] => Monthly Service
    [20] => PBX & Infrastructure
    [21] => Phones
    [22] => Lease App
    [23] => New Services
    [24] => Rent Quote Data
    [25] => New Products
    [26] => Prof Serv
)
Here's the error it throws
PHP Fatal error:  Uncaught exception 'Exception' with message 'Sheet index is out of bounds.' in /var/www/includes/PHPExcel.php:286
Stack trace:
#0 /var/www/includes/PHPExcel/Reader/Excel2007.php(1751): PHPExcel->getSheet('9')
#1 /var/www/portal/tester.php(37): PHPExcel_Reader_Excel2007->load('/home/rob/Deskt...')
#2 {main}
  thrown in /var/www/includes/PHPExcel.php on line 286
Coordinator
Feb 7, 2013 at 7:40 AM
If you're using
$objReader->setLoadSheetsOnly( array("Sales Dashboard") );
then it will be sheet 0

If you're not loading sheets 0 through 8, then sheet 9 will become the new sheet 0
Feb 7, 2013 at 1:41 PM
That makes sense, but that's not what's happening here. PHPExcel fails while loading.

I am using this code and it fails right when trying to load.
$objReader->setLoadSheetsOnly( array("Sales Dashboard") );
$objPHPExcel = $objReader->load($filename);  <-- failure
$objWorksheet = $objPHPExcel->getSheetByName("Sales Dashboard");
The 'PHPExcel->getSheet('9')' in the exception is something internal to the load() function.
Feb 8, 2013 at 10:29 PM
Edited Feb 8, 2013 at 10:29 PM
I narrowed down my problem to defined names.

in Excel2007.php line 1717
if ($xmlWorkbook->definedNames) {
      foreach ($xmlWorkbook->definedNames->definedName as $definedName) {
      ....
    if ($worksheet = $docSheet->getParent()->getSheetByName($range[0])) {
        $extractedRange = str_replace('$', '', $range[1]);
        $scope = $docSheet->getParent()->getSheet((string)$definedName['localSheetId']);

        $excel->addNamedRange( new PHPExcel_NamedRange((string)$definedName['name'], $worksheet, $extractedRange, true, $scope) );
    }
      ....
Basically, i think the $definedName['localSheetId'] is pulling directly from the workbook and doesn't have a modified 'localSheetId'. It seems to be using the same sheet ID's regardless of the setLoadSheetsOnly() function.

If I comment out this entire block of code, I'm able to load my workbook and view data properly.
Coordinator
Feb 11, 2013 at 4:55 PM
Can you generate a small demo workbook to demonstrate this problem?
Feb 26, 2013 at 8:13 AM
Edited Feb 26, 2013 at 8:16 AM
Hi guys,

We've encountered this problem too, and the workaround of commenting out lines 1717 to 1780 solved it.

We've cross posted this to Github as well.

Not sure if removing these lines will affect future codes...

We're using version 1.7.8.

We have a workbook with 2 sheets, one which is fine, the other triggers this error. Should/Can we upload it here?
Coordinator
Feb 26, 2013 at 9:40 AM
Simply commenting out that block of lines will have adverse effects with some worksheets, especially if they contain formulae using defined names.

You can upload a file to http://phpexcel.codeplex.com/workitem/10749?ProjectName=phpexcel which will certainly help me to isolate the exact cause of the problem and look at identifying a clean fix that cause other problems.
Feb 27, 2013 at 2:06 AM
Hi MarkBaker,

Thanks! I've uploaded the file called LTETestFile (2sheets 1prob).xlsx
Coordinator
Feb 28, 2013 at 12:32 PM
There should now be a working fix for this in the develop branch on github