Openxlsx Conditional Formatting separated columns

Question

I've noticed something unusual with the conditionalFormatting() function in the Openxlsx package. When you specify a vector of columns, such as c(2,4,6), the function doesn't highlight those three columns specifically, but rather all columns from #2-#6. Consider the code below:

df <- data.frame(One = c('Dog', 'Dog'),
                 Two = c('Cat', 'Cat'),
                 Three = c('Bird', 'Bird'), 
                 Four = c('Cow', 'Cow'),
                 Five = c('Horse', 'Horse'),
                 Six = c('Lion', 'Lion'),
                 Seven = c('Tiger', 'Tiger'))
    
    style1 <- createStyle(fontName = 'Calibri', 
    fontSize = 11,
    bgFill = "#FFC7CE")
    
    conditionalFormatting(wb, 
    sheet, 
    rows = 1:nrow(df),
    cols = c(2,4,6), #<--- treated as all columns in range 2-6
    type = 'contains',
    rule = "A", #<---highlight all rows that contain text 
    style = style1)

However, if you were to use the addStyle() function, then it seems as if it would color those three columns specifically, rather than the entire range:

style2 <- createStyle(fontName = "Calibri", 
fontSize = 11,
fgFill = "#FFC7CE")

addStyle(wb, 
sheet, 
rows = 1:nrow(df),
cols = c(2,4,6), #<---treated as columns 2,4,6
style = style2,
gridExpand = TRUE)

I've tried using a for loop to see if it tricks it into formatting them correctly, but it doesn't.

cols.to.format <- c(2,4,6)
for(col in cols.to.format){
conditionalFormatting(wb, 
sheet,
rows = 1:nrow(df),
cols = col,
style = style2, 
gridExpand = TRUE)
}

Is there a way to use conditional formatting to format a specific segment of columns rather than a range?

Answer 1

Looping should work. conditionalFormatting in the open xml standard allows either a single cell or a cell range, but nothing like "A1:A2;C1:C2" and this is what openxlsx tries to honor. The only issue here is, that a warning could be shown to the user.

For reference this is the loop in openxlsx2:

library(openxlsx2)

df <- data.frame(
  One   = c('Dog', 'Dog'),
  Two   = c('Cat', 'Cat'),
  Three = c('Bird', 'Bird'), 
  Four  = c('Cow', 'Cow'),
  Five  = c('Horse', 'Horse'),
  Six   = c('Lion', 'Lion'),
  Seven = c('Tiger', 'Tiger')
)

wb <- wb_workbook()$add_worksheet()$add_data(x = df)

cols.to.format <- c(2,4,6)
for(col in cols.to.format){
  wb$add_conditional_formatting(
    rows = 1:nrow(df),
    cols = col,
    type = 'containsText',
    rule = "A")
}

if (interactive()) wb$open()

Edit: What OP really wanted:

library(openxlsx2)

df <- data.frame(
  One   = c('Dog', 'Dog'),
  Two   = c('Cat', 'Cat'),
  Three = c('Bird', 'Bird'), 
  Four  = c('Cow', 'Cow'),
  Five  = c('Horse', 'Horse'),
  Six   = c('Lion', 'Lion'),
  Seven = c('Tiger', 'Tiger')
)

wb <- wb_workbook()$
  add_worksheet()$
  add_data(x = df)

cols.to.format <- c(4,6)
for(col in cols.to.format){
  wb$add_conditional_formatting(
    rows = 1:nrow(df) + 1L,
    cols = col,
    rule = 'B2="Cat"')
}

if (interactive()) wb$open()