Gnumeric/LO Calc - how to get cell background color?

Question

I have large spreadsheet, with rows colored according to no regular rule, so the cell color cannot be determined by the cell entries. I want to lookup the cell color and use it as value.

Spreadsheet is like this:

----------
data|data| <-- background row color red
----------
data|data| <-- background row color blue
----------
data|data| <-- background row color yellow
----------

and I want to create new column which will report the cell backgound color, such as "RED" or "EE0000." Excel/Gnumeric/LO Calc all have an information lookup function called cell and attribute "color", which seems to only tell whether the cell is colored or not:

cell("color", A2)

returns either 0 or 1.

So how can I get the cell background color in Gnumeric or LO Calc?

Answer 1

In LibreOffice Calc:

1. Go to Tools > Macros > Organize Macros > LibreOffice Basic >
   My Macros > Standard > Module1

2. Select "Main" and click "Edit."

3. It will contain the empty 3-line template:

   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 to this in the editor:

   

4. Close the Organizer and now you can use the function like any other function:

Examples:

bgcolor(3,1)
bgcolor(3, CELL("Row",A1))
bgcolor(CELL("COL", A1), CELL("ROW", A1))