Inconsistency concerning arrays

English
, ,
#1

“The API / UNO do not have an array concept.” However: the API uses the term “array” in a somewhat wayward manner as part of compound names such as “DataArray” or “FormulaArray” for certain sequences of elements that are themselves sequences of equal length. This representation of content is certainly often advantageous. However, Basic has an “array concept” and UserDefinedFunctions written in Basic receive data from cell ranges, or calculated intermediate results as two-dimensional arrays if so required. For the output of structured data by a UDF, the software now demands -without regard to concepts of the API- the 2D structure, and does not accept a structure corresponding to a DataArray. If results were calculated in this form, the support for the necessary conversion is missing.
Who can explain this to me well?
Did I miss related services?

To better explain my concern I now attach an example .ods also containing a bit of code for actual usage in the module ‘global’:
disaskQuestion88179explainingExample.ods (11.9 KB)
disask88179example.ods (19.3 KB)

Actually the fact that Calc UDF can’t return (to a sheet) the results they got by FunctionAccess calls to array functions was what remined me of the old issue: There are functions I would like to be able to modify concerning the “HOST-FOO-BAR” settings which unfortunately are global to (at least) any document while they should be modifiable per call.

#2

Edit: A more dynamic approach

Sub WritePlainArray()
REM define dimensions
Dim a(3,2)
REM define  target position
sh = 0
r = 24
c = 4
for i = 0 to uBound(a,1)
	for j = 0 to uBound(a,2)
		a(i,j) = i &","& j
	next j
next i
sh = ThisComponent.Sheets(sh)
rg = sh.getCellRangeByPosition(c,  r, c +ubound(a,2), r + ubound(a,1))
rg.setDataArray(a)
End Sub
#3

Sorry. I often use rangeObj.setDataArray(), and I know that it also (strangely) accepts 2D-arrays from Basic. However, this doesn’t touch the current issue. It’s about the principle on the one hand, but on the other hand mainly about the case where I get a function result via a FunctionAccess object as a structure like a DataArray, but the sheet refuses to accept it as the return values from an UDF (NOT Sub).
You may see this more clearly if you play a bit with the attachment to my question.

The intention to get functions accepting modifications of their working otherwise ruled by the “HOST-FOOBAR” settings per call was already behind my approach to simplify (and make more efficient) the usage of FunctionAccess. (This part works nicely, and I also know how to get array-evaluation).

#4

It is as it is. All supported programming langauges support nested arrays, therefore the API returns nested arrays.
The “mechanism” which allows user defined functions in Basic without component registration might be a hack. It should return nested arrays. Possibly there was an intention to not irritate Basic coders.

#5

I don’t find a source for this quote. I see a different phrase, though, in the TypeClass enum documentation:

UNOIDL does not have an array concept

It doesn’t tell anything about “API”, only about UNOIDL.

I really do not quite see what the issue here is, and looking briefly at the attached spreadsheet, and also at the two modules with code in it, didn’t provide a description of the problem like “here I expect this, but get that”. Please clarify where there could be some expectation that is failed.

#6

Thanks for the response.
For what I had in mind concerning the “array concept” I need to search.
Am I wrong and there are arrays (dimension >= 2) implemented for API objects?

The example file I had attached was very poor. Apologies! I will replace it by a hopefully better one only containing one (not realistic) example task in two versions, one simple, but not working as expected (or hoped for), one working, but needing annoying and inefficient parts.

My expectation was that an UDF (and the sheet where it is called from) accept the DataArray structure when assigned as function result. This in specific as array-like results returned by FunctionAccess.callFunction() are nested like DataArrays
This expectation failes, as demonstrated now clearly.

#7

IIUC, the perceived problem is that an array formula’s Basic UDF gets an array of arrays, but must return a 2D array.

I would also think it’s inconsistent. One fictional rationale could be that the input represents cellular data, while output represents abstract array … but one can always invent funny excuses.

Returning a 2D array as data in a 2D cell range
#8
  1. ITYUC. But probably not to the full extent.
  2. If you choose “funny” ways of thinking, your results are similar to what I get the funny way.
  3. An UDF getting passed an array-type parameter always gets it passed 2D - or at least only allows for access to the elements in 2D manner.
  4. A Calc function called via FunctionAccess expects it the same way.
  5. BUT a Calc function called via FunctionAccess returns the result, if an array, as nested strips.
  6. BUT such a result is not accepted if passed to a next CalcFunction-call via FunctionASccess, It also can’t be returned to the sheet as the formula result.
  7. BUT A Calc formula containing a CalcFunction-call as a subexpression and getting back an array for it, can pass it to the next function call. Is the gotten values 2D thus?
  8. How a passed argument “really looks” I don’t know. I wouldn’t start a Bruce2-discussion here. The access is “transparent” in the funny sense this term is misused nowadays. (We constantly get lumps by bumping into transparent windows. True?)
  9. Would you want to get suggestions from an “informed layman” how the inconsistencies might be resolved?
