Laravel Excel导入超20行表格触发类型错误问题求助

问题描述 #

我正在开发支持上传.xlsx多行列文件的项目，使用maatwebsite/excel": "^3.1"。文件行数少于20行时一切正常，但行数超过20行时，抛出如下错误：

Argument 1 passed to Maatwebsite\Excel\Imports\ModelManager::Maatwebsite\Excel\Imports\{closure}() must be an instance of Illuminate\Database\Eloquent\Model, instance of Illuminate\Http\RedirectResponse given, called in C:\xampp\htdocs\TCF-CRM\vendor\laravel\framework\src\Illuminate\Collections\Traits\EnumeratesValues.php on line 245

已尝试的操作：

• 修改请求文件配置
• 调整php.ini上传参数（如upload_max_filesize、post_max_size等）
• 将文件先保存到服务器再导入（错误依旧）

相关代码 #

控制器代码 #

public function upload_base (Request $request){ 
         
        $request->validate([
            'base_xls' => 'required|mimes:xlsx, csv, xls'
        ]);  

        Base_comercial::truncate();  
        Excel::import(new BaseComercialImport, request()->file('base_xls')->store('temp'));  
        return redirect()->route('dashboard')->with('success', '¡Base comercial cargada exitosamente!');
}

导入类代码 #

/** 
* @param array $row
* 
* @return \Illuminate\Database\Eloquent\Model|null
*/
public function model(array $row)  
{
    /* id estado converter */
    $estado = $this->estado_validate($row['estado']);
    if ($estado == "ERROR"){
        return redirect()->back()->with('Error', '¡Estado inválido!');
    } 
    /* --- */
    return new Base_comercial([ 
        'fecha' => Date::excelToDateTimeObject($row['fecha']),
        'nom_cliente' => $row['cliente'],
        'nom_proyecto' => $row['nom_proy'], 
        'cod_cc' => $row['cod_cc'],
        'valor_proyecto' => $row['vr_proy'],
        'com_1' => $row['com1'],
        'com_2' => $row['com2'],
        'com_3' => $row['com3'],
        'id_estado' => $estado,
        'fecha_inicio' => Date::excelToDateTimeObject($row['f_inicio']),
        'dura_mes' => Date::excelToDateTimeObject($row['dura_mes']),
        'id_user' => Auth::user()->id,
    ]); 
}

public function estado_validate ($estado){
    switch ($estado){
        case "CERRADO":
            $estado = 1;
            break;
        case "COTIZACION":
            $estado = 2;
            break;
        case "EJECUCIONXFACTURAR":
            $estado = 3;
            break;
        case "PERDIDO":
            $estado = 4;
            break;
        case "PROPUESTA":
            $estado = 5;
            break;
        case "VENTA":
            $estado = 6;
            break;
        case "VENTAEJECUCIÓN":
            $estado = 7;
            break;
        default: 
            $estado = "ERROR";                                 
        break;
    }
    return $estado;
}

路由代码 #

Route::post('/base-upload', [ComercialController::class, 'upload_base'])->middleware(['auth'])->name('base-upload');

问题原因 #

错误核心是导入类的model方法返回了非法类型。根据Laravel Excel 3.1规范，model方法仅允许返回Illuminate\Database\Eloquent\Model实例或null，但你的代码在检测到无效estado时，返回了RedirectResponse对象，完全不符合方法的返回类型要求。

行数少于20行时未报错，是因为这些行的estado都符合验证规则，未触发错误分支；当行数超过20行时，存在至少一行的estado不在预设枚举值里，触发了return redirect()->back()，从而抛出类型错误。

解决方案 #

方案1：使用Laravel Excel自带验证机制（推荐） #

利用框架自带的行级验证功能，自动拦截无效行并收集错误：

修改导入类，实现WithValidation接口：

use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithValidation;
use Illuminate\Validation\Rule;

class BaseComercialImport implements ToModel, WithValidation
{
    /**
    * @param array $row
    * 
    * @return \Illuminate\Database\Eloquent\Model|null
    */
    public function model(array $row)  
    {
        $estado = $this->estado_validate($row['estado']);
        
        return new Base_comercial([ 
            'fecha' => Date::excelToDateTimeObject($row['fecha']),
            'nom_cliente' => $row['cliente'],
            'nom_proyecto' => $row['nom_proy'], 
            'cod_cc' => $row['cod_cc'],
            'valor_proyecto' => $row['vr_proy'],
            'com_1' => $row['com1'],
            'com_2' => $row['com2'],
            'com_3' => $row['com3'],
            'id_estado' => $estado,
            'fecha_inicio' => Date::excelToDateTimeObject($row['f_inicio']),
            'dura_mes' => Date::excelToDateTimeObject($row['dura_mes']),
            'id_user' => Auth::user()->id,
        ]); 
    }

