openxlsx writing if formula from R to excel

Question

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?

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. — S.K.

LyzandeR LyzandeR · Accepted Answer · 2017-11-21T09:58:32

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)

openxlsx writing if formula from R to excel

1 Answers