openxlsx (version 4.1.4)

dataValidation: Add data validation to cells

Description

Add Excel data validation to cells

Usage

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

Columns to apply conditional formatting to

rows

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

# NOT RUN {
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)]) 

# }
# NOT RUN {
saveWorkbook(wb, "dataValidationExample.xlsx", overwrite = TRUE)
# }
# NOT RUN {

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

# }