Cannot insert graph into XLSX via openxlsx package using command line

Question

I am trying to insert a plot into an XLSX file using the openxlsx package in R. When I use the R GUI, I can do this.

However, when using a batch file, a graph is created, but it is not inserted into the XLSX file. Instead, it is created as a separate PDF file adjacent to the newly created XLSX file (automatically called "Rplots.pdf"). The data frame is written to the XLSX file just fine.

R script (named "insertPlot.R"):

 library(ggplot2) library(openxlsx) wb <- createWorkbook() addWorksheet(wb, "Data") addWorksheet(wb, "Graph", gridLines=FALSE) df <- data.frame(c(1:5), c(5:1)) names(df) <- c("x","y") writeData(wb, "Data", df) p <- ggplot(aes(x=x, y=y), data=df) + geom_line(size=1, colour="blue") print(p) #plot needs to be showing insertPlot(wb, "Graph", width=11.18, height=7.82, fileType="png", units="in") saveWorkbook(wb, "test.xlsx", overwrite=TRUE) 

Batch script file:

 "C:\Program Files\R\R-3.1.3\bin\RScript.exe" --no-save --no-environ --no-init-file --no-restore --no-Rconsole "C:\temp\insertPlot.R" 

In conclusion, I am confused about how to execute an RScript batch file.

Has anyone been successful or can point out my mistake?

Answer 1

I believe that I found a way to easily accomplish this by outputting the graph to the png device, and then using insertImage from the openxlsx package.

 library(ggplot2) library(openxlsx) wb <- createWorkbook() addWorksheet(wb, "Data") addWorksheet(wb, "Graph", gridLines=FALSE) df <- data.frame(c(1:5), c(5:1)) names(df) <- c("x","y") writeData(wb, "Data", df) png("graph.png", width=1024, height=768, units="px", res=144) #output to png device p <- ggplot(aes(x=x, y=y), data=df) + geom_line(size=1, colour="blue") print(p) dev.off() #important to shut down the active png device insertImage(wb, "Graph", "graph.png", width=11.18, height=7.82, units="in") saveWorkbook(wb, "test.xlsx", overwrite=TRUE) #unlink("graph.png") #can optionally delete the original png file 

Hope this helps anyone who might run into the same problem.