2

I'm planning on using R markdown to generate summary one-pager PDF's from primary data stored in Excel spreadsheets. My collaborators are already familiar with the plots that are created in these spreadsheets so I don't want to re-create them. Is there an easy way to pull these existing plots out of the Excel spreadsheets as an image and drop them into the R markdown report?

EKnos
  • 35
  • 3
  • Off-topic as it is asking for advice on external packages. However one option and likely a simple one, is to look for a package that grants access to the VBA object models, export the plot as a temporary png, and use the png in your Rmarkdown file. Someone might know of a simpler solution. – Oliver Jul 16 '19 at 23:07
  • 6
    Quick answer: No. there is no single function. There are of course packages that offer varying degrees of interoperability between R and Excel. However, that means you are asking us for a package rec, and those are off-topic. Perhaps if you did a bit of searching and posted a [MCVE] and `library` calls to load packages that had such capabilities and show how much of the manuals and vignettes you had read and [edit]-ed your Q to offer code to make an attempt at progress ... Then we would have a StackOverflow-worthy question. – IRTFM Jul 16 '19 at 23:07
  • 1
    Personal opinion (having done this same thing, converting "excel-comfortable processes to R-based PDFs"): reproduce the plots as best you can in R with base, `ggplot2`, or `plotly` (esp if chances are good you'll be using the package elsewhere soon), and then hand-hold your collaborators through the changeover process. Often we find there is something significantly better about the R-produced plots that Excel just cannot reproduce (easily if at all). (For me, this is part of evolving from a legacy spreadsheet-based data store to a database.) – r2evans Jul 17 '19 at 01:10
  • 1
    It doesn't currently allow this straight from Excel, but the `officer` package can extract images from PowerPoint docs: https://davidgohel.github.io/officer/articles/officer_reader.html#import-powerpoint-document You might also want to look into `redoc`, which allows tracking changes back and forth between Word and R Markdown: https://github.com/noamross/redoc – Jon Spring Jul 17 '19 at 06:37
  • 1
    If by excel you mean something with an xlsx extension, note the internal contents of these files are often easier to navigate if the file's saved as a .zip file or .rar file and unzipped. By changing the file extension you may be able to identify a folder within the file with the images, as in the last proposed solution here: https://www.excelhowto.com/how-to-quickly-extract-pictures-from-an-excel-file/ – JonMinton Jul 17 '19 at 07:43
  • Your best bet would be use available VBA script to export all charts to images then use Rmarkdown to grab those into the report https://stackoverflow.com/q/11939087/786542 | https://peltiertech.com/export-chart-as-image-file/ | https://stackoverflow.com/q/25353221/786542 | https://stackoverflow.com/q/7492519/786542 – Tung Jul 17 '19 at 16:08

1 Answers1

0

I was able to export a chart from Excel with the following code : (see How to export an Excel sheet range to a picture, from within R)

library(RDCOMClient) 
xlApp <- COMCreate("Excel.Application")
xlWbk <- xlApp$Workbooks()$Open("C:\\Graph Excel_Test2.xlsx")
xlScreen <- 1
xlBitmap <- 2

xlWbk$ActiveSheet()$ChartObjects(1)$Activate()
graph <- xlWbk$ActiveChart()$SeriesCollection(1)
graph$Select()

oCht <- xlApp$ActiveChart()
oCht$Paste()
oCht$Export("C:\\SavedRange_Test2.jpg", "JPG")

# CLOSE WORKBOOK AND APP
xlWbk$Close(FALSE)
xlApp$Quit()

# RELEASE RESOURCES
oCht <- xlWbk <- xlApp <- NULL
rm(oCht, xlWbk, xlApp)
gc()

After, you simply have to import the JPG in an Rmarkdown.

Emmanuel Hamel
  • 1,769
  • 7
  • 19