Exporting multiple sheets using openxlsx in r

1
votes

I am trying to make 170 sheets (3 in the code below) in one excel file using openxlsx package. I used xlsx package before and used the option "append=TRUE" to create multiple sheets in one excel file but I could not find the same option in openxlsx package. 

system.time(
for (i in 1:3) {
write.xlsx(fulldata[[i]], file="fulltable5.xlsx", sheetName=cntry_name[i])
}
)

This code only creates only the third sheet. I have tried overwrite=TRUE but this only returns error. Could you please tell me which option corresponds to "append=TRUE" in the function write.xlsx() in xlsx package?

+ADD)))

This is a data that has similar size with my original data. It works for just one sheet but not for 172 sheets.

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

write.xlsx(a, file="fulltable6.xlsx")

And sadly, the R session is aborted. I need 172 sheets in 1 excel file. I am using Rstudio 3.4.0 and my computer is i3-5005U @ 2GHZ, 8gb RAM

1
rexcelxlsx

1 Answers

2
votes

Just pass your (named) list to write.xlsx: 

fulldata <- split(iris, seq_len(nrow(iris)) %/% 3)
names(fulldata) <- paste("sheet", seq_along(fulldata), sep="_")
library(openxlsx)
write.xlsx(fulldata, file=tf<-tempfile(fileext = ".xlsx"))
shell.exec(tf) # open file on windows