We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

From cell range to array [closed]

asked 2017-02-25 11:08:41 +0200

Fede gravatar image

updated 2017-02-25 11:09:12 +0200


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

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-09-25 17:08:51.032068


Thank you all :))

Fede gravatar imageFede ( 2017-02-26 14:40:26 +0200 )edit

3 Answers

Sort by » oldest newest most voted

answered 2017-02-25 12:11:59 +0200

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

edit flag offensive delete link more


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 gravatar imageLupp ( 2017-02-25 12:25:02 +0200 )edit

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

Mike Kaganski gravatar imageMike Kaganski ( 2017-02-25 12:29:09 +0200 )edit

@Mike Kaganski: 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.

Lupp gravatar imageLupp ( 2017-02-25 12:33:11 +0200 )edit

@Mike Kaganski: "... 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.

Lupp gravatar imageLupp ( 2017-02-25 12:40:21 +0200 )edit

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

Mike Kaganski gravatar imageMike Kaganski ( 2017-02-25 12:47:20 +0200 )edit

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.

Lupp gravatar imageLupp ( 2017-02-25 12:53:41 +0200 )edit

answered 2017-02-25 12:19:25 +0200

Lupp gravatar image

updated 2017-02-25 12:20:24 +0200

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.

edit flag offensive delete link more

answered 2017-02-25 13:46:26 +0200

karolus gravatar image

updated 2017-02-25 13:48:18 +0200


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 )))
edit flag offensive delete link more

Question Tools

1 follower


Asked: 2017-02-25 11:08:41 +0200

Seen: 2,569 times

Last updated: Feb 25 '17