asked 2012-08-17 02:58:03 +0200
Anonymous
I have a cellrange and I'm trying to get the number of cells populated in that range.
oCellRange = rr_sheet.getCellRangeByName("a63:b68")
I tried count=oCellRange.computeFunction(com.sun.star.sheet.GeneralFunction.COUNT), but that gives the total number of rows in the range, I just want the ones that have data.
I tried the above call with COUNTA, but that complained and said it was not found. I could write a little function that loops through the DataArray, but I was trying to avoid that.
Try this:
Sub testCountPopulatedCells
Dim oSheets As Variant
Dim oSheet As Variant
Dim oCellRange As Variant
Dim nCount As Long
oSheets = ThisComponent.getSheets()
oSheet = oSheets.getByIndex(0)
oCellRange = oSheet.getCellRangeByName("A63:B68")
nCount = getCountNonEmpt(oCellRange) REM # See below...
Print "nCount = " + nCount
End Sub
Function getCountNonEmpt(oRange As Variant)
Dim oQry As Variant
Dim oCells As Variant
Dim oEnum As Variant
Dim cnt As Long
REM # Here you can combine any of the flags from
REM # http://www.openoffice.org/api/docs/common/ref/com/sun/star/sheet/CellFlags.html
oQry = oRange.queryContentCells(com.sun.star.sheet.CellFlags.VALUE _
+com.sun.star.sheet.CellFlags.DATETIME _
+com.sun.star.sheet.CellFlags.STRING _
+com.sun.star.sheet.CellFlags.FORMULA _
+com.sun.star.sheet.CellFlags.OBJECTS)
oCells = oQry.getCells()
oEnum = oCells.createEnumeration()
cnt = 0
Do while oEnum.hasMoreElements()
cnt = cnt + 1
oEnum.nextElement()
Loop
getCountNonEmpt = cnt
End Function
LibreOffice is made available by volunteers around the globe, backed by a charitable Foundation. Please support our efforts: Your donation helps us to deliver a better product!
Asked: 2012-08-17 02:58:03 +0200
Seen: 1,067 times
Last updated: Feb 20
Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.
May be cellsCount = oCellRange.getRows().getCount() * oCellRange.getColumns().getCount() Why not?
JohnSUN ( 2012-08-17 08:45:30 +0200 )editthe getCount() does the same thing, it counts all of the rows in the range, not just the ones that are occupied.
ptn4egl ( 2012-08-20 00:37:09 +0200 )editOh, excuse me! I did not notice the word "populated"! :-( Difficult to publish the code in the comments, I'll publish it in a reply
JohnSUN ( 2012-08-20 09:36:23 +0200 )editno worries. Yea, I ended up writing a little function to return the count of populated cells. Thanks, mine looks very similar to yours.
ptn4egl ( 2012-08-21 00:45:58 +0200 )edit