openxlsx无法从R中的.xlsx文件读取 [英] openxlsx not able to read from .xlsx file in R

问题描述

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

I am trying to read value from a .xlsx file using openxlsx package in R. In simple words, I need to write a row of data, which then populates some output cell that has to be read back in R. I will share an example to better explain the problem.

.xlsx文件的初始状态:

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

I'm now trying to write new values to the cell : A2:A3 = c("c", 5). So ideally, I'm expecting 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驻留在其中的文件:

But I get the inital value(3). However, If i open the .xlsx file, I can see the 15 residing there:

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

What could be the reason for not able to read this cell? I tried saving it after writing to the file and again reading it but even that failed. openxlsx is the only option I have due to JAVA errors from XLConnect etc.

推荐答案

?read.xlsx

使用writeFormula写入Workbook对象的公式将不会获得 由read.xlsx()拾取.这是因为只写了公式 并在Excel中打开文件时留待评估.开幕 使用Excel保存并关闭文件即可解决此问题.

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中打开文件，然后将其保存，我可以验证它是否可以正常工作.但是，这可能不适合您.

So the file needs to be opened in Excel and then saved, I can verify that this does work. However this may not be suitable for you.

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)

这篇关于openxlsx无法从R中的.xlsx文件读取的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！