I am facing error in pasting an image in Excel from R using package openxlsx

Question

I am trying to paste an image saved in my folder to Excel using R package openxlsx. I have completely studied the package documentation and followed the steps given in the documentation. But even the example given in package is not working for me

## Create a new workbook
wb <- createWorkbook("Ayanami")
## Add some worksheets
addWorksheet(wb, "Sheet 1")
addWorksheet(wb, "Sheet 2")
addWorksheet(wb, "Sheet 3")
## Insert images
img <- system.file("einstein.jpg", package = "openxlsx")
insertImage(wb, "Sheet 1", img, startRow = 5, startCol = 3, width = 6, height = 5)
insertImage(wb, 2, img, startRow = 2, startCol = 2)
insertImage(wb, 3 , img, width = 15, height = 12, startRow = 3, startCol = "G", units = "cm")
## Save workbook
saveWorkbook(wb, "insertImageExample.xlsx", overwrite = TRUE)

This is the example given in package documentation. Instead of "einstein.jpg", I am using my ".jpg" file. I am trying to paste that image in my workbook 'wb'. The function "system.file" does not fetch the image I am passing. I have made sure that there is no issues related to path whether image has been stored.

Could anyone of help me with this function or has any verified alternative?

Your title says that you are using the xlsx package, but `insertImage` is from openxlsx instead. What are you using? — G5W, Apr 25 '19 at 13:50
Yes, you are right. Openxlsx is the package. It was a typo. Though the codes given in the package are not working for me. That remains true. — Dhruv Desai, Apr 25 '19 at 15:23

score 0 · Answer 1 · answered Apr 29 '19 at 06:26

0

You shouldn't use system.file function as the image that you are trying to paste in the workbook isn't a system file.

instead you will need something like:

img <- "C:/your_dir/your_filename.jpg"

insertImage(wb, "Sheet 1", img, startRow = 5, startCol = 3, width = 6, height = 5)
saveWorkbook(wb, "insertImageExample2.xlsx", overwrite = TRUE)

answered Apr 29 '19 at 06:26

MKa

2,248
16
22

Thanks for your help. However, this is not working for me. The command { img <- "C:/your_dir/your_filename.jpg" } does not load the image file. Instead it only saves the path into the variable "var" – Dhruv Desai May 01 '19 at 17:28
That is what it supposed to do. I thought you were trying to insert a plot saved in your directory to an Excel file? `img` variable will have the path of the variable and when you pass `img` to `insertImage()` function that this should insert the plot to an excel file. – MKa May 01 '19 at 23:33
But the insertImage function is not pasting the plot in my Excel file. I used the same syntax as you have shared. – Dhruv Desai May 02 '19 at 14:13
Does it return any error message when you run `insertImage` function? And make sure you are accessing the correct Excel file, check where this file is saved at: `getwd()` – MKa May 02 '19 at 22:36
No , it does not give any error while running insertImage function. The code is pointing to correct Excel file. I have confirmed the same using getwd(). – Dhruv Desai May 24 '19 at 09:06

score 0 · Answer 2 · answered Dec 03 '20 at 09:16

For context, you are likely stepping through the documentation for openxlsx here: ycphs.github.io/openxlsx/articles/Introduction.html

The link to the image is incorrect on the link. You should use:

img <- readJPEG(file.path(path.package("openxlsx"), "extdata/einstein.jpg"))

score -1 · Answer 3 · answered Nov 01 '20 at 08:16

-1

Try something like this:

    library(jpeg)
    img<-readJPEG("einstein.jpg")
    plotFn(img)
    insertPlot(wb, 1)
    saveWorkbook(wb, "insertImageExample.xlsx", overwrite = TRUE)
    file.show("insertImageExample.xlsx")

answered Nov 01 '20 at 08:16

SHan

1

You are referring to an article that actually creates a function called plotFn. See link here: https://ycphs.github.io/openxlsx/articles/Introduction.html – Nick Dec 03 '20 at 09:14

I am facing error in pasting an image in Excel from R using package openxlsx

3 Answers3