PHP: spreadsheet summary with OpenTBS?

Question

I'm using the PHP library TinyButStrong with the plugin OpenTBS to merge existing Excel (or OpenOffice Calc) spreadsheets with data from a database.

This works fine, but I can't figure out how to define summaries for a column.

For example , I define cell A1 as the cell that merges with the data. After the merge, rows are inserted for A2, A3, etc.

The problem is that I don't know beforehand how many rows are going to be inserted, so it's not an option to leave 10 rows empty, and define A11 as the summary field, because it could also be 100 rows of data, and I want the summary directly below the last row of data.

Any ideas?

Answer 1

OpenTBS is not able to follow a cell when a sheet is merged because any cell can be moved, deleted and duplicated at any time during the merging.

Nevertheless, I can suggest two solutions.

Easy solution:

Decide to place the total above the zone to sum. And use a formula that covers the zone over it can never be.

Example with an ODS file (OpenOffice Spreadsheet):
For Excel, replace "odsNum" with "xlsxNum", and "table:table-row" with "row".

A1: = SUM(A2:A20000)
A2: [a.amount;block=table:table-row;ope=odsNum]

This way the ODS formula stays correct what ever the block "a" can be extended to.

Elaborated solution:

Use a formula that references to the cell just above itself.

Example with an ODS file (OpenOffice Spreadsheet):
For Excel, replace "odsNum" with "xlsxNum", and "table:table-row" with "row".

A1: [a.amount;block=table:table-row;ope=odsNum]
A2: =SUM( A1 : INDIRECT(ADDRESS(ROW()-1;COLUMN())) )

The same but with a fixed column:

A1: [a.amount;block=table:table-row;ope=odsNum]
A2: =SUM( A1 : INDIRECT("A" & (LIGNE()-1))) )