Asked  8 Months ago    Answers:  5   Viewed   80 times

How can I do a loop which based on Excel worksheet columns? I found (and used) WorksheetIterator, RowIterator and CellIterator but nothing about columns.

 Answers

53

There is no ColumnIterator, so you'll have to do this by hand.

For any given worksheet:

To loop rows for a column:

$column = 'A';
$lastRow = $worksheet->getHighestRow();
for ($row = 1; $row <= $lastRow; $row++) {
    $cell = $worksheet->getCell($column.$row);
    //  Do what you want with the cell
}

To loop columns in a row, you can take advantage of PHP's Perls-style ability to increment characters:

$row = 1;
$lastColumn = $worksheet->getHighestColumn();
$lastColumn++;
for ($column = 'A'; $column != $lastColumn; $column++) {
    $cell = $worksheet->getCell($column.$row);
    //  Do what you want with the cell
}

Note that when comparing column letters to test for the last column in the loop, we can't simply use < or <= because we're comparing strings, and "B" > "AZ" in standard string comparison, so we use a != comparison, having incremented the highest column value to give the first column ID past the end point.

You can also use

$worksheet->cellExists($column.$row);

in the loop to test for the existence of a cell before accessing it using getCell() (or not) to emulate the iterator getIterateOnlyExistingCells() behaviour

The iterators are actually fairly slow, so you may well find these simple loops are faster than using the iterators.

UPDATE (2015-05-06)

PHPExcel version 1.8.1 has introduced a new Column Iterator. The Row and Column iterators also allows you to specify a range of rows or columns to iterate, and allow you to use prev() and well as next() when looping through

Wednesday, March 31, 2021
 
coolguy
answered 8 Months ago
39

When you set cell values individually, you have the option of setting the datatype explicitly, but when you use the fromArray() method, you don't have this option.

However, by default, PHP uses a default value binder to identify datatypes from the values passed, and set the cell datatype accordingly. This default behaviour is defined in a class /PHPExcel/Cell/DefaultValueBinder.php.

So you can create your own value binder, as described in the PHPExcel Documentation, that would set every value as a string datatype.

Something like:

class PHPExcel_Cell_MyColumnValueBinder extends PHPExcel_Cell_DefaultValueBinder implements PHPExcel_Cell_IValueBinder
{
    protected $stringColumns = [];

    public function __construct(array $stringColumnList = []) {
        // Accept a list of columns that will always be set as strings
        $this->stringColumns = $stringColumnList;
    }

    public function bindValue(PHPExcel_Cell $cell, $value = null)
    {
        // If the cell is one of our columns to set as a string...
        if (in_array($cell->getColumn(), $this->stringColumns)) {
            // ... then we cast it to a string and explicitly set it as a string
            $cell->setValueExplicit((string) $value, PHPExcel_Cell_DataType::TYPE_STRING);
            return true;
        }
        // Otherwise, use the default behaviour
        return parent::bindValue($cell, $value);
    }
}

// Instantiate our custom binder, with a list of columns, and tell PHPExcel to use it
PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_MyColumnValueBinder(['A', 'B', 'C', 'E', 'F']));

$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->fromArray($dataArray,null,"A2");
Friday, May 28, 2021
 
Wilk
answered 5 Months ago
80

Have you tried...

$mail->AddAttachment($full_path_to_file, "report.xls");
Saturday, May 29, 2021
 
Len_D
answered 5 Months ago
18

You could use rep

data.frame(x = unlist(rep(df, each = 6)))

Checking output with each = 2

data.frame(x = unlist(rep(df, each = 2)))

#   x 
#1  1
#2  3
#3  1
#4  3
#5  3
#6  4
#7  3
#8  4
#9  4
#10 3
#11 4
#12 3
#13 6
#14 1
#15 6
#16 1
Sunday, August 29, 2021
 
fardjad
answered 2 Months ago
99

You're using iterators. Did you look at the code example for iterators in the /Tests directory? If so, you might have seen reference to the WorksheetIterator

Alternatively, the getAllSheets() method of the PHPExcel object returns an array of worksheets, which allows you to use a foreach loop

Tuesday, September 14, 2021
 
vivek
answered 2 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share