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.

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)
#> # A tibble: 15 x 14 #> sheet ref type operator formula1 formula2 allow_blank show_input_mess… #> <chr> <chr> <chr> <chr> <chr> <chr> <lgl> <lgl> #> 1 Shee… A106 whole between 0 9 TRUE TRUE #> 2 Shee… A108 list NA $B$108 NA TRUE TRUE #> 3 Shee… A110 date between 2017-01… 2017-01… TRUE TRUE #> 4 Shee… A111 time between 00:00:00 09:00:00 TRUE TRUE #> 5 Shee… A112 text… between 0 9 TRUE TRUE #> 6 Shee… A114 whole notBetw… 0 9 TRUE TRUE #> 7 Shee… A115… whole equal 0 NA TRUE TRUE #> 8 Shee… A116 whole notEqual 0 NA TRUE TRUE #> 9 Shee… A117 whole greater… 0 NA TRUE TRUE #> 10 Shee… A119 whole greater… 0 NA TRUE TRUE #> 11 Shee… A120 whole lessTha… 0 NA TRUE TRUE #> 12 Shee… A118 whole lessThan 0 NA TRUE TRUE #> 13 Shee… A107 deci… notBetw… 0 9 FALSE FALSE #> 14 Shee… A113 cust… NA A113<=L… NA TRUE TRUE #> 15 Shee… A109 list NA $B$108 NA TRUE TRUE #> # … with 6 more variables: prompt_title <chr>, prompt_body <chr>, #> # show_error_message <lgl>, error_title <chr>, error_body <chr>, #> # error_symbol <chr>
xlsx_validation(examples, 1)
#> # A tibble: 15 x 14 #> sheet ref type operator formula1 formula2 allow_blank show_input_mess… #> <chr> <chr> <chr> <chr> <chr> <chr> <lgl> <lgl> #> 1 Shee… A106 whole between 0 9 TRUE TRUE #> 2 Shee… A108 list NA $B$108 NA TRUE TRUE #> 3 Shee… A110 date between 2017-01… 2017-01… TRUE TRUE #> 4 Shee… A111 time between 00:00:00 09:00:00 TRUE TRUE #> 5 Shee… A112 text… between 0 9 TRUE TRUE #> 6 Shee… A114 whole notBetw… 0 9 TRUE TRUE #> 7 Shee… A115… whole equal 0 NA TRUE TRUE #> 8 Shee… A116 whole notEqual 0 NA TRUE TRUE #> 9 Shee… A117 whole greater… 0 NA TRUE TRUE #> 10 Shee… A119 whole greater… 0 NA TRUE TRUE #> 11 Shee… A120 whole lessTha… 0 NA TRUE TRUE #> 12 Shee… A118 whole lessThan 0 NA TRUE TRUE #> 13 Shee… A107 deci… notBetw… 0 9 FALSE FALSE #> 14 Shee… A113 cust… NA A113<=L… NA TRUE TRUE #> 15 Shee… A109 list NA $B$108 NA TRUE TRUE #> # … with 6 more variables: prompt_title <chr>, prompt_body <chr>, #> # show_error_message <lgl>, error_title <chr>, error_body <chr>, #> # error_symbol <chr>
xlsx_validation(examples, "Sheet1")
#> # A tibble: 15 x 14 #> sheet ref type operator formula1 formula2 allow_blank show_input_mess… #> <chr> <chr> <chr> <chr> <chr> <chr> <lgl> <lgl> #> 1 Shee… A106 whole between 0 9 TRUE TRUE #> 2 Shee… A108 list NA $B$108 NA TRUE TRUE #> 3 Shee… A110 date between 2017-01… 2017-01… TRUE TRUE #> 4 Shee… A111 time between 00:00:00 09:00:00 TRUE TRUE #> 5 Shee… A112 text… between 0 9 TRUE TRUE #> 6 Shee… A114 whole notBetw… 0 9 TRUE TRUE #> 7 Shee… A115… whole equal 0 NA TRUE TRUE #> 8 Shee… A116 whole notEqual 0 NA TRUE TRUE #> 9 Shee… A117 whole greater… 0 NA TRUE TRUE #> 10 Shee… A119 whole greater… 0 NA TRUE TRUE #> 11 Shee… A120 whole lessTha… 0 NA TRUE TRUE #> 12 Shee… A118 whole lessThan 0 NA TRUE TRUE #> 13 Shee… A107 deci… notBetw… 0 9 FALSE FALSE #> 14 Shee… A113 cust… NA A113<=L… NA TRUE TRUE #> 15 Shee… A109 list NA $B$108 NA TRUE TRUE #> # … with 6 more variables: prompt_title <chr>, prompt_body <chr>, #> # show_error_message <lgl>, error_title <chr>, error_body <chr>, #> # error_symbol <chr>