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

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)