exporting big excel file+ ggplots

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 ! !

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

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

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:


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]) 
  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.

This topic was automatically closed 42 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.