Laravel 9使用maatwebsite/excel 3.1导入Excel数据时行数据丢失求助

问题详情 #

使用Maatwebsite-excel 3.1从Excel导入多行数据，执行dd($row)时能正常打印所有行数据，但移除dd语句后，行数据无法正常写入数据库，后续行仿佛"消失"。尝试过去掉collect(trim(row[value]))写法、参考跳过空行的方案，均未解决。

错误截图 #

dd输出截图 #

原代码 #

public function collection(Collection $rows)
{
    $rows->shift();

    $total_amount = 0;
    foreach ($rows as $row){

        if($row->filter()->isNotEmpty()){
            $article_title =collect(trim($row[0]))[0];
            $tag = collect(trim($row[1]))[0];
            $words = collect((int) $row[2])[0];
            $deadline = collect(trim($row[3]))[0];
            $pkw = collect(trim($row[4]))[0];
            $skw1 = collect(trim($row[5]))[0];
            $skw2 = collect(trim($row[6]))[0];
            $instructions = collect(trim($row[7]))[0];
            $my_tag = UserTag::where('name', 'LIKE', '%'.$tag.'%')->first();
            $my_urgency = Urgency::where('name', 'LIKE', '%'.$deadline.'%')->first();
//                dd($tag, $words, $my_urgency);
            $amount  = $my_tag->amount_per_word * $my_urgency->amount * $words;
            $project_deadline = now()->addHours($my_urgency->hours)->format('Y-m-d H:i:s');
            Project::create([
                'user_id'=>request()->user()->id,
                'article_title'=>$article_title,
                'upload_batch_id'=>$this->batch->id,
                'user_tag_id'=>$my_tag->id,
                'urgency_id'=>$my_urgency->id,
                'price_per_word'=>$my_tag->amount_per_word,
                'no_of_words'=>$words,
                'deadline'=>$project_deadline,
                'amount'=>($amount),
                'primary_keyword'=>$pkw,
                'secondary_keyword_1'=>$skw1,
                'secondary_keyword_2'=>$skw2,
                'instructions' => $instructions
            ]);
            $total_amount = $total_amount + $amount;
        }
    }
    $this->batch->amount = $total_amount;
    $this->batch->update();
}

问题根源分析 #

1. 空行判断不严谨：$row->filter()->isNotEmpty()无法过滤所有空行（比如仅含空白字符的行），无效行进入处理流程后引发错误。
2. 模型查询返回null未处理：UserTag或Urgency查询不到匹配记录时返回null，后续调用$my_tag->amount_per_word会直接抛出致命错误，中断整个循环，导致后续行无法处理。
3. 冗余写法引发类型异常：collect(trim(...))[0]完全没必要，trim返回字符串，用collect包裹再取索引可能导致意外类型问题。
4. 无异常捕获机制：单行出错直接终止整个导入流程，看起来像后续数据"消失"。

修复方案 #

public function collection(Collection $rows)
{
    // 移除表头行
    $rows->shift();

    $total_amount = 0;
    foreach ($rows as $index => $row) {
        // 严格过滤空行：所有字段为空白则跳过
        $isBlank = $row->every(function ($value) {
            return is_null($value) || trim($value) === '';
        });
        if ($isBlank) {
            continue;
        }

        try {
            // 直接处理字段，移除冗余collect写法
            $article_title = trim($row[0] ?? '');
            $tag = trim($row[1] ?? '');
            $words = (int)($row[2] ?? 0);
            $deadline = trim($row[3] ?? '');
            $pkw = trim($row[4] ?? '');
            $skw1 = trim($row[5] ?? '');
            $skw2 = trim($row[6] ?? '');
            $instructions = trim($row[7] ?? '');

            // 校验必填字段，无效则跳过并记录日志
            if (empty($tag) || empty($deadline) || $words <= 0) {
                logger()->warning('导入行数据无效，跳过', ['row_index' => $index + 2, 'row_data' => $row->toArray()]);
                continue;
            }

            // 查询关联模型，无匹配则跳过
            $my_tag = UserTag::where('name', 'LIKE', "%{$tag}%")->first();
            if (!$my_tag) {
                logger()->warning('未找到匹配的UserTag', ['tag' => $tag, 'row_index' => $index + 2]);
                continue;
            }

            $my_urgency = Urgency::where('name', 'LIKE', "%{$deadline}%")->first();
            if (!$my_urgency) {
                logger()->warning('未找到匹配的Urgency', ['deadline' => $deadline, 'row_index' => $index + 2]);
                continue;
            }

            // 计算核心数据
            $amount = $my_tag->amount_per_word * $my_urgency->amount * $words;
            $project_deadline = now()->addHours($my_urgency->hours)->format('Y-m-d H:i:s');

            // 创建项目记录
            Project::create([
                'user_id' => request()->user()->id,
                'article_title' => $article_title,
                'upload_batch_id' => $this->batch->id,
                'user_tag_id' => $my_tag->id,
                'urgency_id' => $my_urgency->id,
                'price_per_word' => $my_tag->amount_per_word,
                'no_of_words' => $words,
                'deadline' => $project_deadline,
                'amount' => $amount,
                'primary_keyword' => $pkw,
                'secondary_keyword_1' => $skw1,
                'secondary_keyword_2' => $skw2,
                'instructions' => $instructions
            ]);

            $total_amount += $amount;
        } catch (\Exception $e) {
            // 捕获异常，记录日志后继续处理下一行
            logger()->error('导入行出错', [
                'row_index' => $index + 2,
                'row_data' => $row->toArray(),
                'error_msg' => $e->getMessage()
            ]);
            continue;
        }
    }

    // 更新批次总金额，用save()更稳妥
    $this->batch->amount = $total_amount;
    $this->batch->save();
}

关键修复点 #

• 严格空行过滤：用every方法判断所有字段是否为空，彻底排除无效行
• 移除冗余写法：直接用trim($row[0] ?? '')处理字段，避免类型异常
• 必填字段校验：提前过滤缺失关键数据的行
• 空模型处理：查询后判断模型是否存在，避免调用null对象属性
• 异常捕获：单行出错不终止整个导入流程，同时记录错误日志便于排查
• 替换update为save：更新批次时用save()，避免fillable限制导致更新失败

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