Split grouped data at import

[shaedrich]

I have an excel spreadsheet like the following:

Group A			Group B			Group C
Column 1	Column 2	Column 3	Column 1	Column 2	Column 3	Column 1	Column 2	Column 3
1	2	3	4	5	6	7	8	9
11	12	13	14	15	16	17	18	19
21	22	23	24	25	26	27	28	29

That results in nine models with that attributes:

• Group
• Column
• Value

I tried to find a solution but I'm stuck:

<?php

namespace App\Imports;

use App\Models\MyModel;
use Maatwebsite\Excel\Concerns\OnEachRow;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Row;

//use Maatwebsite\Excel\Concerns\WithMapping;

class ForecastImport implements ToModel, OnEachRow
{
    /**
    * @param array $row
    *
    * @return \Illuminate\Database\Eloquent\Model|null
    */
    public function model(array $row)
    {
        dd($row);
        return new MyModel([
            'group' => $row[0],
            'column' => $row[1],
            'value' => $row[2]
        ]);
    }

    public function onRow(Row $row)
    {
        $entries = array_map(function($entry) {
            return [
                'group' => $entry[0],
                'column' => $entry[1],
                'value' => $rowentry2]
            ];
        }, array_chunk($row->toArray(), 3));
        /*foreach($entries as $entry) {
            $this->model($entry);
        }*/
        // One row became three
        return $entries;
    }
}

[shaedrich]

I figured, it would be the cleanest way if $rows would be instance properties that could be modified beforehand so that onRow(), model() and similar methods would get the real "rows". Since that's not the case, I needed to make several changes:

app/Extends/Maatwebsite/Excel/TransformRows.php

namespace App\Extends\Maatwebsite\Excel;

interface TransformRows
{
    public function transformRows(array $rows): array;
}

app/Extends/Maatwebsite/Excel/Sheet.php

namespace App\Extends\Maatwebsite\Excel;

use Maatwebsite\Excel\Sheet as ExcelSheet;

class Sheet extends ExcelSheet
{
    public $test = 'test';
    private array $rows = [];
    /**
     * @param object   $import
     * @param int|null $startRow
     * @param null     $nullValue
     * @param bool     $calculateFormulas
     * @param bool     $formatData
     *
     * @return array
     */
    public function toArray($import, int $startRow = null, $nullValue = null, $calculateFormulas = false, $formatData = false)
    {
        $rows = parent::toArray($import, $startRow, $nullValue, $calculateFormulas, $formatData);
        if ($import instanceof TransformRows) {
            $rows = $import->transformRows($rows);
        }
        $this->rows = $rows;
        return $rows;
    }
}

app/Extends/Maatwebsite/Excel/Reader.php

namespace App\Extends\Maatwebsite\Excel;

use Maatwebsite\Excel\Reader as BaseReader;
use App\Extends\Maatwebsite\Excel\Sheet;
use Maatwebsite\Excel\Concerns\SkipsUnknownSheets;
use Maatwebsite\Excel\Exceptions\SheetNotFoundException;

class Reader extends BaseReader
{

    /**
     * @param $import
     * @param $sheetImport
     * @param $index
     *
     * @return Sheet|null
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws SheetNotFoundException
     */
    protected function getSheet($import, $sheetImport, $index)
    {
        try {
            return Sheet::make($this->spreadsheet, $index);
        } catch (SheetNotFoundException $e) {
            if ($import instanceof SkipsUnknownSheets) {
                $import->onUnknownSheet($index);

                return null;
            }

            if ($sheetImport instanceof SkipsUnknownSheets) {
                $sheetImport->onUnknownSheet($index);

                return null;
            }

            throw $e;
        }
    }
}

app/Providers/AppServiceProvider.php

namespace App\Providers;

use App\Extends\Sheet as ExtendsSheet;
use App\Extends\Maatwebsite\Excel\Reader as ExtendsReader;
use Illuminate\Support\ServiceProvider;
use Maatwebsite\Excel\Excel;
use Maatwebsite\Excel\Files\Filesystem;
use Maatwebsite\Excel\QueuedWriter;
use Maatwebsite\Excel\Sheet;
use Maatwebsite\Excel\Writer;

class AppServiceProvider extends ServiceProvider
{
    /**
     * Register any application services.
     *
     * @return void
     */
    public function register()
    {
        $this->app->bind('excel', function ($app) {
            return new Excel(
                $app->make(Writer::class),
                $app->make(QueuedWriter::class),
                $app->make(ExtendsReader::class),
                $app->make(Filesystem::class)
            );
        });
    }
}

[patrickbrouwers]

You can't use onRow and Model together, you have to pick either one.

When using onRow, you have to handle saving the model yourself.

If you use Model, you can return as many model instances as you want (so return 3 models there).

[shaedrich]

Wouldn't returning three models instead of one somewhat defeat the purpose of a method with a singular name? At least, that's not what I'd expect from a method named "model". I don't want to handle saving the model myself (if that's possible). I simply need a hook to manipulate the rows at the very beginning (for example, it would make it so much easier if you'd put stuff into instance properties so you can easily access them instead of having to find a way to hook into the process by nested overriding) and than go on as usual. As described in my answer, I suspect, that's not possible out of the box with the current setup of your package and would either require changes to the package or some overrides like I suggested.

[patrickbrouwers]

model() accepts 1 or multiple models as return. Here you can see an example of a working tests with this behavour: https://github.com/Maatwebsite/Laravel-Excel/blob/3.1/tests/Concerns/ToModelTest.php#L104