0

The documentation for openxlsx::dataValidation demonstrates how to get a dropdown menu by referencing cell(s). However, I'm interested in adding text input ("Yes" and "No") as dropdown menu options, but every attempt I've tried results in corrupted files (on Excel for Mac). Alternatives I've tried for the value argument involves "Yes;No", "Yes,No", "'Yes;No'", "'Yes';'No'", but all are unsuccessful.

Any suggestions on how to resolve this issue? I am aware that I can create yes and no as cell references from, for example, a separate sheet with such list inputs. But I would like to avoid it if possible.

Christian
  • 932
  • 1
  • 7
  • 22
  • This answer may help: https://stackoverflow.com/questions/29898269/possible-to-write-excel-formulas-or-data-validation-using-r/55191118#55191118 – Peter Jan 22 '22 at 22:02
  • Thank you @Peter. It's not my preferred solution to predefine the options in a list, but I may resort to it unless someone has a fix for this. – Christian Jan 23 '22 at 07:50
  • I have exactly the same need and thus, issue. See https://stackoverflow.com/questions/73837535/openxslx-issue-with-datavalidation – Olivier7121 Sep 24 '22 at 14:33

2 Answers2

1

It is not an available option with the dataValidation function in openxlsx. The list operator type only works with cell references in this function.

Bob Sleik
  • 26
  • 1
0

It is possible to work around this limitation.

Enter as value a comma-separated string starting and ending with \".

In the case in discourse: "\"Yes,No\""

# Library
library(openxlsx)

# Create workbook
wb <- createWorkbook()

# Add worksheet "Sheet 1" to the workbook
addWorksheet(wb, 'Sheet 1')

# Adds the first 30 lines of the iris dataset to the sheet "Sheet 1"
writeDataTable(wb, sheet = 1, x = iris[1:30, ])

# Creates a string named "validate" with a 10-unit sample of the Sepal.Length column
validate <-
  paste0("\"",
         paste0(sample(iris$Sepal.Length, 10),collapse = ','),
         "\"")

# Prints the string
print(validate)

[1] "\"4.7,7.2,5.8,4.3,5,5,6.8,7.4,5,6.4\""

# Add drop-downs to the first column on the sheet "Sheet 1"
dataValidation(wb, 1, col = 1, rows = 2:31, type = 'list', value = validate)

# Save workbook
saveWorkbook(wb, 'dataValidationExample.xlsx', overwrite = T)
Carlos Lessa
  • 1
  • 1