Exporting data from R to Excel: formulas do not recalculate

Question

R(3.0.2) / MSOffice 2013 / Win7

Assume we have an existing data.xlsx file with two sheets - "data" and "calc".

"data" sheet is filled through R with:

require(XLConnect)
df <- data.frame(c(1,2,3,4,5), c(2,3,4,5,6))
wb <- loadWorkbook("data.xlsx", create=F)
setStyleAction(wb, type=XLC$"STYLE_ACTION.NONE")
writeWorksheet(wb, data=df, sheet="data", startRow=1, startCol=1, header=F)
saveWorkbook(wb)

So here comes the problem - I have "calc" sheet that refers to "data" sheet with formulas. For some reason updating the data doesn't get recalculated even though formulas refer to cells that were just filled in.

Calculation option in Excel is turned to automatic, even turning it to manual and pressing F9 to force for recalculation doesn't work.

I've found some odd ways to make this work: 1. select particular cell which has proper formula in it and press enter (for each and every cell) 2. drag formulas over malfunctioning cells over again (overwrite formula with the exact same formula..) 3. Refering to data sheet from other file (say, data2.xlsx) does work but this for me is a last resort option. I don't want to bloat file structure with separate data files. Plus, consider this horrible requirement of opening both file in order to make it work.

Thanks in advance.

Answer 1

Looking that this link gets some clicks and solution is hidden in comments section, here's the answer:

wb$setForceFormulaRecalculation(T)

This should work for most of packages (at least ones based on rJava) as this java method is passed within excel, hence is not determined by R language.

Answer 2

After you load a workbook and write in data, force a formula recalculation before saving the workbook to a directory.

This should work for you:

writeWorksheet(wb, data=df, sheet="data", startRow=1, startCol=1, header=F)
setForceFormulaRecalculation(wb,"data",TRUE)
saveWorkbook(wb)