openxlsx2 (version 0.3.1)

wb_add_data_validation: Add data validation to cells

Description

Add Excel data validation to cells

Usage

wb_add_data_validation(
  wb,
  sheet = current_sheet(),
  cols,
  rows,
  type,
  operator,
  value,
  allowBlank = TRUE,
  showInputMsg = TRUE,
  showErrorMsg = TRUE,
  errorStyle = NULL,
  errorTitle = NULL,
  error = NULL,
  promptTitle = NULL,
  prompt = NULL
)

Arguments

wb

A workbook object

sheet

A name or index of a worksheet

cols

Contiguous columns to apply conditional formatting to

rows

Contiguous rows to apply conditional formatting to

type

One of 'whole', 'decimal', 'date', 'time', 'textLength', 'list' (see examples)

operator

One of 'between', 'notBetween', 'equal', 'notEqual', 'greaterThan', 'lessThan', 'greaterThanOrEqual', 'lessThanOrEqual'

value

a vector of length 1 or 2 depending on operator (see examples)

allowBlank

logical

showInputMsg

logical

showErrorMsg

logical

errorStyle

The icon shown and the options how to deal with such inputs. Default "stop" (cancel), else "information" (prompt popup) or "warning" (prompt accept or change input)

errorTitle

The error title

error

The error text

promptTitle

The prompt title

prompt

The prompt text

Examples

wb <- wb_workbook()
wb$add_worksheet("Sheet 1")
wb$add_worksheet("Sheet 2")

wb$add_data_table(1, x = iris[1:30, ])
wb$add_data_validation(1,
  col = 1:3, rows = 2:31, type = "whole",
  operator = "between", value = c(1, 9)
)
wb$add_data_validation(1,
  col = 5, rows = 2:31, type = "textLength",
  operator = "between", value = c(4, 6)
)

## Date and Time cell validation
df <- data.frame(
  "d" = as.Date("2016-01-01") + -5:5,
  "t" = as.POSIXct("2016-01-01") + -5:5 * 10000
)
wb$add_data_table(2, x = df)
wb$add_data_validation(2,
  col = 1, rows = 2:12, type = "date",
  operator = "greaterThanOrEqual", value = as.Date("2016-01-01")
)
wb$add_data_validation(2,
  col = 2, rows = 2:12, type = "time",
  operator = "between", value = df$t[c(4, 8)]
)


######################################################################
## If type == 'list'
# operator argument is ignored.

wb <- wb_workbook()
wb$add_worksheet("Sheet 1")
wb$add_worksheet("Sheet 2")

wb$add_data_table(sheet = 1, x = iris[1:30, ])
wb$add_data(sheet = 2, x = sample(iris$Sepal.Length, 10))

wb$add_data_validation(1, col = 1, rows = 2:31, type = "list", value = "'Sheet 2'!$A$1:$A$10")