Laravel 10集成MaatWebsite在Azure App Service中仅能处理2条Excel记录的问题

算力阿峰

2026-6-14

问题：Azure App Service中MaatWebsite Excel处理仅支持2条记录

Laravel 10应用集成MaatWebsite Excel库处理Excel文件，在Azure Linux虚拟机上可正常处理任意数量的记录，但部署到Azure App Service后，最多只能处理2条记录。以下是排查方向及解决方法：

排查方向

请求超时限制：Azure App Service默认请求超时时间较短（默认230秒），处理大量记录时若耗时超过阈值，会被强制中断；而虚拟机无此严格限制。
资源配额不足：App Service基础层的内存、CPU配额有限，处理Excel大文件时资源耗尽导致进程终止，虚拟机资源更充足可支撑更大负载。
Excel读取模式：MaatWebsite默认使用内存读取全量数据，在资源受限环境下无法加载大量行，虚拟机内存充足则无此问题。
大事务压力：整个导入流程在单个数据库事务中执行，大量记录会导致事务日志过大，触发App Service的数据库连接或资源限制。
隐藏错误未捕获：App Service环境中可能存在未被捕获的报错（如内存溢出），导致流程提前终止，而虚拟机环境中日志更易排查。

解决方法

1. 延长请求超时时间

在Azure Portal的App Service配置中添加以下应用设置：

WEBSITES_CONTAINER_START_TIME_LIMIT：设置为更大值（如1800，单位秒）
WEBSITES_TIMEOUT：设置为更大值（如1800，单位秒）

2. 优化内存与数据库查询

分块读取Excel：实现WithChunkReading接口，分批次处理记录，降低内存占用：

use Maatwebsite\Excel\Concerns\WithChunkReading;

class ExcelSponsorImportTrainees implements ToCollection, WithHeadingRow, WithChunkReading
{
    // ... 原有代码

    public function chunkSize(): int
    {
        return 100; // 每次处理100条记录
    }
}

批量缓存课程数据：提前加载所有课程代码到内存，避免循环中频繁查询数据库：

public function collection(Collection $rows)
{
    // 缓存所有课程代码与ID映射
    $courseMap = Course::select('id', 'code')
                        ->get()
                        ->mapWithKeys(function ($course) {
                            return [strtolower(trim($course->code)) => $course->id];
                        })
                        ->toArray();

    DB::beginTransaction();
    try {
        // ... 原有逻辑

        foreach ($rows as $key => $row) {
            // ... 原有验证逻辑

            $courseCode = strtolower(trim($request->course_code));
            $courseId = $courseMap[$courseCode] ?? null;
            if ($courseId) {
                $course = Course::find($courseId);
                $this->recordSponsoredUser($user, $course, $request, $this->sponsor);
            } else {
                $this->invalidCourseCodes[] = $courseCode;
                $this->messages .= 'Invalid course code: ' . $courseCode . "\n";
            }
        }

        // ... 后续逻辑
    } catch (\Exception $e) {
        // ... 异常处理
    }
}

3. 拆分事务

避免单个大事务，每处理固定数量的记录后提交事务：

$batchSize = 50;
$count = 0;

foreach ($rows as $key => $row) {
    // ... 处理单条记录逻辑

    $count++;
    if ($count % $batchSize === 0) {
        DB::commit();
        DB::beginTransaction();
    }
}

// 提交剩余记录的事务
DB::commit();

4. 启用详细日志排查

在Laravel .env中设置LOG_LEVEL=debug，记录每一步处理细节。
在Azure Portal查看App Service的日志流和应用服务日志，检查是否有内存不足、超时或数据库报错。

5. 临时升级App Service计划

若资源不足是核心问题，可临时升级到更高层级的App Service计划（如从B1升级到B2），验证是否解决问题后再决定是否长期升级。

原导入代码

<?php 

namespace App\Imports;

use App\Helpers\ExcelFileUploadColumnValidatorHelper;
use App\Helpers\Generators;
use App\Mail\InvalidCourseCodeMail;
use App\Mail\SponsoredTraineeMail;
use App\Models\Course;
use App\Models\Sponsor;
use App\Models\SponsoredUser;
use App\Models\User;
use Illuminate\Support\Carbon;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Mail;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Illuminate\Support\Facades\Validator;

