I am trying to export from R to excel an dataframe with two columns which I want to fill with the excel if formula so that the user could later on change the thresholds. My question is how can I export a formula like the following IF(C2>4; "YES";"NO") to every cell of a new column into excel.
The R code :
library(openxlsx)
library(dplyr)
export_df<- mtcars %>% tibble::rownames_to_column(var="carname")
# Formulas in EXCEL IF(C>4; "YES";"NO") IF(E>100; "YES";"NO")
export_df$many_cyl <- paste(paste(paste0(paste0("IF(C" ,seq(2,nrow(export_df)+1 ,1))," > 4")," 'Yes' ", sep=";")," 'No') ", sep=";")
export_df$fast_car <- paste(paste(paste0(paste0("IF(E" ,seq(2,nrow(export_df)+1,1))," > 100")," 'Yes' ", sep=";")," 'No')", sep=";")
class(export_df$many_cyl) <- c(class(export_df$many_cyl), "formula")
class(export_df$fast_Car) <- c(class(export_df$fast_car), "formula")
openxlsx::addWorksheet(wb,sheetName ="mtcars" )
openxlsx::writeData(wb,"mtcars",export_df )
openxlsx::saveWorkbook(wb, "mtcars.xlsx")
The way I try to create the excel formula does not work. The script fails at the step where I declare the columns to be formulas. The second way to create seperate vectors and export them to a workbook doesnt work either.
How can I solve this?
Your Excel formula is wrong. You need double quotes for yes and no and also commas to separate your arguments not semi colons. Try the following:
export_df$many_cyl <- paste(paste(paste0(paste0('=IF(C' ,seq(2,nrow(export_df)+1 ,1)),
' > 4'),' "Yes" ', sep=','),' "No") ', sep=',')
export_df$fast_car <- paste(paste(paste0(paste0('=IF(E' ,seq(2,nrow(export_df)+1,1)),
' > 100'),' "Yes" ', sep=','),' "No")', sep=',')
wb <- createWorkbook()
addWorksheet(wb, "Sheet 1")
writeFormula(wb, sheet = "Sheet 1", x = export_df$many_cyl, startCol = 2, startRow = 1)
saveWorkbook(wb, "writeFormulaExample.xlsx", overwrite = TRUE)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With