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?

I reported this exact bug by e-mail to the package maintainer on 13 March. I should really have filed this as an issue on Github. Maybe you could do that? Note that the authors are much more focussed on openxlsx2 these days. I got around it with a loop, applying the same conditional formatting one column at a time. Not sure why that doesn't work for you? Could you add the code that didn't work? — Knackiedoo
– Knackiedoo, Commented Jun 8, 2023 at 10:04
I'll check out their Github page. I've updated the code above to show the for loop I used as well. — MKN17
– MKN17, Commented Jun 8, 2023 at 23:04

Jan Marvin · Accepted Answer · 2023-06-10 15:47:56Z

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()

edited Jun 10, 2023 at 15:47

answered Jun 10, 2023 at 8:42

Jan Marvin

1,0341 gold badge7 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

MKN17 Over a year ago

Got it. So to make sure I'm understanding: you're saying there isn't a way to do something like highlighting the cell values in columns 4 & 6 based on the cell value in column 2 being equal or not equal to 'Cat'?

Jan Marvin Over a year ago

No, that is not what I intended to say nor what you initially asked for. I answered the question, why columns = c(2, 4, 6)` spans columns B:F` and not B; D;F as one could imagine. containsText highlights columns that match a string, e.g. the letter A as shown in your question above. What you wanted is a conditional formatting depending on another cell. You can check some examples with images here. I have edited my answer above.

MKN17 Over a year ago

Thanks. Sorry, was using the comment as a way for me to interpret what you were saying in your original response, my apologies for the confusion.

Collectives™ on Stack Overflow

Openxlsx Conditional Formatting separated columns

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related