Site icon R-bloggers

Few steps to connect R with Excel: XLConnect!

[This article was first published on MilanoR, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

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:

install.packages("XLConnect")

After the installation the package is to be loaded:

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

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!

The post Few steps to connect R with Excel: XLConnect! appeared first on MilanoR.

To leave a comment for the author, please follow the link and comment on their blog: MilanoR.

R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.