Ask Your Question
2

From cell range to array

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

Fede gravatar image

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

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

edit retag flag offensive close merge delete

Comments

Thank you all :))

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

3 Answers

Sort by » oldest newest most voted
1

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

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

edit flag offensive delete link more

Comments

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
2

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
1

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

karolus gravatar image

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

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 )))
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 1,408 times

Last updated: Feb 25 '17