Skip to content
Advertisement

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

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:

enter image description here

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); 
}

?>

Advertisement

Answer

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;
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement