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

Description Usage Arguments Value Examples

View source: R/xlsx_validation.R

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

1
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.

Examples

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

Example output

    sheet            ref       type           operator            formula1
1  Sheet1           A106      whole            between                   0
2  Sheet1           A108       list               <NA>              $B$108
3  Sheet1           A110       date            between 2017-01-01 00:00:00
4  Sheet1           A111       time            between            00:00:00
5  Sheet1           A112 textLength            between                   0
6  Sheet1           A114      whole         notBetween                   0
7  Sheet1 A115,A121:A122      whole              equal                   0
8  Sheet1           A116      whole           notEqual                   0
9  Sheet1           A117      whole        greaterThan                   0
10 Sheet1           A119      whole greaterThanOrEqual                   0
11 Sheet1           A120      whole    lessThanOrEqual                   0
12 Sheet1           A118      whole           lessThan                   0
13 Sheet1           A107    decimal         notBetween                   0
14 Sheet1           A113     custom               <NA>     A113<=LEN(B113)
15 Sheet1           A109       list               <NA>              $B$108
              formula2 allow_blank show_input_message  prompt_title
1                    9        TRUE               TRUE message title
2                 <NA>        TRUE               TRUE          <NA>
3  2017-01-09 09:00:00        TRUE               TRUE          <NA>
4             09:00:00        TRUE               TRUE          <NA>
5                    9        TRUE               TRUE          <NA>
6                    9        TRUE               TRUE          <NA>
7                 <NA>        TRUE               TRUE          <NA>
8                 <NA>        TRUE               TRUE          <NA>
9                 <NA>        TRUE               TRUE          <NA>
10                <NA>        TRUE               TRUE          <NA>
11                <NA>        TRUE               TRUE          <NA>
12                <NA>        TRUE               TRUE          <NA>
13                   9       FALSE              FALSE          <NA>
14                <NA>        TRUE               TRUE          <NA>
15                <NA>        TRUE               TRUE          <NA>
    prompt_body show_error_message error_title error_body error_symbol
1  message body               TRUE error title error body         stop
2          <NA>               TRUE        <NA>       <NA>      warning
3          <NA>               TRUE        <NA>       <NA>         stop
4          <NA>               TRUE        <NA>       <NA>         stop
5          <NA>               TRUE        <NA>       <NA>         stop
6          <NA>               TRUE        <NA>       <NA>         stop
7          <NA>               TRUE        <NA>       <NA>         stop
8          <NA>               TRUE        <NA>       <NA>         stop
9          <NA>               TRUE        <NA>       <NA>         stop
10         <NA>               TRUE        <NA>       <NA>         stop
11         <NA>               TRUE        <NA>       <NA>         stop
12         <NA>               TRUE        <NA>       <NA>         stop
13         <NA>              FALSE        <NA>       <NA>         stop
14         <NA>               TRUE        <NA>       <NA>         stop
15         <NA>               TRUE        <NA>       <NA>  information
    sheet            ref       type           operator            formula1
1  Sheet1           A106      whole            between                   0
2  Sheet1           A108       list               <NA>              $B$108
3  Sheet1           A110       date            between 2017-01-01 00:00:00
4  Sheet1           A111       time            between            00:00:00
5  Sheet1           A112 textLength            between                   0
6  Sheet1           A114      whole         notBetween                   0
7  Sheet1 A115,A121:A122      whole              equal                   0
8  Sheet1           A116      whole           notEqual                   0
9  Sheet1           A117      whole        greaterThan                   0
10 Sheet1           A119      whole greaterThanOrEqual                   0
11 Sheet1           A120      whole    lessThanOrEqual                   0
12 Sheet1           A118      whole           lessThan                   0
13 Sheet1           A107    decimal         notBetween                   0
14 Sheet1           A113     custom               <NA>     A113<=LEN(B113)
15 Sheet1           A109       list               <NA>              $B$108
              formula2 allow_blank show_input_message  prompt_title
1                    9        TRUE               TRUE message title
2                 <NA>        TRUE               TRUE          <NA>
3  2017-01-09 09:00:00        TRUE               TRUE          <NA>
4             09:00:00        TRUE               TRUE          <NA>
5                    9        TRUE               TRUE          <NA>
6                    9        TRUE               TRUE          <NA>
7                 <NA>        TRUE               TRUE          <NA>
8                 <NA>        TRUE               TRUE          <NA>
9                 <NA>        TRUE               TRUE          <NA>
10                <NA>        TRUE               TRUE          <NA>
11                <NA>        TRUE               TRUE          <NA>
12                <NA>        TRUE               TRUE          <NA>
13                   9       FALSE              FALSE          <NA>
14                <NA>        TRUE               TRUE          <NA>
15                <NA>        TRUE               TRUE          <NA>
    prompt_body show_error_message error_title error_body error_symbol
1  message body               TRUE error title error body         stop
2          <NA>               TRUE        <NA>       <NA>      warning
3          <NA>               TRUE        <NA>       <NA>         stop
4          <NA>               TRUE        <NA>       <NA>         stop
5          <NA>               TRUE        <NA>       <NA>         stop
6          <NA>               TRUE        <NA>       <NA>         stop
7          <NA>               TRUE        <NA>       <NA>         stop
8          <NA>               TRUE        <NA>       <NA>         stop
9          <NA>               TRUE        <NA>       <NA>         stop
10         <NA>               TRUE        <NA>       <NA>         stop
11         <NA>               TRUE        <NA>       <NA>         stop
12         <NA>               TRUE        <NA>       <NA>         stop
13         <NA>              FALSE        <NA>       <NA>         stop
14         <NA>               TRUE        <NA>       <NA>         stop
15         <NA>               TRUE        <NA>       <NA>  information
    sheet            ref       type           operator            formula1
1  Sheet1           A106      whole            between                   0
2  Sheet1           A108       list               <NA>              $B$108
3  Sheet1           A110       date            between 2017-01-01 00:00:00
4  Sheet1           A111       time            between            00:00:00
5  Sheet1           A112 textLength            between                   0
6  Sheet1           A114      whole         notBetween                   0
7  Sheet1 A115,A121:A122      whole              equal                   0
8  Sheet1           A116      whole           notEqual                   0
9  Sheet1           A117      whole        greaterThan                   0
10 Sheet1           A119      whole greaterThanOrEqual                   0
11 Sheet1           A120      whole    lessThanOrEqual                   0
12 Sheet1           A118      whole           lessThan                   0
13 Sheet1           A107    decimal         notBetween                   0
14 Sheet1           A113     custom               <NA>     A113<=LEN(B113)
15 Sheet1           A109       list               <NA>              $B$108
              formula2 allow_blank show_input_message  prompt_title
1                    9        TRUE               TRUE message title
2                 <NA>        TRUE               TRUE          <NA>
3  2017-01-09 09:00:00        TRUE               TRUE          <NA>
4             09:00:00        TRUE               TRUE          <NA>
5                    9        TRUE               TRUE          <NA>
6                    9        TRUE               TRUE          <NA>
7                 <NA>        TRUE               TRUE          <NA>
8                 <NA>        TRUE               TRUE          <NA>
9                 <NA>        TRUE               TRUE          <NA>
10                <NA>        TRUE               TRUE          <NA>
11                <NA>        TRUE               TRUE          <NA>
12                <NA>        TRUE               TRUE          <NA>
13                   9       FALSE              FALSE          <NA>
14                <NA>        TRUE               TRUE          <NA>
15                <NA>        TRUE               TRUE          <NA>
    prompt_body show_error_message error_title error_body error_symbol
1  message body               TRUE error title error body         stop
2          <NA>               TRUE        <NA>       <NA>      warning
3          <NA>               TRUE        <NA>       <NA>         stop
4          <NA>               TRUE        <NA>       <NA>         stop
5          <NA>               TRUE        <NA>       <NA>         stop
6          <NA>               TRUE        <NA>       <NA>         stop
7          <NA>               TRUE        <NA>       <NA>         stop
8          <NA>               TRUE        <NA>       <NA>         stop
9          <NA>               TRUE        <NA>       <NA>         stop
10         <NA>               TRUE        <NA>       <NA>         stop
11         <NA>               TRUE        <NA>       <NA>         stop
12         <NA>               TRUE        <NA>       <NA>         stop
13         <NA>              FALSE        <NA>       <NA>         stop
14         <NA>               TRUE        <NA>       <NA>         stop
15         <NA>               TRUE        <NA>       <NA>  information

tidyxl documentation built on Nov. 16, 2020, 5:09 p.m.