How can i export a ggplot graph in Excel with writexl package in R?-CodePudding

i have a data frame in R :

library(tidyverse)
a = rnorm(10)
b = runif(10)
var = c(rep("chair",5),rep("table",5)  )
d = tibble(a,b,var);d

and a graph :

p2 = ggplot(data = d, aes(x=var, y=a))   
  geom_boxplot(aes(fill=a),outlier.shape=NA) 
  facet_wrap( ~ var, scales="free") 
  ggtitle("boxs") ;p2

now i want to export the data frame in a xlsx file in one sheet and in the same sheet or in a different sheet to export the ggplot graph with the use of writexl package in R.

For the data frame the tasj is easily implemented with :

writexl::write_xlsx(list(d),
                    path = paste("path\\name_file.xlsx"))

but i don't know under the writexl package this can be done for the graph.

Any help ?

CodePudding user response：

TBMK this is not possible using writexl. But using the openxlsx package you could export a ggplot via openxlsx::insertPlot. One remark: insertPlot will export the current (printed) plot. That's why I added the print(p2):

library(openxlsx)

wb <- createWorkbook()
addWorksheet(wb, "Data")
addWorksheet(wb, "Plot")

writeData(wb, "Data", d)
print(p2)
insertPlot(wb, "Plot")

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

UPDATE In case you want to export multiple datasets and plots you could put the code inside a function and use a loop:

Note: For simplicity I duplicated your data and plot.

dlist <- list(d, d)
plist <- list(p2, p2)

library(openxlsx)

wb <- createWorkbook()

export_xl <- function(i) {
  data_sheet <- paste("Data", i)
  plot_sheet <- paste("Plot", i)
  addWorksheet(wb, data_sheet)
  addWorksheet(wb, plot_sheet)
  
  writeData(wb, data_sheet, dlist[[i]])
  print(plist[[i]])
  insertPlot(wb, plot_sheet)
}

for (i in seq(2)) {
  export_xl(i)
}

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