[Bug]: Formula is not calculated even after WithCalculatedFormulas, HasReferencesToOtherSheets

[thenibirahmed]

Is the bug applicable and reproducable to the latest version of the package and hasn't it been reported before?

• Yes, it's still reproducable

What version of Laravel Excel are you using?

3.1

What version of Laravel are you using?

11

What version of PHP are you using?

8.3

Describe your issue

In one file when I'm trying to read data by

class ProductImport implements WithMultipleSheets, WithCalculatedFormulas, HasReferencesToOtherSheets
{
    public Collection $excelData;
  
    protected $filePath;
 
    public function __construct($filePath)
    {
        $this->filePath = $filePath;
        $this->excelData = collect();
    }

  
    public function sheets(): array
    {
        $reader = new XlsxReader();
        $spreadsheet = $reader->load($this->filePath);
     }
}

It's getting with calculated formula

But when directly

class ProductImportToStaging implements ShouldQueue, ToModel, WithBatchInserts, WithChunkReading, WithStartRow, SkipsEmptyRows, WithCalculatedFormulas, HasReferencesToOtherSheets
{
    public function model(array $row)
    {
         ray($row)
    }
}

The formulas are not calculated, returned empty strings.

Formula example:=IF(Introduction!$D$41=""'"'',(1-Introduction!SD$41)*Prices!D9)

How can the issue be reproduced?

Just have a complex formula as I've given in examplethe

What should be the expected behaviour?

The value should be calculated

[thomasgalue]

Hi,

WithCalculatedFormulas concern is not working yet (it's a empty file in the /src/concerns/ directory), i had this problem a few days ago.

This is how I've fixed my problem:

public function onRow(Row $row)
    {
        $data   = $row->toArray(null, true);

Adding null and true as parameters to toArray()

About it:

The toArray() method of the Row class in Laravel Excel (which uses PhpSpreadsheet) converts a row from the spreadsheet into an array. This method can accept two optional parameters:

1. null: This parameter is used to specify a custom header mapping. If null is passed, the default headers from the spreadsheet will be used.
2. true: This parameter indicates whether the formulas in the cells should be evaluated and the calculated values should be returned instead of the formulas themselves.

Maybe because you're using ToModel, you need to adapt it somehow.

Hope this helps!