CODEWITHSUNDEEP
phpmysqlexcellaravellaravel-excel
Dani Fadli
Dani Fadli

Reputation: 383

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!

Upvotes: 5

Views: 13461

Answers (3)

youness Web
youness Web

Reputation: 11

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);
    }
}

Upvotes: 1

Dani Fadli
Dani Fadli

Reputation: 383

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.

Upvotes: 0

Jorge Ir&#250;n
Jorge Ir&#250;n

Reputation: 1753

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.

Upvotes: 30

Related Questions