How to export separate worksheets in a same excel file within a for loop in R?

Question

I have some lists each including some data frames. Suppose the first list is like as:

df1 <- data.frame("id" = 1:2, "weight" = c(10,15), "Name" = c("ha","hu"))
df2 <- data.frame("id" = 3:4, "weight" = c(20,15), "Name" = c("hi","he"))
df3 <- data.frame("id" = 5:6, "weight" = c(10,20), "Name" = c("ho","hy"))
my_list_1 <- list(df1, df2, df3)

and the second one:

df4 <- data.frame("id" = 7:8, "weight" = c(5,6), "Name" = c("ma","mu"))
df5 <- data.frame("id" = 9:10, "weight" = c(20,12), "Name" = c("mi","me"))
df6 <- data.frame("id" = 11:12, "weight" = c(8,20), "Name" = c("mo","my"))
my_list_2 <- list(df4, df5, df6)

in truth, there are much more!

I want to write a for Loop and write within each Loop one of the lists to an Excel worksheet with the same Name as the list.

Dataframes should be placed one after other in the sheet and between any two dataframes there are two blank rows. I tried the following code:

library(openxlsx)
wb <- createWorkbook()
for (i in 1:2){
     addWorksheet(wb, paste0("my_list_",i))
     currRow <- 1
     for(j in 1:3){
          cs <- CellStyle(wb) + Font(wb, isBold=TRUE) + Border(position=c("BOTTOM", "LEFT", "TOP", "RIGHT"))
          addDataFrame(eval(parse(text=paste0("my_list_",i,"[[j]]"))),
                       sheet=paste0("my_list_",i),
                       startRow=currRow,
                       row.names=FALSE,
                       colnamesStyle=cs)
          currRow <- currRow + eval(parse(text=paste0("nrow(my_list_",i,"[[j]])"))) + 2 
     }
}
saveWorkbook(wb, file = "myfile.xlsx"))

but I got the error term:

Error in as.vector(x, "character") : 
  cannot coerce type 'environment' to vector of type 'character'

I will appreciate any contribution!

Answer 1

You are so close ! Look at the code bellow. I use writeData (doc) instead of addDataFrame. The arguments are closely the same.

Your data:

# First list
df1 <- data.frame("id" = 1:2, "weight" = c(10,15), "Name" = c("ha","hu"))
df2 <- data.frame("id" = 3:4, "weight" = c(20,15), "Name" = c("hi","he"))
df3 <- data.frame("id" = 5:6, "weight" = c(10,20), "Name" = c("ho","hy"))
my_list_1 <- list(df1, df2, df3)

# Second list
df4 <- data.frame("id" = 7:8, "weight" = c(5,6), "Name" = c("ma","mu"))
df5 <- data.frame("id" = 9:10, "weight" = c(20,12), "Name" = c("mi","me"))
df6 <- data.frame("id" = 11:12, "weight" = c(8,20), "Name" = c("mo","my"))
my_list_2 <- list(df4, df5, df6)

Export excel

# Module
library(openxlsx)

# Header style of each table in the excel file
hs1 <- createStyle(fgFill = "#DCE6F1", halign = "CENTER", textDecoration = "italic",
                   border = "Bottom")

# create workbook object
wb <- createWorkbook("Fred")

# For each list
for (i in 1:2){
  sheet <- paste0("my_list_",i)
  addWorksheet(wb, sheet)
  currRow <- 1
  for(j in 1:3){
    # Write the data frame
    writeData(wb = wb, 
              sheet = sheet,
              x = eval(parse(text=paste0("my_list_",i,"[[j]]"))),
              startRow = currRow, 
              borders="rows",
              headerStyle = hs1,
              borderStyle = "dashed",
              borderColour = "black"
             )
    # Update index
    currRow <- currRow + eval(parse(text=paste0("nrow(my_list_",i,"[[j]])"))) + 3 
  }
}

# Save file 
saveWorkbook(wb, file = "myfile.xlsx") #, overwrite = TRUE)

The output looks like this: