One last thing to note on importing excel sheets is that often formulas do not behave as you intended them to behave. If you start receiving errors, take a look at the excel sheet itself and figure out if there are any formulas hidden inside.

https://blog.devgenius.io/laravel-excel-p4-importing-multiple-sheets-conditional-loading-d70db3ae1409

Let’s take a look at an example with one a formula that references data on the sheet that’s being imported.

Formula on Import Sheet

The excel sheet that we’ll be looking at has the following columns

• first_name
• last_name
• email
• password
• full_name

The first four columns contain static data, but the last column is generated by combing the first and second columns: =A2&” “&B2.

Let’s create an import and see what this looks like.

<?php

namespace App\Imports;

use App\Models\User;
use Illuminate\Support\Facades\Hash;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;

class SheetWithFormulaImport implements ToModel, WithHeadingRow
{
    /**
     * @param array $row
     *
     * @return \Illuminate\Database\Eloquent\Model|null
     */
    public function model(array $row)
    {
        return new User([
            'name'     => $row['full_name'],
            'email'    => $row['email'],
            'password' => Hash::make($row['password']),
        ]);
    }
}

Our User model expects a name, which we’ll use to pass our full_name formula field, instead of $row['first_name'] . " " . $row['last_name'].

We’ll also need to add a method in our UserController and create a route to be able to call this importer.

<?php

namespace App\Http\Controllers;

//...
use App\Imports\SheetWithFormulaImport;
use App\Models\User;
use Illuminate\Http\Request;
use Maatwebsite\Excel\Facades\Excel;

class UserController extends Controller
{
    //...

    public function import_sheet_with_formula() {
        Excel::import(
            new SheetWithFormulaImport,
            'mock_data/MOCK_DATA_4.xlsx'
        );

        return redirect('/')->with('success', 'Users Imported Successfully!');
    }
}

<?php

use Illuminate\Support\Facades\Route;
use App\Http\Controllers\UserController;
use Illuminate\Support\Facades\Artisan;

//...

Route::get(
    '/import-sheet-with-formula',
    [UserController::class, 'import_sheet_with_formula']
);

If you look at your users table, you’ll see something like this

{
    "id":1,
    "name":"=A2&\"\"&B2",
    "email":"pspinnace0@nsw.gov.au",
    "email_verified_at":null,
    "created_at":"2022-10-05T23:17:08.000000Z",
    "updated_at":"2022-10-05T23:17:08.000000Z"
}

The name contains the formula and not the actual name. So how do we fix it? With the WithCalculatedFormulas concern.

<?php

namespace App\Imports;

use App\Models\User;
use Illuminate\Support\Facades\Hash;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithCalculatedFormulas;
use Maatwebsite\Excel\Concerns\WithHeadingRow;

class SheetWithFormulaImport implements ToModel, WithHeadingRow, WithCalculatedFormulas
{
    /**
     * @param array $row
     *
     * @return \Illuminate\Database\Eloquent\Model|null
     */
    public function model(array $row)
    {
        return new User([
            'name'     => $row['full_name'],
            'email'    => $row['email'],
            'password' => Hash::make($row['password']),
        ]);
    }
}

Run the code again and you should get the results you were looking for.

{
    "id":1,
    "name":"Frank Jackson",
    "email":"pspinnace0@nsw.gov.au",
    "email_verified_at":null,
    "created_at":"2022-10-05T23:22:03.000000Z",
    "updated_at":"2022-10-05T23:22:03.000000Z"
}

What happens when you’re importing multiple sheets and the formula is coming from another sheet? Let’s see if we can make it work.

Formula on Another Sheet

For this example, we’ll have an excel document with two worksheets. They’re both going to have the same headers:

• first_name
• last_name
• email
• password

Our second sheet, user_data_2, will be a replica of user_data_1, but will add a +1 to all emails to make them unique. i.e. dino@gmail.com will now be dino+1@gmail.com. The formula that we’ll use in our Excel document in user_data_2 is =SUBSTITUTE(user_data_1!C3, “@”, “+1@”).

Why did I pick this formula? Because I didn’t want to create another table and this seemed like the quickest way to illustrate the import with our already defined users table. Moving on.

We’ll need to first create our Multi Sheet Importer that can import each of the sheets.

<?php

namespace App\Imports;

use Maatwebsite\Excel\Concerns\WithMultipleSheets;

class MultiSheetWithFormulasImporter implements WithMultipleSheets
{
    public function sheets(): array
    {
        return [
            'user_data_1' => new FirstSheetNoFormulaImporter,
            'user_data_2' => new SecondSheetWithFormulaImporter,
        ];
    }
}

Next, let’s see what the FirstSheetWithNoFormulaImporter looks like. Remember, this importer will import user_data_1, which doesn’t contain any formulas. However, other sheets reference it, so we’ll need to add a HasReferencesToOtherSheets concern to explicitly state to Laravel Excel that other sheets will reference it.

<?php

namespace App\Imports;

use App\Models\User;
use Illuminate\Support\Facades\Hash;
use Maatwebsite\Excel\Concerns\HasReferencesToOtherSheets;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;

class FirstSheetNoFormulaImporter implements ToModel, WithHeadingRow, HasReferencesToOtherSheets
{
    /**
     * @param array $row
     *
     * @return \Illuminate\Database\Eloquent\Model|null
     */
    public function model(array $row)
    {
        return new User([
            'name'     => $row['first_name'] . " " . $row['last_name'],
            'email'    => $row['email'],
            'password' => Hash::make($row['password']),
        ]);
    }
}

Our second importer, SecondSheetWithFormulaImporter, does have a formula in user_data_2 for the new email field. We just need to implement the WithCalculatedFormulas concern and Laravel Excel will know how to handle the rest.

<?php

namespace App\Imports;

use App\Models\User;
use Illuminate\Support\Facades\Hash;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithCalculatedFormulas;
use Maatwebsite\Excel\Concerns\WithHeadingRow;

class SecondSheetWithFormulaImporter implements ToModel, WithHeadingRow, WithCalculatedFormulas
{
    /**
     * @param array $row
     *
     * @return \Illuminate\Database\Eloquent\Model|null
     */
    public function model(array $row)
    {
        return new User([
            'name'     => $row['first_name'] . " " . $row['last_name'],
            'email'    => $row['email'],
            'password' => Hash::make($row['password']),
        ]);
    }
}

All that’s left is to create our method in our UserController and our route and we’ll be ready for testing.

<?php

namespace App\Http\Controllers;

//...
use App\Imports\MultiSheetWithFormulasImporter;
use App\Models\User;
use Illuminate\Http\Request;
use App\Imports\UsersImport;
use Maatwebsite\Excel\Facades\Excel;

class UserController extends Controller
{
    //...

    public function import_multiple_sheets_with_formulas() {
        Excel::import(
            new MultiSheetWithFormulasImporter,
            'mock_data/MOCK_DATA_5.xlsx'
        );

        return redirect('/')->with('success', 'Users Imported Successfully!');
    }
}

<?php

use Illuminate\Support\Facades\Route;
use App\Http\Controllers\UserController;
use Illuminate\Support\Facades\Artisan;

//...

Route::get(
    '/import-multiple-sheets-with-formulas',
    [UserController::class, 'import_multiple_sheets_with_formulas']
);

And the result we get is:

{
    "id":39,
    "name":"Frank Jackson",
    "email":"pspinnace0@nsw.gov.au",
    "email_verified_at":null,
    "created_at":"2022-10-05T23:52:27.000000Z",
    "updated_at":"2022-10-05T23:52:27.000000Z"
},
...,
{
    "id":58,
    "name":"Frank Jackson",
    "email":"pspinnace0+1@nsw.gov.au",
    "email_verified_at":null,
    "created_at":"2022-10-05T23:52:28.000000Z",
    "updated_at":"2022-10-05T23:52:28.000000Z"
},
...

The result that we were expecting! What if we forgot to implement the HasReferenceToOtherSheets concern? Well, it breaks.

Luckily, we know what we have to implement in order to avoid this problem.

I think I’m officially done talking about the various different scenarios in multi-sheet workbooks. Let’s move on to some other exciting features in Laravel Excel in the next article.