Add formulas to row

[vladimirovnikita]

Hello!
Please tell me how to add formulas to a row?
The code below does not do what I expect and inserts formulas into column "A"

library(openxlsx2)

wb <- wb_workbook()

wb$add_worksheet()

wb$add_data(x=data.frame(
                       `A`=c(1,2),
                       `B`=c(3,4),
                       `C`=c(5,6)),
            col_names = FALSE)

wb$add_formula(x = c("A1*A2","B1*B2","C1*C2"), 
               dims = "A3:C3")  

wb_save(wb, file = "test.xlsx")

[JanMarvin]

Hi @vladimirovnikita ,

currently positioning formulas with dims is not yet possible. Though I will open a pull request later today that I have been working on lately, that might change this.

For the time being you can use the following. This constructs a data frame with a formula class that contains the formula that you want to write. A second approach would be to loop over the cells with formulas that you want to write and place them with dims.

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

wb <- wb_workbook()

wb$add_worksheet()

wb$add_data(x=data.frame(
  `A`=c(1,2),
  `B`=c(3,4),
  `C`=c(5,6)),
  col_names = FALSE)

# formula data frame that can be used to write formulas row wise
fml <- data.frame(
  sumA = "A1*A2",
  sumB = "B1*B2",
  sumC = "C1*C2"
)
class(fml$sumA) <- c("formula", "character")
class(fml$sumB) <- c("formula", "character")
class(fml$sumC) <- c("formula", "character")

wb$add_data(x = fml, dims = "A3:C3", col_names = FALSE)

if (interactive()) wb$open()

[JanMarvin]

Just a follow up. With openxlsx2 1.5 the initial code works just fine. No more need for my hack fix above.