Table of Contents
The Office365 AddIns solution consists of two main projects. The ExcelRAddIn project and the RScriptAddIn project. The intention of both projects is quite similar: that is, to make R functionality available in the respective Office applications, Excel and Word.
So, what do these add-ins actually do?
- The ExcelRAddIn allows you to execute R code and get the results back in Excel. This behaves similarly to executing R code in RStudio's Console window. The following is a snapshot after fitting a simple linear model.
- The RScriptAddIn (for Word) allows you to write text and R code in the same document, execute the R code and use the results (if there are any) 'inline'. The following screenshot shows creating a contour plot from a simple function.
The Office365AddIns solution consists of a number of projects detailed below. The projects are all 'Alpha' versions and are in the early stages of development.
The ExcelRAddIn is an Excel-DNA add-in (see below), and the RScriptAddIn is a Word VSTO add-in. Both add-ins make use of the fantastic R.NET library (alongside the DynamicInterop library) to access R functionality. This is encapsulated in the RDotNetProxy project. There is a separate NUnit unit test project for this component. A further project, the REnvironmentControlLibrary, provides a simple list view of the objects in the current R environment and a place to display any informational or error messages. The ExcelRAddIn additionally makes use of the amazing Excel-DNA library.
Details:
- RDotNetProxy - a wrapper around the R.NET library. This deals with presenting the output from R (via R.NET) in a form that is usable in Excel or Word (depending on which add-in is being used).
- RDotNetProxyTest - an NUnit unit test library.
- REnvironmentControlLibrary - a Windows Forms control that contains a list view for displaying objects in the current global R environment and a list view of information and error messages.
- ExcelRAddIn - an Excel-DNA add-in.
- RScriptAddIn - an Office VSTO (C#) add-in.
The solution is built with:
- Visual Studio Community Edition 2022
The following packages are used:
Downloading
The project can be downloaded from the GitHub repository in the usual way.
Building the solution
Ensure that the references (R.NET, DynamicInterop and Excel-DNA) are present in their respective projects. When the project is opened in Visual Studio, NuGet should restore the packages. If not, you may need to add the packages manually to the solution/projects. You can (re)build either the Debug or Release versions.
At this point the projects can be run under the debugger in the usual way. More detailed installation instructions are given below.
- Office365 Microsoft Word
- Office365 Microsoft Excel
- R (a recent version e.g. 4.3.0, 4.3.1)
- Copy the bin\Debug or bin\Release contents to a new directory.
- Start Excel. Open the file ExcelRAddIn-AddIn64.xll. Excel will ask if this add-in should be enabled for this session. Press 'Enable'. The add-in is loaded, and a new menu appears on the right-hand side of the menu bar: R AddIn.
- Select the R AddIn menu. A ribbon bar is displayed with two buttons. Show/Hide Task Pane and Settings.
- Select the Settings. An R Environment Settings dialog box is displayed. Fill in the details for the R Home and the R Path. R Home is where the base R installation lives. R Path is the directory where the R.dll lives. Press OK. Note you can also edit these settings manually in the configuration file ExcelRAddIn-AddIn64.xll.config.
- Finally, for convenience, it is useful to 'Trust' the AddIn location as this will ensure that Excel does not prompt you regarding a potential security concern each time the add-in is loaded. To do this, select the File menu, then Options. The Excel Options dialog box is displayed. Select Trust Center and press the button Trust Center Settings... . The Trust Center dialog is displayed. Use the Add New Location button to add the AddIns location to the list of trusted locations. Press OK when complete. Press OK to close the Excel Settings dialog.
- Open a new blank sheet. In cell B2 type: x <- rnorm(15). Next to this, in cell C2, type: =RScript.Evaluate(B2). The task pane will open. In the lower third, where the Messages are displayed there should be two informational messages indicating that R has been initialized correctly. In the Environment list view you should see the results of evaluating the R script. In the cell C2, the value 'x' should appear. The following screenshot shows the sample session:
- Copy the bin\Debug or bin\Release contents to a new directory. Confirm that in the directory there is a file called RScriptAddIn.vsto. This is the deployment file.
- Double-click this file to install the add-in.
- Start Word, open a blank document. In the menu bar there is an Add-ins menu. Select this and in the ribbon bar you should see the R Tools menu. This consists of a button to Show and Hide the Task Pane, a button to execute R script, and a settings button. Press the Settings button. An R Environment Settings dialog box is displayed. Fill in the details for the R Home and the R Path. R Home is where the base R installation lives. R Path is the directory where the R.dll lives. Press OK.
- Open the document "RScriptAddIn/Tests/AddIn Test.docx". Select the line 'x <- seq(1:50)'. On the Add-ins menu, press the button Run Script. The task pane will open (if it is not already visible). In the lower third, where the Messages are displayed there should be two informational messages indicating that R has been initialized correctly. In the Environment list view you should see the results of evaluating the R script. Continue executing the script. The following screenshot shows the sample session:
Both the ExcelRAddIn and the RScriptAddIn projects have /Tests subdirectories. These contain a number of usage examples.
These are the latest updates from 14/11/2024.
- The new Create plot button allows you to generate the script required for outputting a ggplot (https://ggplot2.tidyverse.org/) graph.
The inputs are divided into Data, Layers, Labels, Scales, Coords, Facets, and Theme. These correspond to the major parts of a ggplot.
The Data page allows you to select the data and set the aesthetic for the plot.
The Layers page allows you to set the specifics of the chart type: the geom. After adding a geom, double clicking the label takes you to further attribute settings appropriate to the specific geom.
The Theme page allows you to select a theme.
Once complete, the script is output, wrapped in a plot(...) function. This can be evaluated using RScript.Evaluate(). The resulting plot can be copied as a metafile and saved in the worksheet.
- Minor UI updates to the panel.
- Added a directory 'Publish' that contains the binaries.
These are the updates from 27/04/2024
- Users can specify packages to load when the add-in is initialised in the Add-In settings available from the R AddIn menu.
Default package loading takes place on the first call to RScript.Evaluate(...).
- Updates to
CreateVector,CreateMatrix,CreateDataFrame. The final parameter ('Type' => character, complex, integer, logical, numeric) is now optional; the RType is now determined from the data if possible. This makes it somewhat easier to create objects to pass to R from Excel. - Two generic calls have been added:
RScript.ParamsandRScript.Function.RScript.Paramsreturns a list of parameters for the requested function andRScript.Functionevaluates the specified function, possibly using the parameter dictionary retrieved from the call toRScript.Params.
- Additional functions for querying models (objects returned from calls to 'lm', 'glm' etc):
Model.Resultsoutputs a list of results from the model.Model.Resultoutputs the result obtained from one item of the list of model results. Optionally, the result can be formatted as a data frame. This is somewhat more convenient than having to evaluate scripts of the form'model name'$coeffcients, etc.Model.Accuracyreturns a number of statistics relating to measures of model accuracy.
One of the motivations for updating the ExcelRAddIn was to provide an improved experience when using more complex R functions in an Excel worksheet. The idea was to avoid building up a script by providing wrapper functions that can handle the variety of parameters passed to the underlying R functions. The option of using a script is always available. However, for a complex function like auto.arima (which can take up to 35 parameters) or glm, it is easier to setup a parameter dictionary with the appropriately named parameters and their values (as shown below)
rather than creating a script, for example: logModel = glm(Purchase~Income+Age+ZipCode, data = purchase, family = binomial(link='logit'))
The parameters and their default values can be retrieved by using the RScript.Params function.
Wrapper functions have been provided for a number of the functions in the forecast library (https://cran.r-project.org/web/packages/forecast/forecast.pdf). These are as follows:
- Forecast.MA - Moving average smoothing
- Forecast.SES - Simple exponential smoothing
- Forecast.Holt - Holt exponential smoothing
- Forecast.HW - Holt-Winters exponential smoothing.
- Forecast.AutoETS - Exponential smoothing state space model.
- Forecast.Arima - Auto-Regressive Integrated Moving Average model
- Forecast.AutoArima - Fit best ARIMA model to univariate time series
- Forecast.FC - Generic function for forecasting from time series or time series models
- Forecast.meanf - Mean forecast
- Forecast.rwf - Forecasts and prediction intervals for a random walk with drift model
- Forecast.splinef - Local linear forecasts and prediction intervals using cubic smoothing splines
- Forecast.thetaf - Forecasts and prediction intervals for a theta method forecast
- Forecast.Croston - Forecasts and other information for Croston’s forecasts
In addition to the wrappers around the forecast package, wrappers have been provided around the following two 'workhorse' functions:
- Regression.LM - Fit a linear model to the data
- Regression.GLM - Fit a generalised linear model to the data
The Forecast.xlsx spreadsheet provides examples of these based on the underlying packages.
- The R Environment AddIn panel has been updated to use WPF (in place of Windows Forms). This allows greater flexibility (at least in theory) for future enhancements.
Future directions:
- Add Changelog
See the open issues for a full list of proposed features (and known issues).
Distributed under the GPL-3.0 License. See LICENSE.md for more information.
Adam Gladstone - (https://www.linkedin.com/in/adam-gladstone-b6458b156/)
Project Link: https://github.com/Adam-Gladstone/Office365AddIns
Helpful resources
