Laravel Excel多Sheet导入：Sheet间变量传递失败求助

使用Laravel Excel导入包含两个Sheet的Excel文件：

• 第一个Sheet对应主建筑数据，导入时生成building_id
• 第二个Sheet对应公寓数据，需要使用第一个Sheet生成的building_id

当前简化实现代码如下：

// 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 [];
  }
}

预期日志输出：

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

实际日志输出：

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

Laravel Excel的执行顺序是：

1. 先调用BuildingImport的sheets()方法，一次性创建所有Sheet的导入类实例
2. 之后才会逐个执行每个Sheet导入类的collection()（或其他导入逻辑）

所以你在sheets()里创建BuildingSheetImport后立刻读取buildingId时，它的collection()方法还没执行，自然拿不到值；等collection()执行设置buildingId时，ApartmentsSheetImport已经创建完成，无法再传递这个值。

方案1：使用共享变量容器（推荐） #

创建一个共享类存储中间变量，让两个Sheet导入类共享同一个实例：

// 共享变量容器类
class ImportSharedData
{
    public ?int $buildingId = null;
}

// main import class
class BuildingImport implements WithMultipleSheets
{
    private $sharedData;

    public function __construct()
    {
        $this->sharedData = new ImportSharedData();
    }

    public function sheets(): array
    {
        return [
            new BuildingSheetImport($this->sharedData),
            new ApartmentsSheetImport($this->sharedData),
        ];
    }
}

// first sheet class
class BuildingSheetImport implements ToCollection
{
    private $sharedData;

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

    public function collection(Collection $collection)
    {
        Log::info("setting building_id in first sheet");
        // 实际场景中这里是创建建筑后获取的ID
        $this->sharedData->buildingId = 100;
        return [];
    }
}

// second sheet class
class ApartmentsSheetImport implements ToCollection, SkipsEmptyRows
{
    private $sharedData;

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

    public function collection(Collection $rows)
    {
        Log::info("building_id in second sheet: " . $this->sharedData->buildingId);
        // 使用$this->sharedData->buildingId关联公寓数据
        return [];
    }
}

方案2：在第一个Sheet中直接处理第二个Sheet #

如果不需要严格拆分两个Sheet的导入类，可以在第一个Sheet的collection()方法中手动读取并处理第二个Sheet的数据：

class BuildingImport implements ToCollection
{
    public function collection(Collection $collection)
    {
        // 处理第一个Sheet的建筑数据，生成building_id
        $buildingId = 100;
        Log::info("setting building_id in first sheet");

        // 手动获取第二个Sheet的数据
        $excel = \Maatwebsite\Excel\Facades\Excel::toCollection(null, request()->file('file'));
        $apartmentRows = $excel[1]; // 第二个Sheet索引为1

        // 处理公寓数据，使用$buildingId
        Log::info("building_id in second sheet: " . $buildingId);
        foreach ($apartmentRows as $row) {
            // 执行公寓数据导入逻辑
        }
    }
}

方案3：利用Laravel Excel事件监听 #

通过WithEvents监听第一个Sheet导入完成的事件，传递生成的ID：

class BuildingImport implements WithMultipleSheets, WithEvents
{
    private ?int $buildingId = null;

    public function sheets(): array
    {
        return [
            new BuildingSheetImport($this),
            new ApartmentsSheetImport($this),
        ];
    }

    public function registerEvents(): array
    {
        return [
            AfterSheet::class => function(AfterSheet $event) {
                // 根据Sheet名称判断是否为第一个Sheet
                if ($event->getSheet()->getTitle() === '建筑数据') { // 替换为你的Sheet名称
                    // 实际场景中这里是你生成的建筑ID
                    $this->buildingId = 100;
                }
            },
        ];
    }

    public function getBuildingId(): ?int
    {
        return $this->buildingId;
    }
}

class BuildingSheetImport implements ToCollection
{
    private $mainImport;

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

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

class ApartmentsSheetImport implements ToCollection, SkipsEmptyRows
{
    private $mainImport;

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

    public function collection(Collection $rows)
    {
        Log::info("building_id in second sheet: " . $this->mainImport->getBuildingId());
    }
}

内容的提问来源于stack exchange，提问作者Lorenzo_inirapmoC