Excel Cell Coloring Using Xlsx

Excel Cell Coloring using xlsx

I doubt it's possible to change Excel's conditional formatting directly from R. So, first open the Excel workbook and set the conditional formatting for the column "X" is going into to whatever color vs value conditions you want. Then when you write from R into the workbook, the coloring will happen.

Get the color of a cell from xlsx with python

Always a solution out there, somewhere in the deep ocean of SO:

def getBGColor(book, sheet, row, col):
    xfx = sheet.cell_xf_index(row, col)
    xf = book.xf_list[xfx]
    bgx = xf.background.pattern_colour_index
    pattern_colour = book.colour_map[bgx]

    #Actually, despite the name, the background colour is not the background colour.
    #background_colour_index = xf.background.background_colour_index
    #background_colour = book.colour_map[background_colour_index]

    return pattern_colour

Oh, wait. this is even better!

EDIT:

Here is a complete code:

from xlrd import open_workbook

wb = open_workbook('cel_lis.xls', formatting_info=True)
sh = wb.sheet_by_name('Sheet1')

def getBGColor(book, sheet, row, col):
    xfx = sheet.cell_xf_index(row, col)
    xf = book.xf_list[xfx]
    bgx = xf.background.pattern_colour_index
    pattern_colour = book.colour_map[bgx]

    #Actually, despite the name, the background colour is not the background colour.
    #background_colour_index = xf.background.background_colour_index
    #background_colour = book.colour_map[background_colour_index]

    return pattern_colour

print("The RGB value of the cell is: {}".format(getBGColor(wb, sh, 0, 0)))

OUTPUT:

The RGB value of the cell is: (255, 0, 0)

Note:

I used a worksheet of type .xls with a name cel_lis.xls having its
sheet called Sheet1 with the first cell A which has a Red
background color.

EDIT 2:

To get the name of the color, you could use webcolors:

from webcolors import rgb_to_name

rgb_Col = getBGColor(wb, sh, 0, 0)
print("The RGB value of the cell is: {} which is equivalent to {}".format(rgb_Col, rgb_to_name(rgb_Col)))

OUTPUT:

The RGB value of the cell is: (255, 0, 0) which is equivalent to red

Color cells with specific character values in r to export to xlsx

So I'll write how I solved it, in case it helps anyone with a similar situation.

I downloaded the openxlsx package.

library(openxlsx) #recall the library

wb <- createWorkbook() # create a workbook

addWorksheet(wb, "Sheet", gridLines = TRUE) #add a worksheet to the workbook

writeData(wb, "Sheet", df) # write my analysis into the worksheet of the workbook, 
 #where df is the name of my data frame

Afterwards, I create the styles, following the createStyle function (see documentation).

In my case, I had to look for specific characters in my data

 warm1Style <- createStyle(fontColour = "#000000", bgFill = "#FFFF00")
 # here search for the respective HEX color-code and assign a name to the style

 conditionalFormatting(wb, "Sheet", cols = 1:ncol(df),
                  rows = 1:nrow(df), rule = "Significantly Warmer", style = warm1Style,
                  type = "contains")
# account the condition where "Significantly Warmer" is contained in a cell,
# then apply the respective style to it (in this case, warm1Style)

Then that's it, like so it can be done to any phrase or characters in the workbook.

Finally, save the workbook as an xlsx:

saveWorkbook(wb, file, overwrite = TRUE)

---

Related Topics