class ExcelSponsorImportTrainees implements ToCollection, WithHeadingRow
{
    private $batchcode;
    private $sponsor;
    private $authUser;
    private $invalidCourseCodes = [];
    private $existCourseCodes = [];
    private $registeredSponsors = [];
    private $messages;
    private $expectedHeaders = [
        'first_name', 'last_name', 'email', 'phone', 'dob', 'gender',
        'course_code', 'sponsor_code', 'centre_code', 'session_code', 'semester_code'
    ];

    public function __construct($sponsor, $batchcode)
    {
        $this->sponsor = $sponsor;
        $this->batchcode = $batchcode;
        $this->authUser = Auth::user();
    }

    public function collection(Collection $rows)
    {
        if ($rows === null || $rows->isEmpty()) {
            throw new \Exception('No data found in the uploaded Excel file.');
        }

        ExcelFileUploadColumnValidatorHelper::validateHeaders($rows->first()->toArray(),$this->expectedHeaders);

        DB::beginTransaction();
        try {
            $this->validateHeaders($rows->first()->keys());

            foreach ($rows as $key => $row) {
                $validation = $this->validateRow($row->toArray(), $key);
                if ($validation->fails()) {
                    $this->messages .= "Validation errors occurred at Row $key: " . implode(', ', $validation->errors()->all()) . "\n";
                    continue;
                }

                if (!empty($row['course_code'])) {
                    $request = $this->mapRowToRequest($row);
                    $course_code = trim($request->course_code);
                    $user = $this->findOrCreateUser($request);

                    if (!$this->isUserAlreadySponsored($user, $request->course_code, $this->sponsor)) {
                        $course = Course::where(\DB::raw('LOWER(code)'), 'LIKE', '%' . strtolower(trim($request->course_code)) . '%')->first();
                        if ($course) {
                            $this->recordSponsoredUser($user, $course, $request, $this->sponsor);
                        } else {
                            $this->invalidCourseCodes[] = $course_code;
                            $this->messages .= 'Invalid course code: ' . $course_code . "\n";
                        }
                    }
                }
            }

            $this->sendSummaryEmail();
            DB::commit();
        } catch (\Exception $e) {
            DB::rollBack();
            $this->messages .= $e->getMessage() . "\n";
            $this->handleFailure($e);
        }

        return $this->registeredSponsors;
    }

    private function validateHeaders($headers)
    {
        $missingHeaders = array_diff($this->expectedHeaders, $headers->toArray());
        if (!empty($missingHeaders)) {
            throw new \Exception('Missing headers: ' . implode(', ', $missingHeaders));
        }
    }

    private function validateRow(array $row, $rowIndex)
    {
        return Validator::make($row, [
            'first_name' => 'required|string|max:255',
            'last_name' => 'required|string|max:255',
            'email' => 'required|email|max:255',
            'phone' => 'required|max:15',
            'dob' => 'required|date|before:today',
            'gender' => 'required|in:Male,Female,other',
            'course_code' => 'required',
            'sponsor_code' => 'required',
            'centre_code' => 'required',
            'session_code' => 'required',
            'semester_code' => 'required',
        ], [
            'email.email' => 'The email address must be valid.',
            'dob.before' => 'The date of birth must be a date before today.',
            'gender.in' => 'The gender must be one of the following: male, female, other.',
        ]);
    }

    private function mapRowToRequest($row)
    {
        $request = new \StdClass;
        $request->first_name = $row['first_name'];
        $request->last_name = $row['last_name'];
        $request->email = $row['email'];
        $request->phone = $row['phone'];
        $request->dob = $row['dob'];
        $request->gender = $row['gender'];
        $request->course_code = $row['course_code'];
        $request->sponsor_code = $row['sponsor_code'];
        $request->centre_code = $row['centre_code'];
        $request->session_code = $row['session_code'];
        $request->semester_code = $row['semester_code'];
        return $request;
    }

    private function findOrCreateUser($request)
    {
        $user = User::where('email', $request->email)->first();
        if (!$user) {
            $user = $this->createNewUser($request);
        }
        return $user;
    }

