How do you preserve formatting of text when assigning value to a cell?

[dan11]

The following snippet copies contents of a cell to the same cell and preserves fromatting (font color, effects etc.).

Sub Main
Set oRange = ThisComponent.CurrentSelection
    For i = 0 To oRange.Rows.getCount() - 1             
        For j = 0 To oRange.Columns.getCount() - 1
            Set oCell = oRange.getCellByPosition( j, i )
             oCell.String = oCell.String
        Next
    Next 
End Sub

However if you concatenate it with another string, for example:
oCell.String = oCell.String + " test appendix"
All text in the cell loses it’s formatting:

Is there any way to edit that text and preserve any formatting? Or is there a way to read formatting from it and recreate it afterwards? I may be remiss on my understanding of formatting in libreoffice in general.

[Lupp]

Recommendadtion: Let it be.
“Solution”: Use a TextCursor, move it to the end of the already contained text (Not the same as the string!), use insertString for the cell (also accessible as a Text service) with that cursor defining the position.
This way the appended string will get the character attributes found at the previous end of text. Play around if you want to set different attributes.
A STRING taken from elswhere doesn’t come with any attributes. You need to handle TextRange objects if you need it differently.Study the API documentation.
…
Let it be. Don’t misuse Calc cells as letters or adds.

[dan11]

Thank you very much, I’ve updated the snippet and now it correctly preserves the formatting like you said.

Sub Main
Set oRange = ThisComponent.CurrentSelection
    For i = 0 To oRange.Rows.getCount() - 1             
        For j = 0 To oRange.Columns.getCount() - 1
            Set oCell = oRange.getCellByPosition( j, i )
            textCursor = oCell.createTextCursor()
            oCell.insertString(textCursor, " test appendix", false)
        Next
    Next 
End Sub

Ultimately I am trying to format part of the text within a cell, so hopefully there will be a corresponding "updateString" function somewhere in the API as well.

[Lupp]

@dan11

Ultimately I am trying to format part of the text within a cell…

An inserted STRING always gets the character attributes taken from the position at insertion point. That’s generally left of the first inserted character. If you want to apply a character attribute to the inserted part, you can do as the code in the attached example shows. This will NOT “remove” the inherited attributes that were not overwritten.
It’s a mess, imo, since Calc doesn’t support character styles.
Did I already suggest to “let it be”? It doesn’t pay. Sheet cells aren’t general writing.
demoPartialCellTextAttributing.ods (12.1 KB)

[dan11]

Thank you very much for this example, that helps a lot. The fact that the macro preserves character styles in this instance may actually be for the better. InStr function will be very useful for looping through occurrences of a word (it may use the current keyword in the “find” field in the final version).

I realize that this isn’t the right way to do this - will definitely need to rewrite it in the future.

[Lupp]

I’m afraid you still hunt a dead beast.
Even if you prepare everything with the help of Writer:
You can’t preserve character attributes that are not supported by spreadsheet cells (text highlighting by background color e.g.). To “reset” char atrribs to defaults is extremely complicated (and error-prone). The paragraph structure of a Writer text,e.g, can’t be transferred to a spreadsheet cell via Copy/Paste (or XTransferable). You would need a lot of additional work to simulate such a transfer …

[dan11]

Thank you for explaining this. In that case I will skip the idea of formatting parts of the text in a cell and just add a few characters next to a sought occurrence:

|my funny text| → |my -->funny text|

This is off-topic to this thread though, but it should be enough to indicate where a particular string is located in a cell (you can then “blink” it again and again with Ctrl-Z, Ctrl-Y).