Skip to main content
Docs
Go to the Studio
Roberty Studio
RPADocuments

Spreadsheet

Spreadsheet

The Spreadsheet module is responsible for performing handling actions on spreadsheet files. Its methods can be accessed as in the example below:

js
const spreadsheet = await Documents.Spreadsheet.openSpreadsheet["v1_0_0"]("C:/Users/Roberty/Documents/teste.xlsx")

Methods​

cloneSheet​

Method responsible for cloning a spreadsheet tab.

js
await Documents.Spreadsheet.cloneSheet["v1_0_0"]({
  filePath: "C:/Users/Roberty/Documents/teste.xlsx",
  from: "Sheet1",
  to: "Sheet2"
})

Required parameters​

  • filePath: String - expects the path of the file that has the tabs that will be cloned. The path must contain the file name and the extension. This value is optional if the spreadSheet parameter is provided.
  • from: String - expects the name of the tab that will be cloned.
  • to: String - expects the name of the tab that will be created.
  • spreadSheet: Object - expects an object that can be obtained through the open action. This value is optional if the filePath parameter is provided.

Optional parameters​

This method has no optional parameters.

Return​

This method has no return.

createSpreadsheet​

Method responsible for creating a new spreadsheet.

js
const spreadsheet = await Documents.Spreadsheet.createSpreadsheet["v1_0_0"]({
  filePath: "C:/Users/Roberty/Documents/teste.xlsx"
})

Required parameters​

  • filePath: String - expects the path of the file that will be created. The path must contain the file name and the extension.

Optional parameters​

This method has no optional parameters.

Return​

The constant or variable created, such as the spreadsheet of the example shown earlier, receives a value of the Object type that gives access to the following properties:

  • spreadsheet._XlsxPopulate: XlsxPopulate.Workbook - returns a structure of the XlsxPopulate.Workbook type that can be used to handle spreadsheets.
  • spreadsheet._exceljs: ExcelJS.Workbook - returns a structure of the ExcelJS.Workbook type that can be used to handle spreadsheets.
  • spreadsheet.tempFilePath: string - returns the path of the temporary file created to handle the spreadsheet.
  • spreadsheet.filePath: string - returns the path of the file that was opened.

deleteSheet​

Method responsible for deleting a spreadsheet tab.

js
await Documents.Spreadsheet.deleteSheet["v1_0_0"]({
  filePath: "C:/Users/Roberty/Documents/teste.xlsx",
  name: "Sheet1"
})

Required parameters​

  • filePath: String - expects the path of the file that has the tabs that will be deleted. The path must contain the file name and the extension. This value is optional if the spreadSheet parameter is provided.
  • name: String - expects the name of the tab that will be deleted.
  • spreadSheet: Object - expects an object that can be obtained through the open action. This value is optional if the filePath parameter is provided.

Optional parameters​

This method has no optional parameters.

Return​

This method has no return.

getCellValue​

Method responsible for getting the value of a cell of a spreadsheet.

js
const value = await Documents.Spreadsheet.getCellValue["v1_0_0"]({
  cell: "A1",
  sheet: sheet,
})

Required parameters​

  • cell: String - expects the column and row of the cell that will be obtained.
  • sheet: Object - expects an object that can be obtained through the getSheet action.

Optional parameters​

This method has no optional parameters.

Return​

The constant or variable created, such as the value of the example shown earlier, receives the value, which has the data type of the cell.

getRowValues​

Method responsible for getting the values of a row of a spreadsheet.

js
const row = await Documents.Spreadsheet.getRowValues["v1_0_0"]({
  row: 1,
  sheet: sheet,
})

Required parameters​

  • row: Number - expects the number of the row that will be obtained.
  • sheet: Object - expects an object that can be obtained through the getSheet action.

Optional parameters​

This method has no optional parameters.

Return​

The constant or variable created, such as the row of the example shown earlier, receives an object that has the following properties:

  • row.values: exceljs.CellValue[] - returns an array of values of the row.
  • row.count: Number - returns the number of filled cells of the row.

getSheet​

Method responsible for getting a spreadsheet tab.

js
const sheet = await Documents.Spreadsheet.getSheet["v1_0_0"]({
  spreadSheet: spreadsheet,
  sheetName: "Sheet1"
})

Required parameters​

  • spreadSheet: Object - expects an object that can be obtained through the openSpreadsheet action.
  • sheetName: String - expects the name of the tab that will be obtained.

Optional parameters​

This method has no optional parameters.

Return​

The constant or variable created, such as the sheet shown in the previous example, receives a value of the Object type that gives access to the following properties:

  • sheet._sheetExceljs: Worksheet - returns a structure of the ExcelJS.Worksheet type that can be used to handle tabs.
  • sheet._sheetXlsxPopulate: Worksheet - returns a structure of the XlsxPopulate.Worksheet type that can be used to handle tabs.
  • sheet._XlsxPopulate: XlsxPopulate.Workbook - returns a structure of the XlsxPopulate.Workbook type that can be used to handle spreadsheets.
  • sheet._exceljs: ExcelJS.Workbook - returns a structure of the ExcelJS.Workbook type that can be used to handle spreadsheets.
  • sheet.tempFilePath: string - returns the path of the temporary file created to handle the spreadsheet.
  • sheet.filePath: string - returns the path of the file the tab was obtained from.

getTableColumns​

Method responsible for getting the names of the columns of a table in a spreadsheet tab.

js
const columns = await Documents.Spreadsheet.getTableColumns["v1_0_0"]({
  tableName: "Table1",
  sheet: sheet,
})

Required parameters​

  • tableName: String - expects the name of the table that will be obtained.
  • sheet: Object - expects an object that can be obtained through the getSheet action.

Optional parameters​

This method has no optional parameters.

Return​

The constant or variable created, such as the columns of the example shown earlier, receives an object that has the following properties:

  • columns.columns: string[] - returns an array of column names.
  • columns.count: Number - returns the number of columns.

getTableValues​

Method responsible for getting the values of a table in a spreadsheet tab.

js
const tableValues = await Documents.Spreadsheet.getTableValues["v1_0_0"]({
  tableName: "Table1",
  sheet: sheet,
})

Required parameters​

  • tableName: String - expects the name of the table that will be obtained.
  • sheet: Object - expects an object that can be obtained through the getSheet action.

Optional parameters​

  • hasHeader: Boolean - expects a value indicating whether the table has a header. By default, the value is true.

Return​

The constant or variable created, such as the tableValues of the example shown earlier, receives a value of the Any[][] type with every value of the table.

listSheets​

Method responsible for getting every tab of a spreadsheet.

js
const sheets = await Documents.Spreadsheet.listSheets["v1_0_0"]({
  spreadSheet: spreadsheet,
})

Required parameters​

  • spreadSheet: Object - expects an object that can be obtained through the openSpreadsheet action. This parameter is optional if the filePath parameter is provided.
  • filePath: String - expects the path of the file that will be obtained. This parameter is optional if the spreadSheet parameter is provided.

Optional parameters​

This method has no optional parameters.

Return​

The constant or variable created, such as the sheets of the example shown earlier, receives a value of the Object type that gives access to the following properties:

  • sheets.sheets: string[] - returns an array of tab names.
  • sheets.count: Number - returns the number of tabs.

listTables​

Method responsible for getting every table in a spreadsheet tab.

js
const tables = await Documents.Spreadsheet.listTables["v1_0_0"]({
  sheet: sheet,
})

Required parameters​

  • sheet: Object - expects an object that can be obtained through the getSheet action.

Optional parameters​

This method has no optional parameters.

Return​

The constant or variable created, such as the tables of the example shown earlier, receives a value of the Object type that gives access to the following properties:

  • tables.tables: string[] - returns an array of table names.
  • tables.count: Number - returns the number of tables.

newSheet​

Method responsible for creating a new tab in a spreadsheet.

js
await Documents.Spreadsheet.newSheet["v1_0_0"]({
  spreadSheet: spreadsheet,
  name: "Sheet1",
})

Required parameters​

  • spreadSheet: Object - expects an object that can be obtained through the openSpreadsheet action. This parameter is optional if the filePath parameter is provided.
  • filePath: String - expects the path of the file that will be obtained. This parameter is optional if the spreadSheet parameter is provided.
  • name: String - expects the name of the tab that will be created.

Optional parameters​

This method has no optional parameters.

Return​

This method has no return.

openSpreadsheet​

Method responsible for opening a spreadsheet file.

js
const spreadsheet = await Documents.Spreadsheet.openSpreadsheet["v1_0_0"]("C:/Users/Roberty/Documents/teste.xlsx")

Required parameters​

This method has no object for the parameters. A string that will be the path of the .xlsx file must be passed as shown in the previous example.

Optional parameters​

This method has no optional parameter.

Return​

The constant or variable created, such as the spreadsheet of the example shown earlier, receives a value of the Object type that gives access to the following properties:

  • spreadsheet._XlsxPopulate: XlsxPopulate.Workbook - returns a structure of the XlsxPopulate.Workbook type that can be used to handle spreadsheets.
  • spreadsheet._exceljs: ExcelJS.Workbook - returns a structure of the ExcelJS.Workbook type that can be used to handle spreadsheets.
  • spreadsheet.tempFilePath: string - returns the path of the temporary file created to handle the spreadsheet.
  • spreadsheet.filePath: string - returns the path of the file that was opened.

rowCount​

Method responsible for counting how many filled rows a spreadsheet tab has.

js
const rowCount = await Documents.Spreadsheet.rowCount["v1_0_0"]({
  sheet: sheet,
})

Required parameters​

  • sheet: Object - expects an object that can be obtained through the getSheet action.

Optional parameters​

This method has no optional parameters.

Return​

The constant or variable created, such as the rowCount of the example shown earlier, receives a value of the object type that gives access to the following properties:

  • rowCount.count: Number - returns the number of filled rows.
  • rowCount.nextRow: Number - returns the index of the next empty row (equivalent to count + 1).

setCellValue​

Method responsible for setting the value of a cell of a spreadsheet tab.

js
await Documents.Spreadsheet.setCellValue["v1_0_0"]({
  cell: 'A1',
  sheet: sheet,
  value: "Roberty",
})

Required parameters​

  • cell: String - expects the column and row of the cell that will be set.
  • sheet: Object - expects an object that can be obtained through the getSheet action.
  • value: Any - expects the value that will be set in the cell.

Optional parameters​

  • formatCell: Object - expects an object that must have the following properties:
    • bold: Boolean - expects a boolean value indicating whether the cell will be bold.
    • italic: Boolean - expects a boolean value indicating whether the cell will be italic.
    • fontSize: Number - expects a numeric value indicating the font size.

Return​

This method has no return.

setColumnValues​

Method responsible for setting the value of a column of a spreadsheet tab.

js
await Documents.Spreadsheet.setColumnValues["v1_0_0"]({
  sheet: sheet,
  startColumn: "A",
  startRow: 1,
  values: [["Hello", "World"]],
})

Required parameters​

  • sheet: Object - expects an object that can be obtained through the getSheet action.
  • startColumn: String|Number - expects the initial column of the column that will be set.
  • startRow: Number - expects the initial row of the column that will be set.
  • values: Array - expects an array containing the values that will be set in the column. It can also receive an array of arrays, where each array represents the values of the columns.

Optional parameters​

  • formatCell: Object - expects an object that must have the following properties:
    • bold: Boolean - expects a boolean value indicating whether the column values will be bold.
    • italic: Boolean - expects a boolean value indicating whether the column values will be italic.
    • fontSize: Number - expects a numeric value indicating the font size.

Return​

This method has no return.

setRowValues​

Method responsible for setting the values in a row of a spreadsheet tab.

js
await Documents.Spreadsheet.setRowValues["v1_0_0"]({
  sheet: sheet,
  startRow: 1,
  startCell: "A",
  values: [["Hello", "World"]],
})

Required parameters​

  • sheet: Object - expects an object that can be obtained through the getSheet action.
  • startRow: Number - expects the number of the row that will receive the values.
  • startCell: String|Number - expects the initial column of the row that will be set.
  • values: Array - expects an array containing the values that will be set in the row. It can also receive an array of arrays, where each array represents rows of the spreadsheet.

Optional parameters​

  • formatCell: Object - expects an object that must have the following properties:
    • bold: Boolean - expects a boolean value indicating whether the row values will be bold.
    • italic: Boolean - expects a boolean value indicating whether the row values will be italic.
    • fontSize: Number - expects a numeric value indicating the font size.

Return​

This method has no return.