Spreadsheet documents and merged rows with formulas

Hello,

I'm wondering to work both with .ods templates and .xlsx templates.

I have a bunch of rows that are merged within the template. On that rows I have set up some columns with formulas, e.g. :

         A       |     B       |    C        |  D
1|  Number | Value     | Quantity | TOTAL
2|  10         |    40      |      2       |  =$B2*$C2
3|  12         |    55      |      1       |  =$B3*$C3
4|  13         |    10      |      5       |  =$B4*$C4

And the template is something like:

         A                                       |     B                                  |    C                                       |  D
1|  Number                                  | Value                                | Quantity                               | TOTAL
2|  [item.number;block=tbs:row]  | [item.value;ope=tbs:num] | [item.quantity;ope=tbs:num] |  =$B2*$C2

The resulting document I'm getting is:

         A       |     B       |    C        |  D
1|  Number | Value     | Quantity | TOTAL
2|  10         |    40      |      2       |  =$B2*$C2
3|  12         |    55      |      1       |  =$B2*$C2
4|  13         |    10      |      5       |  =$B2*$C2


Note that above example of resulting spreadsheet didn't update the formula on column D.
The expecting result is to have the number of column updating to the row index correctly, i.e. B2, B3, B4 ..... for each row.


How can I do that on both ODS and XLSX ?

I'm currently testing mainly on ODS documents.

I have looked at libreoffice source code, and they seem to not have implemented one of the features called forceFullCalc.
Nevertheless, I don't know if that one takes effect on what I'm trying to do.
The libreoffice code I took a look is:
https://github.com/LibreOffice/core/blob/74df76d6ac91fae02ef0d20ce863cb753ba953cb/sc/source/filter/excel/excdoc.cxx#L104

Thank you very much !

Hi,

In the XLSX example given with OpenTBS you've got this note:
----
You may consider the following before building your own Microsoft Excel template:
* Do not use a formula in a cell that may have its position changed after the merge (for example under a TBS block). Otherwise Excel will raise an error message.
    This is because the location of formulas are saved a second time in another sub-file for the order of evaluation.
* If a formula uses a reference to a cell that has moved during the merge, then the reference will not be arraged to be the new cell reference.
----

So the solution is to calculate the result of the formula at the PHP side, or to write formula that have only relative reference (complicated to do but there is an example in the XLSX example).

Hello, thanks for the answer =D !

I just came here to know if is was a known issue.

Since it is, I've came with this solution for now:

         A                                       |     B                                  |    C                                       |  D
1|  Number                                  | Value                                | Quantity                               | TOTAL
2|  [item.number;block=tbs:row]  | [item.value;ope=tbs:num] | [item.quantity;ope=tbs:num] |  =INDIRECT(CONCAT("B";ROW()))*INDIRECT(CONCAT("C";ROW()))


And voila ! =D
No changes on TBS or OpenTBS, just using excel regular formulas. The functions mentioned are all implemented both on Microsoft Excel and LibreOffice Calc.

It's also possible to use R1C1 style on INDIRECT function. But I prefer to use A1 because the column letter appears in the formula.

Thanks again, hope this post helps someone in the future.