Feature request conditional formatting

[Michiel91]

First of all thanks a lot for creating openxlsx2! I've been using it a lot lately and love the functionality.

I have one suggestion for a new functionality that I commonly use. Instead of applying a single fill color I often apply a gradient fill of two colors together with a certain degree/angle. I can currently only achieve this via a manual workaround in R outside of openxlsx2 (by inserting the dxfs code below). In Excel this kind of formatting would be applied as: New conditional formatting rule> Format > Fill Effects > Two colors + shading style.

When loading an existing workbook this kind of conditional formatting I noticed it is already stored by openxlsx2 under styles_mgr$styles$dxfs. One example of a two-color conditional gradient-fill with 45 degree angle:

<dxf><fill><gradientFill degree=\"45\"><stop position=\"0\"><color rgb=\"FFFFC000\"/></stop><stop position=\"1\"><color rgb=\"FF00B0F0\"/></stop></gradientFill></fill></dxf>

It would be awesome if this this kind of conditional formatting would be supported in openxlsx2, e.g. by allowing two color values and adding a degree parameter. It seems the information stored is not very complex so I hope this request would not be too time consuming.

Curious to hear what you think about this request, thanks a lot in advance for taking it into consideration.

[JanMarvin]

Hi @Michiel91 , thanks for the kind words! I could add an argument to create_dxf_style() that can be passed to create_fill(gradientFill). This way you can pass the gradient fill XML string part from above to the dxf style, if this helps you. It's unlikely that I will dig into the gradient fill styles myself, mostly because I don't really have a use case for these.

[JanMarvin]

I've included this in #651 (see the test case for an example how to use this)

[Michiel91]

Thanks for adding the new argument @JanMarvin! I like this implementation and think it provides a very flexible way of adding a custom XML styling string. What still seems to be missing is propagation of the created dxf style to worksheets[[1]]$conditionalFormatting (or other relevant worksheets if multiple exist). Currently the style is created in the wb object as expected, but not applied or visible as it is not propagated.

[JanMarvin]

@Michiel91 sorry, but I do not get you, could you rephrase that? The following works and I can use the style just as I would be able to use any other style.

library(openxlsx2)
gf <- read_xml(
  "<gradientFill degree=\"45\">
      <stop position=\"0\"><color rgb=\"FFFFC000\"/></stop>
      <stop position=\"1\"><color rgb=\"FF00B0F0\"/></stop>
   </gradientFill>",
  pointer = FALSE)

gf_style <- create_dxfs_style(gradientFill = gf)

wb <- wb_workbook()$
  add_worksheet()$
  add_data(x = 1)$
  add_style(gf_style, "gf_style")$
  add_conditional_formatting(cols = 1, rows = 1, rule = "==1", style = "gf_style")

if (interactive()) wb$open()

[Edit:] replaced the example with cleaner code. read_xml() above is not really required, the same could be achieved using simply a character string. This only makes sure that the string is valid XML.

[JanMarvin]

A longer example:

library(openxlsx2)
gf <- read_xml(
  "<gradientFill degree=\"45\">
      <stop position=\"0\"><color rgb=\"FFFFC000\"/></stop>
      <stop position=\"1\"><color rgb=\"FF00B0F0\"/></stop>
   </gradientFill>",
  pointer = FALSE)

gf_style <- create_dxfs_style(gradientFill = gf)

dat <- matrix(sample(0:2, 10L, TRUE), 5, 2)

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

negStyle <- create_dxfs_style(font_color = wb_color(hex = "FF9C0006"), bgFill = wb_color(hex = "FFFFC7CE"))
posStyle <- create_dxfs_style(font_color = wb_color(hex = "FF006100"), bgFill = wb_color(hex = "FFC6EFCE"))
wb$styles_mgr$add(negStyle, "negStyle")
wb$add_style(gf_style, "gf_style")
wb$styles_mgr$add(posStyle, "posStyle")

wb$add_conditional_formatting(cols = 1:2, rows = 1:5, rule = "==2", style = "negStyle")
wb$add_conditional_formatting(cols = 1:2, rows = 1:5, rule = "==1", style = "gf_style")
wb$add_conditional_formatting(cols = 1:2, rows = 1:5, rule = "==0", style = "posStyle")

if (interactive()) wb$open()

[Michiel91]

@JanMarvin all is working like a charm now, it was a silly mistake on my end....
Instead of numeric values I was testing with text values like A, B and C and used the following code:

wb%<>% openxlsx2::wb_add_conditional_formatting(cols = 1:2, rows = 1:5, rule = "=='A'", style = "gradient_style") %>% openxlsx2::wb_add_style(gradient_style)

Which doesn't work due to invalid the single quotes in the rule =='A'. It should have been:

wb%<>% openxlsx2::wb_add_conditional_formatting(cols = 1:2, rows = 1:5, rule = '=="A"', style = "gradient_style") %>% openxlsx2::wb_add_style(gradient_style)

Thanks again for the nice and very fast implementation! Keep up the good work on openxlsx2 :).