Python API

Excel.Files

class RPA.Excel.Files.Files

Bases: object

The Excel.Files library can be used to read and write Excel files without the need to start the actual Excel application.

It supports both legacy .xls files and modern .xlsx files.

Note: To run macros or load password protected worksheets, please use the Excel application library.

Examples

Robot Framework

A common use-case is to load an existing Excel file as a table, which can be iterated over later in a Robot Framework keyword or task:

*** Settings ***
Library    RPA.Tables
Library    RPA.Excel.Files

*** Keywords ***
Read orders as table
    Open workbook    ${ORDERS_FILE}
    ${worksheet}=    Read worksheet   header=${TRUE}
    ${orders}=       Create table     ${worksheet}
    [Return]         ${orders}
    [Teardown]       Close workbook

Processing all worksheets in the Excel file and checking row count:

*** Settings ***
Library    RPA.Excel.Files

*** Variables ***
${EXCEL_FILE}   /path/to/excel.xlsx

*** Tasks ***
Rows in the sheet
    [Setup]      Open Workbook    ${EXCEL_FILE}
    @{sheets}=   List Worksheets
    FOR  ${sheet}  IN   @{sheets}
        ${count}=  Get row count in the sheet   ${sheet}
        Log   Worksheet '${sheet}' has ${count} rows
    END

*** Keywords ***
Get row count in the sheet
    [Arguments]      ${SHEET_NAME}
    ${sheet}=        Read Worksheet   ${SHEET_NAME}
    ${rows}=         Get Length  ${sheet}
    [Return]         ${rows}

Creating a new Excel file with a dictionary:

*** Tasks ***
Creating new Excel
    Create Workbook  my_new_excel.xlsx
    FOR    ${index}    IN RANGE    20
        &{row}=       Create Dictionary
        ...           Row No   ${index}
        ...           Amount   ${index * 25}
        Append Rows to Worksheet  ${row}  header=${TRUE}
    END
    Save Workbook

Creating a new Excel file with a list:

*** Variables ***
@{heading}   Row No   Amount
@{rows}      ${heading}

*** Tasks ***
Creating new Excel
    Create Workbook  my_new_excel.xlsx
    FOR    ${index}    IN RANGE   1  20
        @{row}=         Create List   ${index}   ${index * 25}
        Append To List  ${rows}  ${row}
    END
    Append Rows to Worksheet  ${rows}
    Save Workbook

Python

The library can also be imported directly into Python.

from RPA.Excel.Files import Files

def read_excel_worksheet(path, worksheet):
    lib = Files()
    lib.open_workbook(path)
    try:
        return lib.read_worksheet(worksheet)
    finally:
        lib.close_workbook()
ROBOT_LIBRARY_DOC_FORMAT = 'REST'
ROBOT_LIBRARY_SCOPE = 'GLOBAL'
append_rows_to_worksheet(content, name=None, header=False, start=None)

Append values to the end of the worksheet.

Parameters

  • content – Rows of values to append

  • name – Name of worksheet to append to

  • header – Set rows according to existing header row

  • 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:

# 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(path=None, fmt='xlsx')

Create and open a new Excel workbook.

Automatically also creates a new worksheet with the name “Sheet”.

Parameters

  • path – Default save path for workbook

  • fmt – Format of workbook, i.e. xlsx or xls

Examples:

# 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(name, content=None, exist_ok=False, header=False)

Create a new worksheet in the current workbook.

Parameters

  • name – Name of new worksheet

  • content – Optional content for worksheet

  • exist_ok – If False, raise an error if name is already in use

  • header – If content is provided, write headers to worksheet

Examples:

Open workbook       customers.xlsx
Create worksheet    Orders
find_empty_row(name=None)

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

Parameters

name – Name of worksheet

Examples:

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(row, column, name=None)

Get a cell value in the given worksheet.

Parameters

  • row – Index of row to read, e.g. 3

  • column – Name or index of column, e.g. C or 7

  • name – Name of worksheet (optional)

Examples:

# Read header names
${column1}=    Get cell value    1    A
${column2}=    Get cell value    1    B
${column3}=    Get cell value    1    C
get_worksheet_value(row, column, name=None)

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

insert_image_to_worksheet(row, column, path, scale=1.0, name=None)

Insert an image into the given cell.

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.

