Write multiple sheets to multiple xlsx workbooks from multiple lists of dataframes

Question

I'm trying to export multiple lists of dataframes to multiple sheets of multiple .xlsx workbooks. Each list contains a different type of data, with one dataframe for each of >100 locations, and each workbook should contain the data for a single location with different sheets for each data type.

I've tried using various packages (xlsx, write_xlsx, XLConnect) but other answers have led me to openxlsx (e.g. here and here).

library(openxlsx)

My data are from database SQL queries, and the example below replecates my data structure.

placeID <- c("place_1","place_2") # vector of place names

#Create first list of data frames:
place_1<- data.frame(variable1= c(1, 2, 3),
                        variable2 = c('A', 'B', 'C'),
                          variable3 = c("place1","place1","place1"))

place_2 <- data.frame(variable1= c(1, 2, 3),
                      variable2 = c('D', 'E', 'F'),
                      variable3 = c("place2","place2","place2"))

firstdata_all<-list(place_1,place_2) %>% set_names(placeID)

#Create second list of data frames:
place_1 <- data.frame(variable1= c(1, 2, 3),
                      variable2 = c('G', 'H', 'I'),
                      variable3 = c("place1","place1","place1"))

place_2 <- data.frame(variable1= c(1, 2, 3),
                      variable2 = c('J', 'K', 'L'),
                      variable3 = c("place2","place2","place2"))

seconddata_all<-list(place_1,place_2) %>% set_names(placeID)

I can save multiple single data frames to a workbook (i.e. just place 1):

list_of_datasets <- list("Name of sheet 1" = firstdata_all$place_1, "Name of sheet 2" = seconddata_all$place_1)
write.xlsx(list_of_datasets, file = paste("place1_",as.character(Sys.Date()), ".xlsx", sep = ""), append = TRUE)

And I can save the first list of data frames to sheet 1 of multiple workbooks (i.e. one sheet/data type per 'place'):

for(n in names(firstdata_all))
  openxlsx::write.xlsx(firstdata_all[[n]],
                       file = paste(firstdata_all[[n]][,"variable3"][1],"_",
                                    as.character(Sys.Date()), ".xlsx", sep = ""), 
                       sheetName = "First Data")

But can anyone tell me please how I can achieve these things simultaneously? I can't find a way to append a sheet to an existing .xlsx file, and have also tried various way to loop/append/map through the above code. Thanks.

Answer 1

I came up with a working method following the initial suggestion to use workbooks in this answer, before it was expanded, and although not as efficient as that solution I thought it might be helpful to post. Note there is lots of flexibility to add styling and functionality to the workbooks with openxxlsx functions such as writeData, addFormula, setColWidths, addStyle, modifyBaseFont etc.

write_VCexport<- function (names_list, firstdata_all, seconddata_all) {
  
  for(n in names(all_VC_Records)){
    Datatype_1 <- firstdata_all [[n]]         
    Datatype_2 <- seconddata_all [[n]]          
    
    wb <- openxlsx::createWorkbook("n") 
    
    addWorksheet(wb, "Datatype_1") 
    addWorksheet(wb, "Datatype_2")
    
    openxlsx::writeData(wb, sheet = 1, Datatype_2)     
    openxlsx::writeData(wb, sheet = 2, Datatype_1)          

        openxlsx::saveWorkbook(wb, file = paste0(name, ".xlsx"))
    
      }
    }

    write_VCexport(names_list, Datatype_1, Datatype2)

Answer 2

A simple way to achieve what you are looking for are workbooks. Below an example that creates and fills sheets while looping over n. You should be able to loop over workbooks as well.

library(openxlsx)

n <- 42

wb <- createWorkbook()
for (i in 1:n) {
  sheet <- paste("Sheet", i)

  # add worksheet to workbook
  addWorksheet(wb, sheet)

  # write mtcars data
  writeData(wb, sheet, x = mtcars)
}
saveWorkbook(wb, "file.xlsx")

Edit: My comment from below for the answer.

tmp <- c(firstdata_all, seconddata_all)

for (name in placeID) {
  out <- tmp[names(tmp) == name]
  assign(name, out) # if you want to look at it in R
  openxlsx::write.xlsx(out, sheetName = seq_along(out), paste0(name, ".xlsx"))
}