Sheet

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

Method description

Reads values from an Excel file.

Parameters

range<str>: The range to read from. You can specify a column such as 'A', a row such as '1', a cell such as 'A1', or a range of cells such as 'A1:B2'.

read_value_as_str<bool>: Specifies how to read the content. If set to `True`, the content is read as a string. If set to `False`, the content is read as its original value. For a single cell, if the value is a number, it is read as a float. Otherwise, it is read as a string or `None`. For example, if a cell value in Excel is `1`, the method reads it as `1.0`.

only_visible<bool>: Specifies whether to read only visible cells.

skip<int>: The number of rows to skip before starting to read a column.

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

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# The following code provides an example:
excel_file_path = r"D:\2_TestFileArchive\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)

Method description

Writes data to an Excel file.

Parameters

range<str>: The target to write to. You can specify a column such as 'A', a row such as '1', or a cell such as 'A1'. When writing to a range of cells, specify only the starting cell.

value<list>: The data to write. If the `range` is a column or row, specify a one-dimensional array. If the `range` is a cell, specify a string, integer, or float. If the `range` is a range of cells, specify a two-dimensional array.

start_row<int>: The row to start writing from. This parameter is valid only when writing to a column.

start_col<str>: The column to start writing from. This parameter is valid only when writing to a row. You can pass '1', 'A', or 'a'.

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

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# The following code provides an example:
excel_file_path = r"D:\2_TestFileArchive\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)

Method description

Copies the data within a specified range.

Metric descriptions

range<str>: The range to copy. You can specify a column such as 'A', a row such as '1', a cell such as 'A1', or a range of cells such as 'A1:B2'.

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# The following code provides an example:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.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)

Method description

Pastes data into a specified range.

Parameters

range<str>: The paste destination. You can specify a row, a column, or the starting cell of a paste range. For example, 'A' for a column, '1' for a row, or 'A1' for a cell.

paste_type<str>: The data type to paste.

Options:

• all: All.

• Formula: formula

• value: A numerical value.

• format: Formats

• annotation: Comments

• verification: Validation

• sourceUnit: Units that use the source subject

• exceptBorder: All except borders

• colWidth: Column widths

• FormulasNumber: Formulas and number formats

• valueNumber: Values and number formats

• mergeCondition: All merging conditional formats

retry<int>: The number of retries.

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# The following code provides an example:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.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()

Method description

Retrieves the number of rows in the specified sheet.

Return value

Number of rows returned <str>

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# This method returns the number as a string.
# The following code provides an example:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

count = sheet.row_count()

col_count()

Method description

Retrieves the number of columns in the specified sheet.

Return value

Returns the number of columns <str>.

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# This method returns the number as a string.
# The following code provides an example:
excel_file_path = r"D:\2_TestFileArchive\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='<>')

Method description

Calculates the sum of a column.

Parameters

col (str): The column number.

start_row<int>: The starting row number.

condition<str>: The condition that defines which cells are added. The condition can be a number, expression, or text. For example, `<>` sums only numbers, and `>32` sums numbers greater than 32.

Return value

Returns the sum of the column. <float>

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# The following code provides an example:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.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='<>')

Method description

Calculates the sum of a range.

Parameter description

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

condition<str>: The condition that defines which cells are added. The condition can be a number, expression, or text. For example, `<>` sums only numbers, and `>32` sums numbers greater than 32.

Return value

Returns the column sum as a <float>.

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# The following code provides an example:
excel_file_path = r"D:\2_TestFileArchive\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')

Method description

Sets the font style for a specified range.

Parameters

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

font_pattern<str>: The font style for the range.

Options:

• general: Regular

• bold: Bold

• italic: Italic

• boldAndItalic: Bold and Italic

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# The following code provides an example:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()
# Sets the font for the range A1:B2 in Excel to bold.
sheet.set_region_font_pattern_in_sheet('A1:B2',font_pattern='bold')

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

Method description

You can set the style for a specified range.

Parameters

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