    // 定义行验证规则
    public function rules(): array
    {
        return [
            'estado' => [
                'required',
                Rule::in(['CERRADO', 'COTIZACION', 'EJECUCIONXFACTURAR', 'PERDIDO', 'PROPUESTA', 'VENTA', 'VENTAEJECUCIÓN'])
            ],
            // 可添加其他字段验证规则
        ];
    }

    // 自定义验证错误消息（可选）
    public function customValidationMessages()
    {
        return [
            'estado.in' => '¡Estado inválido!',
        ];
    }

    public function estado_validate ($estado){
        switch ($estado){
            case "CERRADO":
                return 1;
            case "COTIZACION":
                return 2;
            case "EJECUCIONXFACTURAR":
                return 3;
            case "PERDIDO":
                return 4;
            case "PROPUESTA":
                return 5;
            case "VENTA":
                return 6;
            case "VENTAEJECUCIÓN":
                return 7;
            default: 
                return 0;                                 
        }
    }
}

控制器中捕获验证异常并返回错误：

use Maatwebsite\Excel\Validators\ValidationException;

public function upload_base (Request $request){ 
    $request->validate([
        'base_xls' => 'required|mimes:xlsx, csv, xls'
    ]);  

    Base_comercial::truncate();  
    try {
        Excel::import(new BaseComercialImport, request()->file('base_xls')->store('temp'));  
        return redirect()->route('dashboard')->with('success', '¡Base comercial cargada exitosamente!');
    } catch (ValidationException $e) {
        $failures = $e->failures();
        $errorMessage = '第 ' . $failures[0]->row() . ' 行：' . $failures[0]->errors()[0];
        return redirect()->back()->with('Error', $errorMessage);
    }
}

方案2：返回null跳过无效行并收集错误 #

若不想使用验证接口，可在model方法返回null跳过无效行，同时收集错误信息：

修改导入类：

use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithBatchInserts;
use Maatwebsite\Excel\Concerns\WithChunkReading;

class BaseComercialImport implements ToModel, WithBatchInserts, WithChunkReading
{
    private $errors = [];

    /**
    * @param array $row
    * 
    * @return \Illuminate\Database\Eloquent\Model|null
    */
    public function model(array $row)  
    {
        $estado = $this->estado_validate($row['estado']);
        if ($estado == "ERROR"){
            $this->errors[] = '第 ' . ($row['row'] ?? '未知') . ' 行：Estado inválido';
            return null; // 返回null跳过该行导入
        } 
        return new Base_comercial([ 
            'fecha' => Date::excelToDateTimeObject($row['fecha']),
            'nom_cliente' => $row['cliente'],
            'nom_proyecto' => $row['nom_proy'], 
            'cod_cc' => $row['cod_cc'],
            'valor_proyecto' => $row['vr_proy'],
            'com_1' => $row['com1'],
            'com_2' => $row['com2'],
            'com_3' => $row['com3'],
            'id_estado' => $estado,
            'fecha_inicio' => Date::excelToDateTimeObject($row['f_inicio']),
            'dura_mes' => Date::excelToDateTimeObject($row['dura_mes']),
            'id_user' => Auth::user()->id,
        ]); 
    }

    public function estado_validate ($estado){
        switch ($estado){
            case "CERRADO":
                return 1;
            case "COTIZACION":
                return 2;
            case "EJECUCIONXFACTURAR":
                return 3;
            case "PERDIDO":
                return 4;
            case "PROPUESTA":
                return 5;
            case "VENTA":
                return 6;
            case "VENTAEJECUCIÓN":
                return 7;
            default: 
                return "ERROR";                                 
        }
    }

    // 获取错误信息
    public function getErrors()
    {
        return $this->errors;
    }

    // 批量插入和分块读取，优化大文件导入（可选）
    public function batchSize(): int
    {
        return 100;
    }

    public function chunkSize(): int
    {
        return 100;
    }
}

控制器中处理错误：

public function upload_base (Request $request){ 
    $request->validate([
        'base_xls' => 'required|mimes:xlsx, csv, xls'
    ]);  

    Base_comercial::truncate();  
    $import = new BaseComercialImport();
    Excel::import($import, request()->file('base_xls')->store('temp'));  

    if (!empty($import->getErrors())) {
        $errorMessage = implode('<br>', $import->getErrors());
        return redirect()->back()->with('Error', $errorMessage);
    }

    return redirect()->route('dashboard')->with('success', '¡Base comercial cargada exitosamente!');
}

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