在R中，openxlsx无法从.xlsx文件中读取

Question

我正在尝试使用openxlsx包在R中从.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在那里:

为什么无法读取这个单元格？我尝试将其写入文件后保存再次读取，但是即使这也失败了。openxlsx是我唯一的选择，因为从XLConnect等处出现JAVA错误。

- joel.wilson

---

请阅读在什么情况下我可以在我的问题中添加“紧急”或其他类似短语，以便获得更快的答案？ - 总结是这不是一个理想的方式来处理志愿者，并且可能会适得其反。请不要在您的问题中添加此内容。 - halfer

@halfer 谢谢您的通知。我还没有收到任何回复，我仍然卡在这里。 - joel.wilson

也许值得添加你提到的Java错误。另外，明天你可以为这个问题添加赏金，以诱使人们回答。 - halfer

Answer 1

?read.xlsx

使用writeFormula写入工作簿的公式将不会被read.xlsx()捕获。这是因为只有公式被写入，待文件在Excel中打开时才进行评估。打开、保存和关闭文件可以解决此问题。因此需要在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)

- zacdav

---

XLConnect可以动态地评估公式并给出答案，但它的问题在于它不支持来自Excel的转置和一些其他函数（用于我的计算）。它一直给我Apache POI数组错误。 - joel.wilson

那么使用openxlsx，保存它，用XLConnect读入，重新计算，然后保存？除此之外，我已经完全回答了这个问题。 - zacdav

虽然我还卡住了，但是打勾是我的职责！谢谢。 - joel.wilson

Answer 2

文件 https://cran.r-project.org/web/packages/openxlsx/openxlsx.pdf 中提到：

Workbook 对象将不会被 read.xlsx() 捕捉到。 这是因为只有公式被写入，而在 Excel 中打开文件时留待计算。 使用 Excel 打开、保存并关闭文件即可解决此问题。 因此，如果您正在使用 Windows，则可将以下文件保存为 vbs 文件，例如 opensaveexcel.vbs。

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("D:\Book2.xlsx")
objWorkbook.Save
objWorkbook.Close 
objExcel.Quit
Set objExcel = Nothing
Set objWorkbook = Nothing

然后你可以在Book1.xlsx中，像A4单元格的公式=A3*5一样编写R代码。

mywritexlsx(fname="d:/Book1.xlsx",data = 20,startCol = 1,startRow = 3)
system("cp d:\\Book1.xlsx d:\\Book2.xlsx")
system("cscript //nologo d:\\opensaveexcel.vbs")
tdt1=read.xlsx(xlsxFile = "d:/Book1.xlsx",sheet = "Sheet1",colNames = FALSE)
tdt2=read.xlsx(xlsxFile = "d:/Book2.xlsx",sheet = "Sheet1",colNames = FALSE)

顺便说一下，mywritexlsx 在我的电脑上可以运行

mywritexlsx<-function(fname="temp.xlsx",sheetname="Sheet1",data,
                  startCol = 1, startRow = 1, colNames = TRUE, rowNames = FALSE)
{
  if(!file.exists(fname))
  {
   wb = openxlsx::createWorkbook()
   sheet = openxlsx::addWorksheet(wb, sheetname)
  }
  else
 {
   wb <- openxlsx::loadWorkbook(file =fname)
   if(!(sum(openxlsx::getSheetNames(fname)==sheetname)))
   sheet = openxlsx::addWorksheet(wb, sheetname)
   else
    sheet=sheetname
  }

  openxlsx::writeData(wb,sheet,data,startCol = startCol, startRow = startRow, 
          colNames = colNames, rowNames = rowNames)
  openxlsx::saveWorkbook(wb, fname,overwrite = TRUE)
}

- makarand kulkarni