r - openxlsx 无法从 R 中的 .xlsx 文件读取

Question

我正在尝试使用 R 中的 openxlsx 包从 .xlsx 文件中读取值。简而言之，我需要写入一行数据，然后填充一些必须在 R 中读回的输出单元格。我将分享一个示例来更好地解释问题。

.xlsx 文件的初始状态:

我现在正在尝试向单元格写入新值:A2:A3 = c("c", 5)。所以理想情况下，我期待 A6 = 15

下面是使用的代码:

require(openxlsx)
path <- "C:/path_to_file/for_SO1.xlsx"
input_row <- c("c", 5)
# Load workbook; create if not existing
wb <- loadWorkbook(path)
# createSheet(wb, name = "1")
writeData(wb, 
          sheet = "Sheet1",
          x = data.frame(input_row),
          startCol=1,
          startRow=1
)  

data_IM <- read.xlsx(wb, 
                     sheet = "Sheet1",
                     rows = c(5,6),
                     cols = c(1))
# Save workbook
saveWorkbook(wb, file = path, overwrite = TRUE)

#> data_IM
#  output_row
#1          3

但我得到了初始值(3)。但是，如果我打开 .xlsx 文件，我可以看到 15 驻留在其中:

无法读取此单元格的原因可能是什么？我尝试在写入文件后保存它并再次读取它，但即使这样也失败了。由于 XLConnect 等的 JAVA 错误，openxlsx 是我唯一的选择。

Answer 1

?read.xlsx

Formulae written using writeFormula to a Workbook object will not get picked up by read.xlsx(). This is because only the formula is written and left to be evaluated when the file is opened in Excel. Opening, saving and closing the file with Excel will resolve this.

因此需要在 Excel 中打开文件然后保存，我可以验证这是否有效。但是这可能不适合您。

XLConnect 似乎具有所需的功能

# rjava can run out of memory sometimes, this can help.
options(java.parameters = "-Xmx1G")
library(XLConnect)

file_path = "test.xlsx"

input_row <- c("c", 5)

wb <- loadWorkbook(file_path, create=F)
writeWorksheet(wb, 1, startRow = 1, startCol = 1, data = data.frame(input_row))
setForceFormulaRecalculation(wb, 1, TRUE)
saveWorkbook(wb)

# checking
wb <- loadWorkbook(file_path, create=F)
readWorksheet(wb, 1)