how to skip empty sheets when import excel file?

[fealvareznicolin]

Prerequisites
[X] Have read and understood: https://github.com/Maatwebsite/Laravel-Excel/blob/3.1/.github/SUPPORT.md
[X] Checked if your Laravel Excel version is still supported: https://docs.laravel-excel.com/3.1/getting-started/support.html#supported-versions
[X] Checked that your question isn't already asked before.
[X] Filled in the entire issue template
Versions
PHP version: v7.4.11
Laravel version: 7.30.4
Package version: 3.1.19

So I have the following problem:
when I import a excel file with 3 sheets , and one of them or more is empty , I get the following error message:

PhpOffice\PhpSpreadsheet\Exception
Start row (2) is beyond highest row >

this is my importer:
``
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Maatwebsite\Excel\Imports\HeadingRowFormatter;
use Maatwebsite\Excel\Row;
use Maatwebsite\Excel\Concerns\OnEachRow;
use Maatwebsite\Excel\Concerns\WithValidation;
use Maatwebsite\Excel\Concerns\WithBatchInserts;//
use Maatwebsite\Excel\Concerns\WithChunkReading;//
use Maatwebsite\Excel\Validators\Failure;
use Maatwebsite\Excel\Concerns\SkipsOnError;
use Maatwebsite\Excel\Concerns\SkipsOnFailure;
use Maatwebsite\Excel\Concerns\Importable;
use Maatwebsite\Excel\Concerns\SkipsErrors;
use Maatwebsite\Excel\Concerns\RemembersRowNumber;//
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\WithMappedCells;
use Maatwebsite\Excel\Concerns\WithMultipleSheets;
use Maatwebsite\Excel\Concerns\WithConditionalSheets;
use Maatwebsite\Excel\Concerns\SkipsEmptyRows;

use Illuminate\Support\Collection;
use Carbon\Carbon;
use DB;

class UploadInventarioPrediosSVD implements
WithHeadingRow,WithValidation,SkipsOnFailure
,OnEachRow,WithMultipleSheets,SkipsEmptyRows
{

public function sheets(): array
{
    return [
         'InventarioGeneral' => new PredioInventarioGeneralSVD(),
         'IngresosGeneral' => new PredioIngresoSVD(),
         'SalidasGeneral'=> new PredioDespachoSVD(),
    ];
}

is there a way to skip empty sheets on onRow method?

[comicat-hu]

I have this issue too.
without modify the source code, I used the following code to make empty sheet not load.

in ServiceProvider

    public function boot()
    {
        Reader::macro('getSheetImports', function (Reader $reader) {
            return $reader->sheetImports;
        });

        Reader::macro('unsetSheetImport', function (Reader $reader, string $index) {
            unset($this->sheetImports[$index]);
        });

        Reader::macro('getImportSheet', function (Reader $reader, $import, $sheetImport, $index) {
            return $this->getSheet($import, $sheetImport, $index);
        });
    }

in Import implement WithMultipleSheets, WithEvents

    public function registerEvents(): array
    {
        return [
            //  preload sheets and filter out empty sheets
            BeforeImport::class => function (BeforeImport $event) {
                $reader = $event->getReader();
                $importer = $event->getConcernable();

                foreach ($reader->getSheetImports() as $index => $sheetImport) {
                    $sheet = $reader->getImportSheet($importer, $sheetImport, $index);
                    if ($sheet && $sheet->getHighestRow() <= $this->sheets[$index]->headingRow()) {
                        $reader->unsetSheetImport($index);
                    }
                }
            }
        ];
    }

But I think the better way is catching exception of each import in Reader->read(), don't interrupt overall import process.

[khushbu-jajal]

Solution that worked for me:
`

        $sheets = Excel::toArray([], $localExcelPath);

        foreach ($sheets as $index => $sheet) {
            if (count($sheet) >= 3) { // first 2 rows are heading rows
                $nonEmptySheets[] = $index;
            }
        }

        $import = new UserImport();

        $import->onlySheets($nonEmptySheets);

        Excel::import($import, $localExcelPath);

`

In Import Class use WithConditionalSheets Trait. and use function.

public function conditionalSheets(): array { return [ // array of all sheets ]; }

[khushbu-jajal]

This solution is for those who are referencing sheets by name and not an index.

           \\ use PhpOffice\PhpSpreadsheet\IOFactory;

            $reader = IOFactory::createReader('Xlsx');
            $reader->setReadDataOnly(true);
            $excel = $reader->load($localExcelPath);
            $sheetNames = $excel->getSheetNames();
            $sheetCount = $excel->getSheetCount();
            for ($index = 0; $index < $sheetCount; $index++) {
                $sheet = $excel->getSheet($index);
                $sheetData = $sheet->rangeToArray('A1:A3'); // my data starts from row 3.
                $sheetData = array_filter($sheetData, function ($row) {
                    return ! empty($row[0]);
                });

                if (count($sheetData) >= 3) {
                    $nonEmptySheets[] = $sheetNames[$index];
                }
            }