[Solved] Selected cells' indices - UNO

Java, C++, C#, Delphi, ??? - Using the UNO bridges

[Solved] Selected cells' indices - UNO

Postby Deer » Sun Mar 23, 2008 2:06 pm

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   Expand viewCollapse view
procedure TForm1.FormShow(Sender: TObject);
var HCalc : THojaCalc;
     ooParams : Variant;
  HCalc:= THojaCalc.create('c:/test.xls', true);
  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);



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) - viewforum.php?f=9
Last edited by Deer on Wed Mar 26, 2008 8:19 pm, edited 2 times in total.
Posts: 4
Joined: Sun Mar 23, 2008 2:24 am

Re: Selected cells' indices - UNO

Postby Villeroy » Sun Mar 23, 2008 2:51 pm

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   Expand viewCollapse view
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
        getRangesSelection = Null
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   Expand viewCollapse view
Sub Main
  oSel = thisComponent.getCurrentSelection()
  oBasket = getSheetCellRanges(oSel)
  if isObject(oBasket) then
REM oBasket is granted to be SheetCellRanges:
    a() = oBasket.getRangeAddresses()
    ' some obj was selected
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
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   Expand viewCollapse view
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()
  msgbox "Selectoin is nothing searchable"
  exit sub
'xray desc
REM this is all we set for now:
result = sel.findAll(desc)
if isObject(result) then
  print result.getImplementationName()
  print "Nothing found"
REM Null falls back to the active cell:
end sub
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Posts: 28440
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Return to External Programs

Who is online

Users browsing this forum: No registered users and 2 guests