Read only specific sheet

ardb picture ardb · Apr 15, 2014 · Viewed 35.3k times · Source

I am trying to read just one sheet from an xls document and I have this:

 $objPHPExcel = $objReader->load('daily/' . $fisierInbound);
 $objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
 foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {

            $worksheetTitle     = $worksheet->getTitle();
            $highestRow         = $worksheet->getHighestRow(); // e.g. 10
            $highestColumn      = $worksheet->getHighestColumn(); // e.g 'F'
            $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
            $dataCalls = $worksheet->getCellByColumnAndRow(2, 2)->getValue();
            $dataSubstr = substr($dataCalls, 53);


        } 

The problem is that it reads all the sheets of the file.

Any ideas?

Answer

Mark Baker picture Mark Baker · Apr 15, 2014

As described in the PHPExcel User Documentation - Reading Spreadsheet Files document in the /Documentation folder (section 5.2. entitled"Reading Only Named WorkSheets from a File"):

If you know the name of the sheet that you want to read.

$inputFileType = 'Excel5'; 
$inputFileName = './sampleData/example1.xls'; 
$sheetname = 'Data Sheet #2'; 

/**  Create a new Reader of the type defined in $inputFileType  **/ 
$objReader = PHPExcel_IOFactory::createReader($inputFileType); 
/**  Advise the Reader of which WorkSheets we want to load  **/ 
$objReader->setLoadSheetsOnly($sheetname); 
/**  Load $inputFileName to a PHPExcel Object  **/ 
$objPHPExcel = $objReader->load($inputFileName); 

If you don't know the name of the worksheet in advance, you can get a list of all worksheets before loading the file

$inputFileType = 'Excel5'; 
$inputFileName = './sampleData/example1.xls'; 

/**  Create a new Reader of the type defined in $inputFileType  **/ 
$objReader = PHPExcel_IOFactory::createReader($inputFileType); 
/**  Read the list of worksheet names and select the one that we want to load  **/
$worksheetList = $objReader->listWorksheetNames($inputFileName)
$sheetname = $worksheetList[0]; 

/**  Advise the Reader of which WorkSheets we want to load  **/ 
$objReader->setLoadSheetsOnly($sheetname); 
/**  Load $inputFileName to a PHPExcel Object  **/ 
$objPHPExcel = $objReader->load($inputFileName);