Split grouped data at import

Question

I have an excel spreadsheet like the following:

That results in nine models with that attributes:

• Group
• Column
• Value

I tried to find a solution using Laravel Excel but I'm stuck. The problem is that one row becomes three rows and I don't know how to achive that since I can't overwrite the rows.

<?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
{
    function headingRow(): int { return 2; }

    /**
    * @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;
    }
}

Answer 1

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". Other solutions look rather hacky to me since I just need to modify the raw input and would like to continue as usual after that which can't be done with the other solutions. Since $rows isn't an instance property, 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)
            );
        });
    }
}