Not able to create an array formula using the openxlsx package writeFormula function

Question

Using the openxlsx r package to write data to excel. Needing to include excel formulas that can be edited by end users so I'm using the openxlsx::writeFormula function to create array formulas that fill down to all applicable rows. When I check the excel file, the formulas are in a single cell only, they do not span vertically to all rows with data.

Example script:

library(tidyverse)
library(openxlsx)

x <- 1:5
data <- tibble(x, y = x * 2)

wb <- createWorkbook()
addWorksheet(wb, sheetName = "test")
writeFormula(wb, "test", x = "=A2/B2", startCol = "C", startRow = 2, array = TRUE)
writeData(wb, "test", data)
saveWorkbook(wb, file = "test.xlsx", overwrite = TRUE)

The excel file I created looks like this: enter image description here

I'm wanting a file that looks like this: enter image description here

Answer 1

An answer for openxlsx2 would be the following (slight extension from the previous answer by @Edward):

library(openxlsx2)
packageVersion("openxlsx2")
#> [1] '1.8'

df <- data.frame(
  x = 1:5,
  y = 1:5 * 2
)

wb <-  wb_workbook()$add_worksheet()$add_data(x = df)

# create required formulas
create_fml <- function(x) {
  lft <- dims_to_dataframe(wb_dims(x = x, cols = "x"), fill = TRUE)
  rgt <- dims_to_dataframe(wb_dims(x = x, cols = "y"), fill = TRUE)
  paste0(unlist(lft), "/", unlist(rgt)) 
}

wb$add_formula(
  x = create_fml(df),
  dims = "C2:C5",
  array = TRUE
)

if (interactive()) wb$open()

Just one additional note: array formulas in spreadsheets are required, when a result inside of the formula returns more than a single value. In modern spreadsheet software it is hidden to the user, when a cell requires array support.

• SUM(A1:A2) returns a single vector
• SUM(ABS(A1:A2)) returns a single vector, but ABS(A1:A2) does not return a single vector

In OPs example only single vectors are returned, therefore it is not necessary to use array formulas. What would help in this case, would be a shared formula. These are used internally by spreadsheet software, whenever you drag a formula horizontally or vertically. Unfortunately shared formulas are not implemented in either openxlsx nor openxlsx2.