    private function createNewUser($request)
    {
        $user = new User();
        $user->name = $request->first_name . ' ' . $request->last_name;
        $user->email = $request->email;
        $user->email_verification_code = Generators::generateRandomString(7);
        $user->password = bcrypt($request->password ?? '123456');
        $user->category = 'Trainee';
        $user->dob = Carbon::parse($request->dob);
        $user->phone = $this->formatPhone($request->phone);
        $user->gender = $this->stringToPascal($request->gender);
        $user->sponsor_id = $this->sponsor->id;
        $user->batchcode = $this->batchcode;
        $user->status = 1;
        $user->code = $this->getUserCodePrefix($user->category) . '-' . time();
        $user->save();

        $user->bio()->create([
            'firstname' => $request->first_name,
            'lastname' => $request->last_name,
            'email' => $request->email,
            'phone' => $user->phone,
            'stateofresidence' => 0,
            'stateoforigin' => 0,
            'nationality' => 0,
            'dob' => Carbon::parse($user->dob),
            'gender' => $user->gender
        ]);

        return $user;
    }

    private function isUserAlreadySponsored($user, $course_code, $sponsor)
    {
        $course = Course::where('code', $course_code)->first();
        if ($course && $user) {
            $sponsoredUser = SponsoredUser::where('user_id', $user->id)
                ->where('course_id', $course->id)
                ->where('sponsor_id', $sponsor->id)
                ->first();
            if ($sponsoredUser) {
                $emailKey = $user->name . ' - ' . $user->email;
                $this->existCourseCodes[$emailKey] = $course->code;
                $this->messages .= $user->email . ' is already sponsored for course ' . $course->name . "\n";
                return true;
            }
        }
        return false;
    }

    private function recordSponsoredUser($user, $course, $request, $sponsor)
    {
        if (!$course) {
            $this->messages .= 'Failed to import trainees: invalid course code' . "\n";
            return null;
        }

        $sponsored_user = new SponsoredUser();
        $sponsored_user->user_id = $user->id;
        $sponsored_user->sponsor_id = $sponsor->id;
        $sponsored_user->batchcode = $this->batchcode;
        $sponsored_user->course_id = $course->id;
        $sponsored_user->centre_code = $request->centre_code;
        $sponsored_user->session_code = $request->session_code;
        $sponsored_user->term_code = $request->semester_code;
        $sponsored_user->save();

        $this->registeredSponsors[] = $sponsored_user;
    }

    private function sendSummaryEmail()
    {
        if (!empty($this->existCourseCodes) || !empty($this->sponsor->template_sponsor_code)) {
            Mail::to($this->authUser->email)->cc('sponsorupload@yopmail.com')->send(new InvalidCourseCodeMail([], $this->sponsor, $this->existCourseCodes));
        }
    }

    private function handleFailure(\Exception $e)
    {
        Mail::to($this->authUser->email)->cc('sponsorupload@yopmail.com')->send(new InvalidCourseCodeMail($this->invalidCourseCodes, $this->sponsor, []));
        Log::error('Failed to import trainees: ' . $e->getMessage());
        $this->messages .= 'Failed to import trainees: ' . $e->getMessage() . "\n";
    }

    private function getUserCodePrefix($category)
    {
        switch ($category) {
            case 'Trainee': return 'TNE';
            case 'Trainer': return 'TNR';
            case 'Staff': return 'STF';
            case 'Super Admin': return 'SA';
            default: return 'USR';
        }
    }

    private function formatPhone($phone)
    {
        if (is_null($phone) || strlen((string)$phone) < 10) {
            return null;
        }
        $phone = (string) $phone;
        if (strlen($phone) == 10) {
            $phone = "0{$phone}";
        }
        if (substr($phone, 0, 1) == '+') {
            $phone = substr($phone, 1);
        }
        if (substr($phone, 0, 3) == '234') {
            $phone = '0' . substr($phone, 3);
        }
        return $phone;
    }

    public function getSponsoredUsers()
    {
        return $this->registeredSponsors;
    }

    private function stringToPascal(string $string): string
    {
        $value = strtolower($string);
        $pascalCaseString = ucfirst($value);
        return $pascalCaseString;
    }

    public function getMessages()
    {
        return $this->messages;
    }
}

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

Laravel 10集成MaatWebsite在Azure App Service中仅能处理2条Excel记录的问题

排查方向 #

解决方法 #

1. 延长请求超时时间 #

2. 优化内存与数据库查询 #

3. 拆分事务 #

4. 启用详细日志排查 #

5. 临时升级App Service计划 #

原导入代码 #