[Solved] Write array to named range

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
AndresSolar
Posts: 72
Joined: Sun Jun 26, 2016 5:42 am

[Solved] Write array to named range

Post by AndresSolar »

Hello together,

I'm trying to optimize the performance of a simulator by using an array(364,839) as buffer for the daily results in a named-range with 1 row and 840 Columns.

Code: Select all

cResults1Y = ThisComponent.Sheets(0).getCellRangeByName("ResultsYearSim")
cResults1D = ThisComponent.Sheets(0).getCellRangeByName("ResultsDaySim")

      ArrayY = cResults1Y.Data         'Result: Object(0 to 364)
      ArrayD = cResults1D.Data         'Result: Object(0 to 0)
In a loop, i fill all 365 elements of the Array:

Code: Select all

      ArrayY(i-1) = ArrayD()         'Result: Object(0 to 364)
After the loop has finished, I try to write the result data of the whole year back to the
named range "ResultsYearSim" (cResults1Y). I have tried several different methods found
on the forum without success.

Code: Select all

cResults1Y.Data = ArrayY
I noticed that by adding ArrayD() to ArrayY(i-1), the structure of ArrayY seems to change.
What am I missing?

Thanks in advance
Last edited by Hagar Delest on Fri Mar 29, 2019 8:53 am, edited 1 time in total.
Reason: tagged solved
LO6.2 on OS-X Mojave 10.14.3
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: write array to named range

Post by Villeroy »

Code: Select all

oSource = Sh1.getCellRangeByName("Source")
oTarget = Sh2.getCellRangeByName("Target")
REM get all data in one nested array of row arrays
a() = oSource.getDataArray() 
REM resize the target range to the size of the source 
r = oSource.Rows.getCount()
c = oSource.Columns.getCount()
rg = getOffsetRange(oTarget, 0, 0, r, c)
REM write all data with a single API call:
rg.setDataArray(a())
using the following helper functions:

Code: Select all

Function getOffsetRange(oRg, nRowOffset&, nColOffset&, nRowResize&, nColResize&)
Dim addr
'calls: getRangeByAddress
	addr = oRg.getRangeAddress()
	addr.StartRow = addr.StartRow + nRowOffset
	addr.EndRow = addr.EndRow + nRowOffset
	addr.StartColumn = addr.StartColumn + nColOffset
	addr.EndColumn = addr.EndColumn + nColOffset
	if nRowResize > 0 then addr.EndRow = addr.StartRow + nRowResize -1
	if nColResize > 0 then addr.EndColumn = addr.StartColumn + nColResize -1
	getOffsetRange = getRangeByAddress(oRg.getSpreadsheet(), addr)
End Function

'pass a spreadsheet-document, sheet or range  together with a c.s.s.table.CellRangeAddress
'return empty if oAddr out of bounds or wrong obj
Function getRangeByAddress(obj, oAddr as com.sun.star.table.CellRangeAddress)
on error goto nullErr:
Dim oSheet
	If obj.supportsService("com.sun.star.sheet.SpreadsheetDocument") then
		REM use the sheet specified by given address
		oSheet = obj.getSheets.getByIndex(oAddr.Sheet)
	else
		REM use given object (range/sheet) as parent range
		oSheet = obj
	endif
	getRangeByAddress = oSheet.getCellRangeByPosition(oAddr.StartColumn,oAddr.StartRow,oAddr.EndColumn,oAddr.EndRow)
exit function
nullErr:
	getRangeByAddress = Null
End Function
'pass a spreadsheet-document, sheet or range together with a c.s.s.table.CellAddress
'return empty if oAddr out of bounds or wrong obj
Function getCellByAddress(obj, oAddr as com.sun.star.table.CellRangeAddress)
on error goto nullErr:
Dim oSheet
	If obj.supportsService("com.sun.star.sheet.SpreadsheetDocument") then
		REM use the sheet specified by given address
		oSheet = obj.getSheets.getByIndex(oAddr.Sheet)
	else
		REM use given object (range/sheet) as parent range
		oSheet = obj
	endif
	getCellByAddress = oSheet.getCellByPosition(oAddr.Column,oAddr.Row)
exit function
nullErr:
	getCellByAddress = Null
End Function
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
AndresSolar
Posts: 72
Joined: Sun Jun 26, 2016 5:42 am

Re: write array to named range

Post by AndresSolar »

Many thanks Vileroy,
I had no idea that this will be that hard to do...

I'm trying to integrate your example code into my loop. Hope I got it right...

Code: Select all

For y = 0 to 24 (intentionally static)

    'add each result array to a array(24)
    For d = 0 to 364 (intentionally static)

        ...simulator stuff
        ...locking controllers & add lock
        ...synchronizing steps
        ...unlocking controllers & rem lock
        ...updating all cells
        rg = getOffsetRange(oTarget, d, 0, r, c)

    Next d
    rg.setDataArray(a())

Next y
...move rg(y) to Array(24)
Question: is it possible to simplify the macro if all named ranges are static (all dimensions are known) and will never change?
Question: Can that function be used for the outer loop as well?

Your example will take me some time to digest but is a great help for me to get into those arrays ..somewhen. Thanks a lot!
LO6.2 on OS-X Mojave 10.14.3
AndresSolar
Posts: 72
Joined: Sun Jun 26, 2016 5:42 am

Re: write array to named range

Post by AndresSolar »

Villeroy... I failed to fully understand your code - and/or I wasn't clear with my description.

The loop to fill the Results(364,839) works fine but changes the topology of the Array from 2 to 3 dimensions as soon as i add the inputArray to the resultArray(i).

the point is: how can i pass a data from a Array ()()() to a named range object ()()?
LO6.2 on OS-X Mojave 10.14.3
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Write array to named range

Post by Villeroy »

I always try to avoid cell-by-cell loops. You can easily fill a whole range of data with a single api call and a correctly sized DataArray: oRange.setDataArray(a())
My example assumed that the source of data is just another cell range (e.g. a range of formula cells) and you want to transfer a flat data copy from that cell range to another cell range. My example code works with any amount of rows and columns because it adjusts the size of the target to the size of the source.

oRange.getDataArray() returns one nested array like this one:

Code: Select all

(
  (a1,b1,c1,d1), 
  (a2,b2,c2,d2),
  (a3,b3,c3,d3)
)
This is an array of 3 arrays where each array represents one row of column values.
getDataArray returns arrays of string values and numbers. Blank cells are represented by an empty string.
This is the exact data type you need in order to fill a whole range of data with one API call instead of 840x365 API calls which is extremely inefficient and cumbersome.

If your source array is generated programmatically:

Code: Select all

Dim a(839) 'count of rows -1
Dim ra(364) 'count of columns -1
for i = 0 to uBound(a()) 
  redim ra(364)
  fillRow ra() 'some routine which fills up one row of values
  a(i) = ra()
next i
oTargetRange.setDataArray(a())
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
AndresSolar
Posts: 72
Joined: Sun Jun 26, 2016 5:42 am

Re: Write array to named range

Post by AndresSolar »

Villeroy,

i implemented your example but always get the problem ()()() instead of ()(), as soon as i pass ra() to a(i), a changes from 2d to 3d and write back to cell range fails (Object not set).

This is what i did:

Code: Select all

Dim cResults1D As Object
    cResults1D = Iop.getCellRangeByName("$DH$45:$AJO$45")
		
Dim cResults1Y As Object
    cResults1Y = Iop.getCellRangeByName("$DH$49:$AJO$413")

Dim aResults1D(839)
Dim aResults1Y(364)

For i = 0 To UBound(aResults1Y)
    ...do sim stuff
    ...Update all simulator cells
    'in my example, ra() = aResults1D() and a() = aResults1Y()
    'fill ra(839) with the results of the simulator (cResults1D)
     aResults1D = cResults1D.getDataArray()
    'redim a(364)
     ReDim aResults1Y(364)
    'a(i) = ra()
     aResults1Y(i-1) = aResults1D()
    'that's where things go wrong...
    'a()() turns into a()()() and get's incompatible
    'with the range object, cResults1Y
Next i

'oTargetRange.setDataArray(a())
cResults1Y.setDataArray(aResults1Y())  ***Msg: Variable not set
LO6.2 on OS-X Mojave 10.14.3
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Write array to named range

Post by Villeroy »

Of course it becomes 3D. You add the 2D-result of getDataArray() to another array. getDataArray()(0) should fix this.

$DH$45:$AJO$45 is a 2D array. It is an array of one row array. If you add this to an array you get a 3D array with one member.

If the source data come from another 2D-range:
The attached example is similar to my other example. It calls a routine with the right document and the 2 range names. It uses a named range as source and a named cell as target. The actual target size is calculated by the macro.
It copies the mere data of range "Source" (any size) to range "Target" which is just a single anchor cell.
Attachments
CopyDataArray.ods
Macro to copy a named range to another named range
(11.11 KiB) Downloaded 273 times
Last edited by Villeroy on Fri Mar 29, 2019 6:23 pm, edited 2 times in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
AndresSolar
Posts: 72
Joined: Sun Jun 26, 2016 5:42 am

Re: Write array to named range

Post by AndresSolar »

I don't know what I did wrong the first time I implemented your example... :D - but now it works perfectly.
Thanks a lot, Villeroy. t's now a challenge to master those arrays.
LO6.2 on OS-X Mojave 10.14.3
Post Reply