xlsgen > overview > R programming

R programming, or as their originators call it a freely available language and environment for statistical computing and graphics which provides a wide variety of statistical and graphical techniques: linear and nonlinear modelling, statistical tests, time series analysis, classification, clustering, etc.

R programming has become popular not just among scientists but among many corporations and hobbyists for one reason : it is an open platform. Anyone can write and publish a R package in order to extend the features and remain free to host the packages on their website, i.e. the authors of R do not have a say on the packages, unlike application stores one can find with Apple, Google and Microsoft. Plus, R itself is an open source project, meaning that anyone can recompile the source code and modify it to meet their needs.

With the R programming environment installed, xlsgen can interface with it by letting any client application evaluate R statements, pass data, that is from an Excel range to a R data frame, and vice versa.

Installing the R programming environment

Using R programming with xlsgen implies that the R environment is installed where xlsgen runs. In other words, xlsgen itself does not include the R run-time dlls and it's assumed that the owner of the client application will install the R environment on his own. Fortunately, the R programming environment is available as a Windows installer with automatic setup, which means no particular knowledge is expected to install it successfully.

Once the R environment is installed, and you can check it installed successfully by running its own end-user tool called Rgui.exe, xlsgen will automatically find it whenever it needs it.

xlsgen will automatically find it whenever it needs it, unless it can't load its root R.dll. For this reason, xlsgen exposes a property that is meant to specify the install folder of the R environment, i.e.

workbook->R_Programming->Options->R_Home = L"C:\\Program Files (x86)\\R-3.4.1"; // R install folder

Evaluating R statements

xlsgen can evaluate any R statement, that is any sentence that includes one or more R keywords using the syntax of the R programming language. Here is one example :

HRESULT hr = workbook->R_Programming->Eval(L"x <- c(1,5)"); // creates a vector (x) of two integers

When a statement is passed by xlsgen, it is parsed, and if it passes the parsing stage, it is evaluated. The result is non-zero if any error occurs. In most client applications, the non-zero result will throw an exception, which can be trapped.

Passing data to the current R session

Given the fact that xlsgen can evaluate any R statement, in theory a client application can pass any statement deemed appropriate to to create variables with the wanted structure. But it is certainly welcome to be able to pass a regular Excel range in order to build the equivalent variable in the R environment, known as a data frame. The reason why is the ease of use such mechanism, i.e.

xlsgen::IXlsRangePtr r = worksheet->NewRange(L"B3:F7"); // creates a regular Excel range
workbook->R_Programming->CreateRDataFrameFromExcelRange[L"df"] = r; // creates a R data frame (df) from the range

Once the data frame is created (df in the example), it can be used in any further R statement as if it had been created by multiple R statements. What xlsgen allows to do is forget about the data type coercing as well as the definition of the data frame column names. If the range has headers, which xlsgen guesses, those headers are used as column names for the data frame. If the range does not have headers, the data frame has the following column names : C1, C2, ...

Retrieving data from the current R session

Retrieving a data frame from the current R session is as easy as other way around. When doing so, it creates an Excel range. The data in the data frame is copied to the Excel range, starting at the top-left of the range, i.e.

xlsgen::IXlsRangePtr rr = worksheet->NewRange(L"B10:B10");
workbook->R_Programming->CreateExcelRangeFromRDataFrame[rr] = L"df"; // retrieves data from an existing R data frame (df)