2

I am trying to create an overflowing array in R using openxlsx. My end result will have the user change items into row 1, so I need the UNIQUE formula to dynamically add/remove values after exporting.

Here's my reprex:

library(openxlsx)

testwb <- createWorkbook()
addWorksheet(testwb, "Sheet1")

df <- data.frame(c("one"),
                 c("two"),
                 c("three"),
                 c("two"),
                 c("four"))

writeData(testwb, sheet = "Sheet1", x = df, startCol = 1, startRow = 1, colNames = FALSE)

writeFormula(testwb, sheet = "Sheet1", x = "TRANSPOSE(UNIQUE(A1:E1, TRUE))", array = FALSE,
             startCol = 1, startRow = 3)

saveWorkbook(testwb, file = "test_output.xlsx", overwrite = TRUE)

openXL(testwb)

My expected output is this (works as intended with this formula): enter image description here

However, the code results in errors whether I set "array" within writeFormula to both TRUE and FALSE.

Output using array = FALSE (forces @ symbols into the formula): enter image description here

and output using array = TRUE (forces {} brackets): enter image description here

Any ideas how I can achieve the first output? Thanks :)

9
  • You may need to update your Excel. Commented Jul 20, 2024 at 1:51
  • To clarify - the output works as intended when I enter the formula directly into Excel (or tidy the formula up after export). It's only the output from openxlsx forcing either the {} brackets or @ symbols which breaks it. Commented Jul 20, 2024 at 2:25
  • 1
    Then you may need to update your R packages because I don't get the curly brackets when I run your code. Commented Jul 20, 2024 at 2:46
  • Oh interesting. I have version 2406 of excel in Microsoft365 which appears to be the most up-to-date version, and there are no updates available for openxlsx. I also get the same error with openxlsx2. Commented Jul 20, 2024 at 2:49
  • 1
    @OP, things like this are not possible with openxlsx. I have implemented a solution for openxlsx2 see here Commented Jul 20, 2024 at 10:37

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.