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
#Installed XLConnect install.packages("XLConnect") #load the package require(XLConnect)
You’re now ready to use XLConnect!
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)
loadWorkbook()function loads a Microsoft Excel workbook, so that it can then be further manipulated. Setting the create argument to
TRUEwill 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
startColare 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:
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.