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