Laravel Excel import - pass variable between sheets

Question

I'm trying to import a two sheet excel file using Laravel Excel.

The excel file contains two sheets, one for the main building and the second one with all the apartments inside, all I need is to pass the building id created from the first sheet to the second one, Here's a simplified version of the code that shows how I'm trying to do this.

// main import class
class BuildingImport implements  WithMultipleSheets
{
  public function sheets(): array
  {
    $buildingImport = new BuildingSheetImport;
    $buildingId = $buildingImport->buildingId ?? null;
    Log::info("fetched building_id: " . $buildingId);
    $apartmentsImport = new ApartmentsSheetImport($buildingId);

    return [
      $buildingImport,
      $apartmentsImport
    ];
  }
}

// first sheet class
class BuildingSheetImport implements ToCollection
{
  public $buildingId;

  public function collection(Collection $collection)
  {
    Log::info("setting building_id in first sheet");
    $this->buildingId = 100;
    return [];
  }
}

// second sheet class
class ApartmentsSheetImport implements ToCollection, SkipsEmptyRows
{
  protected $buildingId;

  public function __construct($buildingId)
  {
    $this->buildingId = $buildingId;
  }

  public function collection(Collection $rows)
  {
    Log::info("building_id in second sheet: " . $this->buildingId);
    return [];
  }
}

What i expect from the logs would be something like:

- setting building_id in first sheet
- fetched building_id: 100
- building_id in second sheet: 100

instead I got:

- fetched building_id:
- setting building_id in first sheet
- building_id in second sheet:

What am I missing? or maybe is there a better way to pass a variable created in a sheet to another?

Thanks.

Answer 1

This is expected because buildingId is initialized with null which is a primitive variable that is directly stored in memory when passing it.

You have to use a non-primitive variable array or object as a DTO and pass it to both sheets then fill this object in the first sheet and access the values in the second sheet.

Your code should be like this

// DTO class
class Dto
{
    public int $buildingId;
}

// main import class
class BuildingImport implements WithMultipleSheets
{
    public function sheets(): array
    {
        $dto = new Dto();

        return [
            new BuildingSheetImport($dto),
            new ApartmentsSheetImport($dto),
        ];
    }
}

// first sheet class
class BuildingSheetImport implements ToCollection
{
    public function __construct(public Dto $dto)
    {
    }

    public function collection(Collection $collection)
    {
        $this->dto->buildingId = 100;

        Log::info('setting building_id in first sheet with: ' . $this->dto->buildingId);

        return [];
    }
}

// second sheet class
class ApartmentsSheetImport implements ToCollection, SkipsEmptyRows
{
    public function __construct(public Dto $dto)
    {
    }

    public function collection(Collection $rows)
    {
        Log::info('building_id in second sheet: ' . $this->dto->buildingId);

        return [];
    }
}