Excel is the basic tool for any type of analysis.
Often using R we have the need to communicate with Excel, for example
- 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 permits to create a formatted spreadsheet usable as a dynamic report of the R analysis.
The package installation is very easy:
After the installation the package is to be loaded:
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
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:
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).
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 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’
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:
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!