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")

# openXL(wb)