How in LibreOffice Calc obtain a number of nonempty cells? Let’s say that we have a cell range called cells, then the question is:
int i = 0;
for (Cell cell : cells)
if (!cell.isEmpty()) ++i;
return i;
How to do it with LibreOffice formula language or how to import own macros in any language? I need platform independent solution.
All you need is the Calcformula:
=SUM(ISEMPTY( your_cell_range ))
as Arrayformula ctrl+shift+enter
This depends on what you call a “nonempty cell”. If this is a cell, say A1, answering TRUE being asked =A1<>"" then you may use for A1:A10 e.g. {=COUNTIF(A1:A10<>"";TRUE())} as an array formula OR =SUMPRODUCT(A1:A10<>"").
If you are talking of cells answering TRUE being asked =NOT(ISBLANK(A1)) the respective formula =SUMPRODUCT(NOT(ISBLANK(A1:A10))) should do. presently won’t work because array evaluation is not applicable to ISBLANK.
(Current versions obviously support array evaluation for ISBLANK as I just tested.)
EDITING: I remembered wrongly. Just tested with “legacy” version 3.3.4 of LibreOffice and it also supported everything.
For completeness: I suspected ISBLANK of not being elegible for implicit iteration in an array formula. In fact the function actually not permitting this kind of evaluation was ISFORMULA.
The much better solution in the second case is the one @PYS suggeted.
Hi - some comments…
Why not simply =COUNTA(A1:A10)
This =SUMPRODUCT(A1:A10<>"") helps to differentiate cells containing a formula but showing no results for instance.
And =SUMPRODUCT(NOT(ISBLANK(A1:A10))) works with the test version 5.0.0.3
But as the question is tagged Script you can do in several ways. For example, the first simple, the second allowing more detail:
dim oSheet as object, oCells as object
dim oResult
dim flag as long
oSheet = thiscomponent.sheets.getByName("Sheet1")
oCells = oSheet.getCellRangeByName("A1:A10")
msgbox "Not empty: " & oCells.computeFunction(com.sun.star.sheet.GeneralFunction.COUNT), 64, ""
flag = com.sun.star.sheet.CellFlags.VALUE
flag = flag + com.sun.star.sheet.CellFlags.DATETIME
flag = flag + com.sun.star.sheet.CellFlags.STRING
'flag = flag + com.sun.star.sheet.CellFlags.FORMULA'
oResult = oCells.queryContentCells(flag)
if oResult.count = 0 then
msgbox "none", 64, ""
else
msgbox "Custom: " & oResult.computeFunction(com.sun.star.sheet.GeneralFunction.COUNT), 64, ""
end if
Regards