xlsx_validation {tidyxl}

Import data validation rules of cells in xlsx (Excel) files

Description

xlsx_validation() returns the data validation rules applied to cells in xlsx (Excel) files. Data validation rules control what constants can be entered into a cell, e.g. any whole number between 0 and 9, or one of several values from another part of the spreadsheet.

Usage

xlsx_validation(path, sheets = NA)

Arguments

path

Path to the xlsx file.

sheets

Sheets to read. Either a character vector (the names of the sheets), an integer vector (the positions of the sheets), or NA (default, all sheets).

Value

A data frame with the following columns.

• sheet The worksheet that a validation rule cell is from.

• ref Comma-delimited cell addresses to which the rules apply, e.g. A106 or A115,A121:A122'.

• ⁠type Data type of input, one of ⁠whole⁠, ⁠decimal⁠, ⁠list⁠, ⁠date⁠, ⁠time⁠, ⁠textLength⁠, ⁠custom⁠, and ⁠whole'.

• operator Unless type is list or custom, then operator is one of between, notBetween, equal, notEqual, greaterThan, lessThan, greaterThanOrEqual, lessthanOrEqual.

• formula1 If type is list, then a range of cells whose values are allowed by the rule. If type is custom, then a formula to determine allowable values. Otherwise, a cell address or constant, coerced to character. Dates and times are formatted like "2017-01-27 13:30:45". Times without dates are formatted like "13:30:45".

• formula2 If operator is between or notBetween, then a cell address or constant as with formula1, otherwise NA.

• allow_blank Boolean, whether or not the rule allows blanks.

• show_input_message Boolean, whether or not the rule shows a message when the user begins entering a value.

• prompt_title Text to appear in the title bar of a popup message box when the user begins entering a value.

• prompt_body Text to appear in a popup message box when the user begins entering a value. When NA, then some default text is shown.

• show_error_message Boolean, whether or not the rule shows a message when the user has entered a forbidden value. When NA, then some default text is shown.

• error_title Text to appear in the title bar of a popup message box when the user enters a forbidden value. When NA, then some default text is shown.

• error_body Text to appear in a popup message box when the user enters a forbidden value. When NA, then some default text is shown.

• error_symbol Name of a symbol to appear in the popup error message when the user enters a forbidden value.

Examples

examples <- system.file("extdata/examples.xlsx", package = "tidyxl")
xlsx_validation(examples)
xlsx_validation(examples, 1)
xlsx_validation(examples, "Sheet1")