Can you use WithMappedCells and still get rows of data?

[mmickelson]

I have a spreadsheet where the first few rows have some general information like order date, order number, etc. Below that is the header row, then data for those headings in rows below that. Each AddOnOrder instance has fields for the general info and data from one of the rows. Here's an example of the table.

order number:	1234	order date:	1/23/2021
name:	John
GROUP	ITEM CODE	COLOR
A	45654	black
A	9876	blue
B	67588	gray

Here's the code I expected to work.

namespace App\Imports;

use App\Models\AddOnOrder;
use Maatwebsite\Excel\Concerns\RemembersRowNumber;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithBatchInserts;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Maatwebsite\Excel\Concerns\WithMappedCells;

class AddOnOrderImport implements ToModel, WithHeadingRow, WithBatchInserts, WithMappedCells
{
    use RemembersRowNumber;

    public function model(array $row)
    {
        return new AddOnOrder([

            // import the general info like order date, order #
            'order_number' => $row['order_number'],
            'order_date' => $row['order_date'],
            'from' => $row['name'],

            // import data from each row
            'group' => $row['GROUP'],
            'item_code' => $row['ITEM CODE'],
            'type' => $row['COLOR'],
        ]);
    }

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

    public function mapping(): array
    {
        return [
            'order_number'  => 'B1',
            'order_date' => 'D1',
            'from' => 'B2',
        ];
    }

If I dd($row) at the beginning of the model function, it is an array with ONLY the mapped cells. If I remove the heading row functionality, and just use row indexes ($row[0]) it still errors because the $row array only has the mapped cell values.

Is it possible to gather rows of data when you have other data in specific cells in the same spreadsheet?

[patrickbrouwers]

No that's not possible. MappedCells is meant to map only cells, not columns. You might want two imports or use a BeforeSheet event to save that information before executing the row import.

[mmickelson]

Ok, thanks. Would the BeforeSheet method involve calling another Import in the BeforeSheet handler to get the heading info imported? The file I'm working with has multiple sheets and each sheet has heading data to import as well as rows of data. I have to determine what method of importing to use based on parsing the sheet names for keywords. I think I'm pretty close but it's been messy.

[patrickbrouwers]

In BeforeSheet you have access to the PhpSpreadsheet sheet instance

[abhipoudel]

Can you please provide the working code for demo?