Updating Excel spreadsheet data using xlsx package in R

Question

I have a query related to the xlsx package in R. I know how to use most of the functions in the package and have not yet encountered any problems. First I will show my code, and then I 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 aperture dataset and places it on a sheet called Sheet1, in an Excel table called test.xlsx. Now in excel, I decided to add additional content to the distribution sheet by adding a common row, so the last two rows of the Excel spreadsheet:

 virginica 5.9 3.0 5.1 1.8 876.5 458.6 563.7 179.9 

Another thing I'm doing is adding four more columns to the spreadsheet containing the percentage of each figure from the summary rows. Then I want to load test.xlsx into the workbook R. I define a new data frame called temp, with some random normal values. My intention is to update the numbers in the test.xlsx file so that the resulting row values ​​and percentages subsequently change. The last two lines of the updated spreadsheet should be something else, depending on the output of the rnorm values ​​(150,5,1). I save the data frame to a new table called testa.xlsx. The values ​​are updated, but for some reason, the total row values ​​and percentages remain unchanged, although their cell values, for example, are 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: 

So my question is how to load the workbook, update the numbers so that cells that have formulas in them will be subsequently updated and then saved in a new excel file. Maybe I can do it differently, but this will require additional steps, which I do not mind, but just updating the numbers so that other cells change, it would be so convenient. Again, as in every post I make, I ask you to forgive any grammatical errors and feel free to change any of these messages if you think it is necessary.

Answer 1

Before saving your book ( wb ) add the following:

 wb$setForceFormulaRecalculation(TRUE) 

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

Answer 2

If you want to update the formulas to by opening the Excel file, without , to open Microsoft Excel, in other words, "force recalculation of the formula"; 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") 

Additional Information at R-package-xlsx Google Groups