how to import a transposed excel in Laravel using laravel-excel

I'm trying to import an excel that looks like the image below to an Eloquent model:

I didn't find something straight forward in the docs, i found this solution (https://docs.laravel-excel.com/3.1/imports/mapped-cells.html) but it will only work for 1 line of data.

Reading through the documentation, I think the Laravel Excel package only supports data in the shape of rows, out-of-the-box.

I believe there are two options here:

1. Change your Excel sheet to a row-based lay-out
2. Create your own importer to transpose the data (https://docs.laravel-excel.com/3.1/imports/collection.html)

Create a separate import

In case of the latter, perhaps something like this will work.

<?php

// create file App/Imports/CitiesImport.php

namespace App\Imports;

use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;

class CitiesImport implements ToCollection
{
    public function collection(Collection $rows)
    {
        $transposedData = array_map(function (...$rows) {
            return $rows;
        }, ...$rows->values()->toArray());

        $cities = collect($transposedData);

        $headers = $cities->shift(); // The headers are the first entry of the collection, let's shift them for now

        return $cities;
    }
}

You can chain more Collection methods like mapInto() (https://laravel.com/docs/8.x/collections#method-mapinto) to map the collection into a collection of models.

The above code should return:

Illuminate\Support\Collection {#2006
  #items: array:3 [
    0 => array:3 [
      0 => "Madrid"
      1 => "3.233"
      2 => "1"
    ]
    1 => array:3 [
      0 => "Milan"
      1 => "1.352"
      2 => "0"
    ]
    2 => array:3 [
      0 => "Paris"
      1 => "2.161"
      2 => "0"
    ]
  ]
}

I have recreated this problem locally, and confirmed it worked using a test route. Just make sure you .xlsx file is placed in your /public folder or change the location in the code.

<?php

// in routes/web.php

use App\Imports\CitiesImport;
use Maatwebsite\Excel\Facades\Excel;

Route::get('/test/import', function() {
    Excel::import(new CitiesImport(), 'test.xlsx');
});