CODEWITHSUNDEEP
phpexcelphpspreadsheet
blarg
blarg

Reputation: 3893

How to get cell coordinates when iterating in PHPSpreadsheet?

I'm trying to populate and colour some headers in PHPSpreadsheet. The number of columns can be variable. It is recommended in the docs to style cells as a range so I need to know the coordinates of the last cell to do this.

I've tried using getCellByColumnAndRow as I iterate through the columns but this returns the value of the cell instead of the coordinates:

$i = 1;
$lastCellValue = null;

foreach ($headers as $header) {
    $sheet->setCellValueByColumnAndRow($i, 1, $header);
    $lastCellValue = $sheet->getCellByColumnAndRow($i, 1);
    $i++;
}

$spreadsheet->getActiveSheet()->getStyle('A1:'.$lastCellValue)->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()->setARGB('FFFF0000');

How can I get the coordinates of the last cell when iterating?

Upvotes: 3

Views: 15992

Answers (2)

tCot
tCot

Reputation: 357

To retrieve the cell value, you can use this :

$sheet->getCellByColumnAndRow($intColumn, $intRow)->getParent()->getCurrentCoordinate();

In your case, you should try :

enter$lastCellValue = $sheet->getCellByColumnAndRow($i, 1)->getParent()->getCurrentCoordinate();

Upvotes: 2

Mark Baker
Mark Baker

Reputation: 212412

getCellByColumnAndRow() doesn't return the cell value, it returns the cell object.... and that cell object has a number of methods including getColumn(), getRow() and getCoordinate()

So

foreach ($headers as $header) {
    $sheet->setCellValueByColumnAndRow($i, 1, $header);
    $lastCellAddress = $sheet->getCellByColumnAndRow($i, 1)->getCoordinate();
    $i++;
}

$spreadsheet->getActiveSheet()->getStyle('A1:'.$lastCellAddress)->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()->setARGB('FFFF0000');

Upvotes: 7

Related Questions