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

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.

enter image description here

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!

phpmysqlexcellaravellaravel-excel


Solution 1:[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.

Solution 2:[2]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Jorge Irún
Solution 2 Dani Fadli

Related Questions