Extracting pictures/images within an Excel file (xls) using PHP

12,607

Solution 1

Somebody, I don't know if it's yourself, has asked a similar question on the PHPExcel board... that I haven't got round to answering yet.

$objPHPExcel->getActiveSheet()->getDrawingCollection()

will return an ArrayObject of all the image objects in the active worksheet.

These objects will be either PHPExcel_Worksheet_Drawing or PHPExcel_Worksheet_MemoryDrawing objects: you can identify which using is_a(). You can then use the methods appropriate to that class (as described in the API) either to read the image data from file (for PHPExcel_Worksheet_Drawing objects) or directly from the PHPExcel_Worksheet_MemoryDrawing object itself. The getName() and getDescription() methods can be used to retrieve the relevant values fro the image object.

Note that it's also possible to have image objects associated with print headers:

$objPHPExcel->getActiveSheet()->getHeaderFooter()->getImages()

can be used to retrieve images from the header/footer. This is an array of PHPExcel_Worksheet_HeaderFooterDrawing objects. All the PHPExcel_Worksheet_Drawing methods can be used to extract the image file from these objects.

EDIT

Based on your code in the modified question:

$drawing->getName();

should give you what you need

Solution 2

I have seen the example of Reading Images from a worksheet, It is working fine but I customized the same example for my project requirement like bellow.

require 'vendor/autoload.php';

$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load("./excel.xlsx");

$worksheet = $spreadsheet->getActiveSheet();
$worksheetArray = $worksheet->toArray();
array_shift($worksheetArray);

echo '<table style="width:100%"  border="1">';
echo '<tr align="center">';
echo '<td>Sno</td>';
echo '<td>Name</td>';
echo '<td>Image</td>';
echo '</tr>';

foreach ($worksheetArray as $key => $value) {

    $worksheet = $spreadsheet->getActiveSheet();
    $drawing = $worksheet->getDrawingCollection()[$key];

    $zipReader = fopen($drawing->getPath(), 'r');
    $imageContents = '';
    while (!feof($zipReader)) {
        $imageContents .= fread($zipReader, 1024);
    }
    fclose($zipReader);
    $extension = $drawing->getExtension();

    echo '<tr align="center">';
    echo '<td>' . $value[0] . '</td>';
    echo '<td>' . $value[1] . '</td>';
    echo '<td><img  height="150px" width="150px"   src="data:image/jpeg;base64,' . base64_encode($imageContents) . '"/></td>';
    echo '</tr>';
}

OutPut: enter image description here

I have created the GitHub demo project "phpspreadsheet-Reading-Images-from-an-Excel-File" for future reference.

Github Link: https://github.com/rajaramtt/phpspreadsheet-Reading-Images-from-an-Excel-File

Share:
12,607
SammyBlackBaron
Author by

SammyBlackBaron

SOreadytohelp

Updated on September 15, 2022

Comments

  • SammyBlackBaron
    SammyBlackBaron over 1 year

    I have a spreadsheet that I would like to import using PHP. I can import the cell data using PHPExcel, but can't figure out how to use images from within the spreadsheet.

    Is there a way of doing this and then using the images within PHP to save to the server etc?

    Many thanks for the help! :)


    Update:

    @mark-baker - thank you so much for your help with this!

    I have used the code below on a test XLS file with one JPG:

    $objPHPExcel = PHPExcel_IOFactory::load("SH.xls");
    
    foreach ($objPHPExcel->getActiveSheet()->getDrawingCollection() as $drawing) {
        if ($drawing instanceof PHPExcel_Worksheet_MemoryDrawing) {
            ob_start();
            call_user_func(
                $drawing->getRenderingFunction(),
                $drawing->getImageResource()
            );
            $imageContents = ob_get_contents();
            ob_end_clean();
        }
    }
    

    I think I can then output JPEG headers and the contents of $imageContents to show the image.

    How would I get the actual name of the image in the spreadsheet though eg "Picture1"? Is this possible with PHPExcel_Worksheet_MemoryDrawing?

    I can't thank you enough!

  • SammyBlackBaron
    SammyBlackBaron almost 13 years
    thank you so much for taking the time to reply to me - I really appreciate it! I've updated my question - would you be good enough to have a look at that for me?
  • SammyBlackBaron
    SammyBlackBaron almost 13 years
    I tried $drawing->getName(); but it doesn't return anything, just blank. Is getName() only used for PHPExcel_Worksheet_Drawing objects and not PHPExcel_Worksheet_MemoryDrawing objects?
  • Mark Baker
    Mark Baker almost 13 years
    Name should be set if you're reading the images from an Excel 2007 file, but name isn't set if the images are read from an Excel BIFF file... I don't know if it's even available to be read within the BIFF without checking the spec.