
Add conditional formatting to a workbook
Source:vignettes/conditional-formatting.Rmd
conditional-formatting.Rmd
wb <- wb_workbook()
wb$add_dxfs_style(name = "negStyle", font_color = wb_color(hex = "FF9C0006"), bg_fill = wb_color(hex = "FFFFC7CE"))
wb$add_dxfs_style(name = "posStyle", font_color = wb_color(hex = "FF006100"), bg_fill = wb_color(hex = "FFC6EFCE"))
Rule applies to all each cell in range

Basic conditional formatting
wb$add_worksheet("cellIs")
wb$add_data("cellIs", -5:5)
wb$add_data("cellIs", LETTERS[1:11], start_col = 2)
wb$add_conditional_formatting(
"cellIs",
dims = "A1:A11",
rule = "!=0",
style = "negStyle"
)
wb$add_conditional_formatting(
"cellIs",
dims = "A1:A11",
rule = "==0",
style = "posStyle"
)
Highlight row dependent on first cell in row

Moving row conditional formatting
wb$add_worksheet("Moving Row")
wb$add_data("Moving Row", -5:5)
wb$add_data("Moving Row", LETTERS[1:11], start_col = 2)
wb$add_conditional_formatting(
"Moving Row",
dims = "A1:B11",
rule = "$A1<0",
style = "negStyle"
)
wb$add_conditional_formatting(
"Moving Row",
dims = "A1:B11",
rule = "$A1>0",
style = "posStyle"
)
Highlight column dependent on first cell in column

Moving column conditional formatting
wb$add_worksheet("Moving Col")
wb$add_data("Moving Col", -5:5)
wb$add_data("Moving Col", LETTERS[1:11], start_col = 2)
wb$add_conditional_formatting(
"Moving Col",
dims = "A1:B11",
rule = "A$1<0",
style = "negStyle"
)
wb$add_conditional_formatting(
"Moving Col",
dims = "A1:B11",
rule = "A$1>0",
style = "posStyle"
)
Highlight cell dependent on

Dependent conditional formatting
Highlight entire range cols X rows dependent only on cell A1
wb$add_worksheet("Dependent on")
wb$add_data("Dependent on", -5:5)
wb$add_data("Dependent on", LETTERS[1:11], start_col = 2)
wb$add_conditional_formatting(
"Dependent on",
dims = "A1:B11",
rule = "$A$1 < 0",
style = "negStyle"
)
wb$add_conditional_formatting(
"Dependent on",
dims = "A1:B11",
rule = "$A$1>0",
style = "posStyle"
)
Highlight cells in column 1 based on value in column 2
wb$add_data("Dependent on", data.frame(x = 1:10, y = runif(10)), startRow = 15)
wb$add_conditional_formatting(
"Dependent on",
dims = "A16:A25",
rule = "B16<0.5",
style = "negStyle"
)
wb$add_conditional_formatting(
"Dependent on",
dims = "A16:A25",
rule = "B16>=0.5",
style = "posStyle"
)
Highlight duplicates using default style

Duplicated conditional formatting
wb$add_worksheet("Duplicates")
wb$add_data("Duplicates", sample(LETTERS[1:15], size = 10, replace = TRUE))
wb$add_conditional_formatting(
"Duplicates",
dims = "A1:A10",
type = "duplicatedValues"
)
Colorscale colors cells based on cell value

Colorscale conditional formatting (Yep, that is a color scale image)
df <- read_xlsx("https://github.com/JanMarvin/openxlsx-data/raw/main/readTest.xlsx", sheet = 5)
wb$add_worksheet("colorScale", zoom = 30)
wb$add_data(x = df, col_names = FALSE) ## write data.frame
Rule is a vector or colors of length 2 or 3 (any hex color or any of
colors()
). If rule is NULL
, min and max of
cells is used. Rule must be the same length as style or L.
wb$add_conditional_formatting(
sheet = "colorScale",
dims = wb_dims(
rows = seq_len(nrow(df)),
cols = seq_len(ncol(df))
),
# dims = wb_dims(x = df, col_names = FALSE, select = "data")
style = c("black", "white"),
rule = c(0, 255),
type = "colorScale"
)
wb$set_col_widths("colorScale", cols = seq_along(df), widths = 1.07)
wb$set_row_heights("colorScale", rows = seq_len(nrow(df)), heights = 7.5)
Databars

Databar conditional formatting
wb$add_worksheet("databar")
## Databars
wb$add_data("databar", -5:5, start_col = 1)
wb <- wb_add_conditional_formatting(
wb,
"databar",
dims = "A1:A11",
type = "dataBar"
) ## Default colors
wb$add_data("databar", -5:5, start_col = 3)
wb <- wb_add_conditional_formatting(
wb,
"databar",
dims = "C1:C11",
type = "dataBar",
params = list(
showValue = FALSE,
gradient = FALSE
)
) ## Default colors
wb$add_data("databar", -5:5, start_col = 5)
wb <- wb_add_conditional_formatting(
wb,
sheet = "databar",
dims = "E1:E11",
type = "dataBar",
style = c("#a6a6a6"),
params = list(showValue = FALSE)
)
wb$add_data("databar", -5:5, start_col = 7)
wb <- wb_add_conditional_formatting(
wb,
"databar",
dims = "G1:G11",
type = "dataBar",
style = c("red"),
params = list(
showValue = TRUE,
gradient = FALSE
)
)
# custom color
wb$add_data("databar", -5:5, start_col = 9)
wb <- wb_add_conditional_formatting(
wb,
"databar",
dims = wb_dims(rows = 1:11, cols = 9),
type = "dataBar",
style = c("#a6a6a6", "#a6a6a6"),
params = list(showValue = TRUE, gradient = FALSE)
)
# with rule
wb$add_data(x = -5:5, start_col = 11)
wb <- wb_add_conditional_formatting(
wb,
"databar",
dims = wb_dims(rows = 1:11, cols = 11),
type = "dataBar",
rule = c(0, 5),
style = c("#a6a6a6", "#a6a6a6"),
params = list(showValue = TRUE, gradient = FALSE)
)
Top N

Top n conditional formatting
wb$add_data("topN", data.frame(x = 1:10, y = sample(1:100, 10)))
Highlight top 5 values in column x
wb$add_conditional_formatting(
"topN",
dims = wb_dims(rows = 2:11, cols = 1),
style = "posStyle",
type = "topN",
params = list(rank = 5)
)
Highlight top 20 percentage in column y
Bottom N

Bottom n conditional formatting
wb$add_data("bottomN", data.frame(x = 1:10, y = sample(1:100, 10)))
Highlight bottom 5 values in column x
wb$add_conditional_formatting(
"bottomN",
dims = wb_dims(rows = 2:11, cols = 1),
style = "negStyle",
type = "bottomN",
params = list(rank = 5)
)
Highlight bottom 20 percentage in column y
wb$add_conditional_formatting(
"bottomN",
cols = 2,
rows = 2:11,
style = "negStyle",
type = "bottomN",
params = list(rank = 20, percent = TRUE)
)
wb$add_worksheet("logical operators")
Logical Operators

Logical operators conditional formatting
You can use Excels logical Operators
wb$add_data("logical operators", 1:10)
wb$add_conditional_formatting(
"logical operators",
dims = wb_dims(rows = 1:10, cols = 1),
rule = "OR($A1=1,$A1=3,$A1=5,$A1=7)"
)