Laravel 11 Excel导入：Societe表存储及Material表关联societe_id问题

我正在开发Laravel 11项目，需要从Excel文件导入数据。该文件包含多列数据，我需要将特定列（Numero BL、Nom Societe、numero marche）存储到societe表中，然后在导入过程中，将societe_id关联到materials表对应的行中。

Excel包含的关键列有：Numero BL、Nom Societe、numero marche，以及物料相关的库存编号、名称等字段。

我的需求 #

• 导入Excel文件
• 将Numero BL、Nom Societe、numero marche存储到societe表
• 在materials表的对应行中关联societe_id

我已尝试的代码 #

public function importExcel(Request $request)
{
    // Validate the request...
    $request->validate([
        "file" => "required|mimes:xlsx,xls",
    ]);

    // Get the uploaded file
    $file = $request->file('file');

    // Upload the Excel file
    $spreadsheet = IOFactory::load($file);

    // Get the first sheet of the file
    $sheet = $spreadsheet->getActiveSheet();
    $data = $sheet->toArray();

    $skipped = 0;
    $imported = 0;

    // Browse data rows and insert them into the database
    foreach ($data as $key => $row) {
        // Skip header rows (first 8 rows)
        if ($key < 8) continue;

        // Skip if row is empty or first column (N° d'inventaire) is empty
        if (empty($row) || empty($row[0])) continue;

        // Check if material already exists
        if (Material::where('num_inventaire', $row[0])->exists()) {
            $skipped++;
            continue;
        }

        // Manage the societe
        $societe = Societe::firstOrCreate(
            [
                'numero_bl' => $row[10],
                'nom_societe' => $row[11],
                'numero_marche' => $row[12]
            ]
        );

        // clean the inventory number by removing special characters
        $cleanInventaire = preg_replace('/[^0-9-]/', '', $row[0]);

        // If the inventory number contains a dash, we split it into two
        if (strpos($cleanInventaire, '-') !== false) {
            $inventaireNums = explode('-', $cleanInventaire);
            foreach ($inventaireNums as $num) {
                // Skip if the inventory number already exists
                if (Material::where('num_inventaire', trim($num))->exists()) {
                    $skipped++;
                    continue;
                }

                $newMaterial = new Material();
                $newMaterial->num_inventaire = trim($num);
                $newMaterial->date_inscription = !empty($row[1]) ? \Carbon\Carbon::createFromFormat('Y-m-d H:i:s', $row[1])->format('Y-m-d') : now();
                $newMaterial->designation = $row[2];
                $newMaterial->qte = $row[3];
                $newMaterial->marque = $row[4];
                $newMaterial->modele = $row[5];
                $newMaterial->service_id = !empty($row[6]) ? Service::where('nom', $row[6])->first()->id : null;
                $newMaterial->date_affectation = !empty($row[7]) ? \Carbon\Carbon::createFromFormat('Y-m-d H:i:s', $row[7])->format('Y-m-d') : now();
                $newMaterial->num_serie = $row[8];
                $newMaterial->observation = $row[9];
                $newMaterial->numero_bl = $row[10];
                $newMaterial->societe_id = $societe->id;
                $newMaterial->numero_marche = $row[12];
                $newMaterial->type = $row[13];
                $newMaterial->origin = $row[14];
                $newMaterial->etat = !empty($row[15]) ? $row[15] : null;

                if ($newMaterial->save()) {
                    $imported++;
                    MaterialHistory::create([
                        'material_id' => $newMaterial->id,
                        'from_service_id' => null,
                        'to_service_id' => $newMaterial->service_id,
                        'moved_at' => now()
                    ]);
                }
            }
            continue;
        }

        // Create the material
        $material = new Material();
        $material->num_inventaire = $cleanInventaire;
        $material->date_inscription = !empty($row[1]) ? \Carbon\Carbon::createFromFormat('Y-m-d H:i:s', $row[1])->format('Y-m-d') : now();
        $material->designation = $row[2];
        $material->qte = $row[3];
        $material->marque = $row[4];
        $material->modele = $row[5];
        $material->service_id = !empty($row[6]) ? Service::where('nom', $row[6])->first()->id : null;
        $material->date_affectation = !empty($row[7]) ? \Carbon\Carbon::createFromFormat('Y-m-d H:i:s', $row[7])->format('Y-m-d') : now();
        $material->num_serie = $row[8];
        $material->observation = $row[9];
        $material->numero_bl = $row[10];
        $material->societe_id = $societe->id;
        $material->numero_marche = $row[12];
        $material->type = $row[13];
        $material->origin = $row[14];
        $material->etat = !empty($row[15]) ? $row[15] : null;

        if ($material->save()) {
            $imported++;
            MaterialHistory::create([
                'material_id' => $material->id,
                'from_service_id' => null,
                'to_service_id' => $material->service_id,
                'moved_at' => now()
            ]);
        }
    }

    // Create log for import
    Log::create([
        'action' => 'import',
        'table_name' => 'materials',
        'record_id' => 0,
        'performed_by' => Auth::user()->id,
        'performed_at' => now()
    ]);

    return redirect(route('materiels.index'))
    ->with('success', "Import terminé. $imported produits importés, $skipped produits existants ignorés.");
}

遇到的问题 #

• societe表未被正确填充
• materials表未正确关联societe_id
• 导入过程无明显报错，静默失败

我的疑问 #

1. 如何确保导入过程中societe表被正确填充？
2. 如何在materials表中正确关联societe_id？
3. 这个导入函数有哪些最佳实践或优化方案？

提前感谢大家的帮助与指导！

内容的提问来源于stack exchange，提问作者Anas ER-RAKIBI