Illegal XML characters remain in vectors with formula class

[tpsteiner]

I am working on a project that uses write_xlsx() to create an excel data table with formulas. The XML file of the worksheet opens with this error:

Below is some reproducible code showing two working cases and one error case. When opening temp_path3, you should encounter the same error as above. Looking in the XML file, the & inside the "formula" column is not converted to &

library(openxlsx2)

# No error with & using character class
data1 <- tibble::tribble( ~right, '=CONCAT("Johnson & Johnson", 1)')
temp_path1 <- temp_xlsx()
write_xlsx(data1, temp_path1)
xl_open(temp_path1)

# No error with 'and' instead of & using formula class
data2 <- tibble::tribble( ~right, '=RIGHT("Johnson and Johnson", 2)')
temp_path2 <- temp_xlsx()
class(data2$right) <- c(class(data2$right), "formula")
write_xlsx(data2, temp_path2)
xl_open(temp_path2)

# Error XML illegal character & instead of &amp; using formula class
data3 <- tibble::tribble( ~right, '=RIGHT("Johnson & Johnson", 3)')
temp_path3 <- temp_xlsx()
class(data3$right) <- c(class(data2$right), "formula")
write_xlsx(data3, temp_path3)
xl_open(temp_path3)

> packageVersion("openxlsx2")
[1] ‘1.1’

This package has been very useful. Thanks for all that you do!

[JanMarvin]

Hi @tpsteiner , thanks for the kind words! I assume that the issue is another duplicate of the issue that was solved in #834 . Could you please check if this is solved with our development branch?

[tpsteiner]

Hi @JanMarvin thanks for the quick response. I feel dumb for not checking the issues list more thoroughly.
That issue seems to be solved on the dev branch.

devtools::install_github("JanMarvin/openxlsx2")

[JanMarvin]

No worries, thanks for confirming!