Pass a formula to an xls file using R's xlsx package

Question

Here is the code I used.

library(xlsx)
wb <- loadWorkbook('D:/test.xls') 
sheets <- getSheets(wb) 
sheet <- sheets[['my_sheet']]

addDataFrame(x = ds, sheet = sheet, row.names = FALSE, col.names = FALSE, startRow=3, startColumn=1) 

cell.1 <- createCell(rows[1], colIndex=34)[[1,1]]
setCellValue(cell.1, "=A32*B33")
saveWorkbook(wb, 'D:/test.xls')

Adding a dataframe worked without a problem. But when opening the xls file, I saw the text "=A32*B33" in cell A34 (an extra ENTER needs to be pressed in order for the formula to work). Can you help me enter a formula correctly?

Answer 1

I have used package xlsx in the past, and despite great promise, found it severely lacking in functionality and ease of use. When I searched the package manual a minute ago, it doesn't seem possible to do what you want to do.

The good news is there is an alternative in the form of package XLConnect. XLConnect uses the same Java code (from the Apache POI project) as xlsx, so you still have the same high level of interoperability between operating systems.

XLConnect has a function that does what you need: setCellFormula().

References:

• The package manual at http://cran.r-project.org/web/packages/XLConnect/XLConnect.pdf

• Also, I highly recommend the excellent vignette: http://cran.r-project.org/web/packages/XLConnect/vignettes/XLConnect.pdf

---

PS. Can you tell I like this package?

Answer 2

You need to use the following

cell.1$setCellFormula("A32*B33")

instead of

setCellValue(cell.1, "=A32*B33")