From cell range to array

Hello!

I would like to pass from cell range to array
but this way is incorrect:

oCellRange = oSheet.getCellRangeByName(“max_torque_triang”)

svc = createUnoService( “com.sun.star.sheet.FunctionAccess” )

T_pm = svc.callfunction(“MAX”,oCellRange.data)

can you help me?

Thank you

Good Life

Thank you all :))

If a function is called by a FunctionAccess object the actual parameters (even if only 1) are always passed as an array. You have to write
T_pm = svc.callfunction("MAX", Array(oCellRange.Data)).

However, I am afraid you will not get what you expext as soon as there is a (at least one) cell in the range not containing a number and there is no positive value in the range.

For each cell originally empty or containing text the array property oCellRange.Data will contain a number of format Double, namely 2.2250738585072E-308 which is specified to represent NAN (not a number) for Calc. Max will nonetheless treat it as a number and therefore return this as the result under the above mentioned conditions.

I do not know how Calc is handling this if calling MAX for a formula evaluation. The evaluator surely has to preprocess the parameter(s) somehow. There will be a routine for it in the core code. (Do you know that the parameters of MAX are not specified as a list of arrays but as NumberSequenceList in OpenFormula documents?)

As I love full control and (technical) explicitness anyway I would prefer writing a MyMAX function in BASIC over workarounds using standard MAX.

Use oCellRange.getDataArray() or oCellRange.getFormulaArray()

The oCellRange.DataArray will contain empty text instead of the NAN representative for empty (blank) cells of the range and the original text if any. MAX will refuse to accept texts. if called by FunctionAccess, I’m afraid.

@Lupp: Of course; OP should use iteration instead.

@mikekaganski: Just tested. The oCellRange.FormulaArray is accepted (I do not understand the difference made here. Do you?) but MAX will return 0 now under the above mentioned conditions.

@mikekaganski: “… use iteration …”.
Yes. In addition there were cases where I needed to get an array of maximiums calculated for some equally dimensioned arrays on the parameter positions (or rowwise maximums) under array evaluation. The standard function cannot do this for you.

FormulaArray returns strings (unlike DataArray, where Variants returned IIRC), of course besides evident fact that FormulaArray will return formulas instead of values.

Can no longer edit my second comment here. Want to replace the final part by:
… but MAX will return 0 (zero) now under the above mentioned conditions.

Hallo

the difficulties are:
…you need to “flatten” the nested …array
…you need to remove any Text (maybe empty Text) from the …array

fortunately python exists, and there’s no need to stuck with basic.

from itertools import chain

flat = chain.from_iterable

def maxtest():
    doc = XSCRIPTCONTEXT.getDocument()
    sel = doc.CurrentSelection
    dataarray = sel.DataArray
    print( max( entry for entry in flat( dataarray ) if isinstance( entry, float )))