How to skip first 3-4 rows while reading an Excel file.

Topics: Developer Forum, Project Management Forum, User Forum
Jul 28, 2011 at 1:06 PM

I am looking for some solution to skip first 3-4 rows while reading an Excel file. If anybody done this please let me know. you can contact me at umer.pucit@gmail.com

Regards
Umer Farooq.

Jul 28, 2011 at 7:37 PM

Well... Not sure what you want exactly. You can read where you want, you just write the desired row :

$objPHPExcel->getActiveSheet()->getCell('B8')->getValue();

or

$objPHPExcel->getActiveSheet()->getCellByColumnAndRow(1, 8)->getValue();
// cell B8

For looping cells, use loop, variables of your program or use iterators. (see chapter 4.5 of the Developer doc).

You can use ReadFilter if you really need than the loader skip rows (see chapter 4.3. of the doc Reading Spreadsheet Files).

And samples files in Tests directory.

Jul 29, 2011 at 6:41 AM

@LWol

Thank for the help.

Jul 29, 2011 at 7:40 AM

The example I am looking for is as under.

 

Step 1 : I need to skip rows 1 to 3 completely while reading.

Step 2: I have data in column A,B,C and its starting from row 4. This data is what I need only from the Excel file.

 

What I have currently is my code starts reading from row 1 and column filter is applied. But I am unable to skip first 3 rows of the Sheet.

Jul 29, 2011 at 7:45 AM

code example is

 

$objReader = new PHPExcel_Reader_Excel5();
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($inputFileName);
$rowIterator = $objPHPExcel->getActiveSheet()->getRowIterator();

$col_array = array('A','B','C');

 

// C column include date.

$array_data = array();

                foreach ($rowIterator as $row) {
                    $cellIterator = $row->getCellIterator();
                    $cellIterator->setIterateOnlyExistingCells(false); // Loop all cells, even if it is not set
                    //if(1 == $row->getRowIndex ()) continue;//skip first row
                    $rowIndex = $row->getRowIndex();

                    foreach ($cellIterator as $cell) {
                        $count = 1;
                        $array_size = sizeof($col_array);
                        foreach ($col_array as $inner_val) {
                            if ($inner_val == $cell->getColumn()) {
                                if ($array_size == $count) {
                                    $array_data[$rowIndex][$cell->getColumn()] = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), 'YYYY-MM-DD');
                              } else {
                                    $array_data[$rowIndex][$cell->getColumn()] = $cell->getCalculatedValue();
                                }
                            }
                            $count++;
                        }
                    }
                }

Jul 29, 2011 at 8:26 AM

Solution found.

Jul 29, 2011 at 11:10 AM

I don't have integrated in your code, but this is a working sample with your needs :
rows 1-3 skipped, columns>'C' discarded, cells like A1, B2 exists but ignored.

require_once 'Excel/PHPExcel.php';
require_once 'Excel/PHPExcel/Reader/Excel2007.php';
class MyReadFilter implements PHPExcel_Reader_IReadFilter
{
	public function readCell($column, $row, $worksheetName = '') {
		$Retour=false;
		$column=PHPExcel_Cell::columnIndexFromString($column);// Warning ! A=1, not zero as usual
		if($row<4 || $column>3)
			$Retour=false;
		else
			$Retour=true;
		return $Retour;
	}
}
echo '<pre>';
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(true);
$objReader->setReadFilter( new MyReadFilter() );
$objPHPExcel = $objReader->load('cache/testiterator.xlsx');
$worksheet=$objPHPExcel->getSheet(0);
foreach ($worksheet->getRowIterator() as $row) {
		if($row->getRowIndex()>3){
			echo '    - Row number: ' . $row->getRowIndex() . "\r\n";
			
			$cellIterator = $row->getCellIterator();
			//Not needed for you, i think, else add a test if(!is_null($cell)) in foreach
			//$cellIterator->setIterateOnlyExistingCells(false); // Loop all cells, even if it is not set
			foreach ($cellIterator as $cell) {
				echo '        - Cell: ' . $cell->getCoordinate() . ' - ' . $cell->getCalculatedValue() . "\r\n";
			}
		}
	}
echo '</pre>';

Jun 25, 2012 at 10:25 PM

what was your solution to this issue?

Coordinator
Jun 25, 2012 at 11:44 PM
petite_fleure wrote:

what was your solution to this issue?


Have you tried LWol's example?

Sep 17, 2014 at 12:58 PM
Edited Sep 17, 2014 at 1:02 PM
Just wanted to add solution for PHPExcel version 1.8.0. Just pass row offset to getRowIterator method.
/**
 * Get row iterator
 *
 * @param  integer   $startRow    The row number at which to start iterating
 * @return PHPExcel_Worksheet_RowIterator
 */
 public function getRowIterator($startRow = 1) {
   return new PHPExcel_Worksheet_RowIterator($this,$startRow);
 }
Code:
try
{
  $reader = new PHPExcel_Reader_Excel2007();

  // http://stackoverflow.com/questions/13626678/phpexcel-how-to-
  // check-whether-a-xls-file-is-valid-or-not
  //
  if ($reader->canRead("file.xlsx")) !== true )
  {
    echo "Invalid xlsx file.";
    exit();
  }

  /*
         If you're only interested in the cell values in a workbook, 
         but don't need any of the cell formatting information, 
         then you can set the reader to read only the data values 
         and any formulas from each cell using the setReadDataOnly() method.

         It is important to note that Workbooks (and PHPExcel) store dates and 
         times as simple numeric values: they can only be distinguished from 
         other numeric values by the format mask that is applied to that cell. 
         When setting read data only to true, PHPExcel doesn't read the cell 
         format masks, so it is not possible to differentiate between dates/times 
         and numbers.
   */
  $reader->setReadDataOnly(true);

  $excel = $reader->load("file.xlsx");
}
catch(PHPExcel_Reader_Exception $e)
{
  echo "Error loading file" . $e->getMessage();
  exit();
}
catch(Exception $e)
{
  echo "Exception, error loading file 2:" . $e->getMessage();
  exit();
}

// set worksheet
//
$worksheet = $excel->setActiveSheetIndex(0);

// skip first "header" row by adding argument 2 to getRowIterator
//
foreach($worksheet->getRowIterator(2) as $row)
{
  $one   = $worksheet->getCellByColumnAndRow(0, $row->getRowIndex())->getValue();
  $two = $worksheet->getCellByColumnAndRow(1, $row->getRowIndex())->getValue();
  ...