awalker89/openxlsx

Question - conditionalFormatting for column with missing cell values

Opened this issue · 1 comments

Expected Behavior

I have a table of data which I want to use the function conditionalFormatting() with, using "type = colour scale". For columns where the cells contain all non-missing values, the colourScale type works great. However, I have a number of columns where cells contain some NA values (which I do not want to remove). Whenever there is a column with NA my code doesn't work. It currently looks like this:

Expected


Actual Behavior

Conditionally format the columns containing missing cells using a colour scale argument in conditionalFormatting(). When I open up the spreadsheet I have created in Excel then I am able to conditionally format this manually, but I want this to be an automated process. It should look like this:

Actual


Steps to Reproduce the Problem

My code is:

conditionalFormatting(wb, new_sheet2, cols = 1, rows = 2:(nrow(My_Report) + 1), type = "colourScale", rule = c((min(My_Report[1])), (max(My_Report[1]))), style = c("pink", "red"))

conditionalFormatting(wb, new_sheet2, cols = 2, rows = 2:(nrow(My_Report) + 1), type = "colourScale", rule = c((min(My_Report[2])), (max(My_Report[2]))), style = c("pink", "red"))


sessionInfo()

  • Version of openxlsx: 4.2.3
  • Version of R: 4.0.4

ycphs commented

Could you please create an issue at ycphs/openxlsx?

This is the active fork of the package.