Add conditional formatting to cells

Share:

Description

DEPRECATED! USE conditionalFormatting

Usage

1
2
conditionalFormat(wb, sheet, cols, rows, rule = NULL, style = NULL,
  type = "expression")

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 or a vector of colours. See examples.

style

A style to apply to those cells that satisify the rule. A Style object returned from createStyle()

type

Either 'expression', 'colorscale' or 'databar'. If 'expression' the formatting is determined by a formula. If colorScale cells are coloured based on cell value. See examples.

Details

DEPRECATED! USE conditionalFormatting

Valid operators are "<", "<=", ">", ">=", "==", "!=". See Examples. Default style given by: createStyle(fontColour = "#9C0006", bgFill = "#FFC7CE")

Author(s)

Alexander Walker

See Also

createStyle

Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
wb <- createWorkbook()
addWorksheet(wb, "cellIs")
addWorksheet(wb, "moving Row")
addWorksheet(wb, "moving Col")
addWorksheet(wb, "Dependent on 1")
addWorksheet(wb, "colourScale 2 Colours")
addWorksheet(wb, "databar")

negStyle <- createStyle(fontColour = "#9C0006", bgFill = "#FFC7CE")
posStyle <- createStyle(fontColour = "#006100", bgFill = "#C6EFCE")

## rule applies to all each cell in range
writeData(wb, 1, -5:5)
writeData(wb, 1, LETTERS[1:11], startCol=2)
conditionalFormat(wb, 1, cols=1, rows=1:11, rule="!=0", style = negStyle)
conditionalFormat(wb, 1, cols=1, rows=1:11, rule="==0", style = posStyle)

## highlight row dependent on first cell in row
writeData(wb, 2, -5:5)
writeData(wb, 2, LETTERS[1:11], startCol=2)
conditionalFormat(wb, 2, cols=1:2, rows=1:11, rule="$A1<0", style = negStyle)
conditionalFormat(wb, 2, cols=1:2, rows=1:11, rule="$A1>0", style = posStyle)

## highlight column dependent on first cell in column
writeData(wb, 3, -5:5)
writeData(wb, 3, LETTERS[1:11], startCol=2)
conditionalFormat(wb, 3, cols=1:2, rows=1:11, rule="A$1<0", style = negStyle)
conditionalFormat(wb, 3, cols=1:2, rows=1:11, rule="A$1>0", style = posStyle)


## highlight entire range cols X rows dependent only on cell A1
writeData(wb, 4, -5:5)
writeData(wb, 4, LETTERS[1:11], startCol=2)
conditionalFormat(wb, 4, cols=1:2, rows=1:11, rule="$A$1<0", style = negStyle)
conditionalFormat(wb, 4, cols=1:2, rows=1:11, rule="$A$1>0", style = posStyle)

## colourscale colours cells based on cell value

df <- read.xlsx(system.file("readTest.xlsx", package = "openxlsx"), sheet = 4)
writeData(wb, 5, df, colNames=FALSE)  ## write data.frame

## rule is a vector or colours of length 2 or 3 (any hex colour or any of colours())
conditionalFormat(wb, 5, cols=1:ncol(df), rows=1:nrow(df),
   rule =c("black", "white"), type = "colourScale")

setColWidths(wb, 5, cols=1:ncol(df), widths=1.07)
setRowHeights(wb, 5, rows=1:nrow(df), heights=7.5)

## Databars
writeData(wb, "databar", -5:5)
conditionalFormat(wb, "databar", cols = 1, rows = 1:12, type = "databar") ## Default colours

writeData(wb, "databar", -5:5, startCol = 2)
## set negative and positive colours
conditionalFormat(wb, "databar", cols = 2, rows = 1:12,
 rule = c("yellow", "green"), type = "databar")

## Save workbook
saveWorkbook(wb, "conditionalFormatExample.xlsx", overwrite = TRUE)

Want to suggest features or report bugs for rdrr.io? Use the GitHub issue tracker.