Python API

Excel.Files

class RPA.Excel.Files.BaseWorkbook(path: Optional[Union[str, Path]] = None)

Bases: object

Common logic for both .xls and .xlsx files management.

property book

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: Any, name: Optional[str] = None, header: bool = False, start: Optional[int] = None, formatting_as_empty: Optional[bool] = False) → List[dict]

Append values to the end of the worksheet.

Parameters

• content – Rows of values to append

• name – Name of worksheet to append to (optional). Defaults to the active worksheet.

• header – Set rows according to existing header row

• start – Start of data, NOTE: Only required when header is True

• formatting_as_empty – if True, the cells containing only formatting (no values) are considered empty.

Returns

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

auto_size_columns(start_column: Union[int, str], end_column: Optional[Union[int, str]] = None, width: Optional[int] = None)

Auto size column widths.

Note. non-default font sizes might cause auto sizing issues

Parameters

• start_column – column number or name to start from

• end_column – optional column number or name for last column

• width – if given will resize columns to this size, otherwise will auto_size

Examples:

Robot Framework example.

Auto Size Columns   A   D    # will try auto size
Auto Size Columns   B   D   16  # will set A-D columns sizes to 16
Auto Size Columns   A   width=24  # will set column A size to 24

Python example.

lib.auto_size_columns("A", "D")
lib.auto_size_columns("C", width=40)

clear_cell_range(range_string: str)

Clear cell values for a given range.

Parameters

range_string – single cell or range of cells

Examples:

Robot Framework example.

# area of cells
Clear Cell Range    A9:A100
# single cell
Clear Cell Range    A2

Python example.

lib.clear_cell_range("A1")
lib.clear_cell_range("B2:B50")

close_workbook() → None

Close the active workbook.

Examples:

# Close active workbook
Close Workbook

# Close active workbook
lib.close_workbook()

copy_cell_values(source_range: str, target: str)

Copy cells from source to target.

Parameters

• source_range – single cell or range of cells

• target – copy to this cell

Examples:

Robot Framework example.

Copy Cell Values   A1:D4   G10

Python example.

lib.copy_cell_values("A1:D4", "G10")

create_workbook(path: Optional[str] = None, fmt: str = 'xlsx', sheet_name: Optional[str] = None) → Union[XlsWorkbook, XlsxWorkbook]

Create and open a new Excel workbook.

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.

Parameters

• path – Save path for workbook; defaults to robot root if not provided.

• fmt – Format of workbook, i.e. xlsx or xls; Defaults to xlsx if not provided.

• sheet_name – Custom name for the initial sheet.

Returns

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(name: str, content: Optional[Any] = None, exist_ok: Optional[bool] = False, header: Optional[bool] = False) → None

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:

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

delete_columns(start: Union[int, str], end: Optional[Union[int, str]] = None)

Delete column or columns beginning from start column number/name to possible end column number/name.

Parameters

• start – column number or name to start deletion from

• end – optional column number or name for last column to delete

Examples:

Robot Framework example.

Delete Columns   C       # delete column C
Delete Columns   3       # delete column 3 (same as C)
Delete Columns   E  AA   # delete rows E-AA

Python example.

lib.delete_columns("D")
lib.delete_rows(1, "JJ")

delete_rows(start: int, end: Optional[int] = None)

Delete row or rows beginning from start row number to possible end row number.

Parameters

• start – row number to start deletion from

• end – optional row number for last row to delete

Examples:

Robot Framework example.

Delete Rows   2       # delete row 2
Delete Rows   5  10   # delete rows 5-10

Python example.

lib.delete_rows(2)
lib.delete_rows(5,10)

find_empty_row(name: Optional[str] = None) → int

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

Parameters

name – Name of worksheet (optional). Defaults to the active worksheet.

Returns

First row number of empty row

Examples:

${next}=    Find empty row

next = lib.find_empty_row()

get_active_worksheet() → str

Get the name of the worksheet which is currently active.

Returns

Active worksheet name

Examples:

${Active_Worksheet}=    Get Active Worksheet

Active_Worksheet = lib.get_active_worksheet()

get_cell_value(row: int, column: Union[str, int], name: Optional[str] = None) → Any

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). Defaults to active worksheet.

Returns

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(row: int, column: Union[str, int], name: Optional[str] = None) → Any

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

hide_columns(start_column: Union[int, str], end_column: Optional[Union[int, str]] = None)

Hide column or columns in worksheet.

Parameters

• start_column – column number or name to start from

• end_column – optional column number or name for last column

Examples:

Robot Framework example.

Hide Columns   A   D    # hide columns A-D
Hide Columns   A        # hide column A

Python example.

lib.hide_columns("A", "D")
lib.hide_columns("A")

insert_columns_after(column: Union[int, str], amount: int = 1)

Insert column or columns after a column number/name.

Parameters

• column – insert after this column

• amount – number of columns to insert, default 1

Examples:

Robot Framework example.

