updating excel spreadsheet figures using the xlsx package in R -

updating excel spreadsheet figures using the xlsx package in R -

i have query relating xlsx bundle in r. know how utilize bulk of functions in bundle , have not run problems until now. first display code , inquire 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 takes iris info set , set sheet called sheet1 in excel spreadsheet called test.xlsx. in excel decide add together farther contents spread sheet adding total row, final 2 rows of excel spreadsheet are:

virginica 5.9 3.0 5.1 1.8 876.5 458.6 563.7 179.9

another thing add together 4 more columns spreadsheet containing percentage of each figure out of row totals. next, want load test.xlsx r workbook. define new info frame called temp random normal values. intention update figures in test.xlsx file row totals , percentages subsequently alter well. lastly 2 rows of updated spreadsheet should different depending on output of rnorm(150,5,1) values. save info frame new spreadsheet called testa.xlsx. values updated reason row totals , percentages remain unchanged though values of cell illustration 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 thus, how load workbook, update figures such cells have formulas in them subsequently updated , saved new excel file. can in way require more steps not mind doing updating figures other cells alter handy. 1 time again every post do, please forgive grammar mistakes , sense free alter of message if think necessary.

before saving workbook (wb), add together following:

wb$setforceformularecalculation(true)

if not work, there vba solution here.

r