The hardware and bandwidth for this mirror is donated by METANET, the Webhosting and Full Service-Cloud Provider.
If you wish to report a bug, or if you are interested in having us mirror your free-software or open-source project, please feel free to contact us at mirror[@]metanet.ch.
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"))
Basic conditional formatting
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"
)
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"
)
Dependent conditional formatting
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"
)
Duplicated conditional formatting
Contains text conditional formatting
Not contains text conditional formatting
Begins with conditional formatting
Ends with conditional formatting
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)
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)
)
Between conditional formatting
Highlight cells in interval [-2, 2]
Top n conditional formatting
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 conditional formatting
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
Logical operators conditional formatting
You can use Excels logical Operators
These binaries (installable software) and packages are in development.
They may not be fully stable and should be used with caution. We make no claims about them.