qstyle: Conditional cell styles

In epicentre-msf/qxl: Quick Customized Excel Files

Conditional cell styles

Description

Wrapper to openxlsx::createStyle to create cell styles, with additional arguments rows and cols to specify the rows and/or columns that the style should apply to.

Usage

qstyle(
  rows = "data",
  cols = everything(),
  fontName = NULL,
  fontSize = NULL,
  fontColour = NULL,
  border = NULL,
  borderColour = getOption("openxlsx.borderColour", "black"),
  borderStyle = getOption("openxlsx.borderStyle", "thin"),
  bgFill = NULL,
  fgFill = NULL,
  halign = NULL,
  valign = NULL,
  textDecoration = NULL,
  wrapText = FALSE,
  textRotation = NULL,
  indent = NULL,
  locked = NULL,
  hidden = NULL
)

Arguments

rows	Which rows the style should apply to. Can be set using either: Keyword: (e.g. rows = "data" or rows = "all") Keyword "data" (the default) applies a style to all data rows (excludes the header), whereas keyword "all" applies a style to all rows (header and data) Integer rows indexes: (e.g. rows = c(2, 5, 6)) Note that in this case indexes represent Excel rows rather than R rows (i.e. the header is row 1). An expression: (e.g. rows = cyl > 4) Given an expression the style is applied using conditional formatting, with the expression translated into its Excel formula equivalent. Expressions can optionally include a .x selector (e.g. .x == 1) to refer to multiple columns. See section Using a .x selector below. Note that conditional formatting can update in real time if relevant data is changed within the workbook.
cols	Tidy-selection specifying the columns that the style should apply to. Defaults to dplyr::everything to select all columns.
fontName	A name of a font. Note the font name is not validated. If fontName is NULL, the workbook base font is used. (Defaults to Calibri)
fontSize	Font size. A numeric greater than 0. If fontSize is NULL, the workbook base font size is used. (Defaults to 11)
fontColour	Colour of text in cell. A valid hex colour beginning with "#" or one of colours(). If fontColour is NULL, the workbook base font colours is used. (Defaults to black)
border	Cell border. A vector of "top", "bottom", "left", "right" or a single string). "top" Top border bottom Bottom border left Left border right Right border TopBottom or c("top", "bottom") Top and bottom border LeftRight or c("left", "right") Left and right border TopLeftRight or c("top", "left", "right") Top, Left and right border TopBottomLeftRight or c("top", "bottom", "left", "right") All borders
borderColour	Colour of cell border vector the same length as the number of sides specified in "border" A valid colour (belonging to colours()) or a valid hex colour beginning with "#"
borderStyle	Border line style vector the same length as the number of sides specified in "border" none No Border thin thin border medium medium border dashed dashed border dotted dotted border thick thick border double double line border hair Hairline border mediumDashed medium weight dashed border dashDot dash-dot border mediumDashDot medium weight dash-dot border dashDotDot dash-dot-dot border mediumDashDotDot medium weight dash-dot-dot border slantDashDot slanted dash-dot border
bgFill	Cell background fill colour. A valid colour (belonging to colours()) or a valid hex colour beginning with "#". – Use for conditional formatting styles only.
fgFill	Cell foreground fill colour. A valid colour (belonging to colours()) or a valid hex colour beginning with "#"
halign	Horizontal alignment of cell contents left Left horizontal align cell contents right Right horizontal align cell contents center Center horizontal align cell contents justify Justify horizontal align cell contents
valign	A name Vertical alignment of cell contents top Top vertical align cell contents center Center vertical align cell contents bottom Bottom vertical align cell contents
textDecoration	Text styling. bold Bold cell contents strikeout Strikeout cell contents italic Italicise cell contents underline Underline cell contents underline2 Double underline cell contents accounting Single accounting underline cell contents accounting2 Double accounting underline cell contents
wrapText	Logical. If TRUE cell contents will wrap to fit in column.
textRotation	Rotation of text in degrees. 255 for vertical text.
indent	Horizontal indentation of cell contents.
locked	Whether cell contents are locked (if worksheet protection is turned on)
hidden	Whether the formula of the cell contents will be hidden (if worksheet protection is turned on)

Using a .x selector

An expression passed to the rows argument can optionally incorporate a .x selector to refer to multiple columns within the worksheet.

When a .x selector is used, each column specified in arguments cols is independently swapped into the .x position of the expression, which is then translated to the Excel formula equivalent and applied as conditional formatting to the worksheet.

For example, given the following qstyle specification with respect to the mtcars dataset

qstyle(
  rows = .x == 1,
  cols = c(vs, am, carb),
  bgFill = "#FFC7CE"
)

the style bgFill = "#FFC7CE" would be independently applied to any cell in columns vs, am, or carb with a value of 1.

Examples

# apply style halign = "center" to all data rows (by default rows = "data")
qstyle(halign = "center")

# apply style halign = "center" to all rows including header
qstyle(rows = "all", halign = "center")

# apply style halign = "center" to Excel rows 2:10
qstyle(rows = 2:10, halign = "center")

# apply conditional formatting to rows where cyl == 8 & mpg > 16
qstyle(cyl == 8 & mpg > 16, fgFill = "#fddbc7", textDecoration = "bold")

epicentre-msf/qxl documentation built on March 26, 2024, 6:33 p.m.