Skipping Unknown Sheets

When you receive an Excel workbook with multiple sheets, you may devise for import functionality for each sheet, but what happens if you need to know when one of those sheets is missing? Do you want the entire import to crash or do you want to handle it more elegantly and just get a notification that the sheet that you’re expecting is not there?

Laravel-Excel has the SkipsUnknownSheets concern that allows you to implement the onUnknownSheet method. The $sheetName is passed to the method so that you can log it.

For this example, our workbook is going to contain the following sheets.

• user_data_1
• user_data_2

Our importer is going to recognize those and be able to import them.

<?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,
        ];
    }
}

To implement the logging functionality for sheets that are defined, but missing, we need to implement the SkipsUnknownSheets concern.

<?php

namespace App\Imports;

use Illuminate\Support\Facades\Log;
use Maatwebsite\Excel\Concerns\SkipsUnknownSheets;
use Maatwebsite\Excel\Concerns\WithMultipleSheets;

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

    public function onUnknownSheet($sheetName)
    {
        Log::error("Sheet " . $sheetName . " was skipped from import.");
    }
}

In this case, we have a user_data_3 sheet defined, but in our workbook, we don’t have it listed. Our onUnknownSheet method will recognize and log that error. Let’s create our route and add a method to our UserController class and then run that import and check the log file in app/storage/logs/laravel.log.

<?php

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

//...

Route::get(
    '/import-multiple-sheets-skip-unknown',
    [UserController::class, 'import_multiple_sheet_skip_unknown']
);

<?php

namespace App\Http\Controllers;

//...
use App\Imports\MultiSheetSkipUnknownSheet;
use Illuminate\Http\Request;
use Maatwebsite\Excel\Facades\Excel;

class UserController extends Controller
{
    // ...

    public function import_multiple_sheet_skip_unknown() {
        Excel::import(
            new MultiSheetSkipUnknownSheet, 'mock_data/MOCK_DATA_3.xlsx'
        );

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

After we call the route, we get the following error logged, which is what we expected since our Excel workbook does not contain the user_data_3 sheet.

[2022-10-03 20:08:21] local.ERROR: Sheet user_data_3 was skipped from import.

Skipping Specific Sheets and Failing Others

What if some sheets are more important than others? In other words, you want to stop code execution when certain sheets fail, and silently handle other less important sheets. Laravel-Excel has you covered again.

In the previous example, we implemented the SkipsUnknownSheets concern on the Multi-Sheet Selector class, but this time we’ll implement it on a specific Import.

We’ll create a new Multi-Sheet Selector. This time, it will not have the SkipsUnknownSheets concern.

<?php

namespace App\Imports;

use Maatwebsite\Excel\Concerns\WithMultipleSheets;

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

}

We’ll also need to create our ThirdSheetImporter class. This importer will have the SkipsUnknownSheets concern. The FirstSheetImporter and the SecondSheetImporter will not.

<?php

namespace App\Imports;

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

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

    public function onUnknownSheet($sheetName)
    {
        Log::error("Sheet " . $sheetName . " was skipped from import.");
    }
}

If we run this code now, and user_data_1 or user_data_2 sheets are missing, the code will break. If user_data_3 is missing, Laravel-Excel will handle it with the onUnknownSheet method and Log it to the file. Time to create a new route, method in our UserController, and test.

<?php

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

//...

Route::get(
    '/import-multiple-sheets-skip-unknown-sometimes',
    [UserController::class, 'import_multiple_sheet_skip_unknown_sometimes']
);

<?php

namespace App\Http\Controllers;

//...
use App\Imports\MultiSheetSkipUnknownSheetTwo;
use Illuminate\Http\Request;
use Maatwebsite\Excel\Facades\Excel;

class UserController extends Controller
{
    //...

    public function import_multiple_sheet_skip_unknown_sometimes() {
        Excel::import(
            new MultiSheetSkipUnknownSheetTwo, 'mock_data/MOCK_DATA_3.xlsx'
        );

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

We’ll get the same error message if we omit user_data_3.

[2022-10-03 20:44:31] local.ERROR: Sheet user_data_3 was skipped from import.

If user_data_1 is missing, we get the out of bounds exception thrown.