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.
Do not forget to add the prepared spreadsheet in your RPGM project folder:
The generation of the spreadsheet is done entirely from a R script. First, the XLSX file must be open with the
Then a vector or a R matrix can be put in any name range cells of the Excel spreadsheet by calling the
The cell _N_Input_ will then be filled with the value of the variable N, as shown below:
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
This file can be opened again or another one can be processed.
Values can be read from the opened Excel file with the xlsx.getCells() function. See the reference of the function for more information.
An Excel spreadsheet helper is available in the R menu of RPGM Editor:
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.