xlsxopentbstinybutstrong

OpenTBS for Excel MergeBlock does not push merged cells downward

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

enter image description here

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

enter image description here

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

enter image description here

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:

enter image description here

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

Solution

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