addImage() does not work with emf files created by devEMF::emf()

[RichardMeyer-Eppler]

I am using devEMF::emf() to create emf files. When inserting these files using addImage(), the cell of the resulting xlsx file remains blank. When I insert the same file by using "Insert -> Image" in Microsoft Excel, the image is displayed fine so the problem, presumably, is not with the created emf file itself.

I have tested different parameter configurations for devEMF::emf() (e.g. setting emfPLUS = FALSE), to no effect.

Unfortunately, I do not have access to emf files NOT created by devEMF::emf(), so I could not test whether the problem exists for all emf files in general, or only for the ones created by devEMF.

Using other devices / image formats (such as png) with the code below worked with no problems.

I have also looked at other packages, such as openxlsx::insertImage(), but that function does not support emf files in the first place.

It would be fantastic to get this working somehow. I have used devEMF::emf() extensively for R Markdown and Word documents, with great results, so I am very interested in getting this to work for Excel as well.

library(devEMF)
library(XLConnect)
#> Warning: package 'XLConnect' was built under R version 4.1.3
#> XLConnect 1.0.5 by Mirai Solutions GmbH [aut],
#>   Martin Studer [cre],
#>   The Apache Software Foundation [ctb, cph] (Apache POI),
#>   Graph Builder [ctb, cph] (Curvesapi Java library),
#>   Brett Woolridge [ctb, cph] (SparseBitSet Java library)
#> https://mirai-solutions.ch
#> https://github.com/miraisolutions/xlconnect

wb <- loadWorkbook("devEMF.xlsx", create = TRUE)
createSheet(wb, name = "devEMF")
createName(wb, name = "devEMF_plot", formula = "devEMF!$A$1")

devEMF::emf(file = "plot_test.emf")

plot(1,1)

dev.off()
#> png 
#>   2

addImage(wb, filename = "plot_test.emf", name = "devEMF_plot",
         originalSize = TRUE)

saveWorkbook(wb)

^{Created on 2022-05-02 by the reprex package (v2.0.1)}

Session info

``` r sessioninfo::session_info() #> - Session info --------------------------------------------------------------- #> setting value #> version R version 4.1.1 (2021-08-10) #> os Windows 10 x64 #> system x86_64, mingw32 #> ui RTerm #> language en #> collate German_Germany.1252 #> ctype German_Germany.1252 #> tz Europe/Berlin #> date 2022-05-02 #> #> - Packages ------------------------------------------------------------------- #> ! package * version date lib source #> cli 3.2.0 2022-02-14 [1] CRAN (R 4.1.3) #> crayon 1.4.2 2021-10-29 [1] CRAN (R 4.1.1) #> devEMF * 4.0-2 2020-10-01 [1] CRAN (R 4.1.1) #> digest 0.6.27 2020-10-24 [1] CRAN (R 4.1.1) #> ellipsis 0.3.2 2021-04-29 [1] CRAN (R 4.1.1) #> evaluate 0.15 2022-02-18 [1] CRAN (R 4.1.3) #> fansi 0.5.0 2021-05-25 [1] CRAN (R 4.1.1) #> fastmap 1.1.0 2021-01-25 [1] CRAN (R 4.1.1) #> fs 1.5.0 2020-07-31 [1] CRAN (R 4.1.1) #> glue 1.6.2 2022-02-24 [1] CRAN (R 4.1.3) #> highr 0.9 2021-04-16 [1] CRAN (R 4.1.1) #> htmltools 0.5.2 2021-08-25 [1] CRAN (R 4.1.1) #> knitr 1.38 2022-03-25 [1] CRAN (R 4.1.3) #> lifecycle 1.0.1 2021-09-24 [1] CRAN (R 4.1.1) #> magrittr 2.0.1 2020-11-17 [1] CRAN (R 4.1.1) #> pillar 1.7.0 2022-02-01 [1] CRAN (R 4.1.3) #> pkgconfig 2.0.3 2019-09-22 [1] CRAN (R 4.1.1) #> purrr 0.3.4 2020-04-17 [1] CRAN (R 4.1.1) #> R.cache 0.15.0 2021-04-30 [1] CRAN (R 4.1.3) #> R.methodsS3 1.8.1 2020-08-26 [1] CRAN (R 4.1.1) #> R.oo 1.24.0 2020-08-26 [1] CRAN (R 4.1.1) #> R.utils 2.11.0 2021-09-26 [1] CRAN (R 4.1.3) #> reprex 2.0.1 2021-08-05 [1] CRAN (R 4.1.3) #> D rJava 1.0-6 2021-12-10 [1] CRAN (R 4.1.2) #> rlang 1.0.2 2022-03-04 [1] CRAN (R 4.1.3) #> rmarkdown 2.11 2021-09-14 [1] CRAN (R 4.1.1) #> rstudioapi 0.13 2020-11-12 [1] CRAN (R 4.1.1) #> sessioninfo 1.1.1 2018-11-05 [1] CRAN (R 4.1.1) #> stringi 1.7.5 2021-10-04 [1] CRAN (R 4.1.1) #> stringr 1.4.0 2019-02-10 [1] CRAN (R 4.1.1) #> styler 1.7.0 2022-03-13 [1] CRAN (R 4.1.3) #> tibble 3.1.6 2021-11-07 [1] CRAN (R 4.1.1) #> utf8 1.2.2 2021-07-24 [1] CRAN (R 4.1.1) #> vctrs 0.3.8 2021-04-29 [1] CRAN (R 4.1.1) #> withr 2.5.0 2022-03-03 [1] CRAN (R 4.1.3) #> xfun 0.30 2022-03-02 [1] CRAN (R 4.1.3) #> XLConnect * 1.0.5 2021-10-01 [1] CRAN (R 4.1.3) #> yaml 2.2.1 2020-02-01 [1] CRAN (R 4.1.1) #> #> [1] D:/Data/Documents/R/win-library/4.1 #> [2] C:/Program Files/R/R-4.1.1/library #> #> D -- DLL MD5 mismatch, broken installation. ```

[RichardMeyer-Eppler]

After posting this issue, I realized I had not tested setting originalSize = FALSE. Running the example above with originalSize = FALSE leads to an image being displayed.

So the issue seems to be the combination of emf files with the parameter originalSize = TRUE.

My aim is to insert the image with width and height scaled to 100% of the dimensions specified by the graphics device.

[spoltier]

Thanks for reporting. It looks like there are some issues with EMF support in the underlying implementation (apache POI). https://stackoverflow.com/questions/68175940/resizing-an-emf-image-in-xssfworkbook

(Implementation note: if feasible, the fix be will have to be a fix in miraisolutions/xlconnect-java) Edit to add: the problem is directly caused / occurs here: https://github.com/miraisolutions/xlconnect-java/blob/bcdd32e2052e3864df8636c8dfda170b682a7550/src/main/java/com/miraisolutions/xlconnect/Workbook.java#L864)