r - 从 R 写入 Excel 时处理 java.lang.OutOfMemoryError

Question

xlsx 包可用于从 R 读取和写入 Excel 电子表格。不幸的是，即使对于中等大小的电子表格，也可能会发生 java.lang.OutOfMemoryError。特别是，

Error in .jcall("RJavaTools", "Ljava/lang/Object;", "invokeMethod", cl, :
java.lang.OutOfMemoryError: Java heap space

Error in .jcall("RJavaTools", "Ljava/lang/Object;", "newInstance", .jfindClass(class), :
java.lang.OutOfMemoryError: GC overhead limit exceeded

(其他相关异常也是可能的，但较少见。)

在阅读电子表格时，有人就此错误提出了类似的问题。

Importing a big xlsx file into R?

与 CSV 相比，使用 Excel 电子表格作为数据存储介质的主要优点是您可以在同一个文件中存储多个工作表，因此这里我们考虑将数据框列表写入每个工作表一个数据框。此示例数据集包含 40 个数据框，每个数据框有两列，最多 200k 行。它被设计得足够大，可能会出现问题，但您可以通过更改 n_sheets 和 n_rows 来更改大小。

library(xlsx)
set.seed(19790801)
n_sheets <- 40
the_data <- replicate(
  n_sheets,
  {
    n_rows <- sample(2e5, 1)
    data.frame(
      x = runif(n_rows),
      y = sample(letters, n_rows, replace = TRUE)
    )
  },
  simplify = FALSE
)
names(the_data) <- paste("Sheet", seq_len(n_sheets))

将其写入文件的自然方法是使用 createWorkbook 创建工作簿，然后循环调用 createSheet 的每个数据帧和 addDataFrame 。最后可以使用 saveWorkbook 将工作簿写入文件。我已将消息添加到循环中，以便更容易地查看它在哪里失败。

wb <- createWorkbook()  
for(i in seq_along(the_data))
{
  message("Creating sheet", i)
  sheet <- createSheet(wb, sheetName = names(the_data)[i])
  message("Adding data frame", i)
  addDataFrame(the_data[[i]], sheet)
}
saveWorkbook(wb, "test.xlsx")  

在具有 8GB RAM 的计算机上以 64 位运行此程序，首次运行 addDataFrame 时会抛出 超出 GC 开销限制 错误。

如何使用 xlsx 将大型数据集写入 Excel 电子表格？

Answer 1

这是一个已知问题: http://code.google.com/p/rexcel/issues/detail?id=33

虽然尚未解决，但问题页面 links to a solution通过 Gabor Grothendieck建议在加载 rJava 包之前通过设置 java.parameters 选项来增加堆大小。 (rJava 是 xlsx 的依赖项。)

options(java.parameters = "-Xmx1000m")

值1000是Java堆允许使用的RAM的兆字节数；它可以替换为您喜欢的任何值。我的实验表明，值越大越好，您可以愉快地使用完整的 RAM 权利。例如，我使用以下方法获得了最佳结果:

options(java.parameters = "-Xmx8000m")

在具有 8GB RAM 的计算机上。

通过在循环的每次迭代中请求垃圾收集可以获得进一步的改进。正如 @gjabel 所指出的，R 垃圾收集可以使用 gc() 执行。 。我们可以定义一个Java垃圾回收函数，调用Java System.gc()方法:

jgc <- function()
{
  .jcall("java/lang/System", method = "gc")
}    

然后循环可以更新为:

for(i in seq_along(the_data))
{
  gc()
  jgc()
  message("Creating sheet", i)
  sheet <- createSheet(wb, sheetName = names(the_data)[i])
  message("Adding data frame", i)
  addDataFrame(the_data[[i]], sheet)
}

通过这两个代码修复，代码在抛出错误之前一直运行到 i = 29。

我尝试过的一种不成功的技术是使用write.xlsx2在每次迭代时将内容写入文件。这比其他代码慢，并且在第 10 次迭代时失败(但至少部分内容被写入文件)。

for(i in seq_along(the_data))
{
  message("Writing sheet", i)
  write.xlsx2(
    the_data[[i]], 
    "test.xlsx", 
    sheetName = names(the_data)[i], 
    append    = i > 1
  )
}