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
Equation with double quotes from R to Excel?
3 messages · Liao, Hongsheng, Sarah Goslee
This doesn't answer your actual question, but isn't it better practice to use ISBLANK instead of ""? As for your actual question, a check of the parts of your command at the R prompt would probably reveal something interesting:
paste("IF(A2=", dQuote(""), ",", dQuote(""), ",1)", sep="")
[1] "IF(A2=??,??,1)"
options(useFancyQuotes=FALSE)
paste("IF(A2=", dQuote(""), ",", dQuote(""), ",1)", sep="")
[1] "IF(A2=\"\",\"\",1)" This bit of your output should have been a hint: IF(A2=????? ,????? ,1) Sarah
On Fri, May 13, 2016 at 9:24 AM, Liao, Hongsheng <HLiao at odu.edu> wrote:
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
Sarah, Thank you very much. The following codes you sent to me work perfectly. "IF(A2=\"\",\"\",1)" in R becomes "IF(A2="","",1)" in Excel. Wonderful! Hank Hongsheng (Hank) Liao, PhD. Lab Manager Center for Quantitative Fisheries Ecology Old Dominion University 757-683-4571
options(useFancyQuotes=FALSE)
paste("IF(A2=", dQuote(""), ",", dQuote(""), ",1)", sep="")
-----Original Message----- From: Sarah Goslee [mailto:sarah.goslee at gmail.com] Sent: Friday, May 13, 2016 9:41 AM To: Liao, Hongsheng Cc: R-help Mailing List Subject: Re: [R] Equation with double quotes from R to Excel? This doesn't answer your actual question, but isn't it better practice to use ISBLANK instead of ""? As for your actual question, a check of the parts of your command at the R prompt would probably reveal something interesting:
paste("IF(A2=", dQuote(""), ",", dQuote(""), ",1)", sep="")
[1] "IF(A2=??,??,1)"
options(useFancyQuotes=FALSE)
paste("IF(A2=", dQuote(""), ",", dQuote(""), ",1)", sep="")
[1] "IF(A2=\"\",\"\",1)" This bit of your output should have been a hint: IF(A2=????? ,????? ,1) Sarah
On Fri, May 13, 2016 at 9:24 AM, Liao, Hongsheng <HLiao at odu.edu> wrote:
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