A spreadsheet is definitively the wrong application to store 100% textual data. It does not provide the means to handle text other than labels and short descriptions. Sooner or later, when the amount of data has reached a certain level, you will lose all spreadsheet data, assuming that you do not have any backup strategy.
100% agree, but many commercial projects keep misusing them for all kinds of purposes (timesheets, project trackers, timelines) and there is not much I could do to stop it. Some way to search through them flexibly would still be very helpful.
Apologies for the late reply, my account was blocked for 17hours as it was recently created.
I was engaged with different rails
(Apache OpenOffice Community Forum - [Solved] âThis Cellâ formula or function? - (View topic) e.g.) for
some time.
Concerning yopur issue I did a bit of experimenting (my way), but didnât get yet a
satisfying solution. Its partly a problem of handling âentangled codeâ for me. If
I find more time I may suggest a solution moving text with respective highlighting
to annotations (comments) to be added to the respective cells.
For now there is banking, probably taxes, car washing âŚ
Ordinary life, you see.
1 Like
simplified relative addressing would be useful. Thanks for sharing!
That sounds like a good idea, the least âinvasiveâ.
I was trying in the meantime (as you saw in the other thread) to find a way to just change color font of a sought word in the cell. You could then undo it with Ctrl-Z, so it would be a temporary hack.
No worries, while I hope that this idea wonât be forgotten, itâs definitely nothing urgent neither.
As a temporary hack I wrote a Basic script that underlines each occurrence of a string in selected cells (prompts you with an InputBox).
Sadly it doesnât work when selected cells are not adjacent (like with find tool) - Iâll try to find out why:
Also it seem to treat each run of the tc.CharUnderline = 2 assignments as a separate undo item (meaning if there are 4 occurrences highlighted you need to undo 4 times for each one separately), which may be okay, but it would be nice to have an option to âbatchâ them all into a single âundo actionâ.
REM ***** BASIC *****
Sub findAndMark
Dim target As String
target = InputBox ("Search phrase")
Set oRange = ThisComponent.CurrentSelection
For i = 0 To oRange.Rows.getCount() - 1
For j = 0 To oRange.Columns.getCount() - 1
Set cell = oRange.getCellByPosition( j, i )
REM https://stackoverflow.com/questions/35239915/find-all-strings-within-a-string
start = 1
Do
pos = InStr(start, cell.String, target)
If pos > 0 Then
start = pos + 1
tc = cell.CreateTextCursorByRange(cell.Start)
tc.goRight(pos - 1, False)
tc.goRight(Len(target), True)
tc.CharUnderline = 2
End If
Loop While pos > 0
Next
Next
End Sub
Use the object inspection tool to examine the selected range (ranges). Maybe you need one more loop to handle the non adjacent ranges (the Range array). Use the GetByIndex method to get each of the selected ranges. Maybe you need determine the type of the selection before (if it is a Range or Range array)
1 Like
REM ***** BASIC *****
option explicit
Sub findAndUnderline
Dim oCurrSel as object
Dim oRange as object
Dim sTarget As String
Dim selIndex as integer
sTarget = InputBox ("Search phrase")
oCurrSel = ThisComponent.CurrentSelection
'xray oCurrSel
if oCurrSel.ImplementationName = "ScCellRangesObj" then
Print "Some Ranges are selected"
For selIndex = 0 to oCurrSel.getCount() - 1
oRange = oCurrSel.getByIndex(selIndex)
MarkStrings(oRange, sTarget)
Next
end if
if oCurrSel.ImplementationName = "ScCellRangeObj" then
Print "One Range is selected"
oRange = oCurrSel
MarkStrings(oRange, sTarget)
end if
if oCurrSel.ImplementationName = "ScCellObj" then
Print "One Cell is selected"
oRange = oCurrSel
MarkStrings(oRange, sTarget)
end if
End Sub
Sub MarkStrings(oOneRange as object, sText as string)
Dim oCell as object
dim tc as object
Dim i, j, start, pos as integer
For i = 0 To oOneRange.Rows.getCount() - 1
For j = 0 To oOneRange.Columns.getCount() - 1
oCell = oOneRange.getCellByPosition( j, i )
REM https://stackoverflow.com/questions/35239915/find-all-strings-within-a-string
start = 1
Do
pos = InStr(start, oCell.String, sText)
If pos > 0 Then
start = pos + 1
tc = oCell.CreateTextCursorByRange(oCell.Start)
tc.goRight(pos - 1, False)
tc.goRight(Len(sText), True)
tc.CharUnderline = 2
End If
Loop While pos > 0
Next
Next
End Sub
1 Like
That is fantastic, thank you very much for your suggestions and implementation! It works much better now. Also it shows many good conventions and techniques for writing Basic that I need to start using as well. 'xray also seems interesting.
I was wondering if you might also know if there is some âtrickâ in Calcâs basic to batch together TextCursors operations so undoing them would act on all of them after the script runs?
If you are using the Cell Styles (read: ONLY the Cell Styles) for the formatting of the spreadsheet, then you can use the Ctrl-A + Ctrl-M key combinations to delete all of the direct (manual) formatting properties. These underscores (applied by the macro) are direct formatting properties.
You can use the MRI too. Install one of them (XrayTool or MRI) and delete the â character (that means: REM) from the code the Object Inspector will list the properties and methods (and more) of the examined programming object. You must LOAD the inspector before you use it in your code.
1 Like
I donât think he can use this as condition as @dan11 wrote above
So we have to expect direct formatting and maybe a bunch of auto-created styles from conversion.
.
Ctrl-M could be used for cleanup, if we assume âthere is no important formatting inside the cells, endangered to be lost.â
1 Like
Please, do not use (and do not advise using) ImplementationName in your code. Ever. This property (actually, getImplementationName function) value is not guaranteed to be stable. Additionally, an implementation name tells you nothing about what it supports - and the mapping of an implementation name to supported methods is only in a programmerâs head.
If you need to know what you deal with, do it correctly: you need to call a method of an interface â either check if this interface is implemented, directly using HasUnoInterfaces function (this function is specific to Basic, using introspection internally); or check if a service is supported, that includes the needed interface - using XServiceInfo::supportsService method - this is the UNO objectâs own method, so portable to other programming languages. And the mapping from services to interfaces, and from interfaces to methods is documented in the API, and guaranteed to be stable.
2 Likes
I am trying to remember it (and use it) in the future. 
Here is the modified code:
REM ***** BASIC *****
option explicit
Sub findAndUnderline
Dim oCurrSel as object
Dim oRange as object
Dim sTarget As String
Dim selIndex as integer
sTarget = InputBox ("Search phrase")
oCurrSel = ThisComponent.CurrentSelection
' xray oCurrSel
if oCurrSel.supportsService("com.sun.star.sheet.SheetCellRanges") then
Print "Some Ranges are selected"
For selIndex = 0 to oCurrSel.getCount() - 1
oRange = oCurrSel.getByIndex(selIndex)
MarkStrings(oRange, sTarget)
Next
end if
if oCurrSel.supportsService("com.sun.star.sheet.SheetCellRange") then
Print "One Range is selected"
oRange = oCurrSel
MarkStrings(oRange, sTarget)
end if
if oCurrSel.supportsService("com.sun.star.sheet.SheetCell") then
Print "One Cell is selected"
oRange = oCurrSel
MarkStrings(oRange, sTarget)
end if
End Sub
Sub MarkStrings(oOneRange as object, sText as string)
Dim oCell as object
dim tc as object
Dim i, j, start, pos as integer
For i = 0 To oOneRange.Rows.getCount() - 1
For j = 0 To oOneRange.Columns.getCount() - 1
oCell = oOneRange.getCellByPosition( j, i )
REM https://stackoverflow.com/questions/35239915/find-all-strings-within-a-string
start = 1
Do
pos = InStr(start, oCell.String, sText)
If pos > 0 Then
start = pos + 1
tc = oCell.CreateTextCursorByRange(oCell.Start)
tc.goRight(pos - 1, False)
tc.goRight(Len(sText), True)
tc.CharUnderline = 2
End If
Loop While pos > 0
Next
Next
End Sub
2 Likes
Two other things that may be worth noting:
- Similar functionality could be also somewhat achieved with Calcâs builtin âFind and Replaceâ tool (as explained recently in the other thread) with the added benefit of undoing the action with a single press of ctrl+z (current script requires you to undo changes for each occurrence of a keyword separately). This script however also formats the sought keyword, which I think makes it stand out more (YMMV).
- Final use case: If you assign this script to a keybinding (I chose Shift+Alt+F) you can start the search for a keyword on the sheet (Ctrl+F) to select the first cell, close the search tool (Esc) and press the new keybinding (Shift+Alt+F) to highlight the keyword within that cell. If you havenât found it undo the changes (press Ctrl+Z a few times) and search for the next cell with your keyword (press Ctrl+Shfit+F - no need to open the âFindâ tool again). Run the script again on the next cell (Shift+Alt+F). Repeat the last two steps until you found what you were looking for. There are other ways to go about it, but this worked for me.
3*. Also I found tc.CharBackColor = 255 instead of tc.CharUnderline = 2 (on line 50) stand out a bit more. It has a downside though that to undo it you will need to press Ctrl+Z twice.
Meanwhile the question has internally and externally split up in many questions.
Let me name this variant âfind cells containing text, find this text also inside the found cells, and apply a character attribute to those findings (without changing previously set attribution)â.
I now present a preliminary solution to this âmissionâ. It is obviously incomplete but I wonât use the solution myself, and Iâm not on the way to make it pretty for selling. To the contrary I consider to retire from this complex. Let spreadsheets be spreadsheets is good advice though often disregarded.
One more thing: The solution contains a few rather matured parts from my personal Standard library. Thatâs public domain as everything made by me, but I would ask you, to NOT SPREAD REWORKED versions without taking full responsibility (and possibly inform me).
searchIntoCellsDinosaurs_sampleB.ods (27.8 KB)
There was a bug resulting in an error. The version attached below has a workaround for it.
searchIntoCellsDinosaurs_sampleCwithWorkaround.ods (27.7 KB)
2 Likes
Greetings to @JohnSUN. You surely will find a better and more complete solution.
I consider your remark that we share a similar spirit to be high praise.
However, I cannot keep up in many respects.
May peace embrace your life again.
2 Likes
Thank you very much for sharing. I just tried to run it by opening the attached macros directly and got an error below - is it expected or should it be executed in another way?
Sorry. I really dislike images in such cases. Lot of area, no use. If you claim a called macro didnât work, first name the one you tried to run, and tell how you prepared for it, and at what point it failed with what message or effect.
The only macro in the example that makes sense if called directly is findCellsSetAttributesInsideForFindings It has three parameters all declared Optional just for the demo. What default values are used for them you find in the first few lines of the code. (You donât necessarily need to understand the second and the third one from the beginning.)
Clearly you need to understand that the macro takes the CurrentSelection as the SheetCellRanges it will work for. If this is restricted to ranges without any match I expected the fRgs result to be a SheetCellRanges object with .Count=0. This was the reasonable expectation, but actually a NULL object is returned in that case. The .findAll() is not specified this way.
Well any complex software (like LibreOffice or its API) in the world is buggy, and I will not know every possible bug in advance. If you actually want to work with macros and the API, you need to use the tools of the IDE (and probably MRI) to find errors (bugs?) and to repair them.
In this single case I will now insert a workaround for the bug into the code and attach the updated version. Be sure to understand that you never can get sulutions of the kind with any warranty.
See tdf#155822 .
searchIntoCellsDinosaurs_sampleCwithWorkaround.ods (27.7 KB)
searchIntoCellsDinosaurs_sampleDwithUpdatedWorkaround.ods (28.4 KB)
The previouis upload contained an error.
1 Like