updating excel spreadsheet figures using the xlsx package in R

Question

I have a query relating to the xlsx package in R. I know how to use the majority of the functions in the package and have not run into any problems until now. I will first display my code and then will ask my question.

#Code-Section1: 
library(xlsx) 
data1<-iris 
data1<-data1[,c(5,1:4)] 
wb <- createWorkbook() 
sheet1 <- createSheet(wb, sheetName="Sheet1") 
addDataFrame(data1, sheet1, startRow=1, startColumn=1,row.names=FALSE)  
saveWorkbook(wb, file="test.xlsx")  
rm(data1);rm(sheet1);rm(wb) 
#Code-Section1-end: 

This part simply takes the iris data set and put it into a Sheet called Sheet1 in a excel spreadsheet called test.xlsx. Now in excel I decide to add further contents to the spread sheet by adding a total row, so the final two rows of the excel spreadsheet are:

virginica   5.9     3.0     5.1     1.8
            876.5   458.6   563.7   179.9

Another thing I do is add 4 more columns to the spreadsheet containing the percentage of each figure out of the row totals. Next, I want to load the test.xlsx into a R workbook. I define a new data frame called temp with some random normal values. My intention is to update the figures in the test.xlsx file so that the row totals and the percentages will subsequently change as well. The last two rows of the updated spreadsheet should be something different depending on the output of the rnorm(150,5,1) values. I save the data frame into a new spreadsheet called testa.xlsx. The values are updated but for some reason the row totals and the percentages remain unchanged even though the values of their cell for example is still "=sum(b2:b151)" or "=b2/b$152".

#Code-Section2: 
temp <- data.frame(Sepal.Length=rnorm(150,5,1), Sepal.Width=rnorm(150,5,1), Petal.Length=rnorm(150,5,1), Petal.Width=rnorm(150,5,1)) 
wb<-loadWorkbook("test.xlsx") 
f<-getSheets(wb)[[1]] 
addDataFrame(temp, sheet=f, startRow=2, startColumn=2,row.names=FALSE,col.names=FALSE) 
saveWorkbook(wb, file="testa.xlsx") 
#Code-Section2-end: 

My question is thus, how do I load a workbook, update the figures such that the cells that have formulas in them will be subsequently updated and then saved to a new excel file. I can probably do it in another way but it would require more steps which I do not mind doing but simply updating figures so other cells would change would be so handy. Again as with every post I do, please forgive any grammar mistakes and feel free to change any of this message if you think it is necessary.

Answer 1

Before saving your workbook (wb), add the following:

wb$setForceFormulaRecalculation(TRUE)

If it does not work, there is also a VBA solution here.

Answer 2

If you want to update the formulas before opening the Excel file, without needing to open Microsoft Excel, in other words, "force the formula recalculation"; you can use the solution from Apache POI. Adapted for R:

library(xlsx)
wb<-loadWorkbook("test.xlsx")
sheets <- getSheets(wb)
sheet <- sheets[["Sheet1"]]
rows  <- getRows(sheet)
cells <- getCells(rows)
setCellValue(cells[["1.1"]], 999) # Change a number inside the file
wb$getCreationHelper()$createFormulaEvaluator()$evaluateAll() #This is the trick
saveWorkbook(wb,"test.xlsx")

More information at R-package-xlsx Google Groups