Gnumeric / LO Calc - how to get the background color of a cell?
I have a large spreadsheet, the rows of which are colored according to a rule that cannot be removed from the available cell data - there is no such information. So I want to look at the color of the cell and make that value.
The table looks like this:
----------
data|data| <-- background colored row in red
----------
data|data| <-- background colored row in blue
----------
data|data| <-- background colored row in yellow
----------
and I want to create a new column that will have a value according to that color.
Excel / Gnumeric / LO Calc has a search function called information cell
and a "color" attribute that unfortunately does not return a useful value:
cell("color", A2)
returns 0 or 1 and this is just a function that offers a function similar to what I need.
How can I get the background color of a cell back in Gnumeric or LO Calc in any way - via a plugin or anything else?
source to share
In LibreOffice Calc:
-
Go to Tools> Macros> Organize Macros> LibreOffice Basic>
My Macros> Standard> Module1 -
Select "Home" and click "Change".
-
It will contain an empty three-line pattern:
REM ***** BASIC ***** Sub Main End Sub
Add this after the template:
Function bgcolor(c,r) Dim oDoc As Object ' define variables Dim oSheet As Object Dim oCell As Object oDoc = ThisComponent oSheet= oDoc.getSheets().getByIndex(0) oCell = oSheet.getCellByPosition(c-1,r-1) bgcolor = oCell.CellBackColor End Function
You should see something similar in the editor:
-
Close the Organizer and you can now use the function like any other function:
Examples:
bgcolor(3,1) bgcolor(3, CELL("Row",A1)) bgcolor(CELL("COL", A1), CELL("ROW", A1))
source to share