rexcel-formulaopenxlsx

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


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


Solution

  • 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.

    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.