使用openxlsx读写Excel文件时数据丢失问题求助

问题描述 #

我用R编写了一个Airflow自动化流程，从服务器导入数据并写入指定格式的Excel模板，后续步骤需要从该Excel文件读取数据作为输入。具体操作如下：

1. 复制现有Excel模板，将所有货币值替换为零（保留其他工作表的公式）
2. 使用openxlsx加载模板工作簿，将生成的DataFrame写入指定工作表，保存为output.xlsx

手动打开output.xlsx时数据正常，公式也能正确计算，但用R重新加载该文件时，读取到的仍是替换后的零值，只有手动打开并保存一次后，R才能读到新数据。无法使用依赖Java的XLConnect或xlsx包（存在Java堆空间问题），急需自动化的解决方案。

核心代码：

library(tidyverse)
library(lubridate)
library(openxlsx)

# 加载模板
path <- "template.xlsx"
wb <- loadWorkbook(path)

# 将DataFrame写入指定工作表
writeData(wb, sheet = "sheet_name", df, startCol = 1, startRow = 1, colNames = TRUE)

# 保存工作簿
path_save <- "output.xlsx"
saveWorkbook(wb, path_save, overwrite = TRUE)

可行解决方案 #

1. 改用writeDataTable替代writeData #

openxlsx的writeData可能存在单元格数据缓存问题，改用writeDataTable可强制将数据写入为Excel表格对象，确保内容被持久化：

# 替换原writeData代码
writeDataTable(wb, sheet = "sheet_name", df, startCol = 1, startRow = 1, colNames = TRUE)
saveWorkbook(wb, path_save, overwrite = TRUE)

该方法不会影响原有公式的计算，同时避免普通单元格的缓存不一致问题。

2. 强制设置工作簿自动计算模式 #

通过修改工作簿的calcMode属性为auto，确保保存时公式结果被计算并写入文件，而非仅保留公式本身：

# 保存前添加该行代码
wb$calcMode <- "auto"
saveWorkbook(wb, path_save, overwrite = TRUE)

这样后续用R读取时，能直接获取到最新的单元格值，而非旧的缓存数据。

3. 使用readxl读取数据 #

如果后续读取环节使用readxl包（无Java依赖），其读取逻辑可绕过openxlsx的缓存问题：

# 后续脚本读取数据时使用readxl
library(readxl)
new_df <- read_excel("output.xlsx", sheet = "sheet_name")

4. 全流程用openxlsx处理模板 #

将模板的零值替换操作也纳入openxlsx的工作流，避免外部修改导致的缓存不一致：

# 加载原始未修改的模板
wb <- loadWorkbook("original_template.xlsx")
# 替换目标工作表的货币值为零（根据实际单元格范围调整）
zero_df <- data.frame(rep(0, nrow(df))) # 生成对应行数的零值数据
writeData(wb, sheet = "sheet_name", zero_df, startCol = 2, startRow = 2) # 假设货币值在第2列、第2行开始
# 写入新数据
writeData(wb, sheet = "sheet_name", df, startCol = 1, startRow = 1, colNames = TRUE)
# 保存工作簿
saveWorkbook(wb, path_save, overwrite = TRUE)

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