使用“xlsx”导出excel文件时出现冲突和“openxlsx”包装在R [英] Clash when exporting excel file using "xlsx" and "openxlsx" packages in R

问题描述

我有一个数据的大小类似于

I have a data which has a size similar to "a" below

library(openxlsx)
a <- list()
names(a) <- paste("sheet", seq_along(fulldata), sep="_")  ### name for each sheet
for (i in 1:172) {
a[[i]] <- matrix(i,30,60)
}

write.xlsx(a, "a.xlsx")

如果我运行上面的代码，几秒钟后，R会自动关闭。

If I run the code above, few seconds later, R closes automatically.

library(xlsx)
options(java.parameters = "-Xmx4000m")
a <- list()
for (i in 1:172) {
a[[i]] <- matrix(i,30,60)
}
n <- paste("sheet", seq_along(fulldata), sep="_") ### name for each sheet

for (i in 1:172) {
write.xlsx(a[[i]], "c.xlsx", sheetName=n[[i]], append=TRUE)
}

如果我运行上面的代码，10分钟后，它返回一个缺少内存的错误。
我使用

If I run the code above, after 10 minutes, it returns an error about lack of memory. I used

options(java.parameters = "-Xmx4000m") 

增加要使用的内存但仍然是内存不足。

它们都可以用小数据工作，但是当我尝试同时导出172张时，它不起作用。我需要所有的172张表格都包含在一个excel文件中。

to increase the memory to be used but still, it says lack of memory.
Both of them works fine with small data but it doens't work when I try to export 172 sheets all at once. I need all the 172 sheets to be included in one excel file.

推荐答案

使用lapply创建表可能有助于缓解内存问题。

Creating the sheets using lapply may help alleviate the memory issue.

library(xlsx)

# Create the list of matrices
a <- list()
for (i in 1:172) {
  a[[i]] <- matrix(i,30,60)
}

# Set names for the matrices
names(a) <- seq_along(a)

# Create a workbook object
wb <- createWorkbook()

# Add each matrix to it's own worksheet inside of the workbook
lapply(seq_along(a), function(matrices, matrix.names, i){
                       ws <- createSheet(wb, matrix.names[[i]])
                       addDataFrame(matrices[[i]], ws)
                     }, matrices = a, matrix.names = names(a))

# Set the file path to save the workbook to
(output.path <- file.path(tempdir(), "output.xlsx"))

# Save the workbook
saveWorkbook(wb, output.path)

这篇关于使用“xlsx”导出excel文件时出现冲突和“openxlsx”包装在R的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！