Many organizations use MS Excel for data analysis and financial modelling and it is likely that your company also expects you to perform and report data analysis through Excel. However, you may be more interested in performing the actual analysis using R tools and then presenting the results in Excel. The good news is that R has a package which does exactly this. It's called XLConnect. XLConnect is a package that allows for reading, writing and manipulating Microsoft Excel files from within R.

Using XL Connect, you can:

• Read an excel file with multiple sheets
• Write an excel file with multiple sheets
• Read/write formulas
• Export the results of an analysis in an excel report

XLConnect is different from other data importing packages because:

• It runs on all operating systems that support Java. XLConnect is written in Java and runs on Window, Linux, and Mac OS.
• There’s nothing else to load. XLConnect doesn’t require any other libraries or software. If you have Java installed, it should work. It does not even require any installation of Microsoft Excel or any other special drivers to be able to read & write Excel files. The only requirement is a recent version of a Java Runtime Environment (JRE).

Install and Load XLConnect

The package can easily be installed from CRAN via install.packages("XLConnect"). Once installed, we can load it using the require(XLConnect).

#Installed XLConnect
install.packages("XLConnect")
#load the package
require(XLConnect)

You're now ready to use XLConnect!

Example 1

XLConnect can be used to create and manipulate excel files from scratch. In the below example, we will create a new excel workbook, add a new sheet to it and then load the tab-delimited stock price data about Goldman Sachs into it.

#loading the package
require(XLConnect)
#create an Excel workbook. Both .xls and .xlsx file formats can be used.
mywb <- loadWorkbook("StockPrices.xlsx", create = TRUE)
#Create a sheet called GSStock within the Excel workbook
createSheet(mywb, name = "GSStock")
#load data from the flat file into R as a data frame
gs_data <- read.table("GS-Stock-Prices.txt", sep="\t", header=TRUE, stringsAsFactor=FALSE);
#write the gs_data data frame into GSStock sheet in the new workbook
writeWorksheet(mywb, gs_data, sheet = "GSStock", startRow = 1, startCol = 1)
#save the workbook to the corresponding Excel file and writes the file to disk.
saveWorkbook(mywb)

Functions Used

• The loadWorkbook() function loads a Microsoft Excel workbook, so that it can then be further manipulated. Setting the create argument to TRUE will ensure the file will be created, if it does not exist yet. Both .xls and .xlsx file formats can be used.
• createSheet() creates a sheet of a chosen name in the workbook specified as the object argument.
• 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
• saveWorkbook() saves a workbook to the corresponding Excel file and writes the file to disk. you can expect to find the file in the working directory.

The newly created excel file is shown below:

Example 2

In the previous example, we used XLConnect functions to create a new excel file and load data into it. In this example, we will use XLConnect functions to read data from the sheets in a pre-existing excel file.

#Load the workbook in R
mywb = loadWorkbook("stock-prices.xlsx", create = FALSE)
#List all sheets in the workbook
getSheets(mywb)
#reading worksheets of an Excel workbook
data = readWorksheet(mywb, sheet = "Dec-2016")
#print data
data

We first used the loadWorkbook() function to load the workbook into an R object. Then we got a list of sheets using the getSheets() function. Then we used readWorksheet() function to read the data from the sheet named 'Dec-2016'. The dat is loaded into a variable called 'data' which can then be used to perform analysis.

While our objective here was to introduce you to the XLConnect package, we will soon have a full course dedicated to it.

You can learn more about XLConnect using the package vignette, the numerous demos available via demo(package = "XLConnect") or browse through the comprehensive reference manual.