Parameters

  • row – Index of row to write

  • column – Name or index of column

  • path – Path to image file

  • scale – Scale of image

  • name – Name of worksheet

Examples:

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(path)

Open an existing Excel workbook.

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.

Parameters

path – path to Excel file

Examples:

Open workbook    path/to/file.xlsx
${table}=    Read worksheet as table
read_worksheet(name=None, header=False, start=None)

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

Each key in the dictionary will be either values from the header row, or Excel-style column letters.

Parameters

  • name – Name of worksheet to read

  • header – If True, use the first row of the worksheet as headers for the rest of the rows.

Examples:

Open Workbook   orders.xlsx
${rows}=        Read Worksheet     header=True
Close Workbook
read_worksheet_as_table(name=None, header=False, trim=True, start=None)

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

Parameters

  • name – Name of worksheet to read

  • header – If True, use the first row of the worksheet as headers for the rest of the rows.

  • trim – Remove all empty rows from the end of the worksheet

  • start – Row index to start reading data from (1-indexed)

Examples:

Open Workbook   orders.xlsx
${table}=       Read Worksheet As Table     header=True
Close Workbook
remove_worksheet(name=None)

Remove a worksheet from the active workbook.

Parameters

name – Name of worksheet to remove

Examples:

# Remove last worksheet
Open workbook    orders.xlsx
${sheets}=       List worksheets
Remove worksheet    ${sheets}[-1]
rename_worksheet(src_name, dst_name)

Rename an existing worksheet in the active workbook.

Parameters

  • src_name – Current name of worksheet

  • dst_name – Future name of worksheet

Examples:

Create workbook
Rename worksheet    Sheet    Orders
save_workbook(path=None)

Save the active workbook.

Note: No changes to the workbook are saved to the actual file unless this keyword is called.

Parameters

path – Path to save to. If not given, uses path given when opened or created.

set_active_worksheet(value)

Set the active 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.

Parameters

value – Index or name of worksheet

Examples:

Open workbook    complex.xlsx
Set active worksheet    Customers
Append rows to worksheet    ${rows}
set_cell_format(row, column, fmt, name=None)

Set number format for cell.

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:

# Set value to have one decimal precision
Set cell value    2  B    ${value}
Set cell format   2  B    00.0
set_cell_value(row, column, value, name=None, fmt=None)

Set a cell value in the given worksheet.

Parameters

  • row – Index of row to write, e.g. 3

  • column – Name or index of column, e.g. C or 7

  • value – New value of cell

  • name – Name of worksheet (optional)

  • 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_worksheet_value(row, column, value, name=None, fmt=None)

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

worksheet_exists(name)

Return True if worksheet with given name is in workbook.

class RPA.Excel.Files.XlsWorkbook(path=None)

Bases: object

Container for manipulating legacy Excel files (.xls)

property active
append_worksheet(name=None, content=None, header=False, start=None)
close()
create(sheet='Sheet')
create_worksheet(name)
property extension
find_empty_row(name=None)
get_cell_value(row, column, name=None)
insert_image(row, column, image, name=None)
open(path=None, read_only=False, write_only=False)
read_worksheet(name=None, header=False, start=None)
remove_worksheet(name=None)
rename_worksheet(title, name=None)
save(path=None)
set_cell_format(row, column, fmt, name=None)
set_cell_value(row, column, value, name=None)
property sheetnames
class RPA.Excel.Files.XlsxWorkbook(path=None)

Bases: object

Container for manipulating moden Excel files (.xlsx)

property active
append_worksheet(name=None, content=None, header=False, start=None)
close()
create()
create_worksheet(name)
property extension
find_empty_row(name=None)
get_cell_value(row, column, name=None)
insert_image(row, column, image, name=None)
open(path=None, read_only=False, write_only=False)
read_worksheet(name=None, header=False, start=None)
remove_worksheet(name=None)
rename_worksheet(title, name=None)
save(path=None)
set_cell_format(row, column, fmt, name=None)
set_cell_value(row, column, value, name=None)
property sheetnames
RPA.Excel.Files.ensure_unique(values)

Ensures that each string value in the list is unique. Adds a suffix to each value that has duplicates, e.g. [Banana, Apple, Lemon, Apple] -> [Banana, Apple, Lemon, Apple_2]

RPA.Excel.Files.get_column_index(column)

Get column index from name, e.g. A -> 1, D -> 4, AC -> 29. Reverse of get_column_letter()