sheetCellRanges.getCells() doesn’t return all the cells skipping the cells with no content.
Is there a way to access all cells on sheetCellRanges, other than(pseudo code):
For range in ranges
For c in range.columns
For r in range.rows
access cell(c,r)
next r
next c
next range
Thanks,
Mike
There are usually more effective methods for solving specific problems.
In 25 years, I never needed any cell-by-cell access, and I’ve never seen any practical use case for method getCells(). Yes, if I ever had a use case for cell-by-cell access, I would do it as outlined by your code.
Is there a way to combine the outputs of queryEmptyCells ( ) and queryContentCells ( ) , that might do the trick.
At the moment I can’t think of seeing a rangeAdd function to create a single range from two.
What more effective methods did you have in mind?
Thanks,
Mike
That technique is slow, it takes 2 1/2 seconds to update the market value figures for the stocks in the spreadsheet. If ranges.getCells() didn’t skip c. 4 stocks the time required for that task would be just over 1 second. There are several columns that have to be updated that way and the times add up.
Be well,
Mike
In efficient code, the number of calls to the properties and methods of Calc objects should be minimized.
For a rectangular range of cells, we do this:
arr=range.getDataArray()
' Change the contents of the array arr
range.setDataArray(arr)
“A single range from two” can only work in rare cases. You may mean a kind of “closure” in the sens of “samllest range containing both” or an intersection. However, you can create a SheetCellRanges (plural!) object and add range by range or a multiple of ranges in one go based on their RangeAddress / RangeAddresses.
See LibreOffice: SheetCellRanges Service Reference
Thanks, Lupp but I’m already dealing with sheetCellRanges and hadn’t yet realized that ranges.getdata() returns yet another ranges object rather than single range.
Mike
When I use ranges.getData() I’ve figured out how to see the cell values.
Are the formulas in there too and I just haven’t learned to see them.
Thanks,
Mike
Use getFormulaArray instead. It returns arrays of strings.
"" empty cell
"ABCD" string
"3.14" number
"=SUM(A1:B5)" a formula expression
"'=SUM(A1:B5)" formula expression as string
"'3.14" numeric string
Yes I’ve already found that.
For Each oRng in mvColStocks
Forms = oRng.GetFormulaArray()
'msgBox( "Forms(0) = " & Forms(0) ) ’ BASIC runtime error. Object variable not set.
msgBox( "Forms(0)(0) = " & Forms(0)(0) ) ’ shows formula
msgBox( "Forms(1)(0) = " & Forms(1)(0) ) 'shows formula
'msgBox( "Forms(0)(1) = " & Forms(0)(1) ) ’ Index out of defined range
Next
The necesary addressing of the array has me puzzled, can you point me to an explanation?
Mike
Never mind I found it: Forms(row)(column)
cheers
It’s an array of row arrays like this:
(
(a1,b1,c1),
(a2,b2,c2),
(a3,b3,c3)
)
I walk through it like this:
for each rowArray in formulaArray()
for each s in rowArray()
do_stuff_with s
next
next