[Solved] Selected cells' indices - UNO

[Deer]

I want to search in worksheets by UNO using ExecuteSearch from Delphi.
It works, so the cells I want to find come to be selected.

But I want to know the column and row indices of this - or these cells.

the code - using 'Hoja' unit by Sergio Hernandez:

Code: Select all

...
procedure TForm1.FormShow(Sender: TObject);
var HCalc : THojaCalc;
     ooParams : Variant;
begin
  HCalc:= THojaCalc.create('c:/test.xls', true);
  HCalc.ActivateSheetByIndex(1);
  ooParams :=  VarArrayCreate([0,17], varVariant);

  ooParams[0] := HCalc.ooCreateValue('SearchItem.StyleFamily',2);
  ooParams[1] := HCalc.ooCreateValue('SearchItem.CellType',0);
  ooParams[2] := HCalc.ooCreateValue('SearchItem.RowDirection',true);
  ooParams[3] := HCalc.ooCreateValue('SearchItem.AllTables',false);
  ooParams[4] := HCalc.ooCreateValue('SearchItem.Backward',false);
  ooParams[5] := HCalc.ooCreateValue('SearchItem.Pattern',false);
  ooParams[6] := HCalc.ooCreateValue('SearchItem.Content',false);
  ooParams[7] := HCalc.ooCreateValue('SearchItem.AsianOptions',false);
  ooParams[8] := HCalc.ooCreateValue('SearchItem.AlgorithmType', 0);
  ooParams[9] := HCalc.ooCreateValue('SearchItem.SearchFlags',65536);
  ooParams[10] := HCalc.ooCreateValue('SearchItem.SearchString','text to find');
  ooParams[11] := HCalc.ooCreateValue('SearchItem.ReplaceString','');
  ooParams[12] := HCalc.ooCreateValue('SearchItem.Locale',255);
  ooParams[13] := HCalc.ooCreateValue('SearchItem.ChangedChars',2);
  ooParams[14] := HCalc.ooCreateValue('SearchItem.DeletedChars',2);
  ooParams[15] := HCalc.ooCreateValue('SearchItem.InsertedChars',2);
  ooParams[16] := HCalc.ooCreateValue('SearchItem.TransliterateFlags',1280);
  ooParams[17] := HCalc.ooCreateValue('SearchItem.Command',1);

  HCalc.ooDispatch('.uno:ExecuteSearch',ooParams);

  HCalc.Free;
end;
...

It works, so the proper cells will be selected. But how can I reach of these cells' indices?

ps.: Much thanks to Villeroy for another solution (c.s.s.util.xSearchable) - http://user.services.openoffice.org/en/ ... um.php?f=9

[Villeroy]

OK, we have a selection of either type cell, range or multiple ranges. Let's assume the StarBasic language as lingua franca. The differences between script languages are marginal.
This function returns a collection of cells in any case:

Code: Select all

function getSheetCellRanges(obj)
    if obj.supportsService("com.sun.star.sheet.SheetCellRange") then
' the intersection with it's own address returns a collection with this range alone
' a single cell supports SheetCellRange as well
        getRangesSelection = obj.queryIntersection(obj.getRangeAddress())
    elif obj.supportsService("com.sun.star.sheet.SheetCellRanges") then
        getRangesSelection = obj
    else
        getRangesSelection = Null
    endif
end function

From that collection you can get all the addresses (address-structs c.s.s.table.CellRangeAddress) with all the indices you want:

Code: Select all

Sub Main
  oSel = thisComponent.getCurrentSelection()
  oBasket = getSheetCellRanges(oSel)
  if isObject(oBasket) then
REM oBasket is granted to be SheetCellRanges:
    a() = oBasket.getRangeAddresses()
    printAddresses
  else
    ' some obj was selected
  endif
End Main
sub printAddresses(a())
  for i = 0 to uBound(a())
    x = a(i)
    print x.Sheet, x.StartColumn, x.StartRow, x.EndColumn, x.EndRow
  next
end sub

One of the big inconsistencies of the spreadsheet-API is, that you can not distinguish between a null-selection (one cell having the focus) and a single highlighted cell which may be the only result of a search operation. Thus "getCurrentSelection()" in the above code will return a single cell even if the find operation did not match anything.
So you may prefer "findAll" to the dispatch framework:

Code: Select all

sub test_searchDesc
view = thisComponent.getCurrentController()
sel = view.getSelection()
REM Built-in StarBasic function hasUnoInterfaces:
if hasUnoInterfaces(sel, "com.sun.star.util.XSearchable") then
  desc = sel.createSearchDescriptor()
else
  msgbox "Selectoin is nothing searchable"
  exit sub
endif
'xray desc
REM this is all we set for now:
desc.setSearchString("asdf")
result = sel.findAll(desc)
if isObject(result) then
  print result.getImplementationName()
else
  print "Nothing found"
endif
REM Null falls back to the active cell:
view.select(result)
end sub