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.

Unable to reproduce. After correcting the obvious typo (`df.listr`), the code runs without error and produces the expected output for me. Try restarting R and checking that you have the latest version of xlsx installed. — Limey, Aug 10 '22 at 16:31
Some people have trouble with `xlsx` and it's Java dependency. You may have more luck with another package, like `writexl` (very few dependencies) or `openxlsx` (requires Rtools). — Gregor Thomas, Aug 10 '22 at 17:52

score 0 · Accepted Answer · answered Aug 11 '22 at 07:42

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.

Writing multiple data frames as separate sheets in one Excel file

1 Answers1