#9

You may have read my long comment on Mike’s last post.
If so you will understand that my concern isn’t about one or another case where I need a workaround due to my bad habit of using silly Basic.
In fact I don’t think it’s a Basic issue. On the other hand this somehow ticklish topic might not be the optimal point to start my Python-age.
Therefore: Could you spare the time to move my example to Python and to compare the behavior/results?

#10

Python does not support multi-dimensional arrays. It always works with nested arrays, therefore there is no such inconsistency.
LO’s Python bridge does not support macro scripts serving as user defined functions. For user defined functions you need to build an add-in (extension with sheet functions). These functions appear in the formula wizard but not in the macro dialogs.
Years ago I have written a Sudoku add-in that takes a 9x9 array and returns a 9x9 array utilizing a solver algorithm by Peter Norvig: Solving Every Sudoku Puzzle who is a programmer by his trade.
Writing the Python code involved a most simple type conversion from 9x9 data array to 81 characters and backwards plus implementing the c.s.s.sheet.AddIn service. I have no understanding about what the solver algorithm does, except that it works pretty well.
The complex part – which I do not understand neither – is described here: http://www.biochemfusion.com/doc/Calc_addin_howto.html. Nevertheless I was able to build my add-in like I can cook by recipe. I learned nothing from it.

I have never seen any add-in implementing LibreOffice: XVolatileResult Interface Reference which might be necessary for time zone aware date-time functions.

#11

So far I knew.

Thanks for this clarification.
But Python (the bridge) surely allows for the instantiation of a FunctionAccess service. Can this service accept data from a CellRange and call array functions working with them (Regard the “optional property”!). Can a next FunctionAccess.callFunction("MSOME", Array(intermedResult1, intermedResult2)) then accept the intermediary results. The question if it can pass an array result directly to a calling position of a sheet is withdrawn due to your calrification.

#12

I can’t repro this. In my testing, converting a 2D array into array of arrays, and passing to MMULT, succeeds.

#13

Thank you for your patience, and please bear with me for my lack of care. I really thought I had clearly demonstrated the problem I mentioned, but cannot reproduce it now. In fact, Calc functions called via FunctionAccess can handle quasiarrays that are sequences of strips of equal length, and presumably that’s what they do internally anyway. A 2D array is probably converted automatically.
The only remaining inconsistency is the fact that the inverse conversion to output the result to a spreadsheet is missing. It is not done automatically. But I can’t find a service to do it either. A formula will of course know its output range (after the calculations if necessary), and can then output directly to the associated DataArray. A UDF cannot do that.
Again, sorry for wasting your time.
The background of my investigations and the question was an attempt to get functions allowing to change document-wide settings with effect for the single call with the help of UDF. Many of these functions require array-evaluation and should be able to output arrays.
For interested visitors: See new attachment:
disask88179exampleRe3.ods (24.7 KB)

#14

First, please don’t apologize.
It seems that it would be nice to have a dedicated Basic utility functions for the array conversion, similar to ConvertToURL, CDateFromUnoTime, and so on… Would you please file an enhancement request?

#15

solved:
numpy_matmul

!! works also without explizit convert into np.array #7 #8 !!

#16

You may not be too surprised if I hint you to the fact that Calc comes with MMULT() and many standard functions for the usage with matrices.
Another expectable issue is that I don’t understand in what way the shown code will help to solve a problem with user defined functions.
Was “Villeroy” wrong with his statement, that UDFs written in Python can’t be made available for sheet formulas “on the fly”?
After all the MMULT by FunctionAccess only was a otherwise useless example solely made for the demonstration of the problem with returning an array-strucured (nested!) result to a sheet.
I surely expected that Python has means (a module) for calculation with matrices.

#17

How could I be surprised when I already use the “numpy” counterpart to “mmult” in the example to demonstrate that it would not be necessary in python to “import” regular calc functions from an udf?

Yes&No: because they would also be possible in python, if the developers decided to add a dedicated module or folder to Calc’s search path. ( in other words: its not the fault of python …)

#18

Once again, let’s agree to disagree.
In this case, however, it’s not about any details, or the value of the LibO API (compared to alternatives offered by any programming language), but about the actual meaning of the question asked.
Even though it’s been a while, I’ve dealt with a few programming languages and other “continents” of the IT world.
Inconsistencies existed everywhere, and of course they could not be solved by fundamental preference for an alternative tool.
Injury risks in skiing do not disappear because you decided to hence only play volleyball. One is no longer personally affected by them, but in volleyball there will be risks that did not occur in skiing. (I have done both, by the way).

#19

This is correct. Not “Calc’s search path”, of course; but the code is missing that could allow anything other than Basic in Calc’s UDF: ScCompiler::ParseMacro and ScInterpreter::ScMacro handle that, and it only query SfxObjectShell::GetBasic and its content.

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.