border_type<str>: The border style.

Options:

• no_border: No border (default)

• all_borders: All borders

• bottom_border: The bottom border.

• top_border: Top border

• left_border: Left border

• right_border: Right border

• outline_borders: Outside borders

• thick_outline_borders: Thick outside borders

• double_bottom_borders: Double bottom border

• thick_bottom_borders: Thick bottom border

• upper_and_lower_borders: Top and bottom border

• top_and_thick_bottom_borders: Top and thick bottom border

• top_and_double_bottom_borders: Top and double bottom border

color<str>: The hexadecimal value for the color. The default is '#000000'.

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# The following code provides an example:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()
# Sets the border style for the cell range A1:B2 in Excel to all borders and the color 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)

Method description

Hidden Area

Parameters

hide_region_type<str>: The type of range to hide.

Options:

• row: Hides one or more rows.

• col: Hides one or more columns.

start_row<str>: The starting row number. This parameter is valid only when `hide_region_type` is `row`. For example, '1'.

end_row<str>: The ending row number. This parameter is valid only when `hide_region_type` is `row`. For example, '3'.

start_col<str>: The starting column letter. This parameter is valid only when `hide_region_type` is `col`. For example, 'A' or 'a'.

end_col<str>: The ending column letter. This parameter is valid only when `hide_region_type` is `col`. For example, 'D' or 'd'.

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# The following code provides an example:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

sheet.hide_region(hide_region_type='row', start_row='1', end_row='4', start_col=None, end_col=None)
sheet.hide_region(hide_region_type='col', start_row=None, end_row=None, start_col="a", end_col="f")

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

Method description

Display Area

Parameters

show_region_type<str>: The type of range to show.

Options:

• row: Shows one or more rows.

• col: Shows one or more columns.

start_row<str>: The starting row number. This parameter is valid only when `show_region_type` is `row`. For example, '1'.

end_row<str>: The ending row number. This parameter is valid only when `show_region_type` is `row`. For example, '3'.

start_col<str>: The starting column letter. This parameter is valid only when `show_region_type` is `col`. For example, 'A' or 'a'.

end_col<str>: The ending column letter. This parameter is valid only when `show_region_type` is `col`. For example, 'D' or 'd'.

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# The following code provides an example:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.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)

Method description

Sorting

Parameters

sort_fields<list>: An array of sort fields. Each field is a tuple with the following format: `(column_name, sort_type, sort_order)`.

• `column_name`: The Excel column name, such as 'A', 'B', or 'AA'.

• Sort by:

  • value: Sorts by cell value.

  • cell_color: Sorts by cell color.

  • font_color: Sorts by cell font color.

• Sort order:

  • asc: Ascending

  • desc: Descending

range<str> The range of cells to search, such as 'A1:C5'. If this parameter is not specified, the search defaults to the entire sheet.

match_case<bool>: Specifies whether to perform a case-sensitive sort.

• False: (Default) Performs a case-insensitive sort.

• True: Performs a case-sensitive sort.

sort_method<str>: The sorting method.

Options:

• pinyin: (Default) Sorts based on the Pinyin order of characters.

• stroke: Sorts based on the number of strokes in each character.

contains_header<bool>: Specifies whether the range contains a header.

• False: (Default) The entire range is sorted.

• True: The range is not sorted.

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# The following code provides an example:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()
# Sorts the values in column A in descending order.
sort_fields1 = [('A','value','desc')]
sheet.sort(sort_fields1)
# Sorts the cells in column B by cell color in ascending order.
sort_fields2 = [('B','cell_color','asc')]
sheet.sort(sort_fields2)
# Sorts the cells in column C by font color in ascending order.
sort_fields3 = [('C','font_color','asc')]
sheet.sort(sort_fields3)

filter(col, array, delete=False)

Method description

Filters a column.

Parameters

col <str> The column number.

array<list>: An array of filter values.

