[FIXED] How to fill empty lines in an Excel file ummerged in php?

Issue

I have this code that allows me to read an xlsx file containing merged celles and unmerged them to be able to process them. The xlsx unmerged file is like this :

And I would like the empty lines to be filled like this:

Each time the 'Style' value changes the empty cells are filled with the values of the lines above. I tried with foreach($sheet->getMergeCells() as $cells){ $sheet->setCellValue($cells);} but it doesn't work .

How do I solve my problem?

<?php

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

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

//reading the xlsx file
$sheet = $objPHPExcel->getActiveSheet();
echo 'before unmerge: ', count($sheet->getMergeCells()), PHP_EOL;

    foreach($sheet->getMergeCells() as $cells)
        {
            
            $sheet->unmergeCells($cells);
            foreach($sheet->getMergeCells() as $cells)
            {
                $sheet->setCellValue($cells);
            }
        }

echo 'after unmerge: ', count($sheet->getMergeCells()), PHP_EOL;

$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); 
$objWriter->save('./unmerged_files/unmerged.xlsx');

?>

Solution

Get the value of the first cell of the range. Because it is a 2-dimensional array, we can get the first element via [0][0].

Then we get all cells and most important, the CellReference by setting last param to true. This will make the 2-dimensional array having first being row keys and seconds being column keys.

Having that data and the value, we can unmerge the cells and set all the cells in the range with the same value.

$row = 0;
$skipRows = [1, 3];
foreach($sheet->getMergeCells() as $range) {
    $value = $sheet->rangeToArray($range)[0][0];
    $cells = $sheet->rangeToArray($range, null, true, true, true);
    $sheet->unmergeCells($range);
    if(in_array(++$row, $skipRows)) continue;
    if(!$value) continue;

    foreach($cells as $row => $columns) {
        foreach(array_keys($columns) as $column) {
            $sheet->setCellValue("$column$row", $value);
        }
    }
}

Answered By - Markus Zeller

Answer Checked By - Katrina (PHPFixing Volunteer)