Insert Columns After   C      # insert 1 column after column C
Insert Columns Before   A  3   # insert 3 columns after column A

Python example.

lib.insert_columns_after("C")
lib.insert_columns_after("A", 3)

insert_columns_before(column: Union[int, str], amount: int = 1)

Insert column or columns before a column number/name.

Parameters

• column – insert before this column

• amount – number of columns to insert, default 1

Examples:

Robot Framework example.

Insert Columns Before   C      # insert 1 column before column C
Insert Columns Before   A  3   # insert 3 columns before column A

Python example.

lib.insert_columns_before("C")
lib.insert_columns_before("A", 3)

insert_image_to_worksheet(row: int, column: Union[int, str], path: str, scale: float = 1.0, name: Optional[str] = None) → 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 (optional). Default value is “1.0”.

• 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")

insert_rows_after(row: int, amount: int = 1)

Insert row or rows after a row number.

Parameters

• row – insert after this row

• amount – number of rows to insert, default 1

Examples:

Robot Framework example.

Insert Rows After   3      # insert 1 row after row 3
Insert Rows After   1  3   # insert 3 rows after row 1

Python example.

lib.insert_rows_after(1)
lib.insert_rows_after(1, 3)

insert_rows_before(row: int, amount: int = 1)

Insert row or rows before a row number.

Parameters

• row – insert before this row

• amount – number of rows to insert, default 1

Examples:

Robot Framework example.

Insert Rows Before   3      # insert 1 row before row 3
Insert Rows Before   1  3   # insert 3 rows before row 1

Python example.

lib.insert_rows_before(1)
lib.insert_rows_before(1, 3)

list_worksheets() → List[str]

List all names of worksheets in the given workbook.

Returns

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

move_range(range_string: str, rows: int = 0, columns: int = 0, translate: bool = True)

Move range of cells by given amount of rows and columns.

Formulas are translated to match new location by default.

Note. There is a bug in the openpyxl on moving negative rows/columns.

Parameters

• range_string – cell range

• rows – number of rows to move

• columns – number of columns to move

• translate – are formulas translated for a new location

Examples:

Robot Framework example.

# move range 4 rows down
Move Range   E2:E10    rows=4
# move range 2 rows down, 2 columns right
Move Range   E2:E10    rows=2  columns=2

Python example.

lib.move_range("E2:E10", rows=4)
lib.move_range("E2:E10", rows=2, columns=2)

open_workbook(path: str, data_only: Optional[bool] = False, read_only: Optional[bool] = False) → Union[XlsWorkbook, XlsxWorkbook]

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

Parameters

• path – path to Excel file

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

Returns

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(name: Optional[str] = None, header: Optional[bool] = False, start: Optional[int] = None) → List[dict]

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 (optional). Defaults to the active worksheet.

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

• start – Row index to start reading data from (1-indexed). Default value is row 1.

Returns

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(name: Optional[str] = None, header: bool = False, trim: bool = True, start: Optional[int] = None) → Table

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

Parameters

• name – Name of worksheet to read (optional). Defaults to the active worksheet.

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

• trim – Remove all empty rows from the end of the worksheet. Default value is True.

• start – Row index to start reading data from (1-indexed). Default value is row 1.

Returns

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(name: Optional[str] = None) → None

Remove a worksheet from the active workbook.

Parameters

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(src_name: str, dst_name: str) → None

Rename an existing worksheet in the active workbook.

Parameters

• src_name – Current name of worksheet

• dst_name – Future name of worksheet

Examples:

Rename worksheet    Sheet    Orders

lib.rename_worksheet("Sheet","Orders")

save_workbook(path: Optional[str] = None) → Union[XlsWorkbook, XlsxWorkbook]

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.

Returns

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(value: Union[str, int]) → None

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:

# 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(row: int, column: Union[str, int], fmt: Union[str, float], name: Optional[str] = None) → None

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

Parameters

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

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

• fmt – Format code for cell

• 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_formula(range_string: str, formula: str, transpose: bool = False)

Set cell formula for given range of cells.

If transpose is set then formula is set for first cell of the range and the rest of cells will transpose the function to match to that cell.

Otherwise (by default) all cells will get the same formula.

Parameters

• range_string – cell range

• formula – formula for the cell

• transpose – on True the cell formulas will be transposed

Examples:

Robot Framework example.

# all cells will have same formula
Set Cell Formula   E2:E10    =B2+5
# cells will have transposed formulas
# E2 will have =B2+5
# E3 will have =B3+5
# etc
Set Cell Formula   E2:E10    =B2+5   True

Python example.

lib.set_cell_formula("E2:E10", "=B2+5")
lib.set_cell_formula("E2:E10", "=B2+5", True)

set_cell_value(row: int, column: Union[str, int], value: Any, name: Optional[str] = None, fmt: Optional[Union[str, float]] = None) → 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). Defaults to active worksheet.

• 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_cell_values(start_cell: str, values: Union[list, Table], table_heading: bool = False)

Set cell values given as list of lists or as a RPA.Tables.Table.

Note. Will overwrite cells if table structure causes cells to overlap.

Parameters

• start_cell – starting cell in a string

• values – list of lists or a Table

• table_heading – if values are given as a Table, this parameter defines if Table headings should be inserted as a row

Examples:

Robot Framework example.

@{all_rows}=    Create List
${headers}=    Create List    first    second   third  fourth
FOR    ${num}    IN RANGE    1    2000
    @{row}=    Create List    ${num}    ${num+1}    ${num*2}    ${num*4}
    Append To List    ${all_rows}    ${row}
END
#  Set Cell Values from Table (include headers)
${table}=    Create Table    ${all_rows}    columns=${headers}
Set Cell Values   G1   ${table}   True
#  Set Cell Values from a list of lists
# uncomment if headings should be added
# Append To List  ${all_rows}   ${headers}
Set Cell Values   M1   ${all_rows}

# Simplest form of adding values
@{values}=    Evaluate    [[1,2,3],[4,5,6],['a','b','c','d']]
Set Cell Values   A1   ${values}

Python example.

data =  [[1,2,3],[4,5,6],['a','b','c','d']]
lib.set_cell_values("E2", data)

set_styles(range_string: str, font_name: Optional[str] = None, family: Optional[str] = None, size: Optional[int] = None, bold: bool = False, italic: bool = False, underline: bool = False, strikethrough: bool = False, cell_fill: Optional[str] = None, color: Optional[str] = None, align_horizontal: Optional[str] = None, align_vertical: Optional[str] = None, number_format: Optional[str] = None)

Set styles for range of cells.

Possible values for the align_horizontal:

• general

• left

• center

• right

• fill

• justify

• centerContinuous

• distributed

Possible values for the align_vertical:

• top

• center

• bottom

• justify

• distributed

Some examples for number_formats:

• General

• 0

• 0.00

• #,##0

• #,##0.00

• “$”#,##0_);(“$”#,##0)

• “$”#,##0_);[Red](“$”#,##0)

• 0%

• 0.00%

• 0.00E+00

• # ?/?

• # ??/??

• mm-dd-yy

• d-mmm-yy

• d-mmm

• h:mm AM/PM

• h:mm:ss AM/PM

• h:mm

• h:mm:ss

• m/d/yy h:mm

Parameters

• range_string – single cell or range of cells

• font_name – name of the font

• family – font family name

• size – size for the font

• bold – font style bold

• italic – font style italics

• underline – font style underline

• strikethrough – font style strikethrough

• cell_fill – cell fill color, in hex or color name

• color – font color, in hex or color name

• align_horizontal – cell horizontal alignment

• align_vertical – cell vertical alignment

• number_format – cell number format

Examples:

Robot Framework example.

Set Styles    A1:D4
...  bold=True
...  cell_fill=lightblue
...  align_horizontal=center
...  number_format=h:mm AM/PM

Set Styles    E2
...  strikethrough=True
...  color=FF0000

Python example.

lib.set_styles("A1:D4", bold=True, font_name="Arial", size=24)

set_worksheet_value(row: int, column: Union[str, int], value: Any, name: Optional[str] = None, fmt: Optional[Union[str, float]] = None) → Any

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

unhide_columns(start_column: Union[int, str], end_column: Optional[Union[int, str]] = None)

Unhide column or columns in worksheet.

Parameters

• start_column – column number or name to start from

• end_column – optional column number or name for last column

Examples:

Robot Framework example.

Unhide Columns   A   D    # unhide columns A-D
Unhide Columns   A        # unhide column A

Python example.

lib.unhide_columns("A", "D")
lib.unhide_columns("A")

worksheet_exists(name: str) → bool

Return True if worksheet with given name is in workbook.

Parameters

name – Name of worksheet you are looking for

Returns

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")

class RPA.Excel.Files.XlsWorkbook(*args, **kwargs)

Bases: BaseWorkbook

Container for manipulating legacy Excel files (.xls)

property active

append_worksheet(name=None, content=None, header=False, start=None, formatting_as_empty=False)

property book

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)

static is_sheet_empty(sheet)

open(path=None, read_only=False, write_only=False, data_only=False)

read_worksheet(name=None, header=False, start=None) → List[dict]

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

validate_content()

class RPA.Excel.Files.XlsxWorkbook(*args, **kwargs)

Bases: BaseWorkbook

Container for manipulating modern Excel files (.xlsx)

property active

append_worksheet(name=None, content=None, header=False, start=None, formatting_as_empty=False)

property book

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)

static is_sheet_empty(sheet)

open(path=None, read_only=False, write_only=False, data_only=False)

property read_only

read_worksheet(name=None, header=False, start=None) → List[dict]

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

validate_content()

RPA.Excel.Files.ensure_unique(values: Any) → List[Any]

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: str) → int

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