delete<bool>: Specifies whether to delete the filtered rows. This parameter is deprecated and has no effect in versions 3.4.3 and later.

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# The following code provides an example:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()
# Standard filter
filter_value = ['rpa_test1','rpa_test2']
# Advanced filter. Wildcard characters are supported. ? represents a single character, and * represents any number of characters.
filter_value = ['rpa_test*']
# Filter blank cells
filter_value = ['=']

sheet.filter('A', filter_value)

remove_filter()

Method description

Removes the filter.

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# The following code provides an example:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.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)

Method description

Filters multiple columns.

Parameters

filters_val<dic>: A dictionary that specifies the filter values for each column. Use the following format: `{'A':['testA1','testA2','testA3'], 'B':['testB1','testB2'], ...}`

range<str>: The range to filter, such as 'A1:C5'. If this parameter is not specified, the entire sheet is filtered.

delete<bool>: Specifies whether to delete the filtered rows. This parameter is deprecated and has no effect in versions 3.4.3 and later.

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# The following code provides an example. After running, this code enables filtering and selects rows where column A is 'test01' or 'test02' and column B is '1' or '2'.
excel_file_path = r"D:\2_TestFileArchive\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 description

Runs a macro.

Parameters

file<str>: The path to the macro file.

macro_name<str>: The name of the macro command.

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# This method applies only to macro-enabled Excel files, which typically have the .xlsm extension.
# The following code provides an example. In this example, "Macro1" is used to remove all filters from the sheet. You can generate this macro using the macro recording feature in Excel.
excel_file_path = r"D:\2_TestFileArchive\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

Merges cells.

Parameters

range<str>: The range to merge. The format must be 'A1:B2'.

each_row<bool>: Specifies whether to merge cells within each row.

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# This method uses the each_row parameter to specify the merge type. The default is False, which merges the specified range into a single cell.
# If you set each_row to True, the specified range is merged by row, with each row becoming a single cell.
# The following code provides an example:
excel_file_path = r"D:\2_TestFileArchive\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)

Method description

Insert a feature

Description

range<str>: The range to insert. You can specify a column such as 'A', a row such as '1', a cell such as 'A1', or a range of cells such as 'A1:B2'.

insertDirection<str>: The direction to shift existing cells.

Options:

• down: Shift down

• right: Shift right

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# This method uses the insertDirection parameter to specify the direction to shift adjacent cells after insertion. It supports shifting down and shifting right.
# The following code provides an example. This inserts an empty cell at A1 and shifts the original data down by default.
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

sheet.insert('A1')

delete(range, insertDirection=None)

Method description

Deletes cells.

Parameters

range<str>: The range to delete. You can specify a column such as 'A', a row such as '1', a cell such as 'A1', or a range of cells such as 'A1:B2'.

insertDirection<str>: The direction to shift remaining cells.

Options:

• up: Shift up

• left: Shift left

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# The following code provides an example. This deletes cell A1 and shifts the remaining data up by default.
excel_file_path = r"D:\2_TestFileArchive\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)

Method description

Column deduplication

Metric Description

range<str>: The range to process. You can specify a column such as 'A' or a range of cells such as 'A1:B2'.

cols<list>: A list of column letters to check for duplicates.

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# The columns to check for duplicates must be within the specified range. For example, if the range is A1:C3, the columns can only be selected from A, B, and C.
# The following code provides an example. In this example, duplicate removal is performed on the range A1:C9. A row is considered a duplicate only if the values in all three specified columns are identical to another row.
excel_file_path = r"D:\2_TestFileArchive\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'])

create_pivot_table(data_sheet, data_range, pivot_sheet, pivot_range, pivot_settings)

Method description

Creates a pivot table.

Parameters

data_sheet<str>: The name of the sheet that contains the source data for the pivot table, such as 'Sheet1'.

data_range<str>: The range of the source data, such as 'A1:C10'.

pivot_sheet<str>: The name of the sheet where the pivot table will be inserted, such as 'Sheet2'.

pivot_range<str>: The position to insert the pivot table, such as 'A1'.

