xlsx formula below multiple rows

Hi,

I am using OpenTBS for generating Excel files in a web application.
It works pretty well as long as I don't use formulas in my xlsx template
which are in a row below a row which expands to multiple rows when merging data into it.
If the cell containing the formula moves, Excel promts a warning that the file contains invalid data
and attempts to repair the file (deleted formula in /xl/calcChain.xml-Part).

So formulas do work, but only if they don't change their grid position. Any suggestions?


The template code for generating multiple rows looks like this:

The TBS version is:
- TinyButStrong - 3.8.1
- OpenTBS - 1.8.1-beta-2013-07-20

Thanks in advance

Hi Carsten,

There is a warning about this limitation in the XLSX example provided with the package.

It says :

> 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.

The SXLSX example also show a workaround for a simple Total formula.

Hi Skrol29,

Thank you for your reply!
In the example there are two formula examples.
The simple formula can only be put above the cells whose values are summed up.
The special formula is placed below the generated rows. It changes its own position after merging
and Excel doesn't show a warning. My formula looks similar to that one in the example, but my file generates a warning.
Is there anything I have to do on the PHP side? I already read the demo_merge.php and the demo_ms_excel.php
files but I couldn't find anything.

> the special formula is placed below the generated rows. It changes its own position after merging

Yes but this formula is using a special formula which doesn't use any reference to a cell address, but rather to cells relatively to the current one.

I noticed the syntax of the special formula, but using the special formula doesn't change anything.
Even if I use a formula which actually doesn't reference cells at all (not absolute nor relative) like
"=SUM(3;1)" (which should always be 4), Excel seems to have a problem if the formula changes its position (no matter if it references other cells or not).

Can you send to me an example that reproduce your problem ?

Hi Carsten,

I am having the same problem.
How did you manage it?

I am sure I am close to the solution, but can't figure it out.

Thanks

I have found out that the warning for corrupted subfile "/xl/calcChain.xml-Part" doesn't appears if a sheet is deleted after the merge is done. It seems a recalculation is done only if a sheet is deleted.

That's why the demo worksheet works OK.
But if you do a small merge without deleting a sheet, then the corruption appears.

Ok, I can reproduce the bug.
I'm working on a new release and I'll try to include fix for this.

You may find this usefull: http://openxmldeveloper.org/discussions/development_tools/f/17/p/2828/17032.aspx