How to get specified rows and columns in multiple sheets

Topics: Developer Forum, Project Management Forum, User Forum
Jul 24, 2012 at 9:13 PM

I have 4 worksheets that I want to read.  They should start reading from row 23-40 and column a-f.  Below is my code and I tried to output the cell value for the first worksheet and all i got is blank.  Can someone help?


$inputFileType = 'Excel5';

$inputFileName = 'example.xls';

$sheetnames = array('Networks','Workstation','Communication','Hardware');


    /**  Define a Read Filter class implementing PHPExcel_Reader_IReadFilter  */
    class MyReadFilter implements PHPExcel_Reader_IReadFilter
        private $_startRow = 0;   
        private $_endRow = 0;   
        private $_columns = array();
        /**  Get the list of rows and columns to read  */
        public function __construct($startRow, $endRow, $columns)
            $this->_startRow    = $startRow;
            $this->_endRow= $endRow;
            $this->_columns= $columns;
        public function readCell($column, $row, $worksheetName = '')
            //  Only read the rows and columns that were configured
            if ($row >= $this->_startRow && $row <= $this->_endRow)
                if (in_array($column,$this->_columns))
                    return true;
            return false;
    } //end class MyReadFilter   
    /**  Create an Instance of our Read Filter, passing in the cell range  **/
    $filterSubset = new MyReadFilter(23,40,range('A','F'));
    /**  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  **/
    /** Apply the filter to get row from 23-40 and column A-F  **/
    /**  Load $inputFileName to a PHPExcel Object  **/
    $objPHPExcel = $objReader->load($inputFileName);
    $loadedSheetNames = $objPHPExcel->getSheetNames();
    foreach($loadedSheetNames as $key => $value)
        $objWorksheet = $objPHPExcel->setActiveSheetIndexByName($value);
        foreach ($objWorksheet->getRowIterator() as $row)
            $cellIterator = $row->getCellIterator();
            foreach ($cellIterator as $cell)
                $cellValue = trim($cell->getValue());
                echo "<p>cell name ".$cellValue."</p>";

Jul 24, 2012 at 11:15 PM

Just because you're using a filter to prevent loading of data from rows 1 to 22, doesn't mean they don't still exist - they're just all blank cells - row 23 still loads as row 23; and by default the rowIterator starts reading at row 1.

You want to tell the iterator to start at row 23.

You can do this by passing the start row to the getRowIterator() method:

foreach ($objWorksheet->getRowIterator(23) as $row)
Jul 25, 2012 at 8:02 PM

Oh I forgot about that.  Thanks for pointing that out.  It is now working.  Thanks.