PhpSpreadsheet: Getting 'Invalid cell coordinate' error despite using integer column indices in setCellValueByColumnAndRow

Question

I'm encountering an issue with PhpSpreadsheet where I'm getting an "Invalid cell coordinate" error while trying to export an excel report from laravel project,

Specifically with coordinates like '01'. I've checked my code and ensured that I'm using integer column indices in setCellValueByColumnAndRow:

Here is the full code of my Controller method:

    public function getFestivalPlanRegister($id, $author = null)
        {
            if (request()->has('excel_export')){
                $festival = $festival->where('festival_plan.fsp_condition_plan', '=', 'completed');
            }
            
            if (request()->has('excel_export')) {
                $spreadsheet = new Spreadsheet();
                $spreadsheet->setActiveSheetIndex(0)->setRightToLeft(true);
                $activeSheet = $spreadsheet->getActiveSheet();
    
                $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
                $active_sheet = $spreadsheet->getActiveSheet();
                $active_sheet->setRightToLeft(true);
    
                $style = [
                    'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
                ];
    
                // Define the header row and style
                $headerColumns = [
                   'Registration ID', 'Project Tracking Code', 'Farsi Title of the Project', 
                   'English Title of the Project', 'Section',
                   'Axis', 'Challenge', 'Lead Author Name', 'Research Location', 'Lead Author 
                    Contact Number',
                    'Lead Author Email', 'Lead Author Address', 'Lead Author Postal Code', 'Lead 
                     Author Province',
                     'Lead Author City', 'Lead Author Academic Level', 'Lead Author Educational 
                     Level',
                     'Project Status', 'Collaborator Name', 'Collaborator National ID', 
                     'Collaborator Academic Level',
                     'Collaborator Educational Level', 'Collaborator Province', 'Collaborator 
                      City', 'Collaborator Educational Institution',
                     'Supervisor Name', 'Supervisor Contact Number', 'Supervisor Email', 
                     'Supervisor National ID',
                     'Supervisor Address', 'Supervisor Postal Code', 'Supervisor Academic Level', 
                     'Supervisor Field of Study',
                     'Supervisor Province', 'Supervisor City', 'First Stage Average Score', 
                     'Second Stage Average Score',
                     'Third Stage Average Score', 'Status'
                ];
    
                $style = [
                    'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER],
                ];
    
                $row = 1;
                // Apply header row
                foreach ($headerColumns as $column => $header) {
                    $activeSheet->setCellValue("{$column}{$row}", $header);
                    $activeSheet->getStyle("{$column}{$row}")->applyFromArray($style);
                }
    
                $row = 2;
    
                foreach ($festival as $item) {
                    $activeSheet->setCellValueByColumnAndRow(1, $row, $item->fsp_code);
                    $activeSheet->getStyleByColumnAndRow(1, $row)->applyFromArray($style);
    
                    $activeSheet->setCellValueByColumnAndRow(2, $row, $item->fsp_id);
                    $activeSheet->getStyleByColumnAndRow(2, $row)->applyFromArray($style);

                    ...
                    
                    $row++;
                }
                
                $filename = 'festival_report.xlsx';
                header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
                header("Content-Disposition: attachment;filename=\"$filename\"");
                header('Cache-Control: max-age=0');
    
                $writer = new Xlsx($spreadsheet);
                $writer->save('php://output');
                die();
            }
            return $festival;
        }

And this is the error I get:

Invalid cell coordinate 01

Screenshot:

So help me with this problem please... I'm really stuck at this point!

---

I still struggling with this error, I wrote the Spreadsheet.php class methods here:

$spreadsheet = new Spreadsheet();
$spreadsheet->setActiveSheetIndex(0)->setRightToLeft(true);

$activeSheet = $spreadsheet->getActiveSheet();
            
public function getActiveSheet()
    {
        return $this->getSheet($this->activeSheetIndex);
    }

public function getSheet($pIndex)
    {
        if (!isset($this->workSheetCollection[$pIndex])) {
            $numSheets = $this->getSheetCount();

            throw new Exception(
                "Your requested sheet index: {$pIndex} is out of bounds. The actual number of sheets is {$numSheets}."
            );
        }

        return $this->workSheetCollection[$pIndex];
    }
    
public function getSheetCount()
    {
        return count($this->workSheetCollection);
    }

Answer 1

The issue seems to be in your foreach loop, I used the \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex() method to convert the numeric column index to its corresponding Excel column letter, try this

<?php
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

public function getFestivalPlanRegister($id, $author = null) {
    if (request()->has('excel_export')) {
        $festival = $festival->where('festival_plan.fsp_condition_plan', '=', 'completed');
    }

    if (request()->has('excel_export')) {
        $spreadsheet = new Spreadsheet();
        $spreadsheet->setActiveSheetIndex(0)->setRightToLeft(true);
        $activeSheet = $spreadsheet->getActiveSheet();

        $style = [
            'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
        ];

        // Define the header row and style
        // ... your $headerColumns and $style declarations ...
    
        $row = 1;
        // Apply header row
        foreach ($headerColumns as $columnIndex => $header) {
            $columnLetter = Coordinate::stringFromColumnIndex($columnIndex + 1);
            $activeSheet->setCellValue("{$columnLetter}{$row}", $header);
            $activeSheet->getStyle("{$columnLetter}{$row}")->applyFromArray($style);
        }

        $row = 2;

        foreach ($festival as $item) {
            $columnLetter = Coordinate::stringFromColumnIndex(1);
            $activeSheet->setCellValue("{$columnLetter}{$row}", $item->fsp_code);
            $activeSheet->getStyle("{$columnLetter}{$row}")->applyFromArray($style);

            // ... 

            $row++;
        }

        $filename = 'festival_report.xlsx';
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header("Content-Disposition: attachment;filename=\"$filename\"");
        header('Cache-Control: max-age=0');

        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');
        exit;
    }

    return $festival;
}

Answer 2

setCellValue() expects the cell coordinates in the form 'A1'. That's not what you are doing here:

foreach ($headerColumns as $column => $header) {
    $activeSheet->setCellValue("{$column}{$row}", $header);
    $activeSheet->getStyle("{$column}{$row}")->applyFromArray($style);
}

$column is an integer, not a letter.

To fix this, one way is to use getCellByColumnAndRow():

foreach ($headerColumns as $column => $header) {
    $cell = $activeSheet->getCellByColumnAndRow($column + 1, $row);
    $cell->setValue($header);
    $cell->getStyle()->applyFromArray($style);
}

Alternatively you can call getCell() with an array:

foreach ($headerColumns as $column => $header) {
    $cell = $activeSheet->getCell([$column + 1, $row]);
    $cell->setValue($header);
    $cell->getStyle()->applyFromArray($style);
}