How can I write a formula in data table in openxlsx2

Question

I am trying to create a new column within a datatable that will essentially act like a "Text to Columns" in Excel. But when I try, it just writes the formula in a column outside the table and returns a #REF!.

I am having trouble finding Q/A for openxlsx2, has anyone figured this out?

NOTE: I have tried to "pre-add" a column and fill that too, but it also did not work.

Here is an example of my problem:

library(dplyr)
library(tidyr)
library(openxlsx2)

mtcars_model <- mtcars %>%
  mutate(model = rownames(.)
         # ,
         # formula = NA
         )

temp_path <- file.path(tempdir(), paste0(format(Sys.time(), format = "%m-%d-%H%M-%S"),"_mtcars_openxlsx2_test.xlsx"))

wb <- wb_workbook() %>%
  wb_add_worksheet("Data") 

wb$add_data_table(sheet = "Data",
                  x = mtcars_model,
                  tableName = "mtcars")

wb$add_formula(sheet = "Data",
               x = '=LEFT(mtcars[@model],FIND(" ",mtcars[@model],1))',
               array = TRUE,
               startRow = 2:(nrow(mtcars_model) + 1),
               startCol = which(colnames(mtcars_model) == "model" , arr.ind = TRUE) + 5)

wb$save(path = temp_path)

shell.exec(temp_path)

r openxlsx2 wb-add-formula wb-write-data-table

if you are using R, manipulate the data in R, create the newq variables in R. Ensure everything looks fine. Save the results as xlsx wooksheet/book. If you want all manipulations to be done in excel, i would advice to use excel and VBA — Onyambu
– Onyambu, Commented Sep 13, 2023 at 18:55

Jan Marvin · Accepted Answer · 2023-09-13 18:58:13Z

0

The documentation is hiding in plain sight.

library(openxlsx2)

mtcars_model <- mtcars %>%
  dplyr::mutate(model = rownames(.)
)

temp_path <- file.path(tempdir(), paste0(format(Sys.time(), format = "%m-%d-%H%M-%S"),"_mtcars_openxlsx2_test.xlsx"))

mtcars_model$formula <- 'LEFT(mtcars[[#This Row], [model]],FIND(" ", mtcars[[#This Row], [model]],1))'
class(mtcars_model$formula) <- "formula"

wb <- wb_workbook() %>%
  wb_add_worksheet("Data") %>% 
  wb_add_data_table(x = mtcars_model, table_name = "mtcars")

if (interactive()) wb$open()

answered Sep 13, 2023 at 18:58

Jan Marvin

1,0341 gold badge7 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

emi Over a year ago

thank you this worked! and thanks for the link i never got that as a result when googled. however in your code, the "table_name" argument is actually "tableName" in case you want to edit your response.

Jan Marvin Over a year ago

Thanks, but we switched to snake case in release 1.0, camel case is still supported though no longer the default.

emi Over a year ago

OH. lol im using 0.7.1.... time to update. thank you!

Collectives™ on Stack Overflow

How can I write a formula in data table in openxlsx2

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related