How can I import calculated values?

[cosecantt]

Prerequisites

Versions

• PHP version: 7.2.9
• Laravel version: 5.5.40
• Package version: 3.1

Description

How can I import calculated values?

Imported excel file returns following values and the formula is not calculated for maximum_stock:

{
   "minimum_stock": 10,
   "maximum_stock": "=Z3*2",
   "stock": 5
}

Additional Information

I set config

'pre_calculate_formulas' => true,

but it seems that above config missing for import

namespace App\Imports;

use App\Product;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Maatwebsite\Excel\Concerns\WithBatchInserts;
use Maatwebsite\Excel\Concerns\WithCalculatedFormulas;

class ProductImport implements ToModel, WithHeadingRow, WithBatchInserts, WithCalculatedFormulas
{
    /**
    * @param array $row
    *
    * @return \Illuminate\Database\Eloquent\Model|null
    */
    public function model(array $row)
    {
        $row['user_id'] = auth()->user()->id;
        $row['code'] = $row['product_code'];

        return new Product($row);
    }

    public function headingRow(): int
    {
        return 2;
    }

    public function batchSize(): int
    {
        return 100;
    }
}

Or am I missing something in the code. Cannot find example on documentation. Thanks!

[GlennM]

Hi @cosecantt ,

Here we've listed the available concerns. Please try the Maatwebsite\Excel\Concerns\WithCalculatedFormulas concern.

It's true that this one doesn't have any documentation example yet, so feel free to make a PR for the documentation.

[cosecantt]

Thank you @GlennM . Please check the code above and I have tried with

Maatwebsite\Excel\Concerns\WithCalculatedFormulas

but does not return the calculated value. Above ImportProduct class implements WithCalculatedFormulas or am I missing something. Is that enough to implemement WithCalculatedFormulas like above?

[patrickbrouwers]

Maatwebsite\Excel\Concerns\WithCalculatedFormulas should be enough. You could debug and see if this line get's called https://github.com/Maatwebsite/Laravel-Excel/blob/3.1/src/Cell.php#L62

[patrickbrouwers]

This is the (working) tests we have for it : https://github.com/Maatwebsite/Laravel-Excel/blob/3.1/tests/Concerns/WithCalculatedFormulasTest.php If you find any issue with it, it'd be great if you could commit a failing tests. Makes it easier to figure things out.

[cosecantt]

Maatwebsite\Excel\Concerns\WithCalculatedFormulas should be enough. You could debug and see if this line get's called https://github.com/Maatwebsite/Laravel-Excel/blob/3.1/src/Cell.php#L62

@patrickbrouwers, I debugged and it does not call the line you mentioned. When it calls it works as expected. I think $calculateFormulas always returns false.

[GlennM]

@cosecantt

FYI: I got a calculated formula in my import (instead of the formula) by just adding WithCalculatedFormulas to my Import class.

I didn't set anything like 'pre_calculate_formulas' => true,

E.g. the code I used to test:

    public function model(array $row)
    {
        return new User([
            'name'     => $row['username'],
            'email'    => $row['email'],
            'password' => $row['total'], // <= This column inserts the result of a formula in the CSV to the database
        ]);
    }

[cosecantt]

@GlennM can you check the output of

$array = Excel::toArray(new ProductImport, 'product.xlsx');

[GlennM]

@GlennM can you check the output of

$array = Excel::toArray(new ProductImport, 'product.xlsx');

That one has the formula (so not the calculated value):

[
    [
        [
            "username" => "John"
            "email" => "john@fakemail.com"
            "total" => "=SUM(D2,E2)"
        ],
        [
            "username" => "Jane"
            "email" => "jane@fakemail.com"
            "total" => "=SUM(D3,E3)"
        ]
    ]
]

[cosecantt]

@GlennM, is that expected or a bug? I guess it shoud return calculated value once WithCalculatedFormulas concern is implemented.

[patrickbrouwers]

@cosecantt feel free to submit a PR with a failing test for it. And if you figure out what causes it to not work, feel free to PR that too :)

[patrickbrouwers]

Closing as PR was made by @vega231

[Plueki]

Hi there, not sure if im allowed to response after such a long time.
I also do not know what a PR means.
But i am still having this issue.

I have added the 'use Maatwebsite\Excel\Concerns\WithCalculatedFormulas;'
and the WithCalculatedFormulas.
Yet when i open Excel i still see =COUNT(D4:D400) instead of a calculated value.
Any ideas?

[patrickbrouwers]

There probably is an error while trying to get the calculated value, so it falls back to the formula. You could put a dd() in the Cell class to see what's happening

[Plueki]

There probably is an error while trying to get the calculated value, so it falls back to the formula. You could put a dd() in the Cell class to see what's happening

Hey thanks for your quick reply.
I made a mistake. i used TYPE_STRING instead of TYPE_FORMULA which solved my problem.
Bedankt!

[SamyEcheverria]

I have the same problem. I'm trying to import a spreadsheet with string, date and values ​​filled by formulas.
I tested WithCalculatedFormulas, but now I stopped importing strings and dates.

[Plueki]

I have the same problem. I'm trying to import a spreadsheet with string, date and values ​​filled by formulas.
I tested WithCalculatedFormulas, but now I stopped importing strings and dates.

$event->sheet->setCellValueExplicit(
'A1',
$ticket->name,
\PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING
);

This should do the trick..

[cali4nian]

Ran into the same issues until I saved my Excel file. Geez