Skip to contents

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

Source: R/xlsx_validation.R
xlsx_validation.Rd

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