OpenTBS for Excel MergeBlock does not push merged cells downward

Question

When MergeBlock with openTBS for Excel, I notice that when populating rows with data, the merged cell that is under the row placeholder in the excel template does not get pushed down. it instead stay on the same row address that makes the populated data have formatting issue. How to solve this

EDITED:

Adding the the code for clarity. I actually took the OpenTBS public demo sample for xlsx since I wanted to know if the problem reproducible in the sample, and it was.

All the demo material can be found here, just choose the xlsx template and you can download the template and the php code: https://www.tinybutstrong.com/opentbs.php?demo

Basically just a regular merge rows scenario, that is where we write our placeholder in one row, hoping that it would get populated by our array of data. Here's the template

As you notice in the picture I added a Merged column located in Cell 22D, just two rows below the our data placeholder.

I then run the PHP code to merge my data, the code is the same with OpenTBS demo for xslx

// A recordset for merging tables
$data = array();
$data[] = array('rank' => 'A', 'firstname' => 'Sandra', 'name' => 'Hill', 'number' => '1523d', 'score' => 200,
    'visits' => 15, 'email_1' => 'sh@tbs.com', 'email_2' => 'sandra@tbs.com', 'email_3' => 's.hill@tbs.com');
$data[] = array('rank' => 'A', 'firstname' => 'Roger', 'name' => 'Smith', 'number' => '1234f', 'score' => 800,
    'visits' => 33, 'email_1' => 'rs@tbs.com', 'email_2' => 'robert@tbs.com', 'email_3' => 'r.smith@tbs.com');
$data[] = array('rank' => 'B', 'firstname' => 'William', 'name' => 'Mac Dowell', 'number' => '5491y', 'score' => 130,
    'visits' => 16, 'email_1' => 'wmc@tbs.com', 'email_2' => 'william@tbs.com', 'email_3' => 'w.m.dowell@tbs.com');

// Merge data in the first sheet
$TBS->MergeBlock('a,b', $data);

And the result come out as this

Here we can see all the cells content and format that was previously located under the placeholder get pushed down after the merge, EXCEPT the merging format. Since our data contains 3 entries, it filled the row 20 to 22, and the merged cells content was pushed down from row 22 to 24. But merging format is not moved, it stays in Cell 22D, causing the 3rd entry gets distorted. In addition our "This is a merged columns" content does not really located in a merged columns.

This is the expected result:

In the correct scenario the data is displayed as is, and the merged cell content and format is also displayed unchanged, only relocate from row 22 to 24. Hope this helps

Answer 1

From @Skrol29 answer i understand the current situation and wrote a simple function for my case since I need to work with an excel with dozens of merged cells. What my function did is to push all the merged cells located entirely under the placeholder row

function pushMergedCellsDown($TBS, $placeholderRow, $dataCount) {

    if ($dataCount == 1) {
        return; // No need to move anything size only 1
    }

    $pushDistance = $dataCount - 1; // this also means if $dataCount=0 then the merges will be shifted up
    $pattern = '/<mergeCell ref="([A-Z]+)(\d+):([A-Z]+)(\d+)"\/>/';

    $replaceMap = [];

    // Find all merged cells in the XML
    if (preg_match_all($pattern, $TBS->Source, $matches, PREG_SET_ORDER)) {
        foreach ($matches as $match) {
            $colStart = $match[1];
            $rowStart = intval($match[2]);
            $colEnd = $match[3];
            $rowEnd = intval($match[4]);

            // Check if any mergeCell crosses or is on the placeholder row
            if ($rowStart <= $placeholderRow && $rowEnd >= $placeholderRow) {
                throw new Exception("Merge cell crossing placeholder row detected: {$match[0]}");
            }

            // Only process mergeCells entirely below the placeholder row
            if ($rowStart > $placeholderRow) {

                $newRowStart = $rowStart + $pushDistance;
                $newRowEnd = $rowEnd + $pushDistance;
                $newTag = "<mergeCell ref=\"{$colStart}{$newRowStart}:{$colEnd}{$newRowEnd}\"/>";

                $replaceMap[$match[0]] = $newTag;
            }
        }
    }

    if ($replaceMap)
        $TBS->Source = strtr($TBS->Source, $replaceMap);
}

the function takes 3 parameters: $TBS the opentbs object, $placeholderRow the row where our data placeholder is located, and $dataCount which is the size of our data.

for my example case, the usage is like this

// Merge data in the first sheet
$TBS->MergeBlock('a,b', $data);

pushMergedCellsDown($TBS, 20, count($data));

Appreciated your work on openTBS library @Skrol29 ^^

Answer 2

Unfortunately OpenTBS cannot move merged cells for now (OpenTBS version 1.12.1). This is because in an XLSX sheet, merged cells are not registered as an attribute of the cells, but as a fixed list of cells at the end of the sheet data. Thus it becomes hard (but not impossible) for OpenTBS to follow the moved cells.

Waiting for such a feature, you can manually move merged cells using this tip:

$old_merged_range = 'C22:D22';   
$new_merged_range = 'C24:D24';   
$TBS->Source = str_replace('<mergeCell ref="' . $old_merged_range . '"/>', '<mergeCell ref="' . $new_merged_range . '"/>', $TBS->Source);