Add conditional formatting to cells in a worksheet
Source:R/class-workbook-wrappers.R
wb_add_conditional_formatting.Rd
Add conditional formatting to cells.
You can find more details in vignette("conditional-formatting")
.
Usage
wb_add_conditional_formatting(
wb,
sheet = current_sheet(),
dims = NULL,
rule = NULL,
style = NULL,
type = c("expression", "colorScale", "dataBar", "iconSet", "duplicatedValues",
"uniqueValues", "containsErrors", "notContainsErrors", "containsBlanks",
"notContainsBlanks", "containsText", "notContainsText", "beginsWith", "endsWith",
"between", "topN", "bottomN"),
params = list(showValue = TRUE, gradient = TRUE, border = TRUE, percent = FALSE, rank =
5L),
...
)
wb_remove_conditional_formatting(
wb,
sheet = current_sheet(),
dims = NULL,
first = FALSE,
last = FALSE
)
Arguments
- wb
A Workbook object
- sheet
A name or index of a worksheet
- dims
A cell or cell range like "A1" or "A1:B2"
- rule
The condition under which to apply the formatting. See Examples.
- style
A name of a style to apply to those cells that satisfy the rule. See
wb_add_dxfs_style()
how to create one. The default style hasfont_color = "FF9C0006"
andbg_fill = "FFFFC7CE"
- type
The type of conditional formatting rule to apply. One of
"expression"
,"colorScale"
or others mentioned in Details.- params
A list of additional parameters passed. See Details for more.
- ...
additional arguments
- first
remove the first conditional formatting
- last
remove the last conditional formatting
Details
openxml uses the alpha channel first then RGB, whereas the usual default is RGBA.
Conditional formatting type
accept different parameters. Unless noted,
unlisted parameters are ignored.
expression
[style]
AStyle
object[rule]
An Excel expression (as a character). Valid operators are:<
,<=
,>
,>=
,==
,!=
colorScale
[style]
Acharacter
vector of valid colors with length2
or3
[rule]
NULL
or acharacter
vector of valid colors of equal length tostyles
dataBar
[style]
Acharacter
vector of valid colors with length2
or3
[rule]
Anumeric
vector specifying the range of the databar colors. Must be equal length tostyle
[params$showValue]
IfFALSE
the cell value is hidden. DefaultTRUE
[params$gradient]
IfFALSE
color gradient is removed. DefaultTRUE
[params$border]
IfFALSE
the border around the database is hidden. DefaultTRUE
duplicatedValues
/uniqueValues
/containsErrors
[style]
AStyle
objectcontains
[style]
AStyle
object[rule]
The text to look for within cellsbetween
[style]
AStyle
object.[rule]
Anumeric
vector of length2
specifying lower and upper bound (Inclusive)topN
[style]
AStyle
object[params$rank]
Anumeric
vector of length1
indicating number of highest values. Default5L
[params$percent]
IfTRUE
, uses percentagebottomN
[style]
AStyle
object[params$rank]
Anumeric
vector of length1
indicating number of lowest values. Default5L
[params$percent]
IfTRUE
, uses percentageiconSet
[params$showValue]
IfFALSE
, the cell value is hidden. DefaultTRUE
[params$reverse]
IfTRUE
, the order is reversed. DefaultFALSE
[params$percent]
IfTRUE
, uses percentage[params$iconSet]
Uses one of the implemented icon sets. Values must match the length of the icons in the set 3Arrows, 3ArrowsGray, 3Flags, 3Signs, 3Stars, 3Symbols, 3Symbols2, 3TrafficLights1, 3TrafficLights2, 3Triangles, 4Arrows, 4ArrowsGray, 4Rating, 4RedToBlack, 4TrafficLights, 5Arrows, 5ArrowsGray, 5Boxes, 5Quarters, 5Rating. The default is 3TrafficLights1.
See also
Other worksheet content functions:
col_widths-wb
,
filter-wb
,
grouping-wb
,
named_region-wb
,
row_heights-wb
,
wb_add_data()
,
wb_add_data_table()
,
wb_add_formula()
,
wb_add_pivot_table()
,
wb_add_slicer()
,
wb_add_thread()
,
wb_freeze_pane()
,
wb_merge_cells()
Examples
wb <- wb_workbook()
wb$add_worksheet("a")
wb$add_data(x = 1:4, col_names = FALSE)
wb$add_conditional_formatting(dims = wb_dims(cols = "A", rows = 1:4), rule = ">2")