pivot_settings<PivotTableSettings>: The pivot table settings.

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# The following code provides an example. In this example, the date is used as a filter, "Buyer" is used as the row heading, and "Type" is used as the column heading. The "Amount" is summed.
# The result is a pivot table that shows the total transaction amount for each buyer and type, filtered by date.
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet('ConsumptionData')

pivot_settings = rpa.app.microsoft.excel.PivotTableSettings('PivotTableSettings')
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 field

sheet.create_pivot_table('ConsumptionData', 'A:D', 'PivotTable', 'A1', pivot_settings)

excel.save()
excel.close()

Description

The `pivot_settings.values` parameter specifies how to process the pivot table field values. The following table lists the available options for the `Function` property.

| Function | Description | | xlSum | Sum | | xlCount | Count | | xlAverage | Average | | xlMax | Maximum | | xlMin | Minimum | | xlProduct | Product | | xlCountNums | Count Numbers | | xlStDev | Standard Deviation | | xlStDevP | Population Standard Deviation | | xlVar | Variance | | xlVarP | Population Variance |

refresh_pivot_table(index=1)

Method description

Refreshes a pivot table.

Parameters

index<int>: The index of the pivot table. If there are multiple pivot tables, specify which one to refresh.

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# The following code provides an example. This example updates the pivot table values based on the 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)

Method description

Retrieves all items from a pivot table filter column.

Parameters

index<int>: The index of the pivot table. If there are multiple pivot tables, specify which one to use.

name<str>: The field name.

Return value

Returns the filtered results <list>.

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# The following code provides an example. This example gets all unique values from the "Buyer" field in the pivot table.
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')

options_of_field = sheet.get_all_pivot_field_items('Buyer')

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

Method description

Selects or deselects a set of pivot table filter items.

Metric description

index<int>: The index of the pivot table. If there are multiple pivot tables, specify which one to use.

name<str>: The field name.

array<list>: A list of values to select or deselect.

select<bool>: Specifies whether to select or deselect the items.

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# The following code provides an example. In this example, data related to the values "AA" or "BB" in the "Buyer" field is deselected, and the corresponding data is hidden.
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.select_pivot_field_items('Buyer', ['AA','BB'], select=False)

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

Method description

Query

Metric descriptions

text<str>: The content to find.

range<str>: The range to search, such as 'A1:C5'. If this parameter is not specified, the entire sheet is searched.

count<int>: The number of matching items to find. `0` (default) finds all matching items.

Return value

Returns a two-dimensional array of matching items. Each item is an array that contains the row number, column number, and value of the matched cell. Returns `0` if no matching items are found.<list>

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# You can set the number of matching items using the count parameter. The default is 0, which returns all matching results. If set to 1, it returns the first matching cell, searching from left to right and top to bottom.
# The following code provides an example:
excel_file_path = r"D:\2_TestFileArchive\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

Inserts a picture at a specified position.

Parameters

file<str>: The path of the picture to insert.

col<str>: The column letter of the top-left cell.

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

width<str>: The width of the picture.

height<str>: The height of the picture.

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# The following code provides an example:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

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

get_row_height(row)

Method description

Retrieves the height of a specified row.

Parameters

row<str>: The row number.

Return value

Returns the row height.<float>

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# The following code provides an example:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.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)

Method description

Sets the height of a specified row.

Parameters

row<str>: The row number.

height<str>: The row height.

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# The following code provides an example:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.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 description

Retrieves the width of a specified column.

Parameters

col<str>: The column letter.

Return value

Returns the column width.<float>

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# The following code provides an example:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.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

Sets the width of a specified column.

Parameters

col<str>: The column letter.

width<str>: The column width.

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# The following code provides an example:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.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)

Method description

Retrieves the formula from a specified range.

Parameters

range<str>: The range to retrieve the formula from. You can specify a column such as 'A', a row such as '1', a cell such as 'A1', or a range of cells such as 'A1:B2'.

Return value

