exporting big excel file+ ggplots

[help]

hi I want to export a big excel with all the data frames in my environment starting with "animals" and also export all graphs that are also starting with "animals". note that this excel spreadsheet should include the elements in the lists that start with "animals" too.

I also want this excel spreadsheet to have a tab named after either the data frame being exported or the element in the list. is there a way someone can do this? here is my starter code but im very much struggling here. thank u ! !

library(openxlsx)
export <- mget(ls(pattern = "^animals"))

write.xlsx(export, file = "animals.xlsx", append = TRUE, rowNames = FALSE, overwrite = TRUE)

[DavoWW]

Hi @help,
Assuming that all your dataframes are in the current environment, and all the graphs are already created, then this code should get you started:

library(ggplot2)
library(openxlsx)

set.seed(42)
animals_dog <- data.frame(aa = c(1:10), bb=rnorm(10, 20, 3))
animals_cat <- data.frame(aa = c(1:10), bb=rnorm(10, 10, 3))
animals_pig <- data.frame(aa = c(1:10), bb=rnorm(10, 50, 3))

plot_dog <- ggplot(animals_dog, aes(x=aa, y=bb)) + geom_point(col="red")
plot_cat <- ggplot(animals_cat, aes(x=aa, y=bb)) + geom_point(col="blue")
plot_pig <- ggplot(animals_pig, aes(x=aa, y=bb)) + geom_point(col="skyblue")

all_dfs <- ls(pattern = "^animals")
all_plots <- ls(pattern = "^plot")

wb <- createWorkbook()

for (k in seq_along(all_plots)) {
  name <- addWorksheet(wb, all_plots[k]) 
  print(get(all_plots[k]))
  insertPlot(wb, sheet=name)                     
}

for (k in seq_along(all_dfs)) {
  name <- addWorksheet(wb, all_dfs[k]) 
  writeDataTable(wb, name, get(all_dfs[k]), startCol=2, startRow=3, rowNames = FALSE)
}

saveWorkbook(wb,"testing.xlsx", overwrite=TRUE)

Hope this helps.