"3 Stars" or other Conditional Formatting parameters in the package

[N-bl-t]

Hey, Jan, and all contributors to openxlsx2!

I have been wishing to use the conditional formatting in my tables, however, the "5-Arrows" setting doesn't quite fit the kind of work I am meaning to do. That being said, would it be possible to add other symbols to the conditional formating commands of the package? I am mainly interested in using the "3 Stars" symbols in my tables, but would welcome any new addition.
Additionally, would also like to ask about the possibility to work further in the conditional formatting parameters, such as to deselect the symbols in the list I wish unapplied to the tables.

Thank you all for your answer!
And thank you very much for this awesome package, and for your work!

[JanMarvin]

Hi @N-bl-t ,

thanks for your kind words! I checked and realized that there is a conditional formatting in x14 (modern Excel which includes the 3Stars icon set). I'll have a look soon(tm) :)

By deselecting you mean something like this, setting the rule parameters to some duplicated value?:

x <- c(-67, -33, 0, 33, 67)

wb <- wb_workbook()
wb$add_worksheet()
wb$add_data(x = x, colNames = FALSE)
wb$add_conditional_formatting(
  dims = wb_dims(x = x),
  rule = c(0, 0, 0, 33, 67),
  type = "iconSet",
  params = list(
    percent = FALSE,
    iconSet = "5Arrows",
    reverse = TRUE)
)

I only use basic conditional formatting myself, you might have to educate me what you want to do.

[JanMarvin]

With the current development branch the following is possible:

library(openxlsx2)

x <- matrix(
  sample(c(-1:1), size = 20*10, replace = TRUE),
  20,
  10
)

wb <- wb_workbook()
wb$add_worksheet()
wb$add_data(x = x, colNames = FALSE, dims = wb_dims(x = x))

wb$add_conditional_formatting(
  dims = wb_dims(x = x, col_names = FALSE),
  rule = c(0.1, 0.1, 1),
  type = "iconSet",
  params = list(
    percent = FALSE,
    iconSet = "3Stars",
    reverse = FALSE)
)

if (interactive()) wb$open()

[N-bl-t]

Hey @JanMarvin!
Sorry for taking this long in answering you, but, I am out of words for all your inputs! Thank you very much!

As for my testing of your suggested code, this is the result of running it,

Unsure why I couldn't reach your output, I tried however to convert it to the "3-Stars" in Excel:

As a result, the complete IconSet of the 3-Stars shows up. Unsure whether it is my version of Excel, or else, I once more ask for your feedback.
And, adding to it, I would also like to know if it would be possible to have just the symbols displayed ("nur Symbol anzeigen" option on the conditional formating menu), making thus the values invisible:

Once more, thank you for your answer!
And again thank you very much for your awesome work!

[JanMarvin]

It might be because it’s in the development version. Can you try with the r-universe binary builds or install via remotes?

[N-bl-t]

Hey @JanMarvin!

Sorry for the longest delay in returning to your question!
To answer it (but only partially), I could test the output of the code (but not the development version) in distinct versions of Excel, and came to a discovery: in the newest versions, it is displayed as the desired "3-Starts" Theme, whereas in older versions (for example, the Screenshots I posted were taken in the 2019 Office version) it will be shown as the "Traffic Lights" Theme.
From this context, I ask: is there a way to address this through the package?

Thank you (again!) for your answer in advance!

[JanMarvin]

Hi @N-bl-t , please try to update to release 1.8. It works for me with Excel 2016 and MS365 (don’t have 2019 to test).

[N-bl-t]

Hey @JanMarvin! Updating the package allowed me to reproduce your code with the same results. So, thank you very much for the implementation of the feature!
Will be tinkering a bit in both Excel and R, and if anything, will let you know. For now, have my gratitude!