Possible to write Excel formulas or data validation using R?

Question

I am trying to write a R data frame to Excel and want to add additional cells/columns that have Excel formulas and/or data validation values (e.g. using the Data/Validation menu in Excel to provide drop-down lists of allowable values for a cell)

I've looked at the R packages: xlsx, XLConnect and openxlsx. These are great for writing cells but not formulas or data validation settings.

I'm currently thinking that I have to resort to post-processing the xlsx files using either AppleScript (on Mac) or a VBA solution. I'd like to do it all in R if possible so the logic doesn't have to be split across programs.

Of the two: writing the data validation settings for a cell (vs. writing Excel formulas) is more important. The use case is writing a R data frame to Excel and including empty columns for making corrections. For the empty columns, I want to have drop-down lists of the acceptable values (e.g. "Yes"/"No") for that column.

Thanks in advance.

Answer 1

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