RPGM 2.0 Docs

Excel R functions

This set of functions can open and edit an Excel’s XLSX file. Do not forget to xlsx.open a file before editing or reading a document. Also, do not forget to select the current worksheet with xlsx.selectWorksheet to make sure that all other functions are executed in the correct sheet. The modification will be saved only after the call to xlsx.saveAs.

Here is a complete example:

# Open the XLSX
xlsx.open(rpgm.pgmFilePath("template.xlsx"));

# Cells management
xlsx.mergeCells("A6", "B6");
xlsx.unmergeCells("A2", "B2");
test1 <- xlsx.getCells("C4");
test2 <- xlsx.getCells("D5:H6");
test3 <- xlsx.getCells("D5:H6", asNumber = FALSE);
xlsx.setCell("A1", "A string to put in the A1 cell");
xlsx.setName("cellnametest", 12.56);

# Document info
xlsx.setTitle("New excel title");
xlsx.setAuthor("John Doe");
xlsx.setDescription("This is a description");

# Columns & Rows
xlsx.removeRows(13, 2);
xlsx.removeColumns("M", 3);
xlsx.copyRows(4, 5, 9);
xlsx.copyColumns("G", "H", "J");
xlsx.addRows(3, 2);
xlsx.addColumns("E", 2)

# Worksheets
xlsx.addWorksheet("New worksheet");
xlsx.copyWorksheet("New worksheet", "Copy");
xlsx.moveWorksheet("Copy", 0);
xlsx.renameWorksheet("Copy", "First Worksheet");
xlsx.selectWorksheet("First Worksheet");

# Images
xlsx.insertImage(rpgm.pgmFilePath("logo.png"), "I18");

# Save
xlsx.saveAs("result.xlsx");

See also the manual page for the Excel spreadsheets for further details.

xlsx.open(filename)

Opens the filename Excel spreadsheet file for editing.

This function MUST be called before any other XLSX functions. As other methods will be executed on the last Excel spreadsheet opened with xlsx.open.

xlsx.getCells(cells, asNumber = TRUE)

Retreives values in the cells from the opened spreadsheet.

  • cells can be a single cell like B3 or a range like C4:F9
  • asNumber will return a matrix of numbers if set to TRUE. Set to FALSE for returning a matrix of strings.
This function only returns the values of the ORIGINAL document. Opening an Excel file, setting a custom value in a cell and then getting the same cell’s value will return the original value, not the one which was set afterwards.

This function is pretty slow to execute. Try to minimise the calls to this function. Prefer calling getCell once with a bigger range value (even if some cells in the range are empty or not needed) rather than calling getCell multiple times.

xlsx.setTitle(title)

Sets the title property of the Excel spreadsheet to title.

xlsx.setAuthor(author = Sys.info()[[“user”]])

Sets the Author property of the Excel spreadsheet to author. By default the value will be Sys.info()[["user"]] which is the name of the user currently connected in the Operating System of the computer.

xlsx.setDescription(desc)

Sets the description property to desc of the Excel spreadsheet.

xlsx.addWorksheet(name)

Creates a new worksheet in the Excel spreadsheet named name.

xlsx.copyWorksheet(origin, target)

Duplicate an existing worksheet named origin to a new worksheet named target.

xlsx.moveWorksheet(origin, position)

Moves the worksheet named origin to a new position.

  • Position is a number. The first worksheet is at the position 0. The second at 1, and so on.

xlsx.renameWorksheet(oldname, newname)

Renames an existing worksheet named oldname to newname.

xlsx.removeWorksheet(target)

Deletes a worksheet names target.

xlsx.selectWorksheet(target)

Selects a worksheet for other operations.

xlsx.addRows(origin, number)

Adds a number of rows after the origin row. origin is a number.

xlsx.addColumns(origin, number)

Adds a number of columns after the origin column. origin is a string representing a column (Example: A, DF).

xlsx.copyRows(start, end, number)

Copy all rows from start to end to the position number.

  • start, end and number are numbers representing the row.
  • For copying a single row, start and end should be the same value.

xlsx.copyColumns(start, end, target)

Copy all columns from start to end to the position target.

  • start, end and target are strings representing the columns (like A or DF).
  • For copying a single column, start and end should be the same value.

xlsx.removeRows(origin, number)

Deletes number rows from origin.

xlsx.removeColumns(origin, number)

Deletes number columns from origin.

xlsx.insertImage(path, cell)

Inserts an image at the cell cell. path is the path to the image.

xlsx.setCell(cell, value)

Sets the cell cell to the value value.

xlsx.setName(name, value)

Sets the cell named name to the value value.

xlsx.mergeCells(start, end)

Merges the cells from start to end to a single cell.

xlsx.unmergeCells(start, end)

Unmerges the cells from start to end.

xlsx.saveAs(outputfile)

Saves all the modifications made to a new file outputfile.