Possible to write Excel formulas or data validation using R?
The example below shows how to add drop-down lists to Excel cells.
# Libraries
library(openxlsx)
# Create workbook
wb = createWorkbook()
# Add worksheet "Customers" to the workbook
addWorksheet(wb, "Customers")
# Create Customers dataframe
customers_df = data.frame("Name" = c("Alex", "Kate", "Mary"), "Gender" =
c("male", "female", "female"))
# Add Customers dataframe to the sheet "Customers"
writeData(wb, sheet = "Customers", x = customers_df, startCol = 1)
# Add worksheet "Drop-down values" to the workbook
addWorksheet(wb, "Drop-down values")
# Create drop-down values dataframe
gender_values_df = data.frame("Gender" = c("male", "female"))
# Add drop-down values dataframe to the sheet "Drop-down values"
writeData(wb, sheet = "Drop-down values", x = gender_values_df, startCol =
1)
# Add drop-downs to the column Gender on the worksheet "Customers"
dataValidation(wb, "Customers", col = 2, rows = 2:4, type = "list", value =
"'Drop-down values'!$A$2:$A$3")
# Save workbook
saveWorkbook(wb, "D:/Customers.xlsx", overwrite = TRUE)
More information can be found here: dataValidation