I am trying to fill a row in an excel workbook with openxlsx
based on text in a column from that row. For example, if cell C3
contains A
then highlight row 3.
Ideally, this would not be conditionalFormatting
. I would like it be the actual color of the row based on the text in a cell, but conditionalFormatting
seems to be the only option. If I am able to make it the actual color, please let me know.
The code below shows that you can highlight a row based on the value
of cell in that row, but not if you are trying to use text. In addition, if the type
is set to contains
then it does not seem to be accepting any styles.
wb <- createWorkbook()
addWorksheet(wb, "Dependent on")
addWorksheet(wb, "containsText")
negStyle <- createStyle(fontColour = "#9C0006", bgFill = "#FFC7CE")
posStyle <- createStyle(fontColour = "#006100", bgFill = "#C6EFCE")
## highlight row based on value in column 2
writeData(wb, "Dependent on", data.frame(x = 1:10, y = runif(10)), startRow = 15)
conditionalFormatting(wb, "Dependent on", cols=1:2, rows=16:25, rule="$B16<0.5", style = negStyle)
conditionalFormatting(wb, "Dependent on", cols=1:2, rows=16:25, rule="$B16>=0.5", style = posStyle)
## cells containing text
fn <- function(x) paste(sample(LETTERS, 10), collapse = "-")
writeData(wb, "containsText", data.frame(letters = sapply(1:10, fn), numbers = 1:10))
conditionalFormatting(wb, "containsText", cols = 1:2, rows = 1:10, type = "contains", rule = "A")
openXL(wb) ## opens a temp version