I don't manage to make the function dataValidation of the package openxlsx work.
I did my homework and I'm aware of these 2 questions that unfortunately don't provide answers to my question:
`openxlsx` `dataValidation`: providing text input in list insteaf of cell reference
Possible to write Excel formulas or data validation using R?
Below is a reprex:
library("openxlsx")
OutputFolder <- file.path(".", "Output")
if(!dir.exists(OutputFolder)) dir.create(OutputFolder)
OutputFile <- file.path(OutputFolder, "Reprex_Openxlsx_dataValidation_list.xlsx")
Workbook4Export <- createWorkbook()
addWorksheet(wb = Workbook4Export, sheetName = "Tab_1", zoom = 80, gridLines = FALSE)
writeData(wb = Workbook4Export, sheet = "Tab_1", x = 2016, startRow = 1, startCol = 3)
writeData(wb = Workbook4Export, sheet = "Tab_1", x = 2017, startRow = 1, startCol = 4)
writeData(wb = Workbook4Export, sheet = "Tab_1", x = 2018, startRow = 1, startCol = 5)
writeData(wb = Workbook4Export, sheet = "Tab_1", x = 2019, startRow = 1, startCol = 6)
#dataValidation(wb = Workbook4Export, sheet = "Tab_1", rows = 1, col = 1, type = "list", value = eval(expression(paste0(2016:2019, collapse = "; "))))
dataValidation(wb = Workbook4Export, sheet = "Tab_1", rows = 1, col = 1, type = "list", value = "'Tab_1'!C1:F1")
writeFormula(wb = Workbook4Export, sheet = "Tab_1", startRow = 1, startCol = 2, x = '= HYPERLINK("[Reprex_Openxlsx_dataValidation_list.xlsx]Tab_1!" & CELL("address", INDEX(C1:F1, MATCH(A1, C1:F1, 0))), "Go to the selected column")')
saveWorkbook(wb = Workbook4Export, file = OutputFile, overwrite = TRUE)
First, I get this warning in R:
Warning message:
In sprintf("<x14:dataValidation type=\"list\" allowBlank=\"%s\" showInputMessage=\"%s\" showErrorMessage=\"%s\">", :
one argument not used by format '<x14:dataValidation type="list" allowBlank="%s" showInputMessage="%s" showErrorMessage="%s">'
Second, there is no drop-down list created in the generated Excel file.
Any idea of a solution?