How to export an Excel sheet range to a picture, from within R

Question

We are trying to automate the creation of some picture files within an R Script.

We have the Excel files looking the way that we want them to, but now need to make a JPG or PNG picture-copy of those excel tables for easier web posting.

We've been using the library(xlsx) package for most of our interactions between R and Excel, and it looks like we should be able to send specific java commands through something like ?.jmethods but it's unclear how we would pass as many lines as we need to.

In an R session, here's a minimal reproducible example...

Here's an example Excel file with a range to print

library(xlsx)
file <- system.file("tests", "test_import.xlsx", package = "xlsx")
file

And here's the Excel macro that exports the Excel range to a picture file

Sub Tester()

    Worksheets("deletedFields").Range("A8:J36").CopyPicture xlScreen, xlBitmap

    Application.DisplayAlerts = False
    Set oCht = Charts.Add
    With oCht
        .Paste
        .Export Filename:="C:\temp\SavedRange.jpg", Filtername:="JPG"
        .Delete
    End With


End Sub

Any help automating this would be much appreciated!

Have you tried [this](http://stackoverflow.com/questions/19404270/run-vba-script-from-r)? — NicE, Mar 31 '17 at 15:39
thanks @nice ! i believe that requires the macro to already be embedded in the `.xlsx` file, but we are creating those dynamically hundreds of times. this might work if we could auto-add the macro inside the R-generated `.xlsx` files as well? thank you — Anthony Damico, Mar 31 '17 at 16:45
Is there some reason pictures of excel files are better than the html format tables created by either `xtable` or `shiny`? From the little information that you have provided it seems like there might be a better way to achieve your primary goal. — Bishops_Guest, Mar 31 '17 at 17:28
hi, @Bishops_Guest thanks. we need the excel files to send to researchers and reporters, so those tables need to be constructed either way. we could potentially re-do everything, but it would save time to simply take a snapshot of the excel range that we already have. — Anthony Damico, Mar 31 '17 at 18:27

score 13 · Accepted Answer · answered Apr 02 '17 at 16:49

Consider having R do exactly as VBA does in your macro: making a COM interface to the Excel object library. You can do so with the RDCOMClient package, retaining nearly same code as macro in the R syntax.

library(RDCOMClient)

xlApp <- COMCreate("Excel.Application")
xlWbk <- xlApp$Workbooks()$Open("C:\\Path\\To\\test_import.xlsx")
xlScreen = 1
xlBitmap = 2

xlWbk$Worksheets("deletedFields")$Range("A8:J36")$CopyPicture(xlScreen, xlBitmap)

xlApp[['DisplayAlerts']] <- FALSE

oCht <- xlApp[['Charts']]$Add()
oCht$Paste()
oCht$Export("C:\\Temp\\SavedRange.jpg", "JPG")
oCht$Delete()

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

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

Output (random data/chart)

I am a newbie. Thanks for your useful response. I have a question. The image using xlScreen = 1 and xlBitmap = 2, also the above code exports blurry image. Is it anyway to fix? — Bình Dương Lương, May 23 '23 at 15:59

Ian Wesley · Answer 2 · 2017-04-01T05:22:10.420

You can do this with vbs. Most vbs is identical to vba so you can write out your dynamic vbs script which includes your macro as text and then call it with shell.

Here is a working example:

fileConn<-file("c:/rworking/test/test.vbs")
writeLines(c("Dim xlApp, xlBook, xlSht",
    "Dim filename",
    "filename = \"c:\\Rworking\\test\\test_import.xlsx\"",
    "Set xlApp = CreateObject(\"Excel.Application\")",
    "xlApp.Visible = True",
    "set xlBook = xlApp.WorkBooks.Open(filename)",
    "set xlSht = xlApp.Worksheets(1)",
    "set rng = xlSht.Range(\"A8:J36\")",
    "rng.CopyPicture",
    "Set oCht = xlApp.Charts",
    "oCht.Add() ",
    "Set oCht = oCht(1)",
    "oCht.paste",
    "oCht.Export \"C:\\rworking\\test\\Test.jpg\", \"JPG\""), 
    fileConn)

close(fileConn)

shell.exec("c:/rworking/test/test.vbs")

score 3 · Answer 3 · answered Apr 01 '17 at 09:20

Hmm, not sure about posting, maybe it got redundant through Ian´s post. Its a bit more generic, but I can also remove it.

library(xlsx)

OutputPicFileName <- "Chart.jpg"
ScriptFileName <- "Chart.vbs"
xclFileName <- "test_import.xlsx"
xclRng <- "A8:J36"
file <- system.file("tests", xclFileName, package = "xlsx")
fileDirec <- unlist(strsplit(file, xclFileName))

CreateChart <- function(fileDirec, OutputPicFileName, ScriptFileName, xclRng){
  setwd(fileDirec)
  filePath <- file(paste0(fileDirec, ScriptFileName))
  writeLines(
    c(
      "Dim App, WBook, Sht, Rng, FileName, ChartObj, Chart",
       paste0("FileName = \"", gsub("/", "\\\\", fileDirec), xclFileName ,"\""),
       "Set App = CreateObject(\"Excel.Application\")",
       "Set WBook = App.WorkBooks.Open(FileName)",
       "Set Sht = App.Worksheets(1)",
       paste0("Set Rng = Sht.Range(\"", xclRng,"\")"),
       "Rng.CopyPicture",
       "Set ChartObj = App.Charts",
       "Set Chart = ChartObj.Add() ",
       "Chart.paste",
       paste0("Chart.Export \"", gsub("/", "\\\\", fileDirec) , OutputPicFileName ,"\", \"JPG\"")
    ), 
    filePath
  )
  close(filePath)
  shell.exec(ScriptFileName)
}

CreateChart(fileDirec, OutputPicFileName, ScriptFileName, xclRng)

# Result in: fileDirec

How to export an Excel sheet range to a picture, from within R

3 Answers3

Linked