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.

Fenn

I think expecting an R package to provide all the facilities of VBA is expecting way too much. – Mastat

I'm not asking for R to replace VBA - just wanted to know if this capability exists or if people have an interest in seeing something like this developed. The data validation use case in particular would be very helpful for taking R data frames and exporting them into a more user-friendly environment for non-R users. – Fenn

Most of us hardcore R users consider Excel to be less "user friendly". – Mastat

@BondedDust - thanks for your views on R and Excel! Much appreciated. Anyone else have any information relevant to the question posed? – Fenn

I think the packages you mention only read excel data. See thertrader.com/2014/02/11/a-million-ways-to-connect-r-and-excel for other way around (through VBA). – Cerebral

You can use setCellFormula in XLConnect to set cell formulas. Data validations, however, are not yet supported. – Pion

@MartinStuder - thanks for the info on XLConnect - didn't realize that it had the capability to write cell formulas. Looking forward to seeing data validations supported :-) – Fenn

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

Oxyacid

Thanks for providing a reproducible example. Confirmed that it works and marked this solution as answering the original question. – Fenn

Is it possible to use values for the list that are not predefined in the Excel sheet somewhere? as in dataValidation(wb, "Customers", col = 2, rows = 2:4, type = "list", value = c("Yes", "No")) – Stralka

But how can I add dataValidation package to Angular?? – Iconography

Answer 2

As someone who goes back and forth between R and Excel, I would suggest you let Excel drive the workflow in this case. Either write Excel macros to import the data from a file that R creates or use RExcel (an Excel add-in) which allows interactive control of an R session from Excel.

Mystic

Good suggestion. I'm running R scripts on an AWS linux box and was hoping to avoid post-processing using Excel on a Windows PC. Thanks for the heads up on running an R session from within Excel using RExcel - will take a look at that. Perhaps I can execute a script that loads the data frames that I need from a saved image (RData file) and then create what I need from within Excel. – Fenn