Webinar

November 3rd, 2021 12:00 PM EDT
Automation for Field Services & DistributionNovember 3rd, 2021 12:00 PM EDT
Learn how creating a digital workforce can improve your supply chain processes!

Append Rows To Worksheet

Append values to the end of the worksheet.

Arguments

Argument Type Default value Description
contentnull
nameNone
headerFalse
startNone
param content:Rows of values to append
param name:Name of worksheet to append to
param header:Set rows according to existing header row
param start:Start of data, NOTE: Only required when headers is True

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:

*** Tasks ***
# Append an existing Table object
Open workbook    orders.xls
Append rows to worksheet    ${table}

# Append to a worksheet with headers on row 5
Open workbook    customers.xlsx
Append rows to worksheet    ${table}    header=${TRUE}   start=5

Close Workbook

Close the active workbook.

Create Workbook

Create and open a new Excel workbook.

Arguments

Argument Type Default value Description
pathNone
fmtxlsx

Automatically also creates a new worksheet with the name "Sheet".

param path:Default save path for workbook
param fmt:Format of workbook, i.e. xlsx or xls

Examples:

*** Tasks ***
# Create modern format workbook
Create workbook    fmt=xlsx
Save workbook    orders.xlsx

# Create legacy format workbook
Create workbook    fmt=xls
Save workbook    orders.xls

Create Worksheet

Create a new worksheet in the current workbook.

Arguments

Argument Type Default value Description
namenull
contentNone
exist_okFalse
headerFalse
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:

*** Tasks ***
Open workbook       customers.xlsx
Create worksheet    Orders

Find Empty Row

Find the first empty row after existing content, and return the row number.

Arguments

Argument Type Default value Description
nameNone

Find the first empty row after existing content, and return the row number.

param name:Name of worksheet

Examples:

*** Tasks ***
Append rows to worksheet     ${rows}
${next}=    Find empty row
Insert image to worksheet    ${next}    A    screenshot.png

Get Active Worksheet

Get the name of the worksheet which is currently active.

Get Cell Value

Get a cell value in the given worksheet.

Arguments

Argument Type Default value Description
rownull
columnnull
nameNone
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)

Examples:

*** Tasks ***
# Read header names
${column1}=    Get cell value    1    A
${column2}=    Get cell value    1    B
${column3}=    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
rownull
columnnull
nameNone

Alias for keyword Get cell value, see the original keyword for documentation.

Insert Image To Worksheet

Insert an image into the given cell.

Arguments

Argument Type Default value Description
rownull
columnnull
pathnull
scale1.0
nameNone

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
param name:Name of worksheet

Examples:

*** Tasks ***
Create worksheet    Order
Append rows to worksheet     ${details}
${last_row}=    Find empty row
Insert image to worksheet    ${last_row}    A    screenshot.png

List Worksheets

List all names of worksheets in the given workbook.

Open Workbook

Open an existing Excel workbook.

Arguments

Argument Type Default value Description
pathnull

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 .xlsx format.

param path:path to Excel file

Examples:

*** Tasks ***
Open workbook    path/to/file.xlsx
${table}=    Read worksheet as table

Read Worksheet

Read the content of a worksheet into a list of dictionaries.

Arguments

Argument Type Default value Description
nameNone
headerFalse
startNone

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
param header:If True, use the first row of the worksheet as headers for the rest of the rows.

Examples:

*** Tasks ***
Open Workbook   orders.xlsx
${rows}=        Read Worksheet     header=True
Close Workbook

Read Worksheet As Table

Read the content of a worksheet into a Table container. Allows sorting/filtering/manipulating using the ``RPA.Tables`` library.

Arguments

Argument Type Default value Description
nameNone
headerFalse
trimTrue
startNone

Read the content of a worksheet into a Table container. Allows sorting/filtering/manipulating using the RPA.Tables library.

param name:Name of worksheet to read
param header:If True, use the first row of the worksheet as headers for the rest of the rows.
param trim:Remove all empty rows from the end of the worksheet
param start:Row index to start reading data from (1-indexed)

Examples:

*** Tasks ***
Open Workbook   orders.xlsx
${table}=       Read Worksheet As Table     header=True
Close Workbook

Remove Worksheet

Remove a worksheet from the active workbook.

Arguments

Argument Type Default value Description
nameNone
param name:Name of worksheet to remove

Examples:

*** Tasks ***
# Remove last worksheet
Open workbook    orders.xlsx
${sheets}=       List worksheets
Remove worksheet    ${sheets}[-1]

Rename Worksheet

Rename an existing worksheet in the active workbook.

Arguments

Argument Type Default value Description
src_namenull
dst_namenull
param src_name:Current name of worksheet
param dst_name:Future name of worksheet

Examples:

*** Tasks ***
Create workbook
Rename worksheet    Sheet    Orders

Save Workbook

Save the active workbook.

Arguments

Argument Type Default value Description
pathNone

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.

Set Active Worksheet

Set the active worksheet.

Arguments

Argument Type Default value Description
valuenull

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:

*** Tasks ***
Open workbook    complex.xlsx
Set active worksheet    Customers
Append rows to worksheet    ${rows}

Set Cell Format

Set number format for cell.

Arguments

Argument Type Default value Description
rownull
columnnull
fmtnull
nameNone

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.

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:

*** Tasks ***
# Set value to have one decimal precision
Set cell value    2  B    ${value}
Set cell format   2  B    00.0

Set Cell Value

Set a cell value in the given worksheet.

Arguments

Argument Type Default value Description
rownull
columnnull
valuenull
nameNone
fmtNone
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)
param fmt:Format code for cell (optional)

Examples:

*** Tasks ***
# 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 Worksheet Value

Alias for keyword ``Set cell value``, see the original keyword for documentation.

Arguments

Argument Type Default value Description
rownull
columnnull
valuenull
nameNone
fmtNone

Alias for keyword Set cell value, see the original keyword for documentation.

Worksheet Exists

Return True if worksheet with given name is in workbook.

Arguments

Argument Type Default value Description
namenull