CODEWITHSUNDEEP
phpexcelphpexcel
user14004334
user14004334

Reputation:

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

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 : enter image description here

And I would like the empty lines to be filled like this: enter image description here

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

?>

Upvotes: 0

Views: 252

Answers (1)

Markus Zeller
Markus Zeller

Reputation: 9153

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

Upvotes: 1

Related Questions