1

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?

Olivier7121
  • 151
  • 1
  • 11
  • The warning has been fixed here https://github.com/wleoncio/openxlsx/commit/9641184566c47bc2fb51fdf8345c754db3ecd078 but it looks like it hasn't made it to a release yet. Despite the warning, the code does seem to work. I successfully created a spreadsheet where the list options were cells c1:f1. Perhaps your version of openxlsx is behind the latest? – Andrew Chisholm Sep 24 '22 at 15:26
  • No, I use version 4.2.5, the latest to date. – Olivier7121 Sep 24 '22 at 17:14
  • I have the same - although I'm using Ubuntu 22 - and I can see a spreadsheet with the list correctly created. – Andrew Chisholm Sep 24 '22 at 20:37
  • Thanks for the feedback. I was initially using Windows 10. Now I just used Ubuntu (still 20) and OpenOffice 5.3 Calc to open the generated Excel file and I also saw the correct drop-down list. But under Ubuntu the hyperlink doesn't work anymore. I didn't get any warning in R BTW. – Olivier7121 Sep 24 '22 at 21:24
  • 1
    I created a new issue on Github: https://github.com/ycphs/openxlsx/issues/386 – Olivier7121 Sep 26 '22 at 14:48
  • The issue was that at least this particular feature of `openxlsx` (`dataValidation` with lists), and for that version of the package (4.2.5), wasn't working with Excel 2007 - it is working from Excel 2010 upwards. It is currently being corrected. See github link above. – Olivier7121 Oct 02 '22 at 08:16

0 Answers0