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

Question

I'm trying to accomplish something that shouldn't be that difficult, yet it escapes my attempts.

So I have an R Data Frame (df) which looks something like this:

df

MeanTemperature(ºC) Longitude Latitude Height  FinalConsiderations
5                   91ºW      152ºS    548m    Slightly Cooler
16                  185ºE     53ºN     722m    Unchanged
22                  16ºW      2ºS      206m    Significantly Warmer

The dataframe is a result of a filtered analyzed data. The end product is an Excel (xlsx) where the final column is the conclusion of the overall analysis. So, those steps are all taken care of, but these tables are rather large, so it would be nice to be able to color, for example, in RED where it says "Significantly Warmer".

I've tried using the workbook from said dataframe

  wb <- loadWorkbook(file) #where file is the location path
 

Here I want to collect those cells where it reads 'Significantly Warmer' in red, and afterwards export the workbook to xlsx.

 fo1 <- Fill(foregroundColor="red")    # create fill object # 1
 cs1 <- CellStyle(wb, fill=fo1)        # create cell style # 1
 sheets <- getSheets(wb)               # get all sheets

But now I can't find a way of making a function in the wb setting where at the column

$FinalConsiderations == 'Slightly Cooler', the cells foreground is colored red, afterwards exporting to xlsx.

Answer 1

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)