Calc. How to get formatting style of a cell?

[hfreanzr]

Hi!

There is a column A:
The cell A1 with the text “some text 1” and background-color #FF0000
The cell A2 with the text “some text 2” and background-color #00FF00
The cell A3 with the text “some text 3” and background-color #0000FF

As a result I need to get a column B:
The cell B1 with the text “#FF0000”
The cell B2 with the text “#00FF00”
The cell B3 with the text “#0000FF”

In short, I need to get the formatting of the cells of some column to be output in another column. I found, there is CellBackColor but trying to apply it as =A1.CellBackColor did not work.

[Villeroy]

https://forum.openoffice.org/en/forum/viewtopic.php?t=2762

[Lupp]

There isn’t a thing like formatting style in Calc. There are named cell styles, but the cell attributes they come with can be overridden by hard/direct formatting.
Fortunately you can remove direct formatting in a simple way. By default: Press Ctrl+M
BTW: A Calc cell has about 100 formatting attributes.

@hfreanzr

… there is CellBackColor but trying to apply it as =A1.CellBackColor did not work.

• You can’t use API functions as formula functions in Calc cells. Making them available. requires user code. (See post by @Villeroy.)
• The color information given by 6 “HEX” digits isn’t well human readable. What do you want to achieve based on the results?
• The question seems to tell that you use direct formatting to “code an information”.
  That’s a bad idea. Use cell styles instead.
• If you can accept the user code, you can easily get a UDF returning the name of the cell style applied to a specific cell. See attachment.
  disask125500_FunctionCellStyleName.ods (12.7 KB)

[hfreanzr]

It is working! BIG THANK YOU!

[hfreanzr]

@Lupp

“HEX” digits isn’t well human readable.

To me it is quite readable.

@Lupp

The question seems to tell that you use direct formatting to “code an information”.
That’s a bad idea. Use cell styles instead.

I have an OLD file which has no named styles, only direct formatting. I need to sort the data by formatting precisely in order to replace direct formatting with named styles. This will allow me to tidy up the file.

[Lupp]

@hfreanzr

I need to sort the data by formatting precisely in order to replace direct formatting with named styles.

Congratulations.

What you want to achieve would also be well supported by the enhanced AutoFilter of younger versions of Calc which can filter by background color.
If I remember correctly it works also with colors applied by conditional formatting.

[koyotak]

my indirect way showing the background color via formatted cell name
000_LO-CALC_countstyle-cellstyle_070533.ods (28.2 KB)

[hfreanzr]

The function CELLSTYLE() of the COUNTSTYLE extension just printing the name of named style.