Returns the formula for the specified range.<str>

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# If the cell does not contain a formula, this method returns the original value of the cell (empty or original characters).
# The following code provides an example:
excel_file_path = r"D:\2_TestFileArchive\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)

Method description

Sets the formula for a specified range.

Parameters

range<str>: The range to set the formula for. You can specify a column such as 'A', a row such as '1', a cell such as 'A1', or a range of cells such as 'A1:B2'.

formula <str> The formula.

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# The following code provides an example:
excel_file_path = r"D:\2_TestFileArchive\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')

Method description

Retrieves the style of a specified range.

Parameters

range<str>: The range to retrieve the style from. You can specify a column such as 'A', a row such as '1', a cell such as 'A1', or a range of cells such as 'A1:B2'.

style<str>: The style property.

Options:

• fontsize: Font size

• fontcolor: Font color

• fontname: Font name

• bgcolor: Background color

color_format<str>: The color format. This parameter is valid only when `style` is `fontcolor` or `bgcolor`. Options are 'HEX' and 'RGB'.

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# The following code provides an example:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

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

Description

The valid values for the style parameter are fontsize, fontcolor, fontname, and bgcolor, which represent the font size, font color, font name, and background color, respectively.

The `color_format` parameter accepts two values: `HEX` for a hexadecimal color code, such as `#FFFFFF`, and `RGB` for an RGB color code, such as `RGB(255,255,255)`.

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

Method description

Sets the style for a specified range.

Parameters

range<str>: The range to set the style for. You can specify a column such as 'A', a row such as '1', a cell such as 'A1', or a range of cells such as 'A1:B2'.

style<str>: The style property.

Options:

• fontsize: Font size

• fontcolor: Font color

• fontname: Font name

• bgcolor: Background color

value<str>: The value for the style property. If `style` is `fontcolor` or `bgcolor`, the value can be '#FFFFFF' or '255,255,255'.

color_format<str>: The color format. This parameter is valid only when `style` is `fontcolor` or `bgcolor`. Set `color_format` to 'HEX' or 'RGB'.

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# The following code provides an example:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.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)

Method description

Retrieves the comment from a cell.

Parameters

range<str> The cell. For example, 'A1'.

Return value

Returns the comment text as a string. Returns `None` if the cell has no comment.<str>

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# If the target cell does not have a comment, this method returns None.
# The following code provides an example:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.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)

Method description

Inserts a comment into a cell.

Parameters

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

comment<str>: The comment text.

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# The following code provides an example:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.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 <str> Specifies the content to be replaced.

replacement<str>: The replacement content.

range<str>: The range to search, such as 'A1:C5'. If this parameter is not specified, the entire sheet is searched.

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

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# If the content to be replaced does not exist in the sheet, the program performs no action.
# The following code provides an example:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.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 description

Convert to PDF

Parameters

file<str>: The path to save the PDF file.

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# The following code provides an example:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

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

activate()

Method description

Activates the current sheet.

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# After you use this method and save the Excel file, the corresponding sheet becomes the default sheet that opens when you start the Excel file.
# The following code provides an example:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet("NonDefaultSheet")

sheet.activate()

excel.close()

get_number_format(range, format)

Method description

Retrieves the number format for a specified range.

Parameters

range<str>: The range to retrieve the number format from. You can specify a column such as 'A', a row such as '1', a cell such as 'A1', or a range of cells such as 'A1:B2'.

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# The following code provides an example:
excel_file_path = r"D:\2_TestFileArchive\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

Sets the number format for a specified range.

Metric descriptions

range<str>: The range to set the number format for. You can specify a column such as 'A', a row such as '1', a cell such as 'A1', or a range of cells such as 'A1:B2'.

format<str>: The number format code, such as '@'. For more information, see the custom number formats in Excel.

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

# Note: Before you use this method, make sure that Microsoft Office is installed.
# The following code provides an example:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.microsoft.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

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

Description

For more information about available format codes, see the Custom category on the Number tab of the Format Cells dialog box in Excel.