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

close_workbook()

Close the active workbook.

create_workbook(path=None, fmt='xlsx')

Create and open a new Excel workbook.

Parameters

  • path – Default save path for workbook

  • fmt – Format of workbook, i.e. xlsx or 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

find_empty_row(name=None)

Find the first empty row after existing content.

Parameters

name – Name of worksheet

get_active_worksheet()

Get the name of the worksheet which is currently active.

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

Insert an image into the given cell.

Parameters

  • row – Index of row to write

  • column – Name or index of column

  • image – Path to image file

  • scale – Scale of image

  • name – Name of worksheet

list_worksheets()

List all names of worksheets in the given workbook.

open_workbook(path)

Open an existing Excel workbook.

Parameters

path – path to Excel file

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.

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)

remove_worksheet(name=None)

Remove a worksheet from the active workbook.

Parameters

name – Name of worksheet to remove

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

save_workbook(path=None)

Save the active workbook.

Parameters

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

set_active_worksheet(value)

Set the active worksheet.

Parameters

value – Index or name of worksheet

set_worksheet_value(row, column, value, name=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

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)
insert_image(row, column, image, name=None)
open(path_or_file=None)
read_worksheet(name=None, header=False, start=None)
remove_worksheet(name=None)
rename_worksheet(title, name=None)
save(path=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)
insert_image(row, column, image, name=None)
open(path=None)
read_worksheet(name=None, header=False, start=None)
remove_worksheet(name=None)
rename_worksheet(title, name=None)
save(path=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()