[Solved] Calc: italicizing first two words in a cell

[mars729]

I am making a vascular plant checklist. In one column is the scientific name of the plant which includes the author.

So I would need this:
Verbena simplex Lehmann

to look like this:
Verbena simplex Lehmann


dim cursor as object
dim Doc as object
dim Sheet as object
dim t as integer
Doc = ThisComponent
Sheet = Doc.Sheets(10)
for t= 1 to 665
Cell = Sheet.GetCellByPosition(14, t)
Cursor = Cell.Text.createTextCursor()
Cursor.gotoNextWord(true)
Cursor.gotoNextWord(True)
Cursor.CharPosture = com.sun.star.awt.FontSlant.ITALIC
next

The error given is Property or method not found for gotoNextWord. Adding two cells together doesn't work either (I split the column into two, bolded one column and attempted to add them together). This task can be done manually so there should be a way to do it by code. I don't relish italicizing two words in over 650 cells.

Thanks for your help, Brent

[FJCC]

Calc isn't set up to handle text manipulation very well. The reason you are getting the macro error is that the text cursor does not have a gotoNextWord method. I would handle this task without a macro as follows.
Save a copy of your document!
Open a new Writer document and set its font and font size to match what you are using in Calc.
In the Calc document, select all of the cells you want to affect and copy them.
Go to the Writer and use Paste Special (Ctrl + Shift + V) to paste as Unformatted text.
Still in Writer, select Edit -> Find & Replace
In the Search For box, enter ^\w+\s+\w+
Click More Options and select Regular Expressions
Click Find All. You should see the first two words of every row selected.
Click the Italic icon on the formatting tool bar. The first two words of every row will no be italic.
Select all of the text (Ctrl + A) and copy it.
Paste that into the Calc document, either directly in the original column or, more safely, in another column.

[Lupp]

Compounds are evil!
You may use a spreadsheet as a surrogate for something like a database for small data sets under specific conditions.
Anyway some well proven principles of databasing should be regarded then, too.

Since there were some questions about the behaviour of services and interfaces I was interested in, I wrote the following code based on the originally posted lines. I would NOT suggest to use it, but to split the given compounds to get a better design. You may use the 'Text to Columns...' tool for the purpose.

Code: Select all

Option Explicit
Sub doIt() REM (pSheetIndex As Long, pCol As Long, pStartRow As Long, pEndRow As Long, pDelim As String, pOccNum As Long)
Dim pSheetIndex As Long, pCol As Long, pStartRow As Long, pEndRow As Long, pDelim As String, pOccNum As Long
pSheetIndex = 0 : pCol = 2 : pStartRow = 1 : pEndRow = 10 : pDelim = " " : pOccNum = 2 REM Interpretation of pOccNum not implemented!
Dim hCursor As Object
Dim doc0 As Object
Dim theSheet As Object
Dim k As Long, delim2Pos As Long
Dim stringToParse As String
Dim kCell As Object, undoMgr As Object
doc0          = ThisComponent
undoMgr       = doc0.UndoManager
theSheet      = doc0.Sheets(pSheetIndex)
undoMgr.EnterUndoContext("Italicise")
For k = pStartRow To pEndRow
REM The following only implements action assuming pOccNum=2 is intended.
REM To implement the functionality pOccNum is includeed for I would suggest to write a helper function.
REM InStr() as available does not directly support the feature.
  kCell         = theSheet.getCellByPosition(pCol, k)
  hCursor       = kCell.Text.createTextCursorByRange(kCell.Text.Start)
  stringToParse = kCell.String
REM Due to the fact that a textfield is counted for ONE concerning the cursor poitions
REM While InStr counts each character of the shown string, this proceeding cannot handle 
REM cells containing textfileds. A workaround would be rather complicated.
REM AFAIK hyperlinks are the only kind of textfields supported for spreadsheet cells. 
  delim2Pos     = InStr(Instr(stringToParse, pDelim)+Len(pDelim), stringToParse, pDelim)
  hCursor.goRight(delim2Pos-1, True)
  hCursor.CharPosture _
                = com.sun.star.awt.FontSlant.ITALIC
Next k
undoMgr.LeaveUndoContext
End Sub

REM Though a TextCursor defined for a spreadsheet cell claims to support the service "com.sun.star.text.TextCursor"
REM and this service is specified to export the interface "com.sun.star.text.XWordCursor" the respective functionality
REM is obviously not provided. I am knowing far too little about the API to understand this.

See also attachment.

[Villeroy]

The two words belong into a separate database field which a column of cells when using poor man's database.

In fact the two words belong into 2 separate database fields for genus and species. Just in case you do not collect all that info just for reading.

[mars729]

Thanks, FJCC, it worked.

Lupp > I needed the column to be formatted that way to improve readability in the final printed checklist.

I decided to use spreadsheets with this project because the learning curve is much higher for learning how to use the database. I could do it, but it would have taken more time. The source data came in .CSV format from two sources, a few online sources that don't offer an easy download option and many print sources. Much of the effort in the project was vetting the species and using a name server to match up older scientific names. I will be updating the checklist every couple of years or so. More observations from iNaturalist, herbariums and other sources will be made and the taxonomy is going to change with splits, lumps, rearrangements etc to both the scientific name and common names for plants. A friend of mine stated that the DNA boys are driving the taxonomists and field observers crazy with all the taxonomic changes.

Cheers, Brent

[Villeroy]

You must not use a spreadsheet for this. Even if you insist in using sheets, you should split the genus, species and the name of the author in 3 fields rather than highlighting parts of text. For this taxonomic project it is vital to fetch all species of a genus or other species described by the same author and map them to other taxonomic systems.
csv is a database exchange format. It has nothing to do with arithmetic calculators misused as databases.

[Lupp]

I needed the column to be formatted that way to improve readability in the final printed checklist.

Yes. Of course I assumed you wanted to output/print information in this way, but for what secret reason do you want it not aligned in the obviously reasonable and clear way? In other words: What's good in having contents of equal role (taxonomic/bibliographic nomen) run down the page in wavy rivers. Mimicking old style print?

A listing of this kind is a table functionally after all. Why shouldn't it look like a table?
And emphasising the improvement of readabilty I dare use my own ruler: Best readable ist the aligned table in this case.

Just for fun: See https://ask.libreoffice.org/de/question ... en-suchen/
(It's in German unfortunately, but you should understand the main features, in specific if you have a look into the demo. I gave a solution for the somehow reverse problem there.)

[Villeroy]

You can get a "pretty print" from normalized data at any time, even with formatted substrings. However, data analysis requires a more computer friendly structure. Databases always have been THE software tool for taxonomy because every category belongs to exactly one parent category.

[mars729]

It's too late in the project to go back ... I have already vetted all the observations I have available (3000 observations from online sources and dot maps from books) and decided which species to include in the printed checklist (654 species plus a number of subspecies, varieties and hybrids). I have also nearly wrote all the code I need to process any further data I might receive. If I was firmly comfortable with databases from the beginning I might have been able to chip off maybe 25% off my time needed to create this checklist, but at this point learning database couldn't possibly save me any time now.

[RusselB]

If you feel that you want to go to a database, you can start your database at the point you are currently at, thus allowing you to update the information later on.
While the learning curve for a database is higher than that of a spreadsheet, the advantages, in the long run, I think would be worth the time investment.
There's also no reason why you can't work with what you currently have for a test/learning database. The database files can be linked to your spreadsheet, but they won't replace them as they use a different file extension.
Just something for you to consider.

[Lupp]

...which species to include in the printed checklist (654 species plus a number of subspecies, varieties and hybrids).

In what way will you sort "subspecies, varieties and hybrids" into your system of creating as-if- columns by applying text attributes?
The occurence of more than two taxonomic levels will spoil the tiny chance to work reasonably (to some extent) that way.
Split your compounds! This is easy without any user code, and also without moving anything to a database. In addition I would suppose it to be far from aggravating the completion of your apparatus of formulae. To the contrary it should ease it significantly.