openxlsx包saveWorkbook()保存后公式列读取出NA的问题

问题场景 #

用R给Excel列填充公式并调用saveWorkbook()保存后，手动打开Excel显示正常，但用read.xlsx()读取时公式列返回NA。只有手动打开Excel并保存一次，才能读取到正确的计算值。

初始读取结果 #

read.xlsx("data/rough.xlsx", sheet = "Sheet1", rows = c(1:4), cols = c(2,3))

  a  b
1 1 NA
2 2 NA
3 3 NA

填充公式并保存的代码 #

wb <- loadWorkbook("data/rough.xlsx")
formula_vector <- c(paste0("B", seq(2,4), "*2"))
writeFormula(wb, sheet = "Sheet1", x = formula_vector, startCol = 3, startRow = 2)
saveWorkbook(wb, "data/rough.xlsx", overwrite = TRUE)

保存后读取仍返回NA #

read.xlsx("data/rough.xlsx", sheet = "Sheet1", rows = c(1:4), cols = c(2,3))

  a  b
1 1 NA
2 2 NA
3 3 NA

手动保存后读取正常 #

read.xlsx("data/rough.xlsx", sheet = "Sheet1", rows = c(1:4), cols = c(2,3))

  a b
1 1 2
2 2 4
3 3 6

原因分析 #

核心问题是公式未被计算并写入文件：writeFormula()仅写入公式字符串，saveWorkbook()保存时不会触发Excel的计算引擎生成对应数值，文件中只有公式，没有计算后的缓存值。而read.xlsx()默认读取的是缓存数值，不是实时解析公式，因此返回NA。手动打开Excel时，Excel会自动计算公式并生成数值缓存，保存后read.xlsx()就能读取到正确结果。

解决办法 #

方法1：保存前强制计算工作簿 #

使用openxlsx包的calculate()函数，在保存前触发公式计算，让文件包含计算后的数值：

wb <- loadWorkbook("data/rough.xlsx")
formula_vector <- c(paste0("B", seq(2,4), "*2"))
writeFormula(wb, sheet = "Sheet1", x = formula_vector, startCol = 3, startRow = 2)
# 强制计算所有公式
calculate(wb)
saveWorkbook(wb, "data/rough.xlsx", overwrite = TRUE)

保存后直接用read.xlsx()就能读取到正确数值。

方法2：读取时处理公式 #

如果不想修改原文件，可以读取公式字符串后手动计算：

# 读取公式字符串而非缓存数值
df <- read.xlsx("data/rough.xlsx", sheet = "Sheet1", rows = c(1:4), cols = c(2,3), getValues = FALSE)
# 手动计算b列值
df$b <- df$a * 2

适合只需要最终数值、不需要保留公式的场景。

方法3：使用xlsx包触发计算（需Java环境） #

xlsx包依赖Java，但可以在写入公式后强制计算：

library(xlsx)
wb <- loadWorkbook("data/rough.xlsx")
sheet <- getSheet(wb, "Sheet1")
# 循环写入公式
for(i in 2:4){
  cell <- createCell(sheet, rowIndex = i, colIndex = 3)[[1,1]]
  setCellFormula(cell, paste0("B", i, "*2"))
}
# 强制计算所有公式
evaluate(wb)
saveWorkbook(wb, "data/rough.xlsx")

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