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
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.
Opens the filename Excel spreadsheet file for editing.
Retreives values in the cells from the opened spreadsheet.
getCellonce with a bigger range value (even if some cells in the range are empty or not needed) rather than calling
Sets the title property of the Excel spreadsheet to title.
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.
Sets the description property to desc of the Excel spreadsheet.
Creates a new worksheet in the Excel spreadsheet named name.
Duplicate an existing worksheet named origin to a new worksheet named target.
Moves the worksheet named origin to a new position.
Renames an existing worksheet named oldname to newname.
Deletes a worksheet names target.
Selects a worksheet for other operations.
Adds a number of rows after the origin row. origin is a number.
Adds a number of columns after the origin column. origin is a string representing a column (Example: A, DF).
Copy all rows from start to end to the position number.
Copy all columns from start to end to the position target.
Deletes number rows from origin.
Deletes number columns from origin.
Inserts an image at the cell cell. path is the path to the image.
Sets the cell cell to the value value.
Sets the cell named name to the value value.
Merges the cells from start to end to a single cell.
Unmerges the cells from start to end.
Saves all the modifications made to a new file outputfile.