Can I write Excel formulas or validate data with R?

Question

I am trying to write an R data file in Excel and want to add additional cells / columns with Excel formulas and / or data (for example, using the Data / Validation menu in Excel to provide drop-down lists of valid cell values)

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

Currently, I think I have to resort to post-processing xlsx files using AppleScript (on Mac) or a VBA solution. I would like to do all this in R, if possible, so the logic should not be shared between programs.

Of the two: writing data verification parameters for a cell is more important (compared to writing Excel formulas). A use case is to write an R data frame in Excel and include empty columns for corrections. For empty columns, I want to have drop-down lists of valid values ​​(for example, "Yes" / "No") for this column.

Thanks in advance.

Answer 1

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