This doc is a quick tutorial for the XLConnect R package. XLConnect allows for reading, writing and manipulating Microsoft Excel files from within R.
XLConnect allows you to produce formatted Excel reports, including graphics, straight from within R.This enables automation of manual formatting and reporting processes. Reading and writing named ranges enables you to process complex inputs and outputs in an efficient way.
- Reading & writing of Excel worksheets (via data.frames)
- Reading & writing of named ranges (via data.frames)
- Creating, removing, renaming and cloning worksheets
- Adding graphics
- Specifying cellstyles: data formats, borders, back- and foreground fill color, fill pattern, text wrapping
- Controlling sheet visibility
- Defining column width and row height
- Merging/unmerging cells
- Setting/getting cell formulas
- Defining formula recalculation behavior (when workbooks are opened)
- Setting auto-filters
- Style actions: controlling application of cell styles when writing (e.g. when using templates)
- Defining behavior when error cells are encountered
- Package Requirements
- Installing XLConnect
- Loading XLConnect
- Loading/Creating and Excel workbook
- Creating Sheets
- Writing data to WorkSheets
- Saving workbooks
- Basic Example
- Reading data from Worksheet
- Read Data straight from File
- Write Data straight to File
- Named Regions
- Further readings
- Session Info
XLConnect is completely cross-platform and as such runs under Windows, Unix/Linux and Mac (32- and 64-bit). It does not require an installation of Microsoft Excel, or any special drivers.
All you need to use XLConnect are the following:
- R, version 2.10.0 or higher
- Java Runtime Environment (JRE), version 6.0 or higher
Loading/Creating and Excel workbook
Whether you are creating or manipulating an existing is always a good practice to assign the workbook to a variable for further manipulation.
createSheet() creates a sheet of a chosen name in the workbook specified as the object argument.
Let’s create 4 sheets for the following data: cars, iris, rivers and quakes
Writing data to WorkSheets
writeWorksheet() writes data into a worksheet (name or index specified as the sheet argument) of an Excel workbook (object). The startRow and startCol are both 1 by default, meaning that if they are not explicitly specified, the data will start being filled into the A1 cell of the worksheet.
Until now, everything you’ve done is in memory so you need to save your work to the disk. saveWorkbook() saves a workbook to the corresponding Excel file and writes the file to disk.
A complete basic example
Read Data from Worksheet
readWorksheet() allows for reading data from a workbook that has been previously loaded and is passed
as the object argument. The name or index of the worksheet to read from should be passed as the sheet
argument. The startRow and startCol arguments specify the location of the top left corner of data to
be read, while endRow and endCol specify the bottom right corner. If
header = TRUE, the first row is
interpreted as column names of the data.frame object read in.
Read Data straight from File
readWorksheetFromFile() allows for reading data from a workbook with one call, without loading the workbook first. The
file argument is the path of the file to read from. All arguments of the
readWorksheet() function can be passed within the
Write Data straight to File
writeWorksheetToFile() is a wrapper function, calling
functions subsequently. It therefore allows for writing data into worksheets of an Excel file in one call.
The file is the path name of the file to write to. The styleAction argument controls the application of cell
styles when writing to Excel (type
?setStyleAction into the console to see the supported styles). Within
’...’ argument, arguments for the writeWorksheet function can be passed.
A Named Range or region is way to describe your formulas. So you don’t have to have this in a cell:
You can replace the cell references between the round brackets. You replace them with a descriptive name, all of your own. So you could have this, instead:
Behind the Monthly_Totals, though, Excel is hiding the cell references. We’ll see how it works using XLConnect.
There 2 ways to do named regions, Step by Step and straight to File methods. When using a template that later on will be renamed to a new filename I suggest using the Step by Step option
To ilustrate how easy is to use named regions in XLconnect let’s create a spreadsheet (
invoice_template.xlsx for example) with some named regions to fill specific areas of the invoice with data.
The file comes with the following predefined named regions:
We also are going to create some named regions for description, units, Cost per Unit.