Laravel excel dynamic sheet handling

Question

I have Excel with multiple sheets having dynamic sheet names every time. I am trying to import using WithMultipleSheets interface and conditionalSheets method. I defined 3 classes for sheets according to their name.

Sheetname	Class Name
topsheet	TopSheetImport
rccsheet_dyn_name	RCCSheetImport
finishing_dyn_name	FinishingImport

dyn_name will be dynamic name. How can I manage this in Laravel Excel? and also i want to fetch sheet names present in excel file.

Answer 1

Assuming you are using the Laravel Excel package, you could leverage the WithEvents method to check the start of some of your sheet names and map the relevant import class. For example, something like the following might work:

<?php

namespace App\Imports;

use Maatwebsite\Excel\Concerns\WithMultipleSheets;
use Maatwebsite\Excel\Imports\HeadingRowFormatter;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Events\BeforeSheet;

class TopSheetImport implements WithMultipleSheets, WithEvents
{
    private array $dyanamicSheets;

    public function sheets(): array
    {
        return [
            // Any standard sheets can be mapped here
            'topsheet' => new TopSheetImport(),
            // Dynamic sheet imports here
            ...$this->dyanamicSheets,
        ];
    }

    public function registerEvents(): array
    {
        return [
            BeforeSheet::class => function(BeforeSheet $event) {

                // Extract the sheet name
                $sheetName = $event->getSheet()->getTitle();

                // RCC Sheet Import
                if(Str::startsWith($sheetName, 'rccsheet')) {
                    $this->dyanamicSheets[$sheetName] = new RCCSheetImport();
                // Finishing Sheet Import
                } elseif(Str::startsWith($sheetName, 'finishing')) {
                    $this->dyanamicSheets[$sheetName] = new FinishingImport();
                }
            } 
        ];
    }
}

Having a read of the documentation, you could alternatively potentially leverage the onUnknownSheet method to read the name of the sheet and check if the sheet name begins with rccsheet or finishing and then return the appropriate import class. For example:

public function onUnknownSheet($sheetName)
{
    if(Str::startsWith($sheetName, 'rccsheet')) return new RCCSheetImport();
}

Finally, if you can rely on the order of the sheets always being the same, you could list the import classes in the order you wish to import them (this method is much less secure). For example:

public function sheets(): array
{
    return [
        new RCCSheetImport(),
        new FinishingImport()
    ];
}