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
Answer from Mark Heckmann on Stack OverflowHow to Convert Excel .XLSX to PDF: No-Cost Ways
Print/save Excel (.xlsx) sheet to PDF using R - Stack Overflow
Convert xlsx to html, xml or pdf
Microsoft article on embedding Excel on a webpage
More on reddit.comWhen I save my Excel Spreadsheet to PDF it saves it to multiple pages instead of 1
How to change XLSX to PDF?
You can convert XLSX to PDF right on Excel or Google Sheets by exporting or saving the file in PDF format, with the option to save only select sheets or the whole document.
However, for a quick conversion that also lets you make some last-minute design edits, use the XLSX to PDF converter on our platform. Add impact to your data presentation with shapes and graphics from our rich library of elements. Highlight columns in custom colors, draw connections between numerical fields, and underline important figures with our free Draw tool. You can even use Magic Write to insert engaging copy that complements your data.
Our platform lets you easily transform your plain XLSX file to a stunning visual-first sheet before converting it into a shareable PDF.
What is the best XLSX to PDF converter?
Is it safe to convert XLSX to PDF online?
Videos
Converting Excel to PDF is a common task in data sharing and presenting. Yet it can still be tricky since issues like formatting errors and loss of data may occur.
The free PDF converter PDFgear facilitates quick spreadsheets-to-PDF transformation without losing the layout, making your report easy to read.
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
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.