Append Rows To Worksheet
Append values to the end of the worksheet.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
content | Any | null | Rows of values to append |
name | str, None | None | Name of worksheet to append to (optional). Defaults to the active worksheet. |
header | bool | False | Set rows according to existing header row |
start | int, None | None | Start of data, NOTE: Only required when header is True |
formatting_as_empty | bool, None | False | if True, the cells containing only formatting (no values) are considered empty. |
param content: | Rows of values to append |
---|---|
param name: | Name of worksheet to append to (optional). Defaults to the active worksheet. |
param header: | Set rows according to existing header row |
param start: | Start of data, NOTE: Only required when header is True |
param formatting_as_empty: | |
if True, the cells containing only formatting (no values) are considered empty. | |
return: | List of dictionaries that represents the worksheet |
The content argument can be of any tabular format. Typically, this is a Table object created by the RPA.Tables library, but it can also be a list of lists, or a list of dictionaries.
If the header flag is enabled, the existing header in the worksheet is used to insert values in the correct columns. This assumes that that source data has this data available.
If the header is not on the first row of the worksheet, the start argument can be used to give the correct row index.
Examples:
# Append an existing Table object
# Create a new table using a Dictionary of Lists
@{table_name}= Create List Sara Beth Amy
@{table_age}= Create List ${48} ${21} ${57}
&{table}= Create Dictionary name=${table_name} age=${table_age}
Create Table ${table}
Append rows to worksheet ${table}
Save Workbook
# Append to a worksheet with headers on row 5
# Create a new table using a Dictionary of Lists
@{table_name}= Create List Sara Beth Amy
@{table_age}= Create List ${48} ${21} ${57}
&{table}= Create Dictionary name=${table_name} age=${table_age}
Create Table ${table}
Append rows to worksheet ${table} header=${TRUE} start=5
Save Workbook
# Append an existing Table object
table = {
"name": ["Sara", "Beth", "Amy"],
"age": [ 48, 21, 57],
}
lib.append_rows_to_worksheet(table)
lib.save_workbook()
# Append to a worksheet with headers on row 5
table = {
"name": ["Sara", "Beth", "Amy"],
"age": [ 48, 21, 57],
}
lib.append_rows_to_worksheet(table, header=True, start=5)
lib.save_workbook()
Close Workbook
Close the active workbook.
Examples:
# Close active workbook
Close Workbook
# Close active workbook
lib.close_workbook()
Create Workbook
Create and open a new Excel workbook.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
path | str, None | None | Save path for workbook; defaults to robot root if not provided. |
fmt | str | xlsx | Format of workbook, i.e. xlsx or xls; Defaults to xlsx if not provided. |
sheet_name | str, None | None | Custom name for the initial sheet. |
Automatically also creates a new worksheet with the name sheet_name. (defaults to "Sheet")
Note: Must be paired with the Save Workbook keyword or the newly created workbook will be deleted upon robot completion.
Note: The file name/path must be set in either the Create Workbook keyword or the Save Workbook keyword and must include the file extension.
param path: | Save path for workbook; defaults to robot root if not provided. |
---|---|
param fmt: | Format of workbook, i.e. xlsx or xls; Defaults to xlsx if not provided. |
param sheet_name: | |
Custom name for the initial sheet. | |
return: | Workbook object. |
Examples:
# Create modern format workbook.
Create Workbook
Save Workbook orders.xlsx
# Create modern format workbook with custom sheet name.
Create Workbook sheet_name=MyCustomSheetName
Save Workbook orders.xlsx
# Create modern format workbook with a path set.
Create Workbook path=${OUTPUT_DIR}${/}orders.xlsx
Save Workbook
# Create legacy format workbook.
Create Workbook fmt=xls
Save Workbook orders.xls
# Create legacy format workbook with a path set.
# Note that the file name must be set in the Create Workbook keyword
# if the path argument is used.
Create Workbook path=${OUTPUT_DIR}${/}orders.xls fmt=xls
Save Workbook
# Create modern format workbook with defaults.
lib = Files()
lib.create_workbook()
lib.save_workbook("orders.xlsx")
# Create modern format workbook with a path set.
lib = Files()
lib.create_workbook(path="./output/orders.xlsx", fmt="xlsx")
lib.save_workbook()
# Create legacy format workbook.
lib = Files()
lib.create_workbook(fmt="xls")
lib.save_workbook("orders.xls")
# Create legacy format workbook with a path set.
# Note that the file name must be set in the Create Workbook keyword
# if the path is used.
lib = Files()
lib.create_workbook(path="./output/orders.xls", fmt="xls")
lib.save_workbook()
Create Worksheet
Create a new worksheet in the current workbook.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
name | str | null | Name of new worksheet |
content | Any, None | None | Optional content for worksheet |
exist_ok | bool, None | False | If False, raise an error if name is already in use |
header | bool, None | False | If content is provided, write headers to worksheet |
param name: | Name of new worksheet |
---|---|
param content: | Optional content for worksheet |
param exist_ok: | If False, raise an error if name is already in use |
param header: | If content is provided, write headers to worksheet |
Examples:
# Create a new blank worksheet named "Customers"
Create Worksheet Customers
# Create a new worksheet with headers and contents using
# a List of Dictonaries
# Don't forget to Save Workbook once your changes are complete
&{Employees_Row1}= Create Dictionary name=Mark age=${58}
&{Employees_Row2}= Create Dictionary name=John age=${22}
&{Employees_Row3}= Create Dictionary name=Adam age=${67}
@{Worksheet_Data}= Create List
... ${Worksheet_Data_row1}
... ${Worksheet_Data_row2}
... ${Worksheet_Data_row3}
Create Worksheet
... name=Employees
... content=${Worksheet_Data}
... header=True
Save Workbook
# Create a new workseet using a Dictionary of Lists
# Don't forget to Save Workbook once your changes are complete
@{Employees_name}= Create List Mark John Adam
@{Employees_age}= Create List ${58} ${22} ${67}
&{Worksheet_Data}= Create Dictionary
... name=${Worksheet_Data_name}
... age=${Worksheet_Data_age}
Create Worksheet
... name=Employees
... content=${Worksheet_Data}
... header=True
Save Workbook
# Create a new blank worksheet named "Customers"
lib.create_worksheet("Customers")
# Create a new workseet using a List of Dictionaries
# Don't forget to Save Workbook once your changes are complete
Worksheet_Data = [
{"name": "Mark", "age": 58},
{"name": "John", "age": 22},
{"name": "Adam", "age": 67},
]
lib.create_worksheet(name="Employees",content=Worksheet_Data,header=True)
lib.save_workbook()
# Create a new workseet using a Dictionary of Lists
# Don't forget to Save Workbook once your changes are complete
Worksheet_Data = {
"name": ["Mark", "John", "Adam"],
"age": [ 58, 22, 67],
}
lib.create_worksheet(name="Employees",content=Worksheet_Data,header=True)
lib.save_workbook()
Find Empty Row
Find the first empty row after existing content, and return the row number.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
name | str, None | None | Name of worksheet (optional). Defaults to the active worksheet. |
param name: | Name of worksheet (optional). Defaults to the active worksheet. |
---|---|
return: | First row number of empty row |
Examples:
${next}= Find empty row
next = lib.find_empty_row()
Get Active Worksheet
Get the name of the worksheet which is currently active.
return: | Active worksheet name |
---|
Examples:
${Active_Worksheet}= Get Active Worksheet
Active_Worksheet = lib.get_active_worksheet()
Get Cell Value
Get a cell value in the given worksheet.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
row | int | null | Index of row to read, e.g. 3 |
column | str, int | null | Name or index of column, e.g. C or 7 |
name | str, None | None | Name of worksheet (optional). Defaults to active worksheet. |
param row: | Index of row to read, e.g. 3 |
---|---|
param column: | Name or index of column, e.g. C or 7 |
param name: | Name of worksheet (optional). Defaults to active worksheet. |
return: | Cell value |
Examples:
# Read header names
${column1}= Get cell value 1 A
${column2}= Get cell value 1 B
${column3}= Get cell value 1 C
# Read header names
column1 = lib.get_cell_value(1, "A")
column2 = lib.get_cell_value(1, "B")
column3 = lib.get_cell_value(1, "C")
Get Worksheet Value
Alias for keyword Get cell value, see the original keyword for documentation.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
row | int | null | |
column | str, int | null | |
name | str, None | None |
Insert Image To Worksheet
Insert an image into the given cell.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
row | int | null | Index of row to write |
column | int, str | null | Name or index of column |
path | str | null | Path to image file |
scale | float | 1.0 | Scale of image (optional). Default value is "1.0". |
name | str, None | None | Name of worksheet (optional). Defaults to the active worksheet. |
The path argument should be a local file path to the image file.
By default the image is inserted in the original size, but it can be scaled with the scale argument. It's scaled with a factor where the value 1.0 is the default.
param row: | Index of row to write |
---|---|
param column: | Name or index of column |
param path: | Path to image file |
param scale: | Scale of image (optional). Default value is "1.0". |
param name: | Name of worksheet (optional). Defaults to the active worksheet. |
Examples:
Insert image to worksheet ${last_row} A screenshot.png
lib.insert_image_to_worksheet(last_row, "A", "screenshot.png")
List Worksheets
List all names of worksheets in the given workbook.
return: | List containing the names of the worksheets |
---|
Examples:
# List Worksheets will read the worksheet names into a list variable
# The variable should be declared with the List type "@" when being used
# to store the sheet names from the List Worksets keyword
@{sheets}= List Worksheets
# List Worksheets will read the worksheet names into a list variable
# The variable should be declared with the List type "@" when being used
# to store the sheet names from the List Worksets keyword
sheets = lib.list_worksheets()
Open Workbook
Open an existing Excel workbook.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
path | str | null | path to Excel file |
data_only | bool, None | False | controls whether cells with formulas have either the formula (default, False) or the value stored the last time Excel read the sheet (True). Affects only .xlsx files. |
Opens the workbook in memory and sets it as the active workbook. This library can only have one workbook open at a time, and any previously opened workbooks are closed first.
The file can be in either .xlsx or .xls format.
param path: | path to Excel file |
---|---|
param data_only: | |
controls whether cells with formulas have either the formula (default, False) or the value stored the last time Excel read the sheet (True). Affects only .xlsx files. | |
return: | Workbook object |
Examples:
# Open workbook with only path provided
Open Workbook path/to/file.xlsx
# Open workbook with path provided and reading formulas in cells
# as the value stored
# Note: Can only be used with XLSX workbooks
Open Workbook path/to/file.xlsx data_only=True
# Open workbook with only path provided
lib.open_workbook(path="path/to/file.xlsx")
# Open workbook with path provided and reading formulas in cells
# as the value stored
# Note: Can only be used with XLSX workbooks
lib.open_workbook(path="path/to/file.xlsx", data_only=True)
Read Worksheet
Read the content of a worksheet into a list of dictionaries.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
name | str, None | None | Name of worksheet to read (optional). Defaults to the active worksheet. |
header | bool, None | False | If True, use the first row of the worksheet as headers for the rest of the rows. Default is False. |
start | int, None | None | Row index to start reading data from (1-indexed). Default value is row 1. |
Each key in the dictionary will be either values from the header row, or Excel-style column letters.
param name: | Name of worksheet to read (optional). Defaults to the active worksheet. |
---|---|
param header: | If True, use the first row of the worksheet as headers for the rest of the rows. Default is False. |
param start: | Row index to start reading data from (1-indexed). Default value is row 1. |
return: | List of dictionaries that represents the worksheet |
Examples:
# The most simple form. Column keys will be Column letters.
${rows}= Read Worksheet
# Since header=True the keys will be the header values
${rows}= Read Worksheet header=True
# Uses the header values as keys and starts reading at row 3
${rows}= Read Worksheet header=True start=${3}
# The most simple form. Keys will be Column letters.
rows = lib.read_worksheet()
# Since header=True the keys will be the header values
rows = lib.read_worksheet(header=True)
# Uses the header values as keys and starts reading at row 3
rows = lib.read_worksheet(header=True, start=3)
Read Worksheet As Table
Read the contents of a worksheet into a Table container. Allows sorting/filtering/manipulating using the RPA.Tables library.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
name | str, None | None | Name of worksheet to read (optional). Defaults to the active worksheet. |
header | bool | False | If True, use the first row of the worksheet as headers for the rest of the rows. Default value is False. |
trim | bool | True | Remove all empty rows from the end of the worksheet. Default value is True. |
start | int, None | None | Row index to start reading data from (1-indexed). Default value is row 1. |
param name: | Name of worksheet to read (optional). Defaults to the active worksheet. |
---|---|
param header: | If True, use the first row of the worksheet as headers for the rest of the rows. Default value is False. |
param trim: | Remove all empty rows from the end of the worksheet. Default value is True. |
param start: | Row index to start reading data from (1-indexed). Default value is row 1. |
return: | Table object that represents the worksheet |
Examples:
# The most simple form. Column keys will be Column letters.
${table}= Read Worksheet As Table
# Since header=True the keys will be the header values
${table}= Read Worksheet As Table header=True
# Uses the header values as keys and starts reading at row 3
${table}= Read Worksheet As Table header=True start=${3}
# The most simple form. Keys will be Column letters.
table = lib.read_worksheet_as_table()
# Since header=True the keys will be the header values
table = lib.read_worksheet_as_table(header=True)
# Uses the header values as keys and starts reading at row 3
table = lib.read_worksheet_as_table(header=True, start=3)
Remove Worksheet
Remove a worksheet from the active workbook.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
name | str, None | None | Name of worksheet to remove (optional). Defaults to the active worksheet. |
param name: | Name of worksheet to remove (optional). Defaults to the active worksheet. |
---|
Examples:
# Remove last worksheet
${sheets}= List worksheets
Remove worksheet ${sheets}[-1]
# Remove worksheet by name
Remove Worksheet Sheet
# Remove last worksheet
sheets = lib.list_worksheets()
lib.remove_worksheet(sheets[-1])
# Remove worksheet by name
lib.remove_worksheet("Sheet")
Rename Worksheet
Rename an existing worksheet in the active workbook.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
src_name | str | null | Current name of worksheet |
dst_name | str | null | Future name of worksheet |
param src_name: | Current name of worksheet |
---|---|
param dst_name: | Future name of worksheet |
Examples:
Rename worksheet Sheet Orders
lib.rename_worksheet("Sheet","Orders")
Save Workbook
Save the active workbook.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
path | str, None | None | Path to save to. If not given, uses path given when opened or created. |
Note: No changes to the workbook are saved to the actual file unless this keyword is called.
param path: | Path to save to. If not given, uses path given when opened or created. |
---|---|
return: | Workbook object |
Examples:
# Saving the active workbook to a new location/filename or saving to
# a new location/filename
# Note: You cannot use Save Workbook to convert from XLSX to XLS
# or vice-versa
Save Workbook path=${OUTPUT_DIR}${/}orders.xlsx
# Saving the active workbook changes if location/filename were set
# in Create Workbook or Open Workbook
Save Workbook
# Saving the active workbook to a new location/filename or saving to
# a new location/filename
# Note: You cannot use Save Workbook to convert from XLSX to XLS
# or vice-versa
lib.save_workbook(path="./output/orders.xlsx")
# Saving the active workbook changes if location/filename were set
# in Create Workbook or Open Workbook
lib.save_workbook()
Set Active Worksheet
Set the active worksheet.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
value | str, int | null | Index or name of worksheet |
This keyword can be used to set the default worksheet for keywords, which removes the need to specify the worksheet name for each keyword. It can always be overridden on a per-keyword basis.
param value: | Index or name of worksheet |
---|
Examples:
# Set using the name of the worksheet
Set Active Worksheet Customers
# Set using the index of the worksheet
# Worksheet index begings at 0
Set Active Worksheet 2
# Set using the name of the worksheet
lib.set_active_worksheet("Customers")
# Set using the index of the worksheet
# Worksheet index begings at 0
lib.set_active_worksheet(2)
Set Cell Format
Set format for cell.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
row | int | null | Index of row to write, e.g. 3 |
column | str, int | null | Name or index of column, e.g. C or 7 |
fmt | str, float | null | Format code for cell |
name | str, None | None | Name of worksheet (optional). Defaults to active worksheet. |
Does not affect the values themselves, but changes how the values are displayed when opening with an external application such as Microsoft Excel or LibreOffice Calc.
param row: | Index of row to write, e.g. 3 |
---|---|
param column: | Name or index of column, e.g. C or 7 |
param fmt: | Format code for cell |
param name: | Name of worksheet (optional). Defaults to active worksheet. |
The fmt argument accepts all format code values that are supported by the aforementioned applications.
Some examples of valid values:
Format | Explanation |
---|---|
0.00 | Number with two decimal precision |
0% | Percentage without decimals |
MM/DD/YY | Date with month, day, and year |
@ | Text value |
BOOLEAN | Boolean value |
Examples:
# Set value to have one decimal precision
Set cell format 2 B 00.0
# Set value to have one decimal precision
lib.set_cell_format(2, "B", 00.0)
Set Cell Value
Set a cell value in the given worksheet.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
row | int | null | Index of row to write, e.g. 3 |
column | str, int | null | Name or index of column, e.g. C or 7 |
value | Any | null | New value of cell |
name | str, None | None | Name of worksheet (optional). Defaults to active worksheet. |
fmt | str, float, None | None | Format code for cell (optional) |
param row: | Index of row to write, e.g. 3 |
---|---|
param column: | Name or index of column, e.g. C or 7 |
param value: | New value of cell |
param name: | Name of worksheet (optional). Defaults to active worksheet. |
param fmt: | Format code for cell (optional) |
Examples:
# Set a value in the first row and column
Set cell value 1 1 Some value
Set cell value 1 A Some value
# Set a value with cell formatting
Set cell value 2 B ${value} fmt=0%
# Set a value in the first row and column
lib.set_cell_value(1, 1, "Some value")
lib.set_cell_value(1, "A", "Some value")
# Set a value with cell formatting
lib.set_cell_value(2, "B", value, fmt="0%")
Set Worksheet Value
Alias for keyword Set cell value, see the original keyword for documentation.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
row | int | null | |
column | str, int | null | |
value | Any | null | |
name | str, None | None | |
fmt | str, float, None | None |
Worksheet Exists
Return True if worksheet with given name is in workbook.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
name | str | null | Name of worksheet you are looking for |
param name: | Name of worksheet you are looking for |
---|---|
return: | True if the worksheet exists, False otherwise |
Examples:
# To use Worksheet Exists in a conditional statement set it to
# a variable first, like you see here, and then compare the
# variable to ${TRUE} or ${FALSE}
${Does_Worksheet_Exist}= Worksheet Exists Sheet
Does_Worksheet_Exist = lib.worksheet_exists("Sheet")