Formula in calc

[bb39700]

Use ods to make calc report
No problem with the creation of the report, but the sum in the result is static.
Now i try to use the sum() calc fonction to make the sum of lines created with python loop, and so to have dynamic sum.

1) i try to put the sum() in a cell, but the loop insert line without changing the range in the fonction.
2) don't find the syntax in python code to put fonction in cell and not text.

Is someone know the syntax to use in python code to put the sum function in the report, or the way in ods file to change the range of sum function when inseerting line ?

[emearg]

Hi, You need to use python code, not calc formulas. In documentation sum function is described, or for more complex totals, generally I create a custom parser which holds the totals and then refer to them. Usually a dictionary or a list and have a helper function which returns a string for example

total(0) or total('assets')

def total(self, index)
return '%.2f' % self.totals[index]

[bb39700]

I already do it. But the sheet is static.

The goal is to make sheet with value extract from openerp, and when i modify values in the sheet, i want that all sum and calculated field are recalculate (the normal use of sheet).

[sraps]

Hi!

I already do it. But the sheet is static.

The goal is to make sheet with value extract from openerp, and when i modify values in the sheet, i want that all sum and calculated field are recalculate (the normal use of sheet).

OOo has some differences from MS Excel, some of them are great, some... I would not say so. One of, no so great differences, is that OOo not always update it's cell references in itself (the same with OOo Writer, yes Writer can do some calculations and charts as well, in opposite to Word). As Aeroo is manipulating the document directly, and it is not purposely developed spreadsheet processor, it does not know of the cell references.

Yes someday it would probably be possible to implement this feature, still we have to be aware that all of these tiny tricks consume 90% of the performance. Aeroo in itself is as fast as ReportLab RML reports are, in creating ODF template -> ODF document. The drawback in performance is ODF -> PDF conversion. So it wouldn't be wise to sacrifice this reasonably good performance to some features.

One tip, if you really desire, some spreadsheet features, like state of the art formulas or charts, you have to reserve the number of rows and columns to fit all your records and keep inside the reference boundaries.

P.S. At the moment there is no feature to write cell functions from python.

BR
Kaspar

[jluitz]

I know this is a very old thread, but I found a "tricky solution" to this problem that I want to share:

Instead of referencing the cell to be used for a formula directly, you can construct the relative cell address as follows:

=CONCAT(CHAR(COLUMN()+64-1);ROW())

this gives the address left-neighbor cell,

to get the value of that cell use:

=INDIRECT(CONCAT(CHAR(COLUMN()+64-1);ROW())))

Indeed for bigger formulas this is not easy to read, but at least it works.

All the best
Joachim