Writing multiple data frames as separate sheets in one Excel file

Question

I have a list of data frames:

df1 <- data.frame(
  col1  = c("1","2","3"),
  col2 = c("3","2","1")
)

df2 <- data.frame(
  col1  = c("1","1","1"),
  col2 = c("3","2","1")
)

df.list <- list(df1,df2)
names(df.list) <- c("df1","df2")

I want to write them to an Excel file with each data frame as a separate sheet, for which I tried the following:

library(xlsx)
excel <- "sample.xlsx"
for (i in 1:length(df.list)){
  sheet <- names(df.list)[i]
  if (i == 1) {
    write.xlsx(df.list[[i]], file=excel, sheetName=sheet, row.names = FALSE)
  }else{write.xlsx(df.list[[i]], file=excel, sheetName=sheet, append = TRUE, row.names = FALSE)}
  
}

But I get the following error:

Error in .jnew("org/apache/poi/xssf/usermodel/XSSFWorkbook") : 
  Java Exception <no description because toString() failed>createWorkbook(type = ext)new("jobjRef", jobj = <pointer: 0x5572117f3a90>, jclass = "java/lang/Throwable")

I have no clue what this error means and how to get the code to work.

Answer 1

Restarting R helped to get over the reported error. With my real data, I also got an out of memory error which was solved according to this solution. Seems that the best long-term solution is to switch to another package, such as openxlsx, as suggested by Gregor Thomas.