Sheet

read(range, only_visible=False, skip=0, max=1000, read_value_as_str=True)

Method

Read values from Excel

Parameters

range<str> Use A for a column, 1 for a row, A1 for a cell, and A1:B2 for a range.

read_value_as_str<bool> Controls how cell values are read. If set to True, all values are returned as strings. If False, the original data types are preserved, with numbers returned as floats and other values as strings or None. For example, if a cell in Excel contains the number 1, this method returns 1.0.

only_visible<bool> Reads only visible cells.

skip<int>The number of rows to skip from the start of the read operation.

max<int> The maximum number of rows to read.

Code sample: rpa.app.microsoft.excel.Sheet.read

# Prerequisite: Ensure Microsoft Office is installed.
# Example:
excel_file_path = r"D:\Test_File_Archive\TestExcel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

cell_value = sheet.read('A1')
row_value = sheet.read('1')
column_value = sheet.read('A')
range_value = sheet.read('A1:C3')

excel.close()

write(range, value, start_row=1, start_col='A', max=1000)

Description

Write data to Excel

Parameters

range<str>Specifies the column, row, cell, or range to write to. For a range, specify only the start cell. For example: A for a column, 1 for a row, and A1 for a cell.

value<list>Pass a one-dimensional array for a column or row, a str, int, or float for a cell, and a two-dimensional array for a range.

start_row<integer>The starting row for the write operation. This parameter is only used when writing to a column.

start_col<str>The starting column when writing to a row. Accepts a 1-based index (e.g., 1) or a letter (e.g., A or a).

max<int>The maximum number of items to write to a column, row, or range.

Example: rpa.app.microsoft.excel.Sheet.write

# Prerequisite: Ensure Microsoft Office is installed.
# Example:
excel_file_path = r"D:\Test_File_Archive\TestExcel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

row_value = ["Row Data 1", "RPA Test"]
cell_value = "RPA Cell Value"
column_value = ["Column Data 1", "RPA Test"]
range_value = [["Range Data 1", "Range Data 2"], ["Range Data 3", "Range Data 4"]]

sheet.write('1' , row_value )
sheet.write('C' , column_value )
sheet.write('A2' , cell_value )
sheet.write('A4' , range_value )

excel.close()

copy(range)

Description

Copy the data from the range.

Parameters

range<str>Specify 'A' for a column, '1' for a row, 'A1' for a cell, or 'A1:B2' for a range of cells.

Code sample: rpa.app.microsoft.excel.Sheet.copy

# Prerequisite: Microsoft Office must be installed.
# Example:
excel_file_path = r"D:\2_测试文件归档\测试Excel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

sheet.copy('1' )
sheet.copy('C' )
sheet.copy('A2' )
sheet.copy('A4:B5' )

paste(range, paste_type='value', retry=3)

Description

Paste data into the range.

Parameter description

range<str> The paste destination can be a column, a row, or the start cell. Use 'A' for a column, '1' for a row, and 'A1' for a cell.

paste_type<str> Specifies the data type.

Optional:

• all: all

• formula: formula

• value: value

• format: format

• annotation: annotation

• verification: validation

• sourceUnit: Cells using the source theme

• exceptBorder: All content except borders

• colWidth: Column width

• FormulasNumber: Formulas and number formats

• valueNumber: Values and number formats

• mergeCondition: Merged conditional formats

retry<int> The maximum number of retries.

Sample call: rpa.app.microsoft.excel.Sheet.paste

# Prerequisite: Microsoft Office must be installed.
# Example:
excel_file_path = r"D:\2_test_file_archive\test_excel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

sheet.copy('1' )
sheet.paste('2')

sheet.copy('C' )
sheet.paste('D')

sheet.copy('A2' )
sheet.paste('B2')

sheet.copy('A4:B5' )
sheet.paste('C4' )

row_count()

Description

Returns the row count of the specified sheet.

Return value

Number of rows<str>

Example: rpa.app.microsoft.excel.Sheet.row_count

# Prerequisite: Microsoft Office must be installed.
# Returns an integer.
# Example:
excel_file_path = r"D:\2_test_file_archive\test_excel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

count = sheet.row_count()

col_count()

Description

Returns the column count for the specified sheet.

Return value

Returns the number of columns<str>

Code sample: rpa.app.microsoft.excel.Sheet.col_count

# Requires Microsoft Office.
# Returns a string.
# Code sample:
excel_file_path = r"D:\Test_File_Archive\TestExcel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

count = sheet.col_count()

calculate_sum_of_col(col, start_row=1, condition='<>')

Description

Calculate the column sum.

Parameters

col<str>Column number.

start_row<int>The starting row number.

condition<str>Determines which cells to sum. The condition can be a number, expression, or text. For example, use <> to sum only numeric values or >32 to sum values greater than 32.

Return value

Returns the column total. (float)

Example - rpa.app.microsoft.excel.Sheet.calculate_sum_of_col

# Prerequisite: Ensure Microsoft Office is installed.
# Code example:
excel_file_path = r"D:\2_test_file_archive\test_excel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

col_sum = sheet.calculate_sum_of_col('A', start_row=1)
print(col_sum)

calculate_sum_of_range (range, condition='<>')

Description

Calculates the sum for the region.

Parameters

range<str>A range of cells, such as 'A1:B2'.

condition<str>The condition that determines which cells to sum. The condition can be a number, an expression, or text. For example, use <> to sum only numbers or >32 to sum numbers greater than 32.

Return value

Returns the sum of the column (<float>).

Example: rpa.app.microsoft.excel.Sheet.calculate_sum_of_range

# Requires Microsoft Office.
# Example:
excel_file_path = r"D:\Test_File_Archive\TestExcel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

range_sum = sheet.calculate_sum_of_range('A1:C5')
print(range_sum)

set_region_font_pattern_in_sheet (range, font_pattern='general')

Description

Set the font for a selection.

Parameters

range<str>The cell range, such as 'A1:B2'.

font pattern<str>Specifies the font pattern for the area.

Options:

• general: general

• bold: bold

• italic: italic

• boldAndItalic: bold and italic

Code example: rpa.app.microsoft.excel.Sheet.set_region_font_pattern_in_sheet

# Prerequisite: Microsoft Office must be installed.
# Example:
excel_file_path = r"D:\Test_File_Archive\TestExcel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()
# Set the font in the A1:B2 region to bold.
sheet.set_region_font_pattern_in_sheet('A1:B2',font_pattern='bold')

set_region_border(range, border_type, color='000000')

Description

Style the specified range.

Parameters

range<str>The cell range, such as A1:B2.

border_type (string): The border style.

Options:

• no_border: No border (default)

• all_borders: All borders

• bottom_border: Bottom border

• top_border: Top border

• left_border: Left border

• right_border: Right border

• outline_borders: Outline borders

• thick_outline_borders: Thick outline borders

• double_bottom_borders: Double bottom border

• thick_bottom_borders: Thick bottom border

• upper_and_lower_borders: Top and bottom borders

• top_and_thick_bottom_borders: Top and thick bottom borders

• top_and_double_bottom_borders: Top and double bottom borders

color<str>The hex value. Default: '#000000'.

Example: rpa.app.microsoft.excel.Sheet.set_region_border

# Note: Microsoft Office must be installed.
# Code sample:
excel_file_path = r"D:\Test_File_Archive\TestExcel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()
# Set all borders for the A1:B2 region to black.
sheet.set_region_border('A1:B2','all_borders',color='000000')

hide_region(hide_region_type='row', start_row=None, end_row=None, start_col=None, end_col=None)

Description

Hidden area

Parameters

hide_region_type<str> The type of region to hide.

Options:

• row: Hides one or more rows.

• col: Hides one or more columns.

start_row<str>Specifies the starting row number for the hidden area. This parameter applies only when the hidden area type is row. For example, '1'.

end_row<str>The ending row number of the hidden region. This parameter is only effective when hide_region_type is set to row. Example: '3'.

start_col<str>The starting column of the region, valid only when hide_region_type is set to col. For example, 'A' or 'a'.

end_col<str>The end column letter of the area. This applies only when the hidden area type is col. For example, 'D' or 'd'.

Code example: rpa.app.microsoft.excel.Sheet.hide_region

show_region(show_region_type='row', start_row=None, end_row=None, start_col=None, end_col=None)

Description

Display region

Parameters

show_region_type<str> The region type to display.

Options:

• row: Displays one or more rows.

• column: Displays one or more columns.

start_row<str>The starting row number of the region. This parameter is valid only when the region type is row. Example: '1'.

end_row<str>The end row number. This parameter applies only when the region type is row. For example, '3'.

start_col<str>The starting column of the region, for example, 'A' or 'a'. This parameter is valid only when the region type is 'col'.

end_col<str>Specifies the end column of the region, effective only when the region type is col. For example: 'D' or 'd'.

Example: rpa.app.microsoft.excel.Sheet.show_region

# Note: Ensure Microsoft Office is installed.
# Code example:
excel_file_path = r"D:\2_test_file_archive\test_excel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

sheet.show_region(show_region_type='row', start_row='1', end_row='3', start_col=None, end_col=None)
sheet.show_region(show_region_type='col', start_row=None, end_row=None, start_col="b", end_col="c")

sort(sort_fields, range=None, match_case=False, sort_method='pinyin', contains_header=False)

Description

Sort

Parameters

sort_fields<list>An array of sort fields in the format [(<column name>, <sort type>, <sort order>)].

• Excel column name, such as A, B, and AA.

• Sort type:

  • value: Sort by cell value.

  • cell_color: Sort by cell color.

  • font_color: Sort by font color.

• Sort order:

  • asc: Ascending order.

  • desc: Descending order.

range<str>The search range in A1 notation, such as 'A1:C5'. Defaults to the entire sheet if omitted.

match_case<bool>Specifies whether the match is case-sensitive.

• false (default): Performs a case-insensitive sort.

• true: Performs a case-sensitive sort.

sort_method<str>The sorting method.

Options:

• pinyin: Sorts characters by pinyin. (Default)

• stroke: Sorts characters by stroke count.

contains_header<bool>Indicates whether the data has a header row.

• false (default): Sorts the entire range.

• true: Does not sort the entire range.

Sample code: rpa.app.microsoft.excel.Sheet.sort

# Note: Ensure Microsoft Office is installed.
# Code example:
excel_file_path = r"D:\Test_File_Archive\TestExcel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()
# Sort column A by value in descending order.
sort_fields1 = [('A','value','desc')]
sheet.sort(sort_fields1)
# Sort column B by cell color in ascending order.
sort_fields2 = [('B','cell_color','asc')]
sheet.sort(sort_fields2)
# Sort column C by font color in ascending order.
sort_fields3 = [('C','font_color','asc')]
sheet.sort(sort_fields3)

filter(col, array, delete=False)

Description

Filter a column.

Parameters

column number<str>The column number.

array<list>An array of filter values.

delete<bool>Determines whether to delete the filtered data. This parameter is deprecated and has no effect in version 3.4.3 and later.

Code sample: rpa.app.microsoft.excel.Sheet.filter

# Prerequisite: Ensure Microsoft Office is installed.
# Code sample:
excel_file_path = r"D:\Test_File_Archive\TestExcel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()
# Basic filter
filter_value = ['rpa_test1','rpa_test2']
# Advanced filter with wildcards: '?' matches a single character, and '*' matches zero or more characters.
filter_value = ['rpa_test*']
# Filter for blank cells
filter_value = ['=']

sheet.filter('A', filter_value)

remove_filter()

Description

Clear filter

Example: rpa.app.microsoft.excel.Sheet.remove_filter

# Prerequisite: Ensure Microsoft Office is installed.
# Example usage:
excel_file_path = r"D:\2_test_file_archive\test_excel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()
filter_value = ['rpa_test1','rpa_test2']

sheet.filter('A', filter_value)
sheet.remove_filter()

multi_filter(filters_val, range=None, delete=False)

Description

Filters multiple columns.

Parameters

filters_val<dict> Use the following format: {'A': ['testA1', 'testA2', 'testA3'], 'B': ['testB1', 'testB2'], ...}

range<str>The cell range to filter, in A1 notation (e.g., 'A1:C5'). If omitted, defaults to the entire sheet.

delete<bool>Whether to delete the filtered data. Ignored in version 3.4.3 or later.

Code sample: rpa.app.microsoft.excel.Sheet.multi_filter

# Prerequisite: Ensure Microsoft Office is installed.
# Filters rows where column A is 'test01' or 'test02' and column B is '1' or '2'.
excel_file_path = r"D:\Test_File_Archive\TestExcel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()
filter_exp = {
    'A':['test01','test02'],
    'B':['1','2']
}
sheet.multi_filter(filter_exp)

run_macro(macro_name, file=None)

Method

Run macro

Parameters

file<str>The path to the macro file.

macro_name<str>The macro name.

Sample code: rpa.app.microsoft.excel.Sheet.run_macro

# Note: Ensure Microsoft Office is installed.
# This method only works with macro-enabled Excel files, which use the .xlsm file extension.
# This example uses the 'Macro1' macro to clear all filters from a sheet. You can create this macro in Excel with the record macro feature.
excel_file_path = r"D:\Test_File_Archive\TestExcel.xlsm"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()
filter_exp = {
    'A':['test01','test02'],
    'B':['1','2']
}
sheet.multi_filter(filter_exp)

sheet.run_macro('Macro1')

merge_cell(range, each_row=False)

Method description

Merge cells

Parameters

range<str>The range of cells to merge, in A1 notation (e.g., 'A1:B2').

each_row<bool>Specifies whether to perform an inline merge.

Sample call: rpa.app.microsoft.excel.Sheet.merge_cell

# Note: Microsoft Office must be installed to use this feature.
# The `each_row` parameter determines how cells are merged. The default (`False`) merges the entire specified range into a single cell. `True` merges each row in the range separately.
# Example:
excel_file_path = r"D:\Test_File_Archive\TestExcel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

sheet.merge_cell('A1:B2', each_row=True)

insert(range, insertDirection=None)

Description

Add feature

Parameters

range<str>Use 'A' for a column, '1' for a row, 'A1' for a cell, and 'A1:B2' for a range of cells.

insertDirection<str>The direction of insertion.

Options:

• down: Moves the selection down.

• right: Moves the selection right.

Sample call: rpa.app.microsoft.excel.Sheet.insert

# Prerequisite: Ensure Microsoft Office is installed.
# Use the insertDirection parameter to specify whether adjacent cells shift down or to the right upon insertion.
# This example inserts an empty cell at A1, shifting existing cells down by default.
excel_file_path = r"D:\Test_File_Archive\TestExcel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

sheet.insert('A1')

delete(range, insertDirection=None)

Description

Delete a feature

Parameters

range<str>Use 'A' for columns, '1' for rows, 'A1' for cells, and 'A1:B2' for ranges.

insertDirection<str>The insertion direction.

Optional:

• up: Move the selection up.

• left: Move the selection left.

Code example: RPA.app.microsoft.excel.Sheet.delete

# Prerequisite: Ensure Microsoft Office is installed.
# This code deletes cell A1, shifting the remaining cells up by default.
excel_file_path = r"D:\Test_File_Archive\TestExcel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

sheet.delete('A1')

remove_duplicated_cols(range, cols)

Description

Column Deduplication

Parameters

range<str>Specify 'A' for a column or 'A1:B2' for a range.

cols<list>Column indexes for deduplication.

Example: rpa.app.microsoft.excel.Sheet.remove_duplicated_cols

# Note: Ensure Microsoft Office is installed before use.
# The deduplication columns must be within the deduplication range. For example, if the deduplication range is 'A1:C3', the deduplication columns can be any subset of 'A', 'B', and 'C'.
# This code example removes duplicate rows from 'A1:C9' by matching values across columns 'A', 'B', and 'C'.
excel_file_path = r"D:\Test_File_Archive\TestExcel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

sheet.remove_duplicated_cols('A1:C9', ['A','B','C'])

# Prerequisite: Make sure that Microsoft Office is installed.
# This example creates a pivot table that uses "Date" as a filter field, "Buyer" as a row label, 
# and "Type" as a column label. It then calculates the sum of "Amount".
# The resulting pivot table summarizes the total transaction amount for each buyer and type, filtered by date.
excel_file_path = r"D:\test_files\TestExcel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet('Consumption Data')

pivot_settings = rpa.app.microsoft.excel.PivotTableSettings('Pivot Table Settings')
pivot_settings.filters['Date'] = {} # Add a filter field
pivot_settings.rows['Buyer'] = {} # Add a row label
pivot_settings.columns['Type'] = {} # Add a column label
pivot_settings.values['Amount'] = {"Function": "xlSum"} # Add a value

sheet.create_pivot_table('Consumption Data', 'A:D', 'Pivot Table', 'A1', pivot_settings)

excel.save()
excel.close()

refresh_pivot_table(index=1)

Method

Refresh the pivot table.

Parameters

index<int>The index of the pivot table. Required when the sheet contains multiple pivot tables.

Example: rpa.app.microsoft.excel.Sheet.refresh_pivot_table

# Note: Before you use this code, make sure that Microsoft Office is installed.
# This code sample updates the values of a pivot table based on its data source.
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet('PivotTable')

sheet.refresh_pivot_table(index=1)

get_all_pivot_field_items(name, index=1)

Description

Returns all items in a filter column of a pivot table.

Parameters

index<int>The index of the pivot table. Required if multiple pivot tables exist.

name<str>The field name.

Return value

Filtered results<list>

Example: rpa.app.microsoft.excel.Sheet.get_all_pivot_field_items

# Prerequisite: Microsoft Office must be installed.
# Retrieves all unique values from the 'Buyer' field in the pivot table.
excel_file_path = r"D:\Test_File_Archive\TestExcel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet('PivotTable')

options_of_field = sheet.get_all_pivot_field_items('Buyer')

select_pivot_field_items(name, array, index=1, select=True)

Description

Select or deselect pivot table filter items.

Parameters

index<int>The index of the pivot table. This is required if the sheet contains multiple pivot tables.

name<str>The field name.

array<list>A list of available values.

select<bool>Selects or deselects items.

Example: rpa.app.microsoft.excel.Sheet.select_pivot_field_items

# Prerequisite: Ensure Microsoft Office is installed.
# This code deselects the 'AA' and 'BB' items in the 'Buyer' field, hiding the corresponding data from the pivot table.
excel_file_path = r"D:\Test_File_Archive\TestExcel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet('PivotTable')

sheet.select_pivot_field_items('Buyer', ['AA','BB'], select=False)

find(text, range=None, count=0)

Method

Query

Parameters

text<str>The text to find.

range<str>The cell range to search, such as 'A1:C5'. Defaults to the entire sheet if omitted.

count<int>A value of 0 returns all matches (default: 0).

Return value

A two-dimensional array where each row represents a match and contains the row number, column number, and cell value. Returns 0 if no matches are found.<list>

Sample call: rpa.app.microsoft.excel.Sheet.find

# Note: Ensure Microsoft Office is installed.
# The `count` parameter specifies the number of matching cells to return: 0 (default) for all matches, or 1 for the first match found in a left-to-right, top-to-bottom search.
# Code sample:
excel_file_path = r"D:\Test_File_Archive\TestExcel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

result = sheet.find('AA', count=1)

add_picture(file, col, row, width=None, height=None)

Method description

Insert an image at the desired location.

Parameters

file<str>The path to the image file.

col<str>Column of the top-left cell.

row<str>Row number of the top-left cell.

width<str>The width of the image.

height<str>The height of the image.

Sample: rpa.app.microsoft.excel.Sheet.add_picture

# Prerequisite: Microsoft Office must be installed.
# Code example:
excel_file_path = r"D:\Test_File_Archive\TestExcel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

pic_path = r'D:\Test_File_Archive\OCR_text_recognition.png'
sheet.add_picture(pic_path,'C', '2')

get_row_height(row)

Description

Returns the specified row height.

Parameters

row<str>The row number.

Return value

Returns the line height<float>

Code sample: rpa.app.microsoft.excel.Sheet.get_row_height

# Prerequisite: Microsoft Office must be installed.
# Usage example:
excel_file_path = r"D:\2_test_file_archive\test_excel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

row_height = sheet.get_row_height('1')

set_row_height(row, height)

Description

Sets the height of a specified row.

Parameters

row<str>Row number.

height<str>The height of the row.

Example: RPA.app.microsoft.excel.Sheet.set_row_height

# Note: Ensure Microsoft Office is installed.
# Code sample:
excel_file_path = r"D:\2_test_file_archive\test_excel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

sheet.set_row_height('1', '20')

get_col_width(col)

Method

Returns the specified column width.

Parameters

col<str>The column number.

Return value

Returns the column width as a <float>.

Example: rpa.app.microsoft.excel.Sheet.get_col_width

# Prerequisite: Ensure Microsoft Office is installed.
# Code example:
excel_file_path = r"D:\2_test_file_archive\test_excel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

col_width = sheet.get_col_width('A')

set_col_width(col, width)

Method description

Set the width of a specific column.

Parameters

col<str> Column number.

width<str>The width of the column.

Code sample: rpa.app.microsoft.excel.Sheet.set_col_width

# Requires Microsoft Office.

excel_file_path = r"D:\TestFiles\Sample.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

sheet.set_col_width('A', '20')

get_formula(range)

Description

Returns the formulas in a specified range.

Parameters

range<str>For example, enter A for a column, 1 for a row, A1 for a cell, or A1:B2 for a range.

Return value

Returns formulas from the specified range<string>

Code sample: rpa.app.microsoft.excel.Sheet.get_formula

# Prerequisite: Ensure Microsoft Office is installed.
# If the target cell contains no formula, this method returns its original value (blank or a raw string).
# Code sample:
excel_file_path = r"D:\Test_File_Archive\TestExcel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

formula = sheet.get_formula("A1")

set_formula(range, formula)

Description

Sets formulas in a specified range.

Parameters

range<str>Use 'A' for a column, '1' for a row, 'A1' for a cell, or 'A1:B2' for a range.

formula<str>The formula.

Example: rpa.app.microsoft.excel.Sheet.set_formula

# Prerequisite: Microsoft Office must be installed.
# Code sample:
excel_file_path = r"D:\Test_File_Archive\TestExcel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

formula = "=TODAY()"
sheet.set_formula("E2:F3", formula)

get_style(range, style, color_format='RGB')

Description

Gets the style for a specified range.

Parameters

range<str>'A' for a column, '1' for a row, 'A1' for a cell, and 'A1:B2' for a range.

style<str>The style.

Optional:

• fontsize: Font size.

• fontcolor: Font color.

• fontname: Font name.

• bgcolor: Background color.

color_format<str>Specifies the color format when style is fontcolor or bgcolor. Valid values: 'HEX' | 'RGB'.

Example: rpa.app.microsoft.excel.Sheet.get_style

# Prerequisite: Ensure Microsoft Office is installed.
# Sample:
excel_file_path = r"D:\Test_File_Archive\TestExcel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

style = sheet.get_style("A1:B2", "fontsize")

Note

The style parameter supports the following optional values: fontsize (font size), fontcolor (font color), fontname (font name), and bgcolor (background color).

The color_format parameter accepts HEX for a hexadecimal color code (e.g., #FFFFFF) or RGB for an RGB color code (e.g., RGB(255,255,255)).

set_style(range, style, value, color_format='RGB')

Method

Apply a style to a specified range.

Parameters

range<str> Specifies the target: a column ('A'), a row ('1'), a cell ('A1'), or a range of cells ('A1:B2').

style<str>Style

Optional:

• fontsize: font size

• fontcolor: font color

• fontname: font name

• bgcolor: background color

value<str>The value of the property. If style is fontcolor or bgcolor, the value can be a hexadecimal string, such as '#FFFFFF', or an RGB string, such as '255,255,255'.

color_format<str>The color format. This parameter is required if style is fontcolor or bgcolor. Valid values are 'HEX' or 'RGB'.

Example: rpa.app.microsoft.excel.Sheet.set_style

# Prerequisite: Ensure Microsoft Office is installed.
# Code sample:
excel_file_path = r"D:\test_files\test_excel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

sheet.set_style("A2", "bgcolor", "RGB(0,255,0)")
sheet.set_style("A3", "bgcolor", "#FF0000","HEX")

get_comment(range)

Description

Returns the comment of the specified cell.

Parameters

range<str>A cell reference, such as 'A1'.

Return value

Returns the cell's comment, or None if the cell lacks one.<str>

Sample code: rpa.app.microsoft.excel.Sheet.get_comment

# Note: Ensure Microsoft Office is installed before use.
# Returns None if the target cell has no comment.
excel_file_path = r"D:\2_测试文件归档\测试Excel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

comment = sheet.get_comment("A1")

set_comment(range, comment)

Description

Add a comment to the specified cell.

Parameters

range<str>A reference to a cell, such as 'A1'.

comment<str>The comment.

Call example: rpa.app.microsoft.excel.Sheet.set_comment

# Prerequisite: Ensure Microsoft Office is installed.
# Code sample:
excel_file_path = r"D:\2_test_file_archive\test_excel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

comment = "RPA Test"
sheet.set_comment("A1", comment)

replace(text, replacement, range=None, match_case=False)

Method description

Replace

Parameters

text<string>The replacement text.

replacement<str>The content to use as the replacement.

range<str>The range of cells to search, such as 'A1:C5'. If omitted, the search defaults to the entire sheet.

match_case<bool>Whether the match is case-sensitive.

Sample call: rpa.app.microsoft.excel.Sheet.replace

# Note: Microsoft Office is required.
# If the target text is not found, the program does nothing.
# Example:
excel_file_path = r"D:\examples\test.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

sheet.replace("test", "RPA_TEST")

to_pdf(file)

Method

Convert to PDF

Parameters

file<str>The path to save the PDF.

Example: rpa.app.microsoft.excel.Sheet.to_pdf

# Prerequisite: Ensure Microsoft Office is installed.
# Code sample:
excel_file_path = r"D:\Test_File_Archive\TestExcel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

path = r"D:\Test_File_Archive\sheet_to_pdf.pdf"
sheet.to_pdf(path)

activate()

Method

Activate the current sheet.

Sample call: rpa.app.microsoft.excel.Sheet.activate

# Prerequisite: Ensure Microsoft Office is installed.
# Activating a sheet and saving the file sets it as the default sheet, which is displayed first when the file is reopened.
# Code sample:
excel_file_path = r"D:\Test_File_Archive\TestExcel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet("AnotherSheet")

sheet.activate()

excel.close()

get_number_format(range, format)

Description

Retrieves the number format for a specified range.

Parameters

range<str>Use 'A' for a column, '1' for a row, 'A1' for a cell, and 'A1:B2' for a range of cells.

Sample code: rpa.app.microsoft.excel.Sheet.get_number_format

# Prerequisite: Ensure Microsoft Office is installed.
# Code example:
excel_file_path = r"D:\2_Test_File_Archive\TestExcel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

sheet.get_number_format("D2")

set_number_format(range, format)

Method description

Set the number format for the specified range.

Parameters

range<str>Specifies a column with 'A', a row with '1', a cell with 'A1', or a range of cells with 'A1:B2'.

format<str>A custom format code for the cell, such as '@'. For more information, see the Excel documentation on custom cell formats.

Code sample: rpa.app.microsoft.excel.Sheet.set_number_format

# Prerequisite: Microsoft Office must be installed.
# Code sample:
excel_file_path = r"D:\My_Files\Test_Workbook.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

sheet.set_number_format("D2","0.00")

Note

For supported format parameter values, refer to Excel's standard syntax under Format Cells > Number > Custom.