How to read a excel file with merged cells in php?

Question

I wrote a php script that allows me to read an uploaded excel file and insert all the images contained in a folder by renaming them with the cell values "style" and "color" to have style_color.jpg. The script works fine but if I upload an xlsx file containing merged cells like this:

images with the same "style" doesn't work.The tool will just put the style on the first image. I would like the first two images to be called :

SCJEG4_1041
SCJEG4_0049

How can I read these merged cells?

<?php

//uploaded xlsx file recovery
$xlsx="C:/wamp64/www/Extract_pictures_Excel/xlsx_files/".date('Y_m_d H-i-s')."_images.xlsx";
move_uploaded_file($_FILES["mon_fichier"]["tmp_name"],$xlsx);

require_once 'PHPExcel/Classes/PHPExcel/IOFactory.php';
$objPHPExcel = PHPExcel_IOFactory::load($xlsx);

//Unique name folder for the pictures
$dirname = uniqid();
mkdir("C:/wamp64/www/Extract_pictures_Excel/pictures_folders/$dirname/");

//reading the xlsx file
$sheet = $objPHPExcel->getActiveSheet();
foreach ($sheet->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();
        switch ($drawing->getMimeType()) {
            case PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_PNG :
                $extension = 'png'; break;
            case PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_GIF:
                $extension = 'gif'; break;
            case PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_JPEG :
                $extension = 'jpg'; break;
        }
    } else {
        $zipReader = fopen($drawing->getPath(),'r');
        $imageContents = '';
        while (!feof($zipReader)) {
            $imageContents .= fread($zipReader,1024);
        }
        fclose($zipReader);
        $extension = $drawing->getExtension();
        $chemin = "C:/wamp64/www/Extract_pictures_Excel/pictures_folders/$dirname/";
    }    
    
    //retrieving cell values for the images name
    $row = (int) substr($drawing->getCoordinates(), 1);        
    $stylecode = $sheet->getCell('H'.$row)->getValue();
    $colorcode = $sheet->getCell('E'.$row)->getValue();
    $finalname = $stylecode.'_'.$colorcode;
    $myFileName = $chemin.$finalname.'.'.$extension;
    file_put_contents($myFileName, $imageContents); 
}

?>

It may be that as you read them in order and if they MUST have a value, you can assume that the last non-blank value is the style, so perhaps `$stylecode = $sheet->getCell('H'.$row) ?: $stylecode;` could do the trick. — Nigel Ren, Oct 01 '20 at 18:53
I just tested with your answer but it doesn't work, each time the second photo is skipped, it will put style_color on the first one and pass to the next merged cell without taking the second photo @NigelRen — Patrick62, Oct 01 '20 at 19:02
Did you try it with `$sheet->getCell('H'.$row)->getValue() ?: $stylecode;` — Nigel Ren, Oct 01 '20 at 19:08
Yes I just changed my line by $stylecode = $sheet->getCell('H'.$row)->getValue() ?: $stylecode; and it works very well! Thanks a lot for your help — Patrick62, Oct 01 '20 at 19:16

score 1 · Accepted Answer · answered Oct 01 '20 at 19:36

1

If you can assume that you read the rows in sequence, you can get the cell value and if the cell is blank, use the previous value. This code use ?: to say if it's blank, use $stylecode ...

$stylecode = $sheet->getCell('H'.$row)->getValue() ?: $stylecode;

answered Oct 01 '20 at 19:36

Nigel Ren

56,122
11
43
55

Thank for your explanations – Patrick62 Oct 01 '20 at 19:48

How to read a excel file with merged cells in php?

1 Answers1