How to enter values into 2D arary / range

zvegelj · February 19, 2022, 10:29am

I’m having trouble to populate range of cells with formulas. I know I could do it with individual statements, but would like to do it in one line if possible.
I’d like to populate formulas into the range A1:B3
Following doesn’t work:

sDoc.SetFormula("Sh1.A1:B3", Array(Array("=A5*B5", "=A6*B5", "=A7*B6"), Array("=A8*B5", "=B5*C5", "=B5*C6")))
REM  In two rows works fine:
`sDoc.SetFormula("Sh1.A1:A3", Array("=A5*B5", "=A6*B5", "=A7*B6")
`sDoc.SetFormula("Sh1.B1:B3", Array("=A8*B5", "=B5*C5", "=B5*C6")

I’d like to avoid entering each element separately also if possible ( range(0, 0) = “A5*B5” …)

KamilLanda · February 19, 2022, 11:31am

Sub setFormulasToRange
	dim oDoc as object, oSheet as object, oRange as object
	oDoc=ThisComponent
	oSheet=oDoc.Sheets.getByName("List1") 'get named sheet
	oRange=oSheet.getCellRangeByName("A1:B3") 'named range
	oRange.setFormulaArray( Array( array("=A5*B5", "=A8*B5"), array("=A6*B5", "=B5*C5"), array("=A7*B6", "=B5*C6") ) ) 'Array( array(cells in the row1), array(cells in row2), array(row3) ) 
	oRange=oSheet.getCellRangeByName("E1:E3")
	oRange.setFormulaArray( Array( array("=A8*B5"), array("=B5*C5"), array("=B5*C6") ) )
End Sub

zvegelj · February 19, 2022, 5:20pm

Thanks @KamilLanda That works
so SetFormula can be used only for 1D array? (ranges within one column or one row)?

sDoc.SetFormula("Sh1.A1:A3", Array("=A5*B5", "=A6*B5", "=A7*B6")

joshua4 · February 19, 2022, 5:33pm

No. KamilLanda is not saying that. It is just that for setFormulaArray a 2D array is an array of arrays.

I would look at it more like this: BASIC sees ranges as 2D arrays and interprets “2D” by accessing a single entry as MyArray(3,2). This is the way a person used to spreadsheets would think about it. But UNO (the system underlying the LO API) sees ranges as 2D arrays and interprets “2D” by accessing a single entry as MyArray(3)(2). This is the way a person used to any of many different programming languages would think about it. The “internal” structure of the array in memory may or may not somehow reflect either way of doing things, although the fact that you can only ReDim Preserve the last dimension of a BASIC array is a big hint about the internal structure (originally) used by BASIC.

There are caveats. The best way to learn about how UNO is using arrays is to snoop around using the MRI tool.

zvegelj · February 19, 2022, 6:04pm

Strangely, I saw example for SetValue

oDoc.SetValue("A1:D2", SF_Array.AppendRow(Array(1, 2, 3, 4), Array(5, 6, 7, 8)))

but with me it only fills up first row, but when I did 3 rows, it fill 2 rows. Can’t really understand

REM bellow fills up formulas A1:C2
sDoc.SetFormula("A1:C3", SF_Array.AppendRow(Array("=1", "=2", "=3"), Array("=4", "=5", "=6"), Array("=7", "=8", "=9")))

but to fill up 3 rows it doesn’t work

REM bellow fills up again only formulas A1:C2
sDoc.SetFormula("A1:C4", SF_Array.AppendRow(Array("=1", "=2", "=3"), Array("=4", "=5", "=6"), Array("=7", "=8", "=9"),  Array("=10", "=11", "=12")))

Don’t get the principle

joshua4 · February 19, 2022, 6:28pm

That’s on me…when you are doing range assignments at all, use the array version of the setters:

Sub Main
	Dim Range As Object
	
	'Setting data directly
	Range = ThisComponent.getSheets().getByIndex(0).getCellRangeByName("A1:D2")
	Range.setDataArray(Array(Array(1, 2, 3, 4), Array(5, 6, 7, 8)))
	
	'Setting formulas
	Range = ThisComponent.getSheets().getByIndex(0).getCellRangeByName("F1:I2")
	Range.setFormulaArray(Array(Array("=A1","=B1","=C1","=D1"), Array("=A2","=B2","=C2","=D2")))
	
End Sub

I didn’t really even process that since you were already talking about arrays. Sorry.

KamilLanda · February 19, 2022, 7:07pm

.SetFormula is only for one cell if you mean a classical API method LibreOffice: XCell Interface Reference

But if you see the prefix SF_ in some example, then it means it is function/method from ScriptForge library and the usage is like this

Sub ScriptForgeSetFormula
	dim oDoc as object
	GlobalScope.BasicLibraries.LoadLibrary("ScriptForge") 'load ScriptForge library
	oDoc=CreateScriptService("SFDocuments.Calc", ThisComponent) 'set methods of SFDocument.Calc to ThisComponent
	oDoc.SetFormula("A1:B3", "=$E$3") 'set formulas to range
End Sub

zvegelj · February 20, 2022, 8:47am

KamilLanda:

.SetFormula is only for one cell if you mean a classical API method LibreOffice: XCell Interface Reference

But if you see the prefix SF_ in some example, then it means it is function/method from ScriptForge library and the usage is like this
Sub ScriptForgeSetFormula
	dim oDoc as object
	GlobalScope.BasicLibraries.LoadLibrary("ScriptForge") 'load ScriptForge library
	oDoc=CreateScriptService("SFDocuments.Calc", ThisComponent) 'set methods of SFDocument.Calc to ThisComponent
	oDoc.SetFormula("A1:B3", "=$E$3") 'set formulas to range
End Sub

OK I see. So SetFormula can be used from classical API or from SF. Also different syntax Classical you apply to the range, SF to the document directly.
I was using SF Library. I believe it’s enough to declare it in one sub within the module. But still don’t see the logic how come it doesn’t work for more than two rows.
oDoc.SetFormula("A1:B3", "=$E$3")
Above works fine, to propagate formulas uniformly. but how to propagate with an array?

oDoc.SetFormula("Sh1.A1:C1", Array("=A5*B5", "=A6*B5", "=A7*B6")

Above works with as it is in one column or row
but nested array doesn’t work

sDoc.SetFormula("Sh1.A1:C3", Array(Array("=A5*B5", "=A6*B5", "=A7*B6"), Array("=A8*B5", "=B5*C5", "=B5*C6")))

Yet when using AppendRow works up to 2 rows, but it sets all following rows to 0

sDoc.SetFormula("A1:C3", SF_Array.AppendRow(Array("=1", "=2", "=3"), Array("=4", "=5", "=6"), Array("=7", "=8", "=9")))

KamilLanda · February 20, 2022, 9:18am

See documentation SFDocuments.Calc service

Vectors are always expanded vertically, except if targetrange has a height of exactly 1 row.

So it seems there isn’t possibility to set 2D array in SF setFormula