[R] Equation with double quotes from R to Excel?
HLiao at odu.edu
Fri May 13 15:24:05 CEST 2016
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
# 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")
Hongsheng (Hank) Liao, PhD.
Center for Quantitative Fisheries Ecology
Old Dominion University
[[alternative HTML version deleted]]
More information about the R-help