5

I want to print an Excel file to a pdf file after manipulating it. For the manipulation I used the .xlsx package which works fine. There is a function printSetup but I cannot find a function to start the printing. Is there a solution for this? 

library(xlsx)
file <- "test.xlsx"
wb <- loadWorkbook(file)  
sheets <- getSheets(wb)       # get all sheets
sheet <- sheets[[1]]          # get first sheet
# HERE: MAGIC TO SAVE THIS SHEET TO PDF

It may be a solution using DCOM through the RDCOMClient package, though I would prefer a plattform independent solution (e.g. using xlsx) as I work on MacOS. Any ideas?

Mark Heckmann
  • 10,943
  • 4
  • 56
  • 88

3 Answers3

5

Below a solution using the DCOM interface via the RDCOMClient. This is not my preferred solution as it only works on Windows. A plattform independent solution would still be appreciated.

library(RDCOMClient)
library(R.utils)

file <- "file.xlsx"                   # relative path to Excel file
ex <- COMCreate("Excel.Application")  # create COM object
file <- getAbsolutePath(file)         # convert to absolute path
book <- ex$workbooks()$Open(file)     # open Excel file
sheet <- book$Worksheets()$Item(1)    # pointer to first worksheet
sheet$Select()                        # select first worksheet
ex[["ActiveSheet"]]$ExportAsFixedFormat(Type=0,    # export as PDF
                                        Filename="my.pdf", 
                                        IgnorePrintAreas=FALSE)
ex[["ActiveWorkbook"]]$Save()         # save workbook
ex$Quit()                             # close Excel
Mark Heckmann
  • 10,943
  • 4
  • 56
  • 88
3

An open source and cross platform way to do this would be with libreoffice as so:

library("XLConnect")
x <- rnorm(1:100)
y <- x ^ 2
writeWorksheetToFile("test.xlsx", data.frame(x = x, y = y), "Data")
tmpDir <- file.path(tempdir(), "LOConv")
system2("libreoffice", c(paste0("-env:UserInstallation=file://", tmpDir), "--headless", "--convert-to pdf",
    "--outdir", getwd(), file.path(getwd(),"test.xlsx")))

Ideally you'd then remove the folder referenced by tmpDir but that would be platform specific.

Note this assumes libreoffice is in your path. If it isn't, then the command would need to be altered to include the full path to the libreoffice executable.

The reason for the env bit is that headless libreoffice will only do anything otherwise if it isn't already running in GUI mode. See http://ask.libreoffice.org/en/question/1686/how-to-not-connect-to-a-running-instance/ for more info.

Nick Kennedy
  • 12,510
  • 2
  • 30
  • 52
  • I cannot make the above work for me. I have added libreoffice/program to my path and I have replaced `"libreoffice"` with `"soffice"`, but the command seems to run forever. Any insights? – GerasimosPanagiotakopoulos Feb 05 '20 at 09:57
  • Upon further research, I am suspecting that `tmpDir` is inconsistent with the contents of my bootstrap file. Is that an issue of incompatibility between the answer and Libreoffice settings or my platform (Windows)? – GerasimosPanagiotakopoulos Feb 05 '20 at 11:18
-4

You could use the pdf function:
pdf(file="myfile.pdf", width=8.5, height=11)
print(firstsheet)
grid.newpage()
print(secondsheet)
grid.newpage()
print(thirdsheet)
dev.off()

Graveywavy
  • 1