R语言如何自动完成指定目录下Excel文件的打开、保存与关闭操作？

1. 现象原因 #

问题根源是第三方系统导出的带公式Excel文件未预存公式计算结果缓存：

• 多数业务系统导出Excel时，只会写入公式表达式，不会执行公式计算、把结果值写入文件的缓存区域
• 手动打开Excel文件时，Office程序会自动触发全表公式重算，执行保存操作时，重算得到的结果值会被写入文件缓存
• R的Excel读取包（包括readxl、openxlsx）本身不具备Excel公式的计算能力，默认只会读取缓存的结果值，没有缓存的情况下就会读出空值/错误值，也就是你遇到的异常结果。

2. 自动处理的实现方案 #

可以通过R调用Excel程序的COM接口完成自动打开、重算、保存、关闭的全流程，无需手动操作，Windows环境下可使用RDCOMClient包实现，代码示例如下：

# 首次运行需先安装依赖包
# install.packages("RDCOMClient")

library(RDCOMClient)

# 定义批量处理Excel的函数
batch_save_excel <- function(file_paths) {
  # 启动Excel进程，后台运行不显示界面
  excel_app <- COMCreate("Excel.Application")
  excel_app[["Visible"]] <- FALSE
  excel_app[["DisplayAlerts"]] <- FALSE # 关闭弹窗提示避免中断
  
  for (fp in file_paths) {
    # 打开目标文件
    wb <- excel_app$Workbooks$Open(normalizePath(fp))
    # 强制重算所有工作表公式
    wb$Calculate()
    # 保存文件，重算结果会被写入文件缓存
    wb$Save()
    # 关闭当前工作簿
    wb$Close()
  }
  # 退出Excel进程，释放系统资源
  excel_app$Quit()
  rm(excel_app)
  gc()
}

# 调用示例：传入你要处理的Excel文件路径向量即可
# batch_save_excel(store.file)

# 处理完成后运行你原有读取逻辑即可，此处修复了你原有代码的入参笔误
store.FUN = function(x)
{
  m = as.data.frame(read_excel(x))
  names(m) = c(1:length(m))
  m[1, 1] = str_sub(m[2, 1], 13)
  m = bind_rows((m)[1, ], subset(m, (m)[1] == "Total Income"))
  m[2, 1] = m[2, 2]
  m = m[-c(2)]
  return(m)
}

district_1.stores = sapply(store.file, store.FUN, simplify=FALSE) %>% 
  bind_rows(.id = "Store ID")

如果你使用macOS系统，可使用openxlsx2包的xl_calc()函数实现公式重算，不需要调用本地Excel程序，兼容性更好。

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