Laravel5.5下如何用PHPspreadsheet同时提取Excel的图片与文本数据？

Hey there! I’ve dealt with this exact issue before when working with PhpSpreadsheet in older Laravel versions, so I can walk you through a solid solution to grab both text data and images in one pass.

The Core Idea #

PhpSpreadsheet handles text via cell iteration, while images are stored in a separate drawing collection per worksheet. The key is to:

• Iterate through each worksheet to extract text data row-by-row
• Pull all images from the worksheet’s drawing collection
• Associate each image with its corresponding cell coordinate (so you know which cell the image is attached to)

Step-by-Step Code Example #

First, make sure you’ve installed a PhpSpreadsheet version compatible with Laravel 5.5 (stick to ^1.6 since newer versions require PHP 7.1+, which Laravel 5.5 might not support):

composer require phpoffice/phpspreadsheet:^1.6

Then, create a method in your controller (or a dedicated service class) to handle the extraction:

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\Storage;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

class ExcelExtractorController extends Controller
{
    public function extractDataAndImages()
    {
        // Path to your Excel file (adjust this to your actual file location)
        $filePath = storage_path('app/uploads/your_excel_file.xlsx');
        
        // Load the spreadsheet
        try {
            $spreadsheet = IOFactory::load($filePath);
        } catch (\Exception $e) {
            return response()->json(['error' => 'Failed to load Excel file: ' . $e->getMessage()], 400);
        }

        $extractedData = [];

        // Iterate through each worksheet
        foreach ($spreadsheet->getWorksheetIterator() as $worksheet) {
            $sheetName = $worksheet->getTitle();
            $extractedData[$sheetName] = [
                'text_data' => [],
                'images' => []
            ];

            // --------------------------
            // Extract text data
            // --------------------------
            $highestRow = $worksheet->getHighestRow();
            $highestColumn = $worksheet->getHighestColumn();
            $highestColumnIndex = Coordinate::columnIndexFromString($highestColumn);

            for ($row = 1; $row <= $highestRow; $row++) {
                $rowData = [];
                for ($col = 1; $col <= $highestColumnIndex; $col++) {
                    $cell = $worksheet->getCellByColumnAndRow($col, $row);
                    $columnName = Coordinate::stringFromColumnIndex($col);
                    // Use getFormattedValue() for readable dates/numbers
                    $rowData[$columnName] = $cell->getFormattedValue();
                }
                $extractedData[$sheetName]['text_data'][$row] = $rowData;
            }

            // --------------------------
            // Extract images and link to cells
            // --------------------------
            foreach ($worksheet->getDrawingCollection() as $drawing) {
                if (!$drawing instanceof Drawing) {
                    continue; // Skip non-drawing elements if any
                }

                $cellCoordinate = $drawing->getCoordinates(); // e.g., "A1"
                $imageExtension = $drawing->getExtension();
                $imageContents = $drawing->getContents();

                // Option 1: Convert image to Base64 (good for immediate display)
                $base64Image = 'data:image/' . $imageExtension . ';base64,' . base64_encode($imageContents);

                // Option 2: Save image to Laravel Storage (persistent storage)
                $imageFileName = uniqid('excel_img_') . '.' . $imageExtension;
                Storage::put('public/excel_images/' . $imageFileName, $imageContents);
                $storageUrl = Storage::url('public/excel_images/' . $imageFileName);

                // Store image data with its cell coordinate
                $extractedData[$sheetName]['images'][$cellCoordinate] = [
                    'coordinate' => $cellCoordinate,
                    'extension' => $imageExtension,
                    'base64' => $base64Image,
                    'storage_url' => $storageUrl
                ];
            }
        }

        // Return or process the extracted data as needed
        return response()->json($extractedData);
    }
}

Important Notes #

• Permissions: Ensure the storage/app/public/excel_images directory exists and has write permissions. Run php artisan storage:link to make the storage directory accessible via web URLs.
• Image Types: This code handles standard Drawing images (most common in Excel). If your file has shapes or other image types, you’ll need to add checks for classes like \PhpOffice\PhpSpreadsheet\Worksheet\Shape.
• Formatted Values: Use getFormattedValue() instead of getValue() if you want dates, currencies, or formatted numbers to appear as they do in Excel.
• Error Handling: The try/catch block helps catch file loading errors (e.g., corrupted files, wrong format).

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