# Quick Intro to XLConnect

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.

### Main Features

• Reading & writing of Excel worksheets (via data.frames)
• Reading & writing of named ranges (via data.frames)
• Creating, removing, renaming and cloning worksheets
• 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

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

back to index

### Installing XLConnect

back to index

back to index

Whether you are creating or manipulating an existing is always a good practice to assign the workbook to a variable for further manipulation.

back to index

### Creating Sheets

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

back to index

### 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.

back to index

### Saving Workbooks

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.

back to index

### Basic Example

A complete basic example

back to index

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.

back to index

### 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 ’...’ argument.

back to index

### Write Data straight to File

writeWorksheetToFile() is a wrapper function, calling loadWorkbook(), createSheet() and saveWorkbook() 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 the ’...’ argument, arguments for the writeWorksheet function can be passed.

back to index

### Named Regions

A Named Range or region is way to describe your formulas. So you don’t have to have this in a cell:

= SUM(B2:B4)

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:

= SUM(Monthly_Totals)

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.

back to index