Laravel 8中无需额外依赖库将指定Excel列数据导入promos表的实现方法咨询

Hey there! Let's break this down for you. First, the key question: Can you import the Excel data without extra dependencies?

The short answer is yes—but it’s going to require some manual work, since Laravel 8 doesn’t include built-in tools for parsing Excel files. XLSX files are actually zip archives containing XML data, so we can use PHP’s native ZipArchive and XML parsing functions to extract the data. That said, if you’re open to using a trusted package, maatwebsite/excel will make this process way simpler. Let’s cover both approaches.

This method uses PHP’s native extensions to unpack the XLSX file and parse its underlying XML data. Here’s how to implement it:

1. Create a controller method (or add to an existing controller) to handle the import:

use Illuminate\Support\Facades\DB;
use ZipArchive;
use SimpleXMLElement;

public function importExcelWithoutPackage()
{
    // Get the absolute path to your uploaded Excel file
    $filePath = public_path('uploads/data.xlsx');
    
    // Open the XLSX file as a zip archive
    $zip = new ZipArchive;
    if ($zip->open($filePath) !== true) {
        return back()->with('error', 'Failed to open the Excel file.');
    }
    
    // Extract the first worksheet's XML data (adjust sheet name if needed)
    $xmlContent = $zip->getFromName('xl/worksheets/sheet1.xml');
    $zip->close();
    
    if (!$xmlContent) {
        return back()->with('error', 'Could not locate the worksheet in the Excel file.');
    }
    
    // Parse the XML content
    $xml = new SimpleXMLElement($xmlContent);
    $rowsToInsert = [];
    
    // Handle Excel's XML namespace
    $xmlNamespace = $xml->getNamespaces(true);
    $sheetRows = $xml->children($xmlNamespace['worksheet'])->sheetData->children($xmlNamespace['worksheet']);
    
    foreach ($sheetRows->row as $rowIndex => $row) {
        // Skip the header row if your Excel file has one (adjust index if your header is on a different row)
        if ($rowIndex === 0) {
            continue;
        }
        
        // Extract the first two columns (index 0 = first column, index 1 = second column)
        $cells = $row->children($xmlNamespace['worksheet'])->c;
        $promoName = (string)($cells[0] ?? '');
        $promoCode = (string)($cells[1] ?? '');
        
        // Only add valid rows (skip empty entries)
        if (!empty($promoName) && !empty($promoCode)) {
            $rowsToInsert[] = [
                'name' => $promoName,
                'code' => $promoCode
            ];
        }
    }
    
    // Bulk insert the data into the promos table
    if (!empty($rowsToInsert)) {
        DB::table('promos')->insert($rowsToInsert);
        return back()->with('success', 'Data imported successfully!');
    }
    
    return back()->with('info', 'No valid data found to import.');
}

Important Notes for This Method: #

• If your Excel file uses a different worksheet name (not sheet1), you’ll need to adjust the path inside getFromName().
• This example skips the first row assuming it’s a header—remove that check if your Excel has no header.
• Excel’s XML structure can vary slightly between versions, so you might need to tweak the parsing logic if you run into issues.

If you don’t mind adding a dependency, this package is the industry standard for Excel handling in Laravel. It handles edge cases (like data formatting, empty rows, and different Excel versions) automatically.

1. Install the package (compatible with Laravel 8):

composer require maatwebsite/excel:^3.1

2. Create an Import class:
   Run this Artisan command to generate a dedicated import class:

php artisan make:import PromoImport --model=Promo

(Note: If you don’t have a Promo model for the promos table, omit the --model=Promo flag.)

3. Update the Import class to map Excel columns to your database fields:

namespace App\Imports;

use App\Models\Promo; // Remove this if you didn't use the --model flag
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithStartRow;

class PromoImport implements ToModel, WithStartRow
{
    // Skip the header row (return 1 if there's no header)
    public function startRow(): int
    {
        return 2;
    }
    
    public function model(array $row)
    {
        // Map Excel column 0 (first column) to 'name', column 1 to 'code'
        return new Promo([ // Replace with DB::table('promos')->insert([...]) if no model
            'name' => $row[0],
            'code' => $row[1],
        ]);
    }
}

4. Use the import class in your controller:

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

public function importExcelWithPackage()
{
    $filePath = public_path('uploads/data.xlsx');
    
    Excel::import(new PromoImport, $filePath);
    
    return back()->with('success', 'Data imported successfully!');
}

This method is far more maintainable and less error-prone than manual parsing, especially if you need to handle complex Excel files later.

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