a question from a new user when reading openxlsx2 book

[anticmason]

Hi，
I‘m the new user for the package:openxlsx2.
Recently, I'm reading the openxlsx2 book, I've encountered a question in the chapter " Data Validation", code related shown as follows:

wb <- wb_workbook()$
    add_worksheet()$add_data(x = iris[1:30, ])$
    add_worksheet()$add_data(sheet = 2, x = sample(iris$Sepal.Length, 10))$
    add_data_validation(sheet = 1, dims = "A2:A11", type = "list", value = '"O1,O2"')$
    add_data_validation(sheet = 1, dims = "A12:A21", type = "list", value = '"O2,O3"')

Why should parameter "value" be shown as ' "O1,O2" ', when I try to change it to
" 'O1,O2' ",it didn't work but sending a warning in excel...

Could you please explain the reason.....

Thanks a lot ~

[JanMarvin]

Hi @anticmason , thanks for asking! (I just converted the issue to a discussion).

Ultimately it boils down to lazy coding as in ... if it works this way, why bother with further tweaking.

In the internals of wb_add_data_validation() we need the value argument as input for our XML. For the drop drown list, OOXML specifies that the drop down elements need to be added as a double quoted, comma separated child to the formula node. We construct the XML string here:

openxlsx2/R/class-worksheet.R

Line 1049 in 665b620

sprintf("<formula%s>%s</formula%s>", i, value[i], i)

We can see how R handles this:

values <- "'O1,O2'"
sprintf("<formula%s>%s</formula%s>", 1, values, 1)
#> [1] "<formula1>'O1,O2'</formula1>"

values <- '"O1,O2"'
sprintf("<formula%s>%s</formula%s>", 1, values, 1)
#> [1] "<formula1>\"O1,O2\"</formula1>"

As we can see, only the second variant returns the expected outcome. Excel has many cases in which it more sloppy with the XML input - and other spreadsheet software as well, e.g. it might be perfectly fine for LibreOffice - but here Excel is picky for some reason.

The ECMA OOXML specifications are not helpful in this case (see below), therefore I assume that it was reverse engineering that revealed the hidden magic Excel demands.

The first formula in the Data Validation dropdown. It is used as a bounds for 'between' and 'notBetween'
relational operators, and the only formula used for other relational operators (equal, notEqual, lessThan,
lessThanOrEqual, greaterThan, greaterThanOrEqual), or for custom or list type data validation. The content can
be a formula or a constant or a list series (comma separated values).

ECMA-376 Part 1, 18. SpreadsheetML Reference Material (p. 1635)

When I mentioned sloppy coding, I guess we could try to capture "'O1,O2'" and convert ' to \". Or even allow "O1,O2", but there might be other cases that rely on the sprintf() logic above. If you want to toy around with it a bit, PRs to do not break what works now and improve the current code, are always welcome.

[JanMarvin]

So this would work as well:

library(openxlsx2)
wb <- wb_workbook()$
  add_worksheet()$add_data(x = iris[1:30, ])$
  add_worksheet()$add_data(sheet = 2, x = sample(iris$Sepal.Length, 10))$
  add_data_validation(sheet = 1, dims = "A2:A11", type = "list", value = '"O1,O2"')$
  add_data_validation(sheet = 1, dims = "A12:A21", type = "list", value = "\"O2,O3\"")