CODEWITHSUNDEEP
phpexcellaravellaravel-5phpexcel
Biacho
Biacho

Reputation: 63

laravel excel 3.1 import value not formula

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

Upvotes: 6

Views: 7292

Answers (2)

SLawpie
SLawpie

Reputation: 1

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
{
     [...]
}

Upvotes: 0

yollicious_
yollicious_

Reputation: 91

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

Upvotes: 9

Related Questions