Mac下导入数据至Excel后公式无法自动更新的程序化解决问询

问题根源 #

openxlsx写入数据后不会触发Excel的内置计算引擎，保存的文件里公式还是旧的计算结果，只有手动打开Excel时才会重新计算，这就导致read_excel读不到最新值。

无需Java的程序化解决方案 #

1. 用openxlsx自带的计算函数 #

openxlsx内置了calculate函数，可以直接在R中触发公式计算，修改你的代码即可：

library(openxlsx)
wb <- loadWorkbook(excel_file)
writeData(wb, sheet = 'data', x = blank, startCol = 2, startRow = 23)
# 强制计算所有工作表的公式
calculate(wb)
saveWorkbook(wb,'Desktop/excel_file.xlsx',overwrite = TRUE)  

这个方法轻量，不需要额外依赖，适合大多数常规公式的批量处理。

2. 调用本地Excel后台计算（支持复杂公式） #

如果你的Excel里有数组公式、自定义函数这类openxlsxcalculate无法处理的内容，可以用RDCOMClient直接调用本地Excel实例完成计算：

library(openxlsx)
library(RDCOMClient)

# 先写入数据并保存临时文件
wb <- loadWorkbook(excel_file)
writeData(wb, sheet = 'data', x = blank, startCol = 2, startRow = 23)
saveWorkbook(wb,'Desktop/temp_excel.xlsx',overwrite = TRUE)  

# 启动Excel后台进程
xl_app <- COMCreate("Excel.Application")
xl_app[['Visible']] <- FALSE  # 不显示Excel界面
xl_wb <- xl_app$Workbooks()$Open(normalizePath('Desktop/temp_excel.xlsx'))

# 触发全表计算并保存
xl_wb$Calculate()
xl_wb$SaveAs(normalizePath('Desktop/excel_file.xlsx'))

# 关闭进程并释放资源
xl_wb$Close()
xl_app$Quit()
rm(xl_wb, xl_app)
gc()

这个方法完全模拟手动打开保存的操作，能处理所有Excel支持的公式，但前提是本地安装了Excel软件。

关于xlsx包的替代问题 #

不需要安装旧版Java用xlsx包，上面两个方案都不需要Java依赖，而且openxlsx的维护更活跃，更适配当前的R版本。

内容的提问来源于stack exchange，提问作者Overtime4728