CODEWITHSUNDEEP
excelrformulaxlsxlsx
Yorgos
Yorgos

Reputation: 30445

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

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?

Upvotes: 5

Views: 4901

Answers (3)

k-zar
k-zar

Reputation: 249

You need to use the following

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

instead of

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

Upvotes: 3

Tucy
Tucy

Reputation: 7

Another Alternative method is output the csv file instead of xls, and then open it with excel.

Upvotes: -3

Andrie
Andrie

Reputation: 179398

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:

PS. Can you tell I like this package?

Upvotes: 7

Related Questions