Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Print/save Excel (.xlsx) sheet to PDF using R

Tags:

r

excel

xlsx

dcom

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?

like image 479
Mark Heckmann Avatar asked Jun 19 '15 16:06

Mark Heckmann


1 Answers

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
like image 171
Mark Heckmann Avatar answered Sep 30 '22 05:09

Mark Heckmann