Iteration skipping certain columns?

Topics: Developer Forum
Mar 11, 2009 at 12:07 AM
I've reviewed http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=8931.  I'm looking to do something similar but I need to exclude certain columns.  Basically I need all rows with data but only certain columns, for instance, 1-3, 5, 7, 9-11.  Any help would be much appreciated, thanks!
Developer
Mar 11, 2009 at 10:46 PM
How about simply checking the column index as you loop through the cells?

$reader = PHPExcel_IOFactory::createReader('Excel2007');
$excel = $reader->load('read.xlsx');

foreach ($excel->getWorkSheetIterator() as $worksheet) {
    foreach ($worksheet->getRowIterator() as $row) {
        $cellIterator = $row->getCellIterator();

        foreach ($cellIterator as $cell) {
            $column = PHPExcel_Cell::columnIndexFromString($cell->getColumn());
            if ($column != 4)  {
                // do something
            }
        }
    }
}

Mar 11, 2009 at 11:25 PM
I think that helps a bit, but it only returns the column indexes.  What if I need to push the associated values into an array?

<?php
$uploadName = $_REQUEST['upload_file_name'];

/** Include path **/
set_include_path(get_include_path().PATH_SEPARATOR.'./PHPExcel/Classes/');
//set_include_path(get_include_path() . PATH_SEPARATOR . '/PHPLinq/Classes/');

/** PHPExcel_IOFactory */
include 'PHPExcel/IOFactory.php';

$filename = "./uploads/" . $uploadName;
$fileInfo = pathinfo($filename);

switch (strtolower($fileInfo['extension'])) {
    case 'xlsx'    : $fileType = 'Excel 2007';
                  $fileReader = 'Excel2007';
                break;
    case 'xls'    : $fileType = 'Excel 5 (BIFF)';
                  $fileReader = 'Excel5';
                break;
}

$objReader = PHPExcel_IOFactory::createReader($fileReader);
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($filename);

//  Select the current worksheet
$objPHPExcel->setActiveSheetIndex(0);
$objWorksheet = $objPHPExcel->getActiveSheet();

foreach ($objWorksheet->getRowIterator() as $row) {
    $cellIterator = $row->getCellIterator();
        foreach ($cellIterator as $cell) {
            $column = PHPExcel_Cell::columnIndexFromString($cell->getColumn());
            if ($column = 1 or $column = 2 or $column = 5 or $column = 10 or $column = 11
            or $column = 15 or $column = 17 or $column = 18 or $column = 25 or $column = 27 or $column = 28 or $column = 29 or $column = 34)  {
                //push to array - toArray()
        }
    }
}
?>
Developer
Mar 11, 2009 at 11:43 PM
You can also get the row index:

$rowIndex = $cell->getRow(); // e.g. 1

Not sure if this is what you are asking about?

Mar 11, 2009 at 11:48 PM
I've got the indexes but I need the values for all rows returned. 

$cellValue = $cell->getValue();
echo $cellValue;

Just gives me A1.  I need to push the values from the rows that meet the IF requirements into an array.  The toArray() function isn't documented yet and I'm not sure I'm using it correctly with the data being returned. 



Developer
Mar 12, 2009 at 12:04 AM
You mean $cell->getValue() is giving you the cell coordinate instead of what is inside the cell? This should not be happening? I'm confused.

Mar 12, 2009 at 12:06 AM
I'm sorry, it gives me the VALUE for A1, which in this case is the sheet name.
Developer
Mar 12, 2009 at 12:19 AM
In your case, I would perhaps not use iterators. Can you try to run this and see if that is what you're looking for?

$reader = PHPExcel_IOFactory::createReader('Excel2007');
$reader->setReadDataOnly(true);

$excel = $reader->load('read.xlsx');
$sheet = $excel->getSheet(0);

$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);

for ($row = 1; $row <= $highestRow; ++$row) {
    for ($col = 0; $col <= $highestColumnIndex; ++$col) {
        if (!in_array($col, array(1, 2, 5, 10, 11, 15, 17, 18, 25, 27, 28, 29, 34))) {
            continue;
        }
        $values[$row][$col] = $sheet->getCellByColumnAndRow($col, $row)->getValue();
    }
}

var_dump($values);

Mar 12, 2009 at 12:32 AM
Ahhh...lifesaver!  My columns were off a bit because I started at 1 instead of 0.  Can't thank you enough!

$objReader = PHPExcel_IOFactory::createReader($fileReader);
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($filename);

//  Select the current worksheet
$objPHPExcel->setActiveSheetIndex(0);
$objWorksheet = $objPHPExcel->getActiveSheet();

$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);

for ($row = 3; $row <= $highestRow; ++$row) {
    for ($col = 0; $col <= $highestColumnIndex; ++$col) {
        if (!in_array($col, array(0, 1, 4, 9, 10, 14, 16, 17, 24, 26, 27, 28, 33))) {
            continue;
        }
        $values[$row][$col] = $objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
    }
}

var_dump($values);