Openxlsx package - Writing multiple tables in one sheet?

Question

Is it possible to write multiple tables in one excel sheet? From what I have tried, I am seeing that only the last object is being written to the file. It could be that I am overwriting the file each time. So I am wondering how I can modify the code to write both tables in the same sheet? Thanks

data.sets <- ls("package:datasets")[c(5,7)]# list of data set names
foonames <- paste0("foo", seq_along(data.sets))
count <- length(data.sets)  
my.list <- list() 
for (i in 1:count) my.list[[foonames[i]]] <- get(data.sets[[i]])
lapply(my.list, dim)

filen<-sprintf('H:\\11_Analysis\\01_Eye Study\\Output\\Eyestudy1_%s.xlsx',
               Sys.Date());

library(openxlsx)
options("openxlsx.dateFormat" = "yyyy-mm-dd")

wb<-write.xlsx(my.list$foo1, filen, 
               startCol =2, startRow = 1, 
               asTable = T, withFilter = T, 
               colWidths="auto")

wb<-write.xlsx(my.list$foo1, filen, 
               startCol =2, startRow = 15, 
               asTable = T, withFilter = T, 
               colWidths="auto")

Answer 1

when you want to write multiple tables to the same sheet, you should create the workbook and then write the data to it and then save it. The way you are doing it seems to be overwriting the data. Here is what I would do:

wb <- createWorkbook()
addWorksheet(wb, sheetName = "sheet1")

writeData(wb, sheet = 1, x = df, startCol = 2, startRow = 2)
writeData(wb, sheet = 1, x = df2, startCol = 2, startRow = 24)

saveWorkbook(wb, file = "path/to/file.xslx")