CODEWITHSUNDEEP
rconditional-formattingopenxlsx
Tumaini Kilimba
Tumaini Kilimba

Reputation: 359

Conditional formatting using openxlsx package in R not working

I have the following code, where I want the column Score to be conditionally formatted based on the value of the column max. If the score is 8 and max is 8, green. If the score is 4 and max is 8, yellow. if the score is 4 and max is 4 then green. If the score is 2 and max is 4 then yellow. If the score is 0 then red. However, the below does not seem to work (only testing for green).

EDITED code based on G5W suggestion

so with the code below I do not get the conditional formatting, but I also get the error message: enter image description here followed by enter image description here

Clicking on the link to the logfile provides me with the following xml output enter image description here

library(tibble)
library(openxlsx)

data <- tribble(
  ~Week, ~Facility, ~Indicator, ~`Indicator Value`, ~`Yellow Gap`, ~`Green Gap`, ~Score, ~max,
  8, "Mngeta Health Center", "3MMD Coverage", 0.96, -13, 10, 4, 8,
  8, "Mngeta Health Center", "12 Month Retention", 0.96, 35, 50, 2, 4,
  8, "Mngeta Health Center", "Appointment Adherence", 0.97, 11, 24, 0, 8,
  8, "Mngeta Health Center", "EID 12 Months", 1, 0, 0, 8, 8,
  8, "Mngeta Health Center", "Early Retention", 1, 0, 0, 8, 8,
  8, "Mngeta Health Center", "Recent Retention", 1.04, -19, -5, 8, 8,
  8, "Mngeta Health Center", "6MMD Coverage", 0.98, -29, -9, 8, 8,
  8, "Mngeta Health Center", "IPT Coverage", 0.99, -15, -1, 4, 4,
  8, "Mngeta Health Center", "EID 2 Months", 1, 0, 0, 8, 8,
  8, "Mngeta Health Center", "Viral Load Coverage", 0.95, -67, -2, 8, 8
)

# Convert Score column to numeric
data$Score <- as.numeric(data$Score)

wb <- createWorkbook()

# Add a new worksheet
addWorksheet(wb, "Formatted Data")

# Write the data to the worksheet
writeData(wb, "Formatted Data", data)

# Create a style object for green color
green_style <- createStyle(bgFill = "green")

conditionalFormatting(wb, sheet = "Formatted Data",
                      cols = which(colnames(data) == "Score"), rows = 1:(nrow(data)+1),
                      rule = '"Score"==8 & "max"==8', style = green_style)

saveWorkbook(wb, "formatted_data.xlsx", overwrite = TRUE)

Created on 2023-02-25 by the reprex package (v2.0.1)

Upvotes: 0

Views: 450

Answers (1)

G5W
G5W

Reputation: 37641

Two problems. First, if you want to use the column name, it must be quoted for Excel. Second, because you have a header row, the rows that you want to test go up to nrow(data)+1. Try:

library(tibble)
library(openxlsx)

data <- tribble(
  ~Week, ~Facility, ~Indicator, ~`Indicator Value`, ~`Yellow Gap`, ~`Green Gap`, ~Score, ~max,
  8, "Mngeta Health Center", "3MMD Coverage", 0.96, -13, 10, 4, 8,
  8, "Mngeta Health Center", "12 Month Retention", 0.96, 35, 50, 2, 4,
  8, "Mngeta Health Center", "Appointment Adherence", 0.97, 11, 24, 0, 8,
  8, "Mngeta Health Center", "EID 12 Months", 1, 0, 0, 8, 8,
  8, "Mngeta Health Center", "Early Retention", 1, 0, 0, 8, 8,
  8, "Mngeta Health Center", "Recent Retention", 1.04, -19, -5, 8, 8,
  8, "Mngeta Health Center", "6MMD Coverage", 0.98, -29, -9, 8, 8,
  8, "Mngeta Health Center", "IPT Coverage", 0.99, -15, -1, 4, 4,
  8, "Mngeta Health Center", "EID 2 Months", 1, 0, 0, 8, 8,
  8, "Mngeta Health Center", "Viral Load Coverage", 0.95, -67, -2, 8, 8
)

# Convert Score column to numeric
data$Score <- as.numeric(data$Score)

wb <- createWorkbook()

# Add a new worksheet
addWorksheet(wb, "Formatted Data")

# Write the data to the worksheet
writeData(wb, "Formatted Data", data)

# Create a style object for green color0
green_style <- createStyle(bgFill = "green")

conditionalFormatting(wb, sheet = "Formatted Data",
                      cols = which(colnames(data) == "Score"), rows = 1:(nrow(data)+1),
                      rule = '"Score"==8 & "max"==8', style = green_style)

saveWorkbook(wb, "formatted_data.xlsx", overwrite = TRUE)

Upvotes: 0

Related Questions