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 (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
The
content
argument can be of any tabular format. Typically, this is a Table object created by theRPA.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.
# Close active workbook Close Workbook
# Close active workbook lib.close_workbook()
-
create_workbook
(path=None, fmt='xlsx')¶ Create and open a new Excel workbook.
Automatically also creates a new worksheet with the name “Sheet”.
Note: Must be paired with the
Save Workbook
keyword or the newly created workbook will be deleted upon Bot completion.Note: The filename must be set in either the
Create Workbook
keyword or theSave 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
Examples:
# Create modern format workbook Create workbook 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_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:
# 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
-
find_empty_row
(name=None)¶ Find the first empty row after existing content, and return the row number.
- Parameters
name – Name of worksheet (optional). Defaults to the active worksheet.
Examples:
${next}= Find empty row
next = lib.find_empty_row()
-
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). Defaults to active worksheet.
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, 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 value1.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")
-
list_worksheets
()¶ List all names of worksheets in the given workbook.
-
open_workbook
(path: str, data_only: bool = False)¶ 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.
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=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 (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.
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=None, header=False, trim=True, start=None)¶ 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.
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=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, dst_name)¶ 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=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.
# 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
-
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:
# 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, column, fmt, name=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_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). 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_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.
- Parameters
name – Name of worksheet you are looking for
-
-
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)¶
-
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)¶
-
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
¶
-
property
-
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)¶
-
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)¶
-
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
¶
-
property
-
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()