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;
    }
}

try changing heading row position public function headingRow(): int { return 2; } — John Lobo, Jul 09 '21 at 11:41
Yes, that's one more thing, I have to do. I'll add that to my question. But that doesn't answer it. — shaedrich, Jul 09 '21 at 11:48
Is second row key for row 3 to 5 in database? something like ["group"=>"group_A",'column_1'=>1,'column_2'=>2,'column_3'=>3] for first model and again ["group"=>"group_B",'column_1'=>4,'column_2'=>5,'column_3'=>6] for second model?? — MHIdea, Jul 17 '21 at 06:52
How you are fetching data from models? Can you show data structure that you get — Yasin Patel, Jul 17 '21 at 18:09

score 0 · Answer 1 · answered Jul 16 '21 at 19:12

It seems like you're on the right track. As mentioned in the related documentation, the OnEachRow concern supposedly grants you more control over what happens in non-standard-case scenarios, where an entire row isn't necessarily a direct model representation (contrary to the ToModel concern). The documentation even advises not to mix both.

In your case, what stops you from populating your models the way you wish? Although I don't quite grasp the three-field structure of your model, I'd suggest you simply go like:

<?php

namespace App\Imports;

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

class ForecastImport implements OnEachRow
{
    function headingRow(): int { return 2; }

    public function onRow(Row $row)
    {
        //splitting the 9-column array into chunks of 3
        $entries = array_map(function($entry) {
            return [
                'group' => $entry[0],
                'column' => $entry[1],
                'value' => $entry[2]
            ];
        }, array_chunk($row->toArray(), 3));

        //populate a model with each chunk
        foreach($entries as $entry) {
            MyModel::create($entry);
        }
    }
}

But again, I don't see the correlation with the column and group you're parsing, since you'd be assigning only values. Anyways, you can modify that yourself - the main point being that using OnEachRow, you're freer to manage model creation yourself. In this occasion, we're creating 3 model records per row, so I suppose that should suffice. Correct me if I've misunderstood anything. Happy coding!

score 0 · Answer 2 · answered Jul 17 '21 at 08:12

If you have following columns in database table

group|column|value

then you can

<?php

namespace App\Imports;

use Maatwebsite\Excel\Concerns\OnEachRow;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Maatwebsite\Excel\Imports\HeadingRowFormatter;
use Maatwebsite\Excel\Row;

$tempHeading = null;
HeadingRowFormatter::extend('custom', function ($value, $key) use (&$tempHeading) {

    if (!empty($value)) {

        $tempHeading = $value;
    }

    return $key . "-" . $tempHeading;


});

HeadingRowFormatter::default('custom');


class UsersImport implements ToModel, WithHeadingRow, OnEachRow
{
    /**
     * @param array $row
     *
     * @return \Illuminate\Database\Eloquent\Model|null
     */


    private $firstRow = [];

    public function model(array $row)
    {

        //onrow same logic you can apply here also
        //any one you can use onRow or model

    }

    public function headingRow(): int
    {
        return 1;
    }


    public function onRow(Row $row)
    {

        $rowIndex = $row->getIndex();
        $row = $row->toArray();

        if ($rowIndex == 2) {

            $this->firstRow = $row;

        } else {

            $result = collect($row)->transform(function ($value, $key) {


                $columnName = $this->firstRow[$key];
                $group = explode("-", $key)[1];

                return [
                    'group' => $group,
                    'column' => $columnName,
                    'value' => $value
                ];
            })->values()->toArray();

            echo "<br>****************Start of Row***************<br>";
            dump($result);
            echo "<br>****************End of Row***************<br>";


        }

    }

}

so output of $result is

So for each row will get multidimensional array so we can use

ModelName::insert($result)

or If you have following columns in database table like below

group|column1|column2|column3

Then

<?php

namespace App\Imports;

use Maatwebsite\Excel\Concerns\OnEachRow;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Maatwebsite\Excel\Imports\HeadingRowFormatter;
use Maatwebsite\Excel\Row;

$tempHeading = null;
HeadingRowFormatter::extend('custom', function ($value, $key) use (&$tempHeading) {

    if (!empty($value)) {

        $tempHeading = $value;
    }

    return $key . "-" . $tempHeading;


});

HeadingRowFormatter::default('custom');


class UsersImport implements ToModel, WithHeadingRow, OnEachRow
{
    /**
     * @param array $row
     *
     * @return \Illuminate\Database\Eloquent\Model|null
     */


    private $firstRow = [];

    public function model(array $row)
    {

        //onrow same logic you can apply here also
        //any one you can use onRow or model

    }

    public function headingRow(): int
    {
        return 1;
    }


    public function onRow(Row $row)
    {


        $rowIndex = $row->getIndex();
        $row = $row->toArray();

        if ($rowIndex == 2) {

            $this->firstRow = $row;

        } else {
            $rows=[];
             foreach ($row as $key=>$value){
                 $columnName = $this->firstRow[$key];
                 $group = explode("-", $key)[1];
                 $rows[$group]['group']=$group;
                 $rows[$group][$columnName]=$value;
             }
             
            echo "<br>****************Start of Row***************<br>";
            dump(array_values($rows));

            echo "<br>****************End of Row***************<br>";


        }

    }

}

Output of dump(array_values($rows));

score 0 · Accepted Answer · answered Jul 19 '21 at 12:58

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)
            );
        });
    }
}

Split grouped data at import

3 Answers3