Few steps to connect R with Excel: XLConnect!

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

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

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

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.

colIndex <- which(names(airquality) == 'isCurrent')
letterDay <- idx2col(which(names(airquality) == 'Day'))
letterMonth <- idx2col(which(names(airquality) == 'Month'))
formulaXls <- paste('IF(AND(',
                    letterMonth,
                    2:(nrow(airquality)+1),
                    '=Input!C3,',
                    letterDay,
                    2:(nrow(airquality)+1),
                    '=Input!C2)',
                    ',1,0)',sep='')
setCellFormula(exc, sheet='Airquality',2:(nrow(airquality)+1),colIndex,formulaXls)
saveWorkbook(exc)

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

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

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

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.

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

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.

XLConnect has also some features as defining cellstyles (data formats, borders, back fill colors..) , hide/unhide sheets, specyfing column width and row height, merged cells, auto-filters, style actions (using templates) and behavior with error cells.

Stay tuned to know the rest!