XLConnect permits to create a formatted spreadsheet usable as a dynamic report of the R analysis.

The package installation is very easy:

1

2

install.packages("XLConnect")

After the installation the package is to be loaded:

1

2

require("XLConnect")

XLConnect offers many features to realize a good connection between R and Excel.

An Excel data grid becomes an R data.frame!

An Excel named region is reported in R and used to place R objects in the right position!

Reading and writing named ranges enables to process complex inputs and outputs in an efficient way.

We can introduce XLConnect by few sequential examples.

Create a new empty xlsx file trough R

We want to create a new empty .xlsx file with one empty sheet named ‘Input’; the syntax is

1

2

3

4

5

6

fileXls<-paste(outDir,"newFile.xlsx",sep='/')

unlink(fileXls,recursive=FALSE,force=FALSE)

exc<-loadWorkbook(fileXls,create=TRUE)

createSheet(exc,'Input')

saveWorkbook(exc)

Command loadWorkbook creates an R workbook object.

Command saveWorkbook save the R object in a xlsx file.

The result is represented in next figure:

Create an Excel empty sheet trough R using XLConnect

Populate an empty xlsx sheet with R

We want to add something to the empty sheet input

1

2

3

4

input<-data.frame('inputType'=c('Day','Month'),'inputValue'=c(2,5))

writeWorksheet(exc,input,sheet="input",startRow=1,startCol=2)

saveWorkbook(exc)

The input data.frame, with 2 rows and 2 column, is created and the command writeWorksheet writes the content of this data.frame in the input sheet starting from the cell (1,2).

Write inside an Excel sheet with R using XLConnect

Add other sheets to the workbook

At this point a second sheet could be created

1

2

3

4

5

6

7

require(reshape)

createSheet(exc,'Airquality')

airquality$isCurrent<-NA

createName(exc,name='Airquality',formula='Airquality!$A$1')

writeNamedRegion(exc,airquality,name='Airquality',header=TRUE)

saveWorkbook(exc)

Command createName create a named region ‘Airquality’ starting from the cell $A$1 of sheet Airquality. Command writeNamedRegion writes airquality data.frame with headers in the named region ‘Airquality’.

Add another variable to an Excel sheet with R using XLConnect

Add an Excel formula to an xlsx sheet trough R

The empty column isCurrent could be populated with a formula that links the Input sheet with the Airquality sheet.

The function idx2col returns the correspondig excel letter for the index column. With the syntax

1

2

letterDay<-idx2col(which(names(airquality)=='Day'))

the variable letterDay contains the excel letter for the column ‘Day’

1

2

letterDay=F

Run an Excel formula with R trough XLConnect

Read and modify with R an existing xlsx file

Suppose that we have to modify the xlsx file just created.

1

2

3

4

5

6

exc2<-loadWorkbook(fileXls,create=FALSE)

dtAir<-readWorksheet(exc2,'Airquality')

createSheet(exc2,name="OzonePlot")

createName(exc2,name='OzonePlot',formula='OzonePlot!$A$1')

saveWorkbook(exc2)

In this case

exc2 is a new XLC object created from the existing excel file (the old file Xls)

the new sheet OzonePlot is added to exc2 object

the new named region OzonePlot is creating starting from OzonePlot!$A$1 cell

Adding an R plot (as image) to Excel

After creating a new sheet it is possible to put in this sheet a picture of a graph created in R

1

2

3

4

5

6

7

8

9

10

11

12

library(ggplot2)

fileGraph<-paste(outDir,'graph.png',sep='/')

png(filename=fileGraph,width=800,height=600)

ozone.plot<-ggplot(dtAir,aes(x=Day,y=Ozone))+

geom_point()+

geom_smooth()+

facet_wrap(~Month,nrow=1)

print(ozone.plot)

invisible(dev.off())

addImage(exc2,fileGraph,'OzonePlot',TRUE)

saveWorkbook(exc2)

The command addImage insert the png image of the graph just saved in the named region OzonePlot.

The result is represented in next figure:

Add an R plot to Excel using XLConnect

Extra functions of XLConnect

In this brief article we don’t focus the attention on the excel layout but on the sheets content. For example we can automatically create an excel calculator with an input sheet and an output sheet managing all information in R.

Graduated in Mathematics from the University of Milan, worked in the field of Statistical and Mathematical Modelling and Data Mining. In particular data analysis at various levels, forecasting on demand planning, clustering, customer satisfaction, fraud detection and design and implementation of custom products and core prediction engine.

1 Comment

A great package! One issue however is that in order to use it for anything related to production, the license should be more flexible, GPL-3 requires that I open up all the source code using that package to handle excel files.