Laravel-excel: [QUESTION] ignoreEmpty @ Laravel Excel 3.1

[SanderMuller]

Prerequisites

• [X] Able to reproduce the behaviour outside of your code, the problem is isolated to Laravel Excel.
• [X] Checked that your issue isn't already filed.
• [X] Checked if no PR was submitted that fixes this problem.

Versions

• PHP version: 7.1.18
• Laravel version: 5.7.9
• Package version: 3.1.2

Description

I would like to skip empty rows. I have an excel file with 4 non-empty rows. When importing it creates a collection with 34 items.

Altho I don't think it's relevant, I'm using the following setup:
Import with WithMultipleSheets concern. The individual sheet containing the empty rows uses the following concerns: ToCollection, WithHeadingRow, WithStartRow.

Previous versions on Laravel Excel had the ignoreEmpty option to filter the empty rows.

Steps to Reproduce

Import excel file that contains empty rows.

Expected behavior:
I would like to get a collection with only the non-empty rows.

Actual behavior:
Collection with both non-empty rows and empty rows.

[RafaelRodriguesFraga]

I need this too. Looking in the issues, I found that we can't do this with Laravel Excel. We'll have to use PhpSpreadsheet for this. I don't know if it's true, if there's a way with Laravel Excel.

[zishiguo]

How to use "ignoreEmpty" in "Multiple Sheets" ? Otherwise, beyond allowed memory size

[RafaelRodriguesFraga]

How to use "ignoreEmpty" in "Multiple Sheets" ? Otherwise, beyond allowed memory size

ignoreEmpty it's not working anymore T_T

[patrickbrouwers]

ignoreEmpty was intended at cells in 2.1, not at rows. There's currently no ignoreEmptyRows functionality in PhpSpreadsheet. Best you can do it do this check your self inside the ToCollection method (e.g. check with (array_filter($row))

[Otienoh]

use ToCollection method the wrap everything within if($row->filter()->isNotEmpty())
```
public function collection(Collection $rows)
{
foreach($rows as $row) {
if($row->filter()->isNotEmpty()){
// you logic can go here

            $user = User::create([
                'name' => ucwords($row['name']),
                'class' => $row['class'],
                ...
            ]);
        }
    }   
}

```

[Yousaf3339]

use ToCollection method the wrap everything within if($row->filter()->isNotEmpty())

public function collection(Collection $rows)
   {
      foreach($rows as $row) {
          if($row->filter()->isNotEmpty()){
              // you logic can go here

              $user = User::create([
                  'name' => ucwords($row['name']),
                  'class' => $row['class'],
                  ...
              ]);
          }
      }   
   }

same will work in "ToModel" ??

[patrickbrouwers]

Version 3.2 will have a SkipsEmptyRows concern.

[siarheipashkevich]

@patrickbrouwers when the version 3.2 will be released?

[mohamedhafezqo]

This is another way to avoid empty rows

        /** @var CellCollection $row */
        foreach ($data as $row) {
            if (!$row->filter()->isEmpty()) {
                // do something 
            }
        }

[jaymoh]

Did anyone figure out a solution to this issue with ToModel while still using the WithValidation concern?

[adiletmaks]

Version 3.2 will have a SkipsEmptyRows concern.

when are you going to release?

[patrickbrouwers]

Currently no time to work on 3.2. If someone wants to backport the concern into 3.1, I'm fine with having it in 3.1

[SanderMuller]

Currently no time to work on 3.2. If someone wants to backport the concern into 3.1, I'm fine with having it in 3.1

@patrickbrouwers I created a backport PR, see #2984

[someoneNameGrey]

Version 3.2 will have a SkipsEmptyRows concern.

Before release of 3.2, and prefer to stay in ToModel, here's my workaround method

1. Takeoff WithValidation, remain the rules() : array function

• This is to avoid auto validation, we want to run validation manually

  2. Create Row Count to get tracking the current row number

• If you have Heading Row, initialise your $rows = 1, else $rows = 0

• Without WithValidation, we lose the row count as well, so we gonna make it manually

  3. Skip the empty rows (Laravel Excel Doc)

• In my case, I check all the required fields whether they're all set. If not, means this is an empty row (Example at Step 4)

  4. Create Validators (Laravel Doc)

use Illuminate\Support\Facades\Validator;
use Illuminate\Validation\ValidationException;

//
public function model(array $rows)
{
   ++$this->rows;

  //#3 Skip the empty rows
  if ( !isset($row['title']) && !isset($row['body'])))
  {
      return null;
  }

 //#4 Create Validators
   $validator = Validator::make($request->all(), [
            'title' => 'required|unique:posts|max:255',
            'body' => 'required',
        ]);

  if ($validation->fails()) {

        //Specify which row got into error
        $errors = $validation->errors()->all();
        foreach ($errors as $key => $err) {
            $errors[$key] = "Error at row #".$this->getRowCount().". ".$err;
        }
        throw ValidationException::withMessages($errors);
    }
}

Hope this helps!