从R导出数据到Excel：公式不重新计算

Question

R(3.0.2) / MSOffice 2013 / Win7

假设我们有一个现有的 data.xlsx 文件，其中包含两个工作表 - "data" 和 "calc"。

"data" 工作表是通过 R 填充的：

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)

所以问题来了 - 我有一个“calc”工作表，它引用了带有公式的“data”工作表。由于某种原因，即使公式引用刚刚填写的单元格，更新数据也不会重新计算。

Excel中的计算选项已经设置为自动，即使将其更改为手动并按下F9强制重新计算也无效。

我发现了一些奇怪的方法来解决这个问题：1.选择具有正确公式的特定单元格，然后按Enter键（对于每个单元格）2.再次拖动故障单元格上方的公式（用完全相同的公式覆盖公式..）3.从其他文件（例如，data2.xlsx）引用数据表确实有效，但对我来说，这是最后的选择。我不想用单独的数据文件膨胀文件结构。此外，请考虑打开两个文件才能使其工作的可怕要求。

提前致谢。

- statespace

---

我可以在Win7上的Excel2010中重现这个确切的问题。我的建议是向XLConnect的作者提交错误报告，并尝试使用xlsx包进行工作。顺便说一句，我尝试跳过您的setStyleAction行，但结果没有改变。我认为XLConnect中的某些内容正在破坏Excel用于“链接”公式到其引用单元格的内容。 - Carl Witthoft

我经常遇到这个问题。我不知道 xlsx 是否有同样的问题。我确实知道，如果我使用旧的文件格式（.xls），公式会重新计算。有时我会从 XLConnect 自己编写公式，但那是一个相当繁琐的解决方案。我同意 XLConnect 开发人员应该解决这个问题。 - joran

@joran 这真的应该成为我们所有人停止使用Excel的又一个理由 :-(。 - Carl Witthoft

我可以使用XLConnect 0.2-5在Excel for Mac 2011中重现这个问题。 - IRTFM

XLConnect附带了一个名为setForceFormulaRecalculation的方法，它控制一个标志，强制Excel在打开工作簿时重新计算公式值。但是，实际行为取决于上述许多因素。 - Martin Studer

Answer 1

如果你点击这个链接并且在评论区找到解决方案，那么答案就在这里：

wb$setForceFormulaRecalculation(T)

对于大多数包（至少是基于rJava的包），这应该有效，因为这个java方法是通过excel传递的，因此不受R语言的限制。

- statespace

Answer 2

在加载工作簿并写入数据后，强制进行公式重新计算，然后将工作簿保存到目录中。

以下代码可供参考：

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

- Dale Kube

Answer 3

我知道这篇文章已经有2.5年的历史了，但我刚遇到了和你一模一样的问题。我怀疑XLConnect存在一些问题。我使用了write.csv，它可以正常工作。我对write.csv的问题是我的数据框中有超过16384列，我无法再向文件中添加另一个表格。

- T-T

---

是的，我也正在经历这个问题。 - David Waterworth

Answer 4

您可以将此 VBA 宏放置在 Excel 文档的模块中，并在数据传输后（从 Excel 中）运行它。

    Sub RefreshCalculations()
        Calculate
    End Sub

即使是最简单的：在Excel中按下F9，这将强制计算所有公式。

对于我之前的答案感到抱歉。

这个宏对我有效，刚刚测试过：

Sub refresh2()
Dim sht As Worksheet

Dim rng0 As Range
Dim rng1 As Range
Dim c As Range

'loop through the sheets
For Each sht In ActiveWorkbook.Worksheets

    'limit the search to the UsedRange
    Set rng0 = sht.UsedRange
    On Error Resume Next ' in case of no formulas

    'reduce looping further
    Set rng1 = rng0.SpecialCells(xlCellTypeFormulas)

    For Each c In rng1 'loop through the SpecialCells only
        c.Formula = c.Formula
    Next

Next
End Sub

- Massimo Fuccillo

---

这不是OP说的：他已经尝试过了，我也是。问题比仅仅重新计算更深入。 - Carl Witthoft

我怀疑宏基本上和按F9键是一样的。并且，我想我已经在帖子中提到过尝试过这个了。 - statespace

那是一个非常有创意的解决方法，但我有两个反对意见。首先，如果工作表中有其他公式，它很可能会失控。其次，由于很明显“XLConnect”正在做一些“xlsx”类型的工作簿不喜欢的事情，因此应该修复“R”-软件包，而不是允许错误传播。 - Carl Witthoft

@Carl Witthof，是的，我同意你的观点，这只是一种解决方法，但你必须了解 Excel。Excel是一种前端软件，计算机制只在您通过键盘将数据输入单元格时触发。因此，解决方法是“触碰”工作表中的每个公式。还有，如果我坚持使用宏，那么问题是否已经得到解决？ - Massimo Fuccillo

让我们不要把这个对话引向主观性。并非每个用户都精通于超越电子表格软件（如 MS Office）之外的工具。 - statespace