[Solved] Write array to named range

Keyboard macros or custom scripts

[Solved] Write array to named range

Postby AndresSolar » Wed Mar 27, 2019 6:59 pm

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   Expand viewCollapse view
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   Expand viewCollapse view
      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   Expand viewCollapse view
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
AndresSolar
 
Posts: 68
Joined: Sun Jun 26, 2016 5:42 am

Re: write array to named range

Postby Villeroy » Wed Mar 27, 2019 7:28 pm

Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26857
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: write array to named range

Postby AndresSolar » Wed Mar 27, 2019 10:21 pm

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   Expand viewCollapse view
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: 68
Joined: Sun Jun 26, 2016 5:42 am

Re: write array to named range

Postby AndresSolar » Thu Mar 28, 2019 7:15 am

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
AndresSolar
 
Posts: 68
Joined: Sun Jun 26, 2016 5:42 am

Re: Write array to named range

Postby Villeroy » Thu Mar 28, 2019 4:15 pm

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   Expand viewCollapse view
(
  (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   Expand viewCollapse view
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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26857
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Write array to named range

Postby AndresSolar » Thu Mar 28, 2019 7:55 pm

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   Expand viewCollapse view
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
AndresSolar
 
Posts: 68
Joined: Sun Jun 26, 2016 5:42 am

Re: Write array to named range

Postby Villeroy » Thu Mar 28, 2019 10:57 pm

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 17 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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26857
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Write array to named range

Postby AndresSolar » Fri Mar 29, 2019 5:28 am

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
AndresSolar
 
Posts: 68
Joined: Sun Jun 26, 2016 5:42 am


Return to OpenOffice Basic, Python, BeanShell, JavaScript

Who is online

Users browsing this forum: No registered users and 1 guest