openxlsx (version 4.0.0)

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'

operator

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

value

a vector of length 1 or 2 depending on operator

allowBlank

logial

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

saveWorkbook(wb, "dataValidationExample.xlsx", overwrite = TRUE)