如何使用Maatwebsite/Laravel-Excel在Laravel中更新数据表？

Hey there! Let's figure out how to get your database records updated with Maatwebsite/Laravel-Excel in Laravel. You've already got the file loading and data parsing down—nice work! Now let's add the missing update logic step by step.

First, you need a unique identifier to match Excel rows to existing database records (like a name, email, or id field—pick one that exists in both your Excel file and database table). Here's how to modify your import method:

use App\Models\YourModel; // Replace with your actual model name

public function import(Request $request) { 
    if($request->file('imported-file')) { 
        $path = $request->file('imported-file')->getRealPath(); 
        $data = Excel::load($path, function($reader) { })->get(); 
        
        if(!empty($data) && $data->count()) { 
            foreach ($data->toArray() as $row) { 
                if(!empty($row)) { 
                    // Step 1: Find the existing record using your unique identifier
                    $record = YourModel::where('name', $row['name'])->first(); // Swap 'name' for your unique field
                    
                    if($record) {
                        // Step 2: Update the record with Excel data
                        $record->update([
                            'age' => $row['age'], 
                            'phone' => $row['phone'],
                            // Laravel automatically handles `updated_at`, no need to add it manually
                        ]);
                    } else {
                        // Optional: Insert a new record if no match is found
                        // YourModel::create([
                        //     'name' => $row['name'],
                        //     'age' => $row['age'],
                        //     'phone' => $row['phone'],
                        // ]);
                    }
                }
            }
        }
        
        // Add a success feedback
        return back()->with('success', 'Records updated successfully!');
    }
    
    return back()->with('error', 'No file uploaded!');
}

If you're using Laravel Excel 3.0 or newer, Excel::load() is deprecated. The recommended approach is to use a dedicated Import class:

1. Create an Import Class #

// app/Imports/YourModelImport.php (or your model's equivalent)
namespace App\Imports;

use App\Models\YourModel;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;

class YourModelImport implements ToModel, WithHeadingRow
{
    public function model(array $row)
    {
        // Find the existing record
        $record = YourModel::where('name', $row['name'])->first();
        
        if($record) {
            // Update if record exists
            $record->update([
                'age' => $row['age'],
                'phone' => $row['phone'],
            ]);
            return $record;
        }

        // Optional: Uncomment to create new records for missing entries
        // return new YourModel([
        //     'name' => $row['name'],
        //     'age' => $row['age'],
        //     'phone' => $row['phone'],
        // ]);
    }
}

2. Update Your Controller Method #

use Maatwebsite\Excel\Facades\Excel;
use App\Imports\YourModelImport;

public function import(Request $request) {
    if($request->hasFile('imported-file')) {
        Excel::import(new YourModelImport, $request->file('imported-file'));
        return back()->with('success', 'Records updated successfully!');
    }
    
    return back()->with('error', 'No file selected!');
}

For large datasets, looping through each row and updating individually can be slow. Use Laravel's upsert() method (available in Laravel 8+) to handle this efficiently—it automatically updates existing records or inserts new ones in a single query:

public function import(Request $request) { 
    if($request->file('imported-file')) { 
        $path = $request->file('imported-file')->getRealPath(); 
        $data = Excel::load($path, function($reader) { })->get(); 
        
        if(!empty($data) && $data->count()) { 
            $updateBatch = [];
            foreach ($data->toArray() as $row) { 
                if(!empty($row)) { 
                    $updateBatch[] = [
                        'name' => $row['name'], // Unique identifier
                        'age' => $row['age'], 
                        'phone' => $row['phone'],
                    ];
                }
            }
            
            // Upsert: [data], [unique keys], [fields to update]
            YourModel::upsert($updateBatch, ['name'], ['age', 'phone']);
        }
        
        return back()->with('success', 'Batch update completed!');
    }
    
    return back()->with('error', 'No file uploaded!');
}

• Unique Identifier: Always use a reliable unique field (like email or id) to match records—never rely on non-unique fields like age to avoid updating the wrong rows.
• Data Validation: Add validation for Excel rows (e.g., check if age is a number, phone has a valid format) to prevent invalid data from entering your database.
• Error Handling: Consider wrapping the update logic in a try/catch block to handle database errors or invalid Excel data gracefully.

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