wb_dims: Error in wb_dims(rows = 2:(nrow(data_in) + 1), cols = sel_columns) : You must supply positive, consecutive values to cols

[Rapsodia86]

Hello!
I would like to apply conditional formatting to selected columns (across all rows), but those columns are not next to each other. E.g. the index is:

> sel_columns
[1]  6  9 12 15 18 21 24 27

When I try:

wb$add_conditional_formatting(my_var, dims = wb_dims(rows = 2:(nrow(data_in)+1), cols = sel_columns), 
rule = ">=2", style = "negative")

the error is:

Error in wb_dims(rows = 2:(nrow(data_in) + 1), cols = sel_columns) : 
 You must supply positive, consecutive values to `cols`

As my simple workaround, I can loop across all elements and apply conditional_formating as many times as needed:

for (i in sel_columns){
   wb$add_conditional_formatting(my_var, dims = wb_dims(rows = 2:(nrow(data_in)+1), cols = i), 
   rule = ">=2", style = "negative")

But I am wondering if am I just missing something and that option is already implemented?
Thanks!

openxlsx2 version 1.1

[olivroy]

Hi, indeed, your workaround is correct.

By disallowing non-consecutive columns, we wanted to avoid that it would not simply apply the conditional formatting to everything. See #796 (comment)

Because dims is a cell range, 6 9 12 15 18 21 24 27, we have to be careful not to select 7,8, 10 etc., hence why the current strictness of wb_dims().

Another syntax to do this would be

# wb_dims can understand what you want to do if you provide `x`, the data. 
# It will perform operations based on that
# to operate on column i, only for the data, the following code is equivalent to the one your provided.
for (i in sel_columns){
   wb$add_conditional_formatting(my_var, dims = wb_dims(x = data_in, cols = i), 
   rule = ">=2", style = "negative")
}

[Rapsodia86]

Ok,
I get the potential problem. Using the occasion, I would like to ask about the rule in add_conditional_formatting()
My wb is created based on dataframe, which has NAs, and so I do not want ⁠#N/A⁠ in my workbook.
wb$add_data(my_var, data_in, na.strings = NULL)
When I apply a different rule:

   wb$add_conditional_formatting(my_var, dims = wb_dims(x = data_in, cols = i), 
   rule = "<= 0.5", style = "positive")

Those empty cells are also colored.
I know I can add yet add_conditional_formatting like:

wb$add_conditional_formatting(my_var, dims = wb_dims(x = data_in, cols = i), rule = '= ""', style = "empty")

So, I was wondering if I could apply a rule with a condition within? Or maybe I should modify the way how I create the wb? Or I could change the rule = "<= 0.5" to add '= ""' somehow?

Thanks again!

[Rapsodia86]

Ok, I just tested the approach. The rule "<0" (in your example posStyle) applies color style on the empty blanks as expected and then
wb$add_conditional_formatting(dims = wb_dims(x = data_in, cols = i), type = "containsBlanks", style = "none") was supposed to change back the empty cells. But it does not.

I changed wb$add_dxfs_style(name = "none") to
wb$add_dxfs_style(name = "none", bg_fill = wb_color(hex = "")) but then I end up without borders.

How I can preserve those borders? Do I need to format them additionally? Though in the Excel's original default style called "Normal" there are no borders.

Thank you both for all the time and help:)

[JanMarvin]

Could you please provide a minimal example? Something I can run to reproduce it?

[Rapsodia86]

Sure!
Three scenarios below:

1. Empty cells get the color of the rule = "<=1"

set.seed(12345)
library(openxlsx2)
wb <- wb_workbook()
wb$add_dxfs_style(name = "negStyle",bg_fill = wb_color(hex = "FDE9D9"))
wb$add_dxfs_style(name = "posStyle",bg_fill = wb_color(hex = "E4DFEC"))
wb$add_dxfs_style(name = "none")
data_in <- data.frame(
  xx = sample(c(1:3, NA_integer_), 11, TRUE),
  yy = sample(c(1:5, NA_integer_), 11, TRUE)
)
wb$add_worksheet()$
  add_data(x = data_in, na.strings = NULL)
for (i in seq_along(data_in)) {
  wb$add_conditional_formatting(dims = wb_dims(x = data_in, cols = i), 
                                rule = ">=3", style = "negStyle")
  wb$add_conditional_formatting(dims = wb_dims(x = data_in, cols = i), 
                                rule = "<=2", style = "posStyle")
  wb$add_conditional_formatting(dims = wb_dims(x = data_in, cols = i), 
                                type = "containsBlanks", style = "none")
}
if (interactive()) wb$open()

2. Empty cells get "no color" but no borders:

wb <- wb_workbook()
wb$add_dxfs_style(name = "negStyle",bg_fill = wb_color(hex = "FDE9D9"))
wb$add_dxfs_style(name = "posStyle",bg_fill = wb_color(hex = "E4DFEC"))
wb$add_dxfs_style(name = "none",bg_fill = wb_color(hex = ""))

3. Empty cells get "no color" and a "default" grey border: This is the expected result:)

wb <- wb_workbook()
wb$add_dxfs_style(name = "negStyle",bg_fill = wb_color(hex = "FDE9D9"))
wb$add_dxfs_style(name = "posStyle",bg_fill = wb_color(hex = "E4DFEC"))
wb$add_dxfs_style(name = "none",border=TRUE,border_color  = wb_color(hex = "D9D9D9"), bg_fill = wb_color(hex = ""))

[JanMarvin]

Oh cool, so you got it working! It looks like Excel does not differentiate if a cell is blank or zero. Therefore by sheer luck my example from above worked.

Another more complex solution looks like this and uses spreadsheet formulas to include the ISBLANK() check into the condition highlighting the values <= 2. But I'd go with your solution. Clean to read and easy to apply.

set.seed(12345)
library(openxlsx2)

wb <- wb_workbook()
wb$add_dxfs_style(name = "negStyle",bg_fill = wb_color(hex = "FDE9D9"))
wb$add_dxfs_style(name = "posStyle",bg_fill = wb_color(hex = "E4DFEC"))

data_in <- data.frame(
  xx = sample(c(1:3, NA_integer_), 11, TRUE),
  yy = sample(c(1:5, NA_integer_), 11, TRUE)
)

wb$add_worksheet()$
  add_data(x = data_in, na.strings = NULL)

for (i in seq_along(data_in)) {
  dims <- wb_dims(x = data_in, cols = i)
  fcell <- strsplit(dims, ":")[[1]][1]
  fml <- sprintf("AND(%s <3, NOT(ISBLANK(%s)))", fcell, fcell)
  
  wb$add_conditional_formatting(dims = dims, 
                                rule = ">=3", style = "negStyle")
  
  wb$add_conditional_formatting(dims = dims,
                                rule = fml,
                                style = "posStyle")
}

if (interactive()) wb$open()

The book is still a work in progress, but answering questions from users, writing code, and fixing bugs that users somehow keep uncovering ( 😉 ) takes up a lot of the free time I'm willing to sacrifice for this package.

[Rapsodia86]

"The book is still a work in progress, but answering questions from users, writing code, and fixing bugs that users somehow keep uncovering ( 😉 ) takes up a lot of the free time I'm willing to sacrifice for this package."

I can only imagine! And so, I do appreciate the time you dedicated to my case and all the explanations!

Also, thank you for the example using spreadsheet formulas, that will be very handy when a more complex rule is needed!:)