laravel excel 3.1 import value not formula

Question

Ok, so i trying to import xlsx file to my Database using Laravel 5.7 and Laravel Excel 3.1. Importing works but unfortunately, I have a file from another source and there are formulas in cells. My problem is, that I imported this file, and it works but instead of values I see formulas themselves:

"=_xlfn.IFNA(VLOOKUP(F13,Data!C26:E92,3,0),"")"

and should be:

Country

I have to see values, not formulas. I've tried to change the calculated option to False or True, but nothing changed.

Could somebody help me with this?

UsersImport.php file:

namespace App\Imports;

use App\Form;
use Maatwebsite\Excel\Concerns\ToArray;
use Maatwebsite\Excel\Concerns\Importable;
use Maatwebsite\Excel\Concerns\WithMappedCells;

class UsersImport implements WithMappedCells, 
                                                        ToArray
{
    /**
    * @param array $row
    *
    * @return \Illuminate\Database\Eloquent\Model|null
    */

    use Importable;
    
    public function mapping(): array
    {
        return [
            'campaign_name' => 'F11',
            'country' => 'F12',
            'language' => 'F15',
            'region' => 'F17',
            'ms_region' => 'F19',
            'retailer_name' => 'F21',
            'start_date' => 'F23',
            'end_date' => 'F25',
            'quarter' => 'F27',
            'campaign_market' => 'F29',
            'campaign_execution_type' => 'F31',
            'reimbursement' => 'F33',
        ];
    }

    public function array(array $row)
    {
        return [ 
            'campaign_name' => $row['campaign_name'],
            'country' => $row['country'],
            'language' => $row['language'],
            'region' => $row['region'],
            'ms_region' => $row['ms_region'],
            'retailer_name' => $row['retailer_name'],
            'start_date' => $row['start_date'],
            'end_date' => $row['end_date'],
            'quarter' => $row['quarter'],
            'campaign_market' => $row['campaign_market'],
            'campaign_execution_type' => $row['campaign_execution_type'],
            'reimbursement' => $row['reimbursement'],
        ];
    }
}

MyController file:

public function import(Request $request)
{
    $data = [];
    $this->validate($request, array(
        'file'      => 'required'
    ));

    if($request->hasFile('file')){
        $extension = File::extension($request->file->getClientOriginalName());
        if ($extension == "xlsx" || $extension == "xls" || $extension == "csv" || $extension == "xlsm") {
            $name = $request->file->getClientOriginalName();

            $data = (new UsersImport)->toArray($name);
            dd($data);
            
            return redirect('/');
            } 
        }else {
            Session::flash('error', 'File is a '.$extension.' file.!! Please upload a valid xls/csv file..!!');
            return back();
        }
    }
}

yollicious_ · Accepted Answer · 2018-11-21 07:27:09Z

9

try use Maatwebsite\Excel\Concerns\WithCalculatedFormulas; on UsersImport.php

answered Nov 21, 2018 at 7:27

yollicious_

914 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mash tan Over a year ago

No offence, but this is the perfect example of a non-answer, made all the more hilarious by the fact that the laravel excel documentation also has absolutely no instruction on HOW to use it. So for anyone stumbling upon this and wondering what "use" means: add: "use Maatwebsite\Excel\Concerns\WithCalculatedFormulas;" in your Data Model. Then implement it with the class, such as: "class MyCustomImport implements WithCalculatedFormulas" The model will then work by themselves.

SLawpie · Accepted Answer · 2023-12-03 11:58:32Z

0

For everyone confused.

You need to use WithCalculatedFormulas in use sections and implement it in class:

MyImportClass.php

namespace App\Imports;

[...]
use Maatwebsite\Excel\Concerns\WithCalculatedFormulas;

class MyImportClass implements WithCalculatedFormulas
{
     [...]
}

answered Dec 3, 2023 at 11:58

SLawpie

11 bronze badge

Collectives™ on Stack Overflow

laravel excel 3.1 import value not formula

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related