[R] Equation with double quotes from R to Excel?

[Liao, Hongsheng]

I am trying to add an equation with “” from R to an Excel workbook.  However, I have found that the Function “setCellFormula” doesn’t take the “” well while a “” in Excel equation stands for a blank cell.  I have tried NA(), EMPTY(), etc, and none of them are what I want.  Does anyone have other ways to put an equation with “” from R to Excel workbook?  Thanks in advance.

Following is simple codes just for the demonstration, and the error is highlighted in red.

#load package "XLConnect" to add functions from R to Excel
library(XLConnect)

# Load workbook (create if not existing)
wb <- loadWorkbook("try adding an equation.xlsx", create = TRUE)

# Create a sheet named 'data'
createSheet(wb, name = 'data')

#the formula contains no double quotes which works well
formula.without.double.quote <- "IF(A2>95, 1, 0)"
setCellFormula(wb, 'data', 2, 10, formula=formula.without.double.quote)

#the formula contains double quotes which doesn’t work with the function “setCellFormula”
formula.with.double.quote <- paste("IF(A2=", dQuote(""), ",", dQuote(""), ",1)", sep="")
setCellFormula(wb, 'data', 3, 10, formula=formula.with.double.quote)
Error: FormulaParseException (Java): Parse error near char 6 '“' in specified formula 'IF(A2=“”,“”,1)'. Expected cell ref or constant literal

#save the workbook
saveWorkbook(wb, file="C:\\Users\\try adding an equation.xlsx")

Hank

Hongsheng (Hank) Liao, PhD.
Lab Manager
Center for Quantitative  Fisheries Ecology
Old Dominion University
757-683-4571




	[[alternative HTML version deleted]]