Laravel-Excel — P2: Importing Multiple Sheets Basics

Master Multi-Sheet Imports with Laravel Excel

Excel sheets will automatically be imported with Laravel-Excel. Sheets 1 through N will be handled the exact same way as sheet 0. We can specify how multiple sheets are handled, and we will in this

article

Importing Multiple Sheets with the Same Format

We’ll use our user mock data from the previous article and create a couple of excel sheets to store the user data. We now have user_data_1 and user_data_2 sheets. Each sheet stores the exact same type of data, both with headers (first_name, last_name, email, password) and 19 rows of user data. The MOCK_DATA.xlsx file is placed in our public directory.

We’ll create our importer with our laravel-excel command:

php artisan make:import MultiSheetSingleFormatImport --model=User

<?php

namespace App\Imports;

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

class MultiSheetSingleFormatImport implements ToModel, WithHeadingRow
{
/**
    * @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']),
        ]);
    }
}

We now need our Controller. We made the controller in our previous article, so we’ll continue to use our UserController and add our method so that it can call our MultiSheetSingleFormatImport importer.

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Imports\UsersImport;
use Maatwebsite\Excel\Facades\Excel;

class UserController extends Controller
{
    //...

    public function import_multiple_single_format() {
        Excel::import(
            new MultiSheetSingleFormatImport, 'MOCK_DATA.xlsx'
        );

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

All we need now is our route and we’ll be good to start the test.

<?php

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

//...

Route::get(
'/import-multiple-sheets-single-format',
    [UserController::class, 'import_multiple_single_format']
);

Go to your browser, type in the route and let’s take a loo

mysql> select * from users;
+----+------------------------+---------------------------------+-------------------+--------------------------------------------------------------+----------------+---------------------+---------------------+
| id | name                   | email                           | email_verified_at | password                                                     | remember_token | created_at          | updated_at          |
+----+------------------------+---------------------------------+-------------------+--------------------------------------------------------------+----------------+---------------------+---------------------+
|  1 | Patrick Spinnace       | pspinnace0@nsw.gov.au           | NULL              | $2y$10$FhrBy9t3YyAU1N3ejDYgluhDGrGdUOwtftarhBkoiLd9H8oSEFEFm | NULL           | 2022-10-02 12:13:01 | 2022-10-02 12:13:01 |
|  2 | Verla Petersen         | vpetersen1@edublogs.org         | NULL              | $2y$10$QD1zt7ZpMJCYkEDUN0e3Cu/MA7NEoWDr7pGzylvO69jLtWCZIflMC | NULL           | 2022-10-02 12:13:01 | 2022-10-02 12:13:01 |
|  3 | Alicea Hobbema         | ahobbema2@unicef.org            | NULL              | $2y$10$gcpiaqZ6tdj49QXNfaZsxuPnE8egwONNv2iJ4CgnZSzpIob.9mT4O | NULL           | 2022-10-02 12:13:01 | 2022-10-02 12:13:01 |
|  4 | Roma Mapstone          | rmapstone3@sfgate.com           | NULL              | $2y$10$gp2E.KFqeZu9AIYB34R8wequuH8WheeDOmWmQjW2grM8N1K0vGHYW | NULL           | 2022-10-02 12:13:01 | 2022-10-02 12:13:01 |
|  5 | Karol Stucke           | kstucke4@vk.com                 | NULL              | $2y$10$D5XaM3qg0XcJMibZHZ.baOg.DJkpXDw/y6TCMh.6bPACk7dxHhkSe | NULL           | 2022-10-02 12:13:01 | 2022-10-02 12:13:01 |
|  6 | Alick Isles            | aisles5@paypal.com              | NULL              | $2y$10$XOU0T1fKY1bRfmOTxO.eT.gGTVarSeu5.Ls/tGqhKloYIQBDGzRX. | NULL           | 2022-10-02 12:13:01 | 2022-10-02 12:13:01 |
|  7 | Brandtr Kenwell        | bkenwell6@xinhuanet.com         | NULL              | $2y$10$JVknCCTH4ARR.nsHKhn/q.IaX3AjiUgHLy99JQ158esKY9QIiCFtm | NULL           | 2022-10-02 12:13:01 | 2022-10-02 12:13:01 |
|  8 | Sabine Ewer            | sewer7@sakura.ne.jp             | NULL              | $2y$10$KmM8ngj25U6YRt6eHgVGr.toDfuH/zz.TXBzUEKV9XWxUrdwQenH. | NULL           | 2022-10-02 12:13:01 | 2022-10-02 12:13:01 |
|  9 | Maude Harmeston        | mharmeston8@ezinearticles.com   | NULL              | $2y$10$kzkmRV/kY10vCtDQu1q1a.GkEUNtx17dUAx8QnQFysKBMFzs3Kl36 | NULL           | 2022-10-02 12:13:01 | 2022-10-02 12:13:01 |
| 10 | Hillel Gogin           | hgogin9@bravesites.com          | NULL              | $2y$10$Ki1/PyYDfsQ5R9VggwAR1uGu4RSL09VQHIWWBR3YW3oFA0VHEIgri | NULL           | 2022-10-02 12:13:01 | 2022-10-02 12:13:01 |
| 11 | Mohandis Drydale       | mdrydalea@patch.com             | NULL              | $2y$10$NHuSBQmT1T38YlsR5M4ISeK6IWa2pQGKeXqXf5HTm65pvu17KgNzG | NULL           | 2022-10-02 12:13:01 | 2022-10-02 12:13:01 |
| 12 | Lil Caff               | lcaffb@squarespace.com          | NULL              | $2y$10$QBWSk.Jacf14CHqenHXsROnmz7K0/2NTmZKPrH7fwX.dIYPQ4rKGu | NULL           | 2022-10-02 12:13:02 | 2022-10-02 12:13:02 |
| 13 | Robinette Chamberlayne | rchamberlaynec@amazon.de        | NULL              | $2y$10$soF3Ta2xV7aP0yu1Zk.cn.kFTDJtuFGCPCoO322BdFoGD4eLgZGjy | NULL           | 2022-10-02 12:13:02 | 2022-10-02 12:13:02 |
| 14 | Stinky Very            | sveryd@google.ru                | NULL              | $2y$10$5EG1plVF2qHSLYCaH7KHxO/8rWw5P2Qw0JDsMkJTA65H0WTyUGPD6 | NULL           | 2022-10-02 12:13:02 | 2022-10-02 12:13:02 |
| 15 | Tressa Dubique         | tdubiquee@istockphoto.com       | NULL              | $2y$10$UpdQd/Wti5Ztzm4IOb8zpuKjCxKoNqtOh6dbWdMV9JlxsCJtBmqmm | NULL           | 2022-10-02 12:13:02 | 2022-10-02 12:13:02 |
| 16 | Bernie Dubarry         | bdubarryf@gravatar.com          | NULL              | $2y$10$YHfLtJbDPlKZKx9WWaet4u4gVuodd2FDpzEpqhI5jiDimj4rLRTIW | NULL           | 2022-10-02 12:13:02 | 2022-10-02 12:13:02 |
| 17 | Orville Dauber         | odauberg@squarespace.com        | NULL              | $2y$10$cIyPRCy3to0PfnVy9emjL.zDSpSuT/1YO2yzCeUJWWy6PoEPQBfjO | NULL           | 2022-10-02 12:13:02 | 2022-10-02 12:13:02 |
| 18 | Oran Freckelton        | ofreckeltonh@furl.net           | NULL              | $2y$10$QsFogzKmpX3mXqAD30sAK.fWT6fZ9LdJtWFLA0K9aXst/G7zYv7um | NULL           | 2022-10-02 12:13:02 | 2022-10-02 12:13:02 |
| 19 | Kelsy Hilliam          | khilliami@icio.us               | NULL              | $2y$10$UGDET/OYXOLkA8osxPPl8eF63SPzheY9hZZwpnujTPnJKYzMYj0aG | NULL           | 2022-10-02 12:13:02 | 2022-10-02 12:13:02 |
| 20 | Theresita Penelli      | tpenellij@amazon.com            | NULL              | $2y$10$vfq7nC4YGZZgrMa1xrfon.M2uibTKmoG6k08h25ZA0NBzz58QYuL2 | NULL           | 2022-10-02 12:13:02 | 2022-10-02 12:13:02 |
| 21 | Reeva Vivien           | rvivienk@nih.gov                | NULL              | $2y$10$Wa4mrYnlTGT58BjLyoWciuQ.S1L6QMMxghgUCs7riLyd1650ANMMa | NULL           | 2022-10-02 12:13:02 | 2022-10-02 12:13:02 |
| 22 | Roxine Lathe           | rlathel@cbsnews.com             | NULL              | $2y$10$nOX6AzoEvp4INss2gXGzd.WfKB.OcubI.a5wAxt7xqo8W0Te5CG12 | NULL           | 2022-10-02 12:13:02 | 2022-10-02 12:13:02 |
| 23 | Ernst Spiby            | espibym@google.com              | NULL              | $2y$10$LHbpL0ZYYkqrK08YgysOVupdsOSRNhFa1p5kjXoED6CefHGfupa.e | NULL           | 2022-10-02 12:13:02 | 2022-10-02 12:13:02 |
| 24 | Bowie Willerton        | bwillertonn@acquirethisname.com | NULL              | $2y$10$NrKd9DXZa2HwkCS9A4JwpeZYvukIbsP8uGmshH7HF7/Q1Rt5nQNW. | NULL           | 2022-10-02 12:13:02 | 2022-10-02 12:13:02 |
| 25 | Tasha Hapke            | thapkeo@booking.com             | NULL              | $2y$10$iCkjlY6pOlA3PLyIwA2vxudPV6HV7ev8v/VgyEv.qITN/eT9lkXJS | NULL           | 2022-10-02 12:13:02 | 2022-10-02 12:13:02 |
| 26 | Blair Filchagin        | bfilchaginp@alexa.com           | NULL              | $2y$10$GKD3vUpl1cS8sLzl9CmRxeS3TzzGrvhCZvnLgroVclGOAekUO6Bp. | NULL           | 2022-10-02 12:13:02 | 2022-10-02 12:13:02 |
| 27 | Roda Costigan          | rcostiganq@360.cn               | NULL              | $2y$10$rYzMYxhG6cdO87LNkck8IulfY3PGn8Isvw3QSrZClLxq1UEmHX.Qq | NULL           | 2022-10-02 12:13:02 | 2022-10-02 12:13:02 |
| 28 | Twila Mc Combe         | tmccomber@freewebs.com          | NULL              | $2y$10$8agoainCcmEUVkeReuMPEeAP8vjcqa6VkRerNSOPOljExnBDT4AmC | NULL           | 2022-10-02 12:13:02 | 2022-10-02 12:13:02 |
| 29 | Maressa Lancaster      | mlancasters@nifty.com           | NULL              | $2y$10$lORYemSZqhtCnXLafSiHV.GGTrZfRKALAZHnnVG/6SF1tpFB7GLfe | NULL           | 2022-10-02 12:13:02 | 2022-10-02 12:13:02 |
| 30 | Domini Pietzke         | dpietzket@cargocollective.com   | NULL              | $2y$10$1KxGb5tRfKpmBqgSrm1dj.tC.1n7u7rzMRmVOw1a/hrxwjIkxHN4. | NULL           | 2022-10-02 12:13:02 | 2022-10-02 12:13:02 |
| 31 | Arlana Weatherhead     | aweatherheadu@nsw.gov.au        | NULL              | $2y$10$N3ecZcExthMn25hXy.1Yh.RQCaN.NaOT70h3jQn6NIpAKUMcln/V2 | NULL           | 2022-10-02 12:13:02 | 2022-10-02 12:13:02 |
| 32 | Christean Grastye      | cgrastyev@ezinearticles.com     | NULL              | $2y$10$XPQFlMf5PQ.ejpC1gXg1M.y7fus7X6e6wHcmIHPS6xhNpyN.tNdMS | NULL           | 2022-10-02 12:13:02 | 2022-10-02 12:13:02 |
| 33 | Siegfried Chawkley     | schawkleyw@wsj.com              | NULL              | $2y$10$ZQjYQTEm1WEuij68QqhnZeUsKqkL1o.vZk4YoxmBjItoAZHM4Axma | NULL           | 2022-10-02 12:13:02 | 2022-10-02 12:13:02 |
| 34 | Loralyn Fibbens        | lfibbensx@vinaora.com           | NULL              | $2y$10$ptR/JxXRAlF0rr7kJ78KAOLlR95rG0E3s0rqXqMaxMMOu8d3dWfO2 | NULL           | 2022-10-02 12:13:02 | 2022-10-02 12:13:02 |
| 35 | Uriel Janusik          | ujanusiky@delicious.com         | NULL              | $2y$10$h1snThHuCHionop8nm6RC.b6/.AZqzXG3GUFXKXc5saSgsywV/U.. | NULL           | 2022-10-02 12:13:03 | 2022-10-02 12:13:03 |
| 36 | Kennie Skaife          | kskaifez@webnode.com            | NULL              | $2y$10$XsWQl41e0IzHavsMs8yH5ejoY.bFgeHlYHNa.ATjSf4S/VTcpFJKG | NULL           | 2022-10-02 12:13:03 | 2022-10-02 12:13:03 |
| 37 | Dill Oakshott          | doakshott10@google.co.jp        | NULL              | $2y$10$cbtilNygQyymXcWFSXuduOiwelmqQHRBvAJ0Il.SX6aQkkGDH9QQy | NULL           | 2022-10-02 12:13:03 | 2022-10-02 12:13:03 |
| 38 | Jens Dibdin            | jdibdin11@stanford.edu          | NULL              | $2y$10$2IpqblagpnCHhaWcvrrLqeaDUbz4gJQbSjp7hLh6Z2oY8zMeDnxey | NULL           | 2022-10-02 12:13:03 | 2022-10-02 12:13:03 |
+----+------------------------+---------------------------------+-------------------+--------------------------------------------------------------+----------------+---------------------+---------------------+
38 rows in set (0.00 sec)

mysql>

We get the result that we were expecting. Laravel-Excel reads the data from the first sheet and imports the 19 rows. It skips the first row since we added the WithHeadingRow concern. Once it completes the import, it then goes to the next sheet. It again assumes that the first row is the header and reads the 19 rows from sheet 2.

Importing Multiple Sheets with Different Formats

What if each sheet had different formats? For example, let’s say that sheet 1 had the following header:

first_name | last_name | email | password

Sheet 2 has a slightly different format

email | password | first_name | last_name | phone_number | dob

Will our importer still work? The answer is…yes! Our importer is using the WithHeadingRow concern, so it knows which column to select the data from.

What if sheet 2 had different names in the header?

email_address | pass | name_first | name_last

Try running the import and you’ll see that you get an error.

We’ll need to specify how to handle each sheet since the heading row is named differently. We’ll need to create three different imports. The first import just selects between the two sheets and calls the appropriate importer, and the other two importers will process each sheet. Let’s create those three right now.

php artisan make:import MultiSheetSelector
php artisan make:import FirstSheetImporter --model=User
php artisan make:import SecondSheetImporter --model=User

Let’s configure our FirstSheetImporter and SecondSheetImporter first.

<?php

namespace App\Imports;

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

class FirstSheetImporter implements ToModel, WithHeadingRow
{
/**
    * @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']),
          ]);
    }
}

<?php

namespace App\Imports;

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

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

Notice that we specified different keys for our $row to match the header for each sheet.

Next, we’ll need to implement our multi-sheet selector. Laravel-Excel requires that the WithMultipleSheets concern is implemented along with the sheets() method.

<?php

namespace App\Imports;

use Maatwebsite\Excel\Concerns\WithMultipleSheets;

class MultiSheetSelector implements WithMultipleSheets
{
    public function sheets(): array
    {
        return [
            new FirstSheetImporter(),
            new SecondSheetImporter(),
        ];
    }
}

The sheets() method returns an array of importers. The order is important. We know that the FirstSheetImporter is linked to the first Excel sheet and the SecondSheetImporter is linked to the second Excel sheet.

We can also remove the collection() method and ToCollection concern since we’re not going to be processing anything with this class. It’s just there to select between the two imports.

We just need to add a method to our UserController and create a route that calls that method and we’ll be ready to test.

<?php

namespace App\Http\Controllers;

use App\Imports\MultiSheetSelector;
use App\Imports\MultiSheetSingleFormatImport;
use Illuminate\Http\Request;
use App\Imports\UsersImport;
use Maatwebsite\Excel\Facades\Excel;

class UserController extends Controller
{
//...

    public function import_multiple_different_formats() 
    {
        Excel::import(
            new MultiSheetSelector, 'MOCK_DATA_2.xlsx'
        );

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

<?php

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

//...

Route::get(
'/import-multiple-sheets-different-formats',
    [UserController::class, 'import_multiple_different_formats']
);

Time for testing…and success. We were able to import both sheets with different headers. You can imagine that if sheet 2 needed some additional cleanup, this could be done in the SecondSheetImporter.

Explicitly Stating Which Sheets to Import

There are all sort of possibilities and one of those is that there are a few sheets that you want to import, but others that you don’t want to import. Let’s pretend that we have 5 sheets inside of our Excel document.

user_data_1
user_data_1_salary
user_data_2
user_data_2_salary
salary_totals

We only want to extract the data out of the user_data_1 and user_data_2 sheets. However, if we use our existing importer, it will attempt to import data from user_data_1 and user_data_1_salary.

The only modification that we’ll need to specify is which sheets to use. This can be done with the sheet number or the sheet name. The numbering system starts at 0.

<?php

namespace App\Imports;

use Maatwebsite\Excel\Concerns\WithMultipleSheets;

class MultiSheetSpecificSelectorByIndex implements WithMultipleSheets
{

    public function sheets(): array
    {
        return [
            0 => new FirstSheetImporter,
            2 => new SecondSheetImporter,
        ];
    }
}

We can also use the sheet name if we wanted to by replacing the index with the sheet name.

<?php

namespace App\Imports;

use Maatwebsite\Excel\Concerns\WithMultipleSheets;

class MultiSheetSpecificSelectorByName implements WithMultipleSheets
{
    public function sheets(): array
    {
        return [
            'user_data_1' => new FirstSheetImporter,
            'user_data_2' => new SecondSheetImporter,
        ];
    }
}

https://github.com/dinocajic/package-laravel-excel

Laravel Series

Continue your Laravel Learning.

Effortless Data Handling with Laravel Excel

Laravel Excel – P1: Setup And Basic Import

Kickstart your journey with Laravel Excel! This guide walks you through the setup process and demonstrates how to perform a basic data import.

Master Multi-Sheet Imports with Laravel Excel

Laravel-Excel — P2: Importing Multiple Sheets Basics

Discover how to efficiently import multiple sheets using Laravel Excel. Manage complex data with ease and streamline your workflows.

Chief Digital Officer & Author | Bridging IT, Marketing & Business

Master Multi-Sheet Imports with Laravel Excel

Importing Multiple Sheets with the Same Format

Importing Multiple Sheets with Different Formats

Explicitly Stating Which Sheets to Import

Laravel Series

Leave a Reply Cancel Reply

Dino Cajic

Chief Digital Officer & Author | Bridging IT, Marketing & Business