RPGM 2.0 Docs

Generating Excel files

The management of Excel spreadsheets is only available in the commercial version of RPGM, and only supports .xlsx files (Excel 2007 or newer).

RPGM needs a template Excel spreadsheet which will contain all the results. Thus, the first step is to create a spreadsheet in Excel. Texts, graphics, formulas and cells formatting will be keep during the process, so feel free to customize your spreadsheet as you like. For convenience, the name of a cell or a range of cells can be change in Excel, and this name can be used in RPGM.

Excel

Do not forget to add the prepared spreadsheet in your RPGM project folder:

Project

Writing values

The generation of the spreadsheet is done entirely from a R script. First, the XLSX file must be open with the xlsx.open() function:

xlsx.open(rpgm.pgmFilePath("Results_template.xlsx"))

Then a vector or a R matrix can be put in any name range cells of the Excel spreadsheet by calling the xlsx.setName() function:

xlsx.setName("N_input", N)

The cell _N_Input_ will then be filled with the value of the variable N, as shown below:

Result

Only values can be put in the Excel sheets, all other Excel features, such as conditionals or formatting settings must be already set in the spreadsheet.
The name the cell is facultative, as the coordinates of the cell can be supplied, but make sure to work in the correct Excel Worksheet:

xlsx.selectWorksheet("Results");
xlsx.setCell("C4", N);

xlsx.setCell() can be called as many times as you want.

A lot of function are available for modifying the spreadsheet and working with columns, rows, worksheet, values and even merging cells. See the function in the Excel reference page of the documentation for a complete list of functions available.

When you are done, the spreadsheet must be saved and closed by using xlsx.saveAs():

xlsx.saveAs("Results.xlsx")

This file can be opened again or another one can be processed.

Reading values

Values can be read from the opened Excel file with the xlsx.getCells() function. See the reference of the function for more information.

Helper

An Excel spreadsheet helper is available in the R menu of RPGM Editor:

Helper

A worksheet and a cell/range can be selected and the corresponding code will then be written in the code editor where the cursor is.