Today, I received a demand to read pictures from Excel. I searched some data on the Internet and basically realized my own demand. However, due to the long-term code found, I can't directly transplant it into my own project, so I need to make some changes.

Here we introduce the functions of reading pictures in Excel by using phpspreadsheet and PHPExcel extension library respectively:

PHPSpreadsheet

First install phpspreadsheet. Since the PHP version of the online server is PHP5.6, you need to install a version compatible with PHP5.6. Install version 1.8.2 here

composer require phpoffice/phpspreadsheet=1.8.2

Then it can be used in the project

use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;

$imageFilePath = './uploads/imgs/'; //Path of picture local storage
if (!file_exists($imageFilePath)) { //Recursively create if directory does not exist
    mkdir($imageFilePath, 0777, true);
}

try {
    $inputFileName = './files/1.xlsx';  //Excel file containing pictures
    $objRead = IOFactory::createReader('Xlsx');
    $objSpreadsheet = $objRead->load($inputFileName);
    $objWorksheet = $objSpreadsheet->getSheet(0);
    $data = $objWorksheet->toArray();

    foreach ($objWorksheet->getDrawingCollection() as $drawing) {
        list($startColumn, $startRow) = Coordinate::coordinateFromString($drawing->getCoordinates());
        $imageFileName = $drawing->getCoordinates() . mt_rand(1000, 9999);

        switch ($drawing->getExtension()) {
            case 'jpg':
            case 'jpeg':
                $imageFileName .= '.jpg';
                $source = imagecreatefromjpeg($drawing->getPath());
                imagejpeg($source, $imageFilePath . $imageFileName);
                break;
            case 'gif':
                $imageFileName .= '.gif';
                $source = imagecreatefromgif($drawing->getPath());
                imagegif($source, $imageFilePath . $imageFileName);
                break;
            case 'png':
                $imageFileName .= '.png';
                $source = imagecreatefrompng($drawing->getPath());
                imagepng($source, $imageFilePath, $imageFileName);
                break;
        }
        $startColumn = ABC2decimal($startColumn);
        $data[$startRow-1][$startColumn] = $imageFilePath . $imageFileName;
    }
    dump($data);die();
} catch (\Exception $e) {
    throw $e;
}

public function ABC2decimal($abc)
{
    $ten = 0;
    $len = strlen($abc);
    for($i=1;$i<=$len;$i++){
        $char = substr($abc,0-$i,1);//Get single character in reverse

        $int = ord($char);
        $ten += ($int-65)*pow(26,$i-1);
    }
    return $ten;
}

As you can see, the image is read and stored in the local server

PHPExcel

PHPExcel's method of reading content from Excel file is almost the same as phpspreadsheet. The note phpspreadsheet is written on the basis of PHPExcel. However, PHPExcel has been abandoned, so it is recommended to give priority to phpspreadsheet. If PHPExcel has been used in the original project, you can continue to use PHPExcel

use PHPExcel_IOFactory;
use PHPExcel_Cell;

try {
    $inputFileName = './files/1.xlsx';
    $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    $objPHPExcel = $objReader->load($inputFileName);
} catch (\Exception $e) {
    die('Error loading file:"'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
}

$sheet = $objPHPExcel->getSheet(0);
$data = $sheet->toArray(); //This method can't read the picture, so the picture needs to be processed separately
$imageFilePath = './uploads/imgs/'; //Path of picture local storage
if (!file_exists($imageFilePath)) {
    mkdir($imageFilePath, 0777, true);
}

//Processing pictures
foreach ($sheet->getDrawingCollection() as $img) {
    list($startColumn, $startRow) = PHPExcel_Cell::coordinateFromString($img->getCoordinates()); //Get the row and column of the picture
    $imageFileName = $img->getCoordinates() . mt_rand(1000, 9999);
    switch($img->getExtension()) {
        case 'jpg':
        case 'jpeg':
            $imageFileName .= '.jpeg';
            $source = imagecreatefromjpeg($img->getPath());
            imagejpeg($source, $imageFilePath.$imageFileName);
            break;
        case 'gif':
            $imageFileName .= '.gif';
            $source = imagecreatefromgif($img->getPath());
            imagejpeg($source, $imageFilePath.$imageFileName);
            break;
        case 'png':
            $imageFileName .= '.png';
            $source = imagecreatefrompng($img->getPath());
            imagejpeg($source, $imageFilePath.$imageFileName);
            break;
    }
    $startColumn = ABC2decimal($startColumn);
    $data[$startRow-1][$startColumn] = $imageFilePath . $imageFileName;

}
var_dump($data);

public function ABC2decimal($abc)
{
    $ten = 0;
    $len = strlen($abc);
    for($i=1;$i<=$len;$i++){
        $char = substr($abc,0-$i,1);//Get single character in reverse

        $int = ord($char);
        $ten += ($int-65)*pow(26,$i-1);
    }
    return $ten;
}

Reference article:

PHPExcel data import (including pictures)

PhpSpreadsheet import picture function