Marking parts of a cell's text as bold in Libreoffice Calc Basic?

Question

I know that it's possible in a localc spreadsheet to manually edit cell text and mark some of the text in it as bold, italic, or whatever.

I want to do the same thing with a macro. I'm building up a summary string that is inserted into a specific cell. The summary string contains multiple lines like:

    Category1: item1, item2, item3
    Category2: item1, item2, ...
    CategoryN: item1, item2, ...

There could be anywhere from ~4 to ~12 Category lines in the cell, depending on whether a given category was empty or not.

The function I have to do that is working, does exactly what I want so far. But now I want to bold the "category" portions of the cell like this:

    Category1: item1, item2, item3
    Category2: item1, item2, ...
    CategoryN: item1, item2, ...

Is it possible to do this in LO Basic? I know it's possible with manual editing in a cell, but that would defeat the entire purpose of scripting this.

This is what I have so far, at the bottom of the function that builds up the summary string and inserts it into the cell object (named "cell", strangely enough):

newline = chr(10)
cell  = ThisComponent.Sheets.getByName("Summary").getCellRangeByName("Skills")



Dim next_nl as Integer
Dim next_colon as Integer
Dim TC as Object ' TextCursor object pointing into the cells text

TC = cell.createTextCursor

next_nl=1 ' start at first char of cell

While (next_nl > 0)
    TC.collapseToStart
    TC.gotoStart(false)  ' move to start of cell

    if (next_nl > 1) Then TC.goRight(next_nl, false) ' move to next_nl

    TC.goRight(0,true) ' begin selection

    next_colon = InStr(next_nl, cell.String, ":")

    If next_colon Then
        TC.goRight(next_colon, true) ' extend selection to next colon
        TC.CharWeight = com.sun.star.awt.FontWeight.BOLD ' bold the selection
        next_nl = InStr(next_colon, cell.String, newline) ' jump to the next LF
    Else
        next_nl = 0 ' no more colons to be found, finish up.'
    Endif

Wend

It partly works. The first line has the Category bold and the items normal text. Perfect, so far.

Unfortunately, everything from the start of the second line to about the middle of the second-last line (huh? what? that's weird) is entirely bold. I'm suspecting that maybe Instr() doesn't work quite right with mixed-format text, gets the character count wrong.

Other things I've tried / ideas:

• I've tried searching for CR chr(13) rather than LF chr(10) but that didn't work at all. I was expecting it to use MS-DOS CR line-endings but it uses proper unix LF line-endings (even if you add CRs between lines, it converts them to LF). This may be because i'm running on Linux, so when I get this working I should probably detect the running environment and use the appropriate line-ending style for linux, windows, or whatever.

• I've tried moving the Tc=cell.createTextCursor inside the while loop in case it needs to be reinitialised on every pass. Makes no difference, same result either way.

  AFAICT, there doesn't seem to be a 'reset selection' type function for the cursor.

• maybe there's some weird variable scope problem but I have pretty much zero knowledge about variable scope in LO, so can't tell - i'm very new to LO macros, I mostly program in sh, awk, perl or python, and the last time I wrote anything in Basic was back in the 1980s.

• I'm not even sure that the above is a good approach to the problem, it was just the first thing that seemed relevant when googling the docs. I'm more than willing to start over with a better idea if necessary.

• I'm beginning to think that the "Skills" named range should be one cell per category rather than one big long string with LFs embedded, then I could loop through them individually. Doing that would require me to make fairly significant changes to other parts of the spreadsheet, so I don't want to do that unless I have to.

PS: I know...I'm being a bit of a perfectionist. Uppercasing the category names would work (in fact, it does) but is kind of ugly. Bolding them would be much nicer...and this Summary page's purpose is for pretty presentation.

Answer 1

InStr() works just fine, even on Windows. There was just a simple bug near goRight().

Sub BoldPartOfCell
    Dim next_nl As Integer  ' position of the next newline
    Dim next_colon As Integer
    Dim cursor As Object  ' TextCursor object pointing into the cell's text

    NEWLINE = Chr(10)  ' the LibreOffice line break character
    cell = ThisComponent.Sheets.getByName("Summary").getCellRangeByName("Skills")
    cursor = cell.createTextCursor
    next_nl = 1  ' start at first char of cell
    Do While True
        cursor.collapseToStart
        cursor.gotoStart(False)  ' move to start of cell
        if (next_nl > 1) Then cursor.goRight(next_nl, False) ' move to next_nl
        next_colon = InStr(next_nl, cell.String, ":")
        If next_colon > 0 Then
            ' extend selection to next colon
            cursor.goRight(next_colon - next_nl + 1, True)
            cursor.CharWeight = com.sun.star.awt.FontWeight.BOLD  ' bold the selection
            next_nl = InStr(next_colon, cell.String, NEWLINE)  ' jump to the next LF
        Else
            Exit Do
        End If
    Loop
End Sub