Exporting data from R to Excel: formulas do not recalculate

user_3087798 0
Viewed 4174

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.

r excel
4 Answers

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)
user_40804830
Related