Add data validation to cells

Add Excel data validation to cells

dataValidation(
  wb,
  sheet,
  cols,
  rows,
  type,
  operator,
  value,
  allowBlank = TRUE,
  showInputMsg = TRUE,
  showErrorMsg = TRUE
)

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

Examples

wb <- createWorkbook()
addWorksheet(wb, "Sheet 1")
addWorksheet(wb, "Sheet 2")

writeDataTable(wb, 1, x = iris[1:30, ])

dataValidation(wb, 1,
  col = 1:3, rows = 2:31, type = "whole",
  operator = "between", value = c(1, 9)
)

dataValidation(wb, 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
)

writeData(wb, 2, x = df)
dataValidation(wb, 2,
  col = 1, rows = 2:12, type = "date",
  operator = "greaterThanOrEqual", value = as.Date("2016-01-01")
)

dataValidation(wb, 2,
  col = 2, rows = 2:12, type = "time",
  operator = "between", value = df$t[c(4, 8)]
)
if (FALSE) {
saveWorkbook(wb, "dataValidationExample.xlsx", overwrite = TRUE)
}


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

wb <- createWorkbook()
addWorksheet(wb, "Sheet 1")
addWorksheet(wb, "Sheet 2")

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

dataValidation(wb, 1, col = 1, rows = 2:31, type = "list", value = "'Sheet 2'!$A$1:$A$10")
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'

# openXL(wb)