Plotting a graph in R and exporting it to Excel in a loop

Question

I have several countries in my data, and each country has 5 products. I performed a regression for each combination, and now I'm trying to plot the predicted values ​​compared to the actual values ​​on the same graph for each combination.

I have only 10 countries, and each country has its own tab in the Excel file. Since there are 5 products, this is a total of 50 schedules. I want to build graphs in R and export them to Excel in a loop. I am using the excel.link ggplot package, and the problem I am facing is that the graphs are displayed as empty space in Excel or if there is some graph in R, a graph appears instead of the required graph.

** Note. I used to get an error with the first graph, which said that "rversion was not found", but now I updated my RStudio and I no longer get this error. The chart will still be exported blank or the previous chart will appear instead.

Here is a simplified loop similar to the one in my code. If I started the loop manually, changing i each time, everything will be exported to OK. If I run for loop , there are problems described above:

require(excel.link)

set.seed(124)

for(i in 1:5){

 # i <- 2 myseq <- seq(1,100, by=1) norm <- rnorm(100) mydata <- as.data.frame(cbind(myseq, norm)) colnames(mydata) ggplot(data = mydata, aes(x=myseq, y=norm, group=1)) + geom_line(size=1, col="blue") + ggtitle(paste("My Plot ", i)) y.plot=current.graphics() xl[a1] = list(y.plot) 

}

Answer 1

First of all, you must explicitly print the ggplot2 graphics in a loop. Secondly, you put the graphs in excel on the same sheet in the same position. Thus, your graphs are located one on one, and you will see only the last graph in Excel. Code for displaying charts on separate sheets:

 library(excel.link) library(ggplot2) set.seed(124) xl.workbook.add() # open new workbook for(i in 1:5){ myseq <- seq(1,100, by=1) norm <- rnorm(100) mydata <- as.data.frame(cbind(myseq, norm)) colnames(mydata) p = ggplot(data = mydata, aes(x=myseq, y=norm, group=1)) + geom_line(size=1, col="blue") + ggtitle(paste("My Plot ", i)) print(p) ## display ggplot graphics y.plot=current.graphics() xl.sheet.add() ## add new sheet in excel workbook xl[a1] = y.plot } 

Code for displaying charts on one sheet from top to bottom:

 library(excel.link) library(ggplot2) set.seed(124) xl.workbook.add() # open new workbook y.plot = lapply(1:5, function(i) { myseq <- seq(1,100, by=1) norm <- rnorm(100) mydata <- as.data.frame(cbind(myseq, norm)) colnames(mydata) p = ggplot(data = mydata, aes(x=myseq, y=norm, group=1)) + geom_line(size=1, col="blue") + ggtitle(paste("My Plot ", i)) print(p) ## display ggplot graphics current.graphics() }) xl[a1] = y.plot