RPA.Excel.Files
Append values to the end of the worksheet.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
content | Any | null | Rows of values to append |
name | str, None | None | Name of worksheet to append to (optional). Defaults to the active worksheet. |
header | bool | False | Set rows according to existing header row |
start | int, None | None | Start of data, NOTE: Only required when header is True |
formatting_as_empty | bool, None | False | if True, the cells containing only formatting (no values) are considered empty. |
param content: | Rows of values to append |
---|---|
param name: | Name of worksheet to append to (optional). Defaults to the active worksheet. |
param header: | Set rows according to existing header row |
param start: | Start of data, NOTE: Only required when header is True |
param formatting_as_empty: | |
if True, the cells containing only formatting (no values) are considered empty. | |
return: | 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:
Auto size column widths.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
start_column | int, str | null | column number or name to start from |
end_column | int, str, None | None | optional column number or name for last column |
width | int, None | None | if given will resize columns to this size, otherwise will auto_size |
Note. non-default font sizes might cause auto sizing issues
param start_column: | |
---|---|
column number or name to start from | |
param end_column: | |
optional column number or name for last column | |
param width: | if given will resize columns to this size, otherwise will auto_size |
Examples:
Robot Framework example.
Python example.
Clear cell values for a given range.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
range_string | str | null | single cell or range of cells |
param range_string: | |
---|---|
single cell or range of cells |
Examples:
Robot Framework example.
Python example.
Close the active workbook.
Examples:
Copy cells from source to target.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
source_range | str | null | single cell or range of cells |
target | str | null | copy to this cell |
param source_range: | |
---|---|
single cell or range of cells | |
param target: | copy to this cell |
Examples:
Robot Framework example.
Python example.
Create and open a new Excel workbook.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
path | str, None | None | Save path for workbook; defaults to robot root if not provided. |
fmt | str | xlsx | Format of workbook, i.e. xlsx or xls; Defaults to xlsx if not provided. |
sheet_name | str, None | None | Custom name for the initial sheet. |
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.
param path: | Save path for workbook; defaults to robot root if not provided. |
---|---|
param fmt: | Format of workbook, i.e. xlsx or xls; Defaults to xlsx if not provided. |
param sheet_name: | |
Custom name for the initial sheet. | |
return: | Workbook object. |
Examples:
Create a new worksheet in the current workbook.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
name | str | null | Name of new worksheet |
content | Any, None | None | Optional content for worksheet |
exist_ok | bool, None | False | If False, raise an error if name is already in use |
header | bool, None | False | If content is provided, write headers to worksheet |
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:
Delete column or columns beginning from start column number/name to possible end column number/name.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
start | int, str | null | column number or name to start deletion from |
end | int, str, None | None | optional column number or name for last column to delete |
param start: | column number or name to start deletion from |
---|---|
param end: | optional column number or name for last column to delete |
Examples:
Robot Framework example.
Python example.
Delete row or rows beginning from start row number to possible end row number.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
start | int | null | row number to start deletion from |
end | int, None | None | optional row number for last row to delete |
param start: | row number to start deletion from |
---|---|
param end: | optional row number for last row to delete |
Examples:
Robot Framework example.
Python example.
Find the first empty row after existing content, and return the row number.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
name | str, None | None | Name of worksheet (optional). Defaults to the active worksheet. |
param name: | Name of worksheet (optional). Defaults to the active worksheet. |
---|---|
return: | First row number of empty row |
Examples:
Get the name of the worksheet which is currently active.
return: | Active worksheet name |
---|
Examples:
Get a cell value in the given worksheet.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
row | int | null | Index of row to read, e.g. 3 |
column | str, int | null | Name or index of column, e.g. C or 7 |
name | str, None | None | Name of worksheet (optional). Defaults to active worksheet. |
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). Defaults to active worksheet. |
return: | Cell value |
Examples:
Alias for keyword Get cell value, see the original keyword for documentation.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
row | int | null | |
column | str, int | null | |
name | str, None | None |
Hide column or columns in worksheet.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
start_column | int, str | null | column number or name to start from |
end_column | int, str, None | None | optional column number or name for last column |
param start_column: | |
---|---|
column number or name to start from | |
param end_column: | |
optional column number or name for last column |
Examples:
Robot Framework example.
Python example.
Insert column or columns after a column number/name.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
column | int, str | null | insert after this column |
amount | int | 1 | number of columns to insert, default 1 |
param column: | insert after this column |
---|---|
param amount: | number of columns to insert, default 1 |
Examples:
Robot Framework example.
Python example.
Insert column or columns before a column number/name.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
column | int, str | null | insert before this column |
amount | int | 1 | number of columns to insert, default 1 |
param column: | insert before this column |
---|---|
param amount: | number of columns to insert, default 1 |
Examples:
Robot Framework example.
Python example.
Insert an image into the given cell.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
row | int | null | Index of row to write |
column | int, str | null | Name or index of column |
path | str | null | Path to image file |
scale | float | 1.0 | Scale of image (optional). Default value is "1.0". |
name | str, None | None | Name of worksheet (optional). Defaults to the active worksheet. |
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 (optional). Default value is "1.0". |
param name: | Name of worksheet (optional). Defaults to the active worksheet. |
Examples:
Insert row or rows after a row number.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
row | int | null | insert after this row |
amount | int | 1 | number of rows to insert, default 1 |
param row: | insert after this row |
---|---|
param amount: | number of rows to insert, default 1 |
Examples:
Robot Framework example.
Python example.
Insert row or rows before a row number.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
row | int | null | insert before this row |
amount | int | 1 | number of rows to insert, default 1 |
param row: | insert before this row |
---|---|
param amount: | number of rows to insert, default 1 |
Examples:
Robot Framework example.
Python example.
List all names of worksheets in the given workbook.
return: | List containing the names of the worksheets |
---|
Examples:
Move range of cells by given amount of rows and columns.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
range_string | str | null | cell range |
rows | int | 0 | number of rows to move |
columns | int | 0 | number of columns to move |
translate | bool | True | are formulas translated for a new location |
Formulas are translated to match new location by default.
Note. There is a bug in the openpyxl on moving negative rows/columns.
param range_string: | |
---|---|
cell range | |
param rows: | number of rows to move |
param columns: | number of columns to move |
param translate: | |
are formulas translated for a new location |
Examples:
Robot Framework example.
Python example.
Open an existing Excel workbook.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
path | str | null | path to Excel file |
data_only | bool, None | False | 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. |
read_only | bool, None | False |
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.
param path: | path to Excel file |
---|---|
param 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. | |
return: | Workbook object |
Examples:
Read the content of a worksheet into a list of dictionaries.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
name | str, None | None | Name of worksheet to read (optional). Defaults to the active worksheet. |
header | bool, None | False | If True, use the first row of the worksheet as headers for the rest of the rows. Default is False. |
start | int, None | None | Row index to start reading data from (1-indexed). Default value is row 1. |
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 (optional). Defaults to the active worksheet. |
---|---|
param header: | If True, use the first row of the worksheet as headers for the rest of the rows. Default is False. |
param start: | Row index to start reading data from (1-indexed). Default value is row 1. |
return: | List of dictionaries that represents the worksheet |
Examples:
Read the contents of a worksheet into a Table container. Allows sorting/filtering/manipulating using the RPA.Tables library.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
name | str, None | None | Name of worksheet to read (optional). Defaults to the active worksheet. |
header | bool | False | If True, use the first row of the worksheet as headers for the rest of the rows. Default value is False. |
trim | bool | True | Remove all empty rows from the end of the worksheet. Default value is True. |
start | int, None | None | Row index to start reading data from (1-indexed). Default value is row 1. |
param name: | Name of worksheet to read (optional). Defaults to the active worksheet. |
---|---|
param header: | If True, use the first row of the worksheet as headers for the rest of the rows. Default value is False. |
param trim: | Remove all empty rows from the end of the worksheet. Default value is True. |
param start: | Row index to start reading data from (1-indexed). Default value is row 1. |
return: | Table object that represents the worksheet |
Examples:
Remove a worksheet from the active workbook.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
name | str, None | None | Name of worksheet to remove (optional). Defaults to the active worksheet. |
param name: | Name of worksheet to remove (optional). Defaults to the active worksheet. |
---|
Examples:
Rename an existing worksheet in the active workbook.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
src_name | str | null | Current name of worksheet |
dst_name | str | null | Future name of worksheet |
param src_name: | Current name of worksheet |
---|---|
param dst_name: | Future name of worksheet |
Examples:
Save the active workbook.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
path | str, None | None | Path to save to. If not given, uses path given when opened or created. |
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. |
---|---|
return: | Workbook object |
Examples:
Set the active worksheet.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
value | str, int | null | Index or name of 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.
param value: | Index or name of worksheet |
---|
Examples:
Set format for cell.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
row | int | null | Index of row to write, e.g. 3 |
column | str, int | null | Name or index of column, e.g. C or 7 |
fmt | str, float | null | Format code for cell |
name | str, None | None | Name of worksheet (optional). Defaults to active worksheet. |
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.
param row: | Index of row to write, e.g. 3 |
---|---|
param column: | Name or index of column, e.g. C or 7 |
param fmt: | Format code for cell |
param 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 cell formula for given range of cells.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
range_string | str | null | cell range |
formula | str | null | formula for the cell |
transpose | bool | False | on True the cell formulas will be transposed |
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.
param range_string: | |
---|---|
cell range | |
param formula: | formula for the cell |
param transpose: | |
on True the cell formulas will be transposed |
Examples:
Robot Framework example.
Python example.
Set a cell value in the given worksheet.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
row | int | null | Index of row to write, e.g. 3 |
column | str, int | null | Name or index of column, e.g. C or 7 |
value | Any | null | New value of cell |
name | str, None | None | Name of worksheet (optional). Defaults to active worksheet. |
fmt | str, float, None | None | Format code for cell (optional) |
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). Defaults to active worksheet. |
param fmt: | Format code for cell (optional) |
Examples:
Set cell values given as list of lists or as a RPA.Tables.Table.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
start_cell | str | null | starting cell in a string |
values | list, Table | null | list of lists or a Table |
table_heading | bool | False | if values are given as a Table, this parameter defines if Table headings should be inserted as a row |
Note. Will overwrite cells if table structure causes cells to overlap.
param start_cell: | |
---|---|
starting cell in a string | |
param values: | list of lists or a Table |
param 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.
Python example.
Set styles for range of cells.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
range_string | str | null | single cell or range of cells |
font_name | str, None | None | name of the font |
family | str, None | None | font family name |
size | int, None | None | size for the font |
bold | bool | False | font style bold |
italic | bool | False | font style italics |
underline | bool | False | font style underline |
strikethrough | bool | False | font style strikethrough |
cell_fill | str, None | None | cell fill color, in hex or color name |
color | str, None | None | font color, in hex or color name |
align_horizontal | str, None | None | cell horizontal alignment |
align_vertical | str, None | None | cell vertical alignment |
number_format | str, None | None | cell number format |
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
param range_string: | |
---|---|
single cell or range of cells | |
param font_name: | |
name of the font | |
param family: | font family name |
param size: | size for the font |
param bold: | font style bold |
param italic: | font style italics |
param underline: | |
font style underline | |
param strikethrough: | |
font style strikethrough | |
param cell_fill: | |
cell fill color, in hex or color name | |
param color: | font color, in hex or color name |
param align_horizontal: | |
cell horizontal alignment | |
param align_vertical: | |
cell vertical alignment | |
param number_format: | |
cell number format |
Examples:
Robot Framework example.
Python example.
Alias for keyword Set cell value, see the original keyword for documentation.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
row | int | null | |
column | str, int | null | |
value | Any | null | |
name | str, None | None | |
fmt | str, float, None | None |
Unhide column or columns in worksheet.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
start_column | int, str | null | column number or name to start from |
end_column | int, str, None | None | optional column number or name for last column |
param start_column: | |
---|---|
column number or name to start from | |
param end_column: | |
optional column number or name for last column |
Examples:
Robot Framework example.
Python example.
Return True if worksheet with given name is in workbook.
Arguments
Argument | Type | Default value | Description |
---|---|---|---|
name | str | null | Name of worksheet you are looking for |
param name: | Name of worksheet you are looking for |
---|---|
return: | True if the worksheet exists, False otherwise |
Examples: