Skip to contents

Create a formatted Excel file based on validation rules

Source: R/global.R
create_valid_excel.Rd

This function creates an Excel file with conditional formatting and data validation based on the given validation rules in a CSV or Excel file. This function is currently compatible with Windows and Linux operating systems. When using a macOS system, the excel file is able to download, but has some bugs with formatting the LOOKUP sheet.

Usage

create_valid_excel(
  file_rules,
  negStyle = createStyle(fontColour = "#9C0006", bgFill = "#FFC7CE"),
  posStyle = createStyle(fontColour = "#006100", bgFill = "#C6EFCE"),
  row_num = 1000
)

Arguments

file_rules

A CSV or Excel file containing validation rules.

negStyle

Style to apply for negative conditions (default is red text on a pink background).

posStyle

Style to apply for positive conditions (default is green text on a light green background).

row_num

Number of rows to create in the output file (default is 1000).

Value

A workbook object containing the formatted Excel file.

Examples

# \donttest{
data("test_rules")
create_valid_excel(file_rules = test_rules)
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> Warning: one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
#> A Workbook object.
#>  
#> Worksheets:
#>  Sheet 1: "RULES"
#>  
#> 
#>  Sheet 2: "particles"
#>  
#> 
#>  Sheet 3: "LOOKUP"
#>  
#> 
#>  Sheet 4: "methodology"
#>  
#> 
#>  Sheet 5: "samples"
#>  
#> 
#>  
#>  Worksheet write order: 1, 2, 3, 4, 5
#>  Active Sheet 1: "RULES" 
#> 	Position: 1
#> 
# }