Add conditional formatting to columns of a worksheet

This function is a wrapper for openxlsx2::wb_add_conditional_formatting() allowing for a more convenient application of conditional formatting to a sheet in a Workbook object. The arguments colour_scale, contains and expression serve as shortcuts to obtaining the respective conditional formatting, but it is still possible to obtain all conditional formatting styles available in {openxlsx2} via the arguments rule, style and type. Note that {openxlsx2} workbooks are modified in place, so wb is updated whether or not the result is assigned.

cond_format(
  wb,
  sheet = NULL,
  columns,
  rows = NULL,
  colour_scale = NULL,
  contains = NULL,
  expression = NULL,
  type = NULL,
  rule = NULL,
  style = NULL,
  font_colour = "white",
  bg_fill = "#ad0000",
  ...,
  sheetName = deprecated(),
  colourScale = deprecated()
)

Arguments

wb: a Workbook object
sheet: a name or index of a worksheet
columns: column names or indices to apply conditional formatting to. Unknown names or indices outside 1:ncol raise an error.
rows: optional row indices (as in the spreadsheet, i.e. the header is row 1) to apply conditional formatting to. If NULL (default), applies to all data rows. Non-consecutive rows are formatted as separate blocks. On a sheet without data rows nothing is formatted.
colour_scale: shortcut argument to apply "colorScale" formatting, e.g. c("red" = 0, "grey" = 50, "green" = 100)
contains: shortcut argument to apply "containsText" formatting, e.g. "word"
expression: shortcut argument to apply "expression" formatting, e.g. ">=50"
type: directly passed to openxlsx2::wb_add_conditional_formatting(). Matching is case-insensitive and type names of the predecessor package {openxlsx} (e.g. "colourScale", "databar", "contains") are translated automatically.
rule: directly passed to openxlsx2::wb_add_conditional_formatting()
style: For type = "colorScale": a vector of 2-3 colours. For "dataBar" and "iconSet": as in openxlsx2::wb_add_conditional_formatting(). For all other types: the name of a dxf style that was registered in wb via openxlsx2::wb_add_dxfs_style(). If NULL (default), a style is registered on the fly from font_colour and bg_fill.
font_colour: font colour of the formatted cells, used when style = NULL
bg_fill: background colour of the formatted cells, used when style = NULL
...: directly passed to openxlsx2::wb_add_conditional_formatting()
sheetName: Use sheet.
colourScale: Use colour_scale.

Value

The Workbook object, invisibly

Examples

wb <- create_wb()
add_sheet(wb, mtcars, "cars")
cond_format(wb, "cars", columns = "mpg", expression = ">25")