[Solved] How to traverse empty cells in CellRanges?

[eeigor]

Empty cells were selected using queryEmptyCells() method.

Code: Select all

    ' ThisComponent.CurrentSelection returnes ScCellRangesObj
    oEnum = ThisComponent.CurrentSelection.Cells.CreateEnumeration
    Do While oEnum.hasMoreElements
        oCell = oEnum.NextElement
        ' Apply instructions to object cell.
        oCell.Formula = "=A1"  'inserts some formula
    Loop

But empty cells are not traversed!
In this situation, is it necessary to organize two <For> loops through the cells of each range?

[FJCC]

Yes, I think you will have to have three loops, one to iterate over the cell ranges and two to iterate over the rows and columns of each range.

It seems like the CurrentSelection was made using the result of a call to queryEmptyCells. You could iterate over the cell ranges of theScCellRangesObj returned by queryEmptyCells(). It may also be faster to work with the FormulaArray or DataArray of each range rather than call each cell individually. It will not make much difference if the ranges are small.

[eeigor]

Thank you. Yes, three cycles. Array formula will restrict data change.
'Сells' (or 'getCells()') returns all cells, in this context they are empty, but 'createEnumeration' ignores them. Am I not mistaken?
I am trying to make up for missing functionality like in Excel where "Go To Special" command allows you to select Blanks, while Ctrl+Enter fills multiple cells.
Alas, I cannot insert into multiple selection by pressing Alt+Enter in Calc. So I have to create 3 loops...
The data is presented as a structure, where the row is not complete: row labels are on some rows above, no duplicates. Such data cannot be loaded into a pivot table.
But if I am reinventing the wheel, please give me a solution.

[Lupp]

You may also consider to use the dispatcher which offers some efficient methods;

Code: Select all

Sub demoInputLikeAltEnterIntoMultiSelection(Optional pInput)

REM The efficient method suggested here, can not distinguish kinds of cell properties.
REM Attributes will not be respected. If you need to preserve attributes an styles,
REM you need additional measures.

If IsMissing(pInput) Then pInput = "=A1" REM Your example, @eeigor.
doc = ThisComponent
cCtrl = doc.CurrentController
frame = cCtrl.Frame
dispatcher = CreateUnoService("com.sun.star.frame.DispatchHelper")
sel = doc.CurrentSelection
emptyRgs = sel.queryEmptyCells REM Works independent of the specific type of the sel object.

REM Of course, you can get the ranges taken from the CurrentSelection here in a different way.
REM You MUST select them with the CurrentController, however, because the dispatcher performs
REM its steps for the respective selected range.  
REM Different approach found in https://forum.openoffice.org/en/forum/viewtopic.php?f=21&t=94965.

REM Now do it finally (six lines of code)!! (No enumeration needed.)
For Each rg In emptyRgs
  leadCell = rg.getCellByPosition(0, 0)
  leadCell.Formula = pInput REM The .Formula property should cover all input needs, even numbers.
  cCtrl.select(rg)
REM (Valid at least for V7.0.3:) There's a bug with the dispatcher's fill commands clearing the
REM source if nothing is actually to fill. The workaround is to skip the command conditionally.
  If rg.Rows.Count>1    Then dispatcher.executeDispatch(frame, ".uno:FillDown", "", 0, Array())
  If rg.Columns.Count>1 Then dispatcher.executeDispatch(frame, ".uno:FillRight", "", 0, Array())
Next rg

REM Restore the selection.
cCtrl.select(sel)
End Sub

[eeigor]

@Lupp, thank you. I have noted the error information.
There are two similar solutions (second solution came from @sokol92 from another forum).
I'm testing and adapting them to fit my needs now.

Both solutions (above and here) are based on the autofill method to avoid iterating over cells.

Code: Select all

' Assigns a formula to cells in a rectangular range
Sub RangePutFormula(oRange, formula)
    oRange.getCellByPosition(0, 0).Formula = formula  'the same leadCell
    oRange.fillAuto(0, 1)
    oRange.fillAuto(1, 1)
End Sub

' Assigns a formula to cells in multiple rectangular ranges
Sub RangesPutFormula(oRanges, formula)
    Dim oRange
    For Each oRange In oRanges
        RangePutFormula oRange, formula
    Next oRange 
End Sub

' Checking for selected cells
Sub TestPutFormula()
    Dim oRanges, formula
    formula= "=A1"
    oRanges = ThisComponent.CurrentSelection

    If hasUnoInterfaces(oRanges, "com.sun.star.sheet.XSheetCellRanges") Then
        RangesPutFormula oRanges, formula
    Else
        RangePutFormula oRanges, formula
    End If     
End Sub

But Google didn’t help me find this information...

[Villeroy]

Feel free to test this extension: http://www.mediafire.com/file/o8ngkwajd ... 8.oxt/file
Install it, restart office, start Calc with your most complex document, call menu:Tools>Add-Ins>SpecialCells>Contents... and menu:Tools>Add-Ins>SpecialCells>Formatting...

[eeigor]

@Villeroy, thanks

[Villeroy]

Did you test it with LO 7.0? Does it work for you? Can you find any errors?

[eeigor]

Installed without errors.
By the way, @Villeroy, I've been reading your posts for a long time.

UPD:
Works without errors. Solves half of the problem: selecting of special cells. And you need to enter formulas in the selected cells. I wrote about Alt+Enter above.