11

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.

zx8754
  • 52,746
  • 12
  • 114
  • 209
Mac471
  • 423
  • 5
  • 16
  • I think expecting an R package to provide all the facilities of VBA is expecting way too much. – IRTFM Apr 27 '15 at 16:14
  • 1
    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. – Mac471 Apr 27 '15 at 16:35
  • 4
    Most of us hardcore R users consider Excel to be less "user friendly". – IRTFM Apr 27 '15 at 16:38
  • @BondedDust - thanks for your views on R and Excel! Much appreciated. Anyone else have any information relevant to the question posed? – Mac471 Apr 27 '15 at 17:04
  • I think the packages you mention only read excel data. See http://www.thertrader.com/2014/02/11/a-million-ways-to-connect-r-and-excel/ for other way around (through VBA). – Roman Luštrik Apr 28 '15 at 11:45
  • You can use setCellFormula in XLConnect to set cell formulas. Data validations, however, are not yet supported. – Martin Studer May 01 '15 at 13:05
  • @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 :-) – Mac471 May 05 '15 at 15:51

2 Answers2

13

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

TheNiceGuy
  • 3,462
  • 8
  • 34
  • 64
Nadzeya Yakimchyk
  • 356
  • 3
  • 7
  • Thanks for providing a reproducible example. Confirmed that it works and marked this solution as answering the original question. – Mac471 Mar 18 '19 at 02:13
  • 2
    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"))` – Max M Apr 16 '19 at 15:15
  • But how can I add dataValidation package to Angular?? – Faruk AYDIN Mar 22 '21 at 02:53
0

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.

Art
  • 1,165
  • 6
  • 18
  • 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. – Mac471 May 05 '15 at 15:48