【calc】Create a macro to find all occurrences of current selection

[wangkaibule]

I’m trying to write a macro that can find all cells with the same content as the one in current selected cell.
Below is what I get so far:

Sub FindAll()
  sheet =  ThisComponent.CurrentController.ActiveSheet
  desc = sheet.createSearchDescriptor()
  cell = ThisComponent.CurrentSelection.getCellByPosition(0,0)
  desc.SearchString = cell.String
  findings = sheet.findAll(desc)
  ThisComponent.CurrentController.select(findings)
End Sub

What still missing is:

1. Cells containing formula is not included in the search results (XSearchable.findAll)
2. The macro always end up to set the first occurrence to be “active to edit” status (I’m not sure the accurate name of this status of cell), so the gui will always get navigated to the position of the first occurence, instead of the cell I have selected before I run the macro

Any one can help me to complete my script by solving these two problems above?

[wangkaibule]

You may want to assign a keyboard shortcut to test this script, or you may not see the effect by running the script directly in macro editor. Or use ThisComponent.CurrentController.Frame.Activate subroutine

[wangkaibule]

So the cursor should be the accurate name of the status. And the select method is setting the cursor to the first range (ScTabViewObj)

[mikekaganski]

… which suggests, that you prepend the wanted address to the list before calling select

[mikekaganski]

@wangkaibule

1. Cells containing formula is not included in the search results (XSearchable.findAll)

The complete (as of today) set of properties defined in ScCellSearchObj - which is the implementation of SearchDescriptor service in Calc, and which extends that service:

Property name	Type	Description
SearchBackwards	boolean	search backwards
SearchByRow	boolean	search directionality
SearchCaseSensitive	boolean	consider or ignore case
SearchRegularExpression	boolean	regular expression
SearchWildcard	boolean	wildcards ‘*’ and ‘?’
SearchSimilarity	boolean	“Weighted Levenshtein Distance” search
SearchSimilarityRelax	boolean	see LEV_RELAXED
SearchStyles	boolean	search for styles
SearchWords	boolean	normal (Boyer-Moore) search / search for word only
SearchSimilarityAdd	short	see insertedChars
SearchSimilarityExchange	short	see changedChars
SearchSimilarityRemove	short	see deletedChars
SearchType	short	search in: 0 = formulas; 1 = values; 2 = notes
SearchFiltered	boolean	search filtered cells
SearchFormatted	boolean	search formatted display strings
WildcardEscapeCharacter	long	see WildcardEscapeCharacter

For your purpose, SearchType should likely be 1 (searching in values / results, not in formulas).

[wangkaibule]

Nice, it works. Always trust the source code, thank you for the reference link !

[sokol92]

@mikekaganski

SearchFormatted

In my notes it is written that

desc.SetPropertyValue "SearchFormatted", True

works, but

desc.SearchFormatted=True

throws a runtime error.

[mikekaganski]

@sokol92 this may happen, when the respective XPropertySetInfo doesn’t expose these properties as available in the object, thus not giving introspection a chance to do the magic

[mikekaganski]

@sokol92: addressed that in https://gerrit.libreoffice.org/c/core/+/187122.

[wangkaibule]

For who interests, I’m currently settled with:

Sub FindAll()
	sheet =  ThisComponent.CurrentController.ActiveSheet
	desc = sheet.createSearchDescriptor()
	cell = ThisComponent.CurrentSelection.getCellByPosition(0,0)
	desc.SearchString = cell.String
	desc.setPropertyValue("SearchType", 1) 
	findings = sheet.findAll(desc)
	ranges = ThisComponent.createInstance("com.sun.star.sheet.SheetCellRanges")
	ranges.addRangeAddress(cell.RangeAddress, false)
	ranges.addRangeAddresses(findings.RangeAddresses, false)
	ThisComponent.CurrentController.select(ranges)
	'ThisComponent.CurrentController.Frame.Activate
End Sub

[sokol92]

@wangkaibule

For who interests, I’m currently settled with:

And we warn potential users that the macro will work if a rectangular range of cells is selected (in particular, one cell), and will raise an error in other cases (several rectangular ranges of cells are selected, one or more objects on the sheet are selected).
You can check the selection type, although, on the other hand, you don’t have to check it if the context of the macro execution is obvious.

	If Not HasUnoInterfaces(ThisComponent.CurrentSelection, "com.sun.star.table.XCellRange") Then
	  Msgbox "Select a cell (a rectangular range of cells)"
	  Exit Sub 
	End If

[fpy]

@wangkaibule

who

and if someone feels like pythonizing it, welcome …
Macros/Calc: Difference between revisions - The Document Foundation Wiki