How to get calculated value from cell formula in Maatwebsite Laravel Excel?

Question

In excel, I have 3 columns named Total, Payment Released, and Payment Return. To calculate Payment Return, I added a formula inside it which is =U2-G2.

Note: U2 is Payment Released and G2 is Total.

I have read the documentation of Maatwebsite Laravel Excel for importing from excel to the database, and the data inserted successfully. But when I check the database, the payment_return column is all filled by zero 0. When I do dd($row['Payment Return'], it shows not the value it contains in the excel, but the formula itself.

How do I solve this, please?

P.S: Have read this too, but I have no idea where the import.php file is, and where should I write the function?

Thanks in advance!

Musketry

hey man, did you get this resolved? having the same problem – Castrato

Check my answer, thanks. Hope your code run well 👍🏻 – Musketry

Answer 1

documentation is terrible but found the fix: add

use Maatwebsite\Excel\Concerns\WithCalculatedFormulas;

and

class MyCustomImport implements WithCalculatedFormulas

in ths Imports/MycustomImports.php file and formulas are now calculated when importing so we get the actual value.

Azygous

To add to this, if you are using toArray to process the data, you need to specify the calculateFormulas: true parameter, else dd will still show the formula. $row->toArray(null, true); The source of toArray: public function toArray($nullValue = null, $calculateFormulas = false, $formatData = true, ?string $endColumn = null) – Hereinto

#75613420 – Neologism

Answer 2

If you using the version 3.1

you can pass the second param to "true"

<?php

namespace App\Imports;

use Maatwebsite\Excel\Row;
use Maatwebsite\Excel\Concerns\OnEachRow;

class ModelImport implements OnEachRow
{

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

Environ

Answer 3

I haven't found the right answer but I was in production, so I take an alternative way. I inserted the values from the excel in my PHP file and manually calculated it. It looks like

$valOne = $row['cell_U2'];

$valTwo = $row['cell_G2'];

$result = $valOne-$valTwo;

The variable $result is what I inserted into the database. By doing this, you can use formulas in excel instead, but It will not be considered in the system.

Musketry