openxlsx2 (version 0.7)

wb_add_conditional_formatting: Add conditional formatting to cells

Description

Add conditional formatting to cells

Usage

wb_add_conditional_formatting(
  wb,
  sheet = current_sheet(),
  cols,
  rows,
  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)
)

Arguments

wb

A workbook object

sheet

A name or index of a worksheet

cols

Columns to apply conditional formatting to

rows

Rows to apply conditional formatting to

rule

The condition under which to apply the formatting. See examples.

style

A style to apply to those cells that satisfy the rule. Default is 'font_color = "FF9C0006"' and 'bgFill = "FFFFC7CE"'

type

The type of conditional formatting rule to apply.

params

Additional parameters passed. See Details for more

Details

See Examples.

Conditional formatting types accept different parameters. Unless noted, unlisted parameters are ignored.

expression

[style]
A Style object

[rule]
An Excel expression (as a character). Valid operators are: <, <=, >, >=, ==, !=

colorScale

[style]
A character vector of valid colors with length 2 or 3

[rule]
NULL or a character vector of valid colors of equal length to styles

dataBar

[style]
A character vector of valid colors with length 2 or 3

[rule]
A numeric vector specifying the range of the databar colors. Must be equal length to style

[params$showValue]
If FALSE the cell value is hidden. Default TRUE

[params$gradient]
If FALSE color gradient is removed. Default TRUE

[params$border]
If FALSE the border around the database is hidden. Default TRUE

duplicatedValues/uniqueValues/containsErrors

[style]
A Style object

contains

[style]
A Style object

[rule]
The text to look for within cells

between

[style]
A Style object.

[rule]
A numeric vector of length 2 specifying lower and upper bound (Inclusive)

topN

[style]
A Style object

[params$rank]
A numeric vector of length 1 indicating number of highest values. Default 5L

[params$percent] If TRUE uses percentage

bottomN

[style]
A Style object

[params$rank]
A numeric vector of length 1 indicating number of lowest values. Default 5L

[params$percent]
If TRUE uses percentage

iconSet

[params$showValue]
If FALSE the cell value is hidden. Default TRUE

[params$reverse]
If TRUE the order is reversed. Default FALSE

[params$percent]
If TRUE 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, 3Symbols, 3Symbols2, 3TrafficLights1, 3TrafficLights2, 4Arrows, 4ArrowsGray, 4Rating, 4RedToBlack, 4TrafficLights, 5Arrows, 5ArrowsGray, 5Quarters, 5Rating. The default is 3TrafficLights1.

Examples

wb <- wb_workbook()
wb$add_worksheet("a")
wb$add_data("a", 1:4, colNames = FALSE)
wb$add_conditional_formatting("a", 1, 1:4, ">2")