User Documentation

Installation and Usage Instructions

Installation

Excelsi-R comes in two parts: an Excel add-in and an R package. You must be running Windows to install the add-in, but (parts of) the package will work under other operating systems such as Linux.

Add-in Installation

To install, first run the installer setup.exe file. Then in Excel and enable the Excelsi-R.xla Add-in. For instance, if you used Excel 2010 and installed Excelsi-R to your C:\Program Files\Excelsi-R directory, you would open Excel and then select File->Options->Add-Ins->Manage Excel Add-ins Go->Browse and then select

   C:\Program Files\Excelsi-R\Excelsi-R.xla

After that, close Excel and restart it. You should now see Connect to R and Disconnect From R buttons on your Add-ins command bar.

R Package installation

To install the ExcelsiR R package, first install the prerequisites Cairo and png packages from CRAN. The ExcelsiR package itself is not on CRAN, so you need to install it manually for now. You can do this by running this command from your R console:

install.packages("c:\\path_to_file\\ExcelsiR_0.6.tar.gz", repos=NULL, type="source")

where "c:\\path_to_file" should be replaced by the directory that the ExcelsiR package file is in. Note you need double backslashes under Windows because the single backslash \ is the R string escape character.

Connecting to R

Before you can use any R functions, you need to connect to a running R server. Currently only local operation is supported. So start R the way you would normally on your computer. At the prompt, type in

 library(Rserve)
 Rserve()

If you get an error about Rserve not being found, you need to install the Rserve package by typing install.packages("Rserve").

Once Rserve is running, you should be able to connect to R and disconnect from R by pressing those buttons on your Add-ins command bar.

Remote Operation

Excelsi-R has the ability to connect to a remote Rserver. However, for security reasons Rserve does not accept remote connections by default. For information on configuring Rserve for remote clients, see the Rserve Documentation. An alternative is to tunnel an Excelsi-R request to a remote computer so it appears as a local connection. For instance, you can connect a windows Excelsi-R client to an Rserve server running on a remote linux machine by using SSH tunneling. Putty SSH is a windows SSH client that supports tunneling.

When setting up Rserve for Excelsi-R, please configure Rserve to use UTF-8 encoding, if you want to use more than 7-bit ASCII. This can be done using the Rserve configuration file, or by passing Rserve the "--RS-encoding utf8" option.

Using Excelsi-R

Excelsi-R comes with several templates that illustrate how it can be used. For example:
Local Linear Regression.xlsx - Using R's sm package, do local linear regression on the given data set. Return predictions and plot.
Basic State Maps.xlsx - Produce a map of the US with the states colored as specified.

It may be best to start with those templates and adapt them to your project.

What the buttons do

• Connect to R - establish a connection to the remote R server
• Disconnect from R - disconnect from the remote R server
• Copy R Code - take the current selection, produce R code which generates that data, and copy that R code to the clipboard. This can be useful for moving data from Excel to R. In contrast to simple copy and paste, Copy R Code preserves a record of where the data came from, and is aware of the same data types that Excelsi-R knows about.
• Run R block - Finds the R block around the active cell and runs it.
• Run R Line - Run the single line of R code at the active cell.
• Create R Block - Create a new Excelsi-R R block at the active cell.
• Erase Block Output - Erase the cells that may have been populated with data from a previous run of the current R block.

Columns in an Excelsi-R R block

The columns in an Excelsi-R block, except for Action, are optional. They can be in any order because the add-in looks at the header.

• Action - this may be:
  • eval - Evaluate R code on the remote server and bring the results back to Excel
  • exec - Execute R code on the remote server and discard the results
  • assign - Assign a variable in R to data in the local Excel worksheet
  • blank - Do nothing
• Excel Range - The name of the Excel named range that the data should be read from (for assign actions) or written to (for eval actions)
• R Code or Variable Name -
  • For eval and exec actions, this should be the text of R code to run on the remote server
  • For assign actions, this should be the name of the R variable to save the data in
• Excel Value - this is an optional column. If it exists, the output of eval actions will be written into the column (assuming the results are simply vectors of length 1).

Settings block

If the workbook has a named range called "ExcelsiRSettings", then default settings will be read from that block. The block is assumed to have two column format. The left column contains keys (the settings names); the right columns contains the values (the settings themselves).

Currently there are only two settings:

• hostname - the name of the Rserve host to connect to
• port - the port to connect to

Graphing

To display an R graph in an Excel spreadsheet, you need to use the ExcelsiRGraph function on the remote side, provided by the ExcelsiR R package. If you are using the ggplot2 package, you can simply put the plot as the second argument of the function. For instance:

Action	R Code or Variable Name
eval	ExcelsiRGraph("plotname", qplot(rnorm(1000)))

If you are using another graphics package, you will need to use the ExcelsiRInitGraph function before each graph. For instance:

Action	R Code or Variable Name
exec	ExcelsiRInitGraph()
exec	plot(1:10)
eval	ExcelsiRGraph("plot1")
exec	ExcelsiRInitGraph()
exec	plot(1:20)
eval	ExcelsiRGraph("plot2")