Sheet

activate()

Method description

Activates the current sheet.

Sample call – rpa.app.wps.excel.Sheet.activate

# Note: Confirm that WPS software is installed before use.
# After using this method and saving the Excel file, the specified sheet becomes the default opening page.
# That is, when you open the Excel file, this sheet appears as the homepage.
# Sample code:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet("NonDefaultSheet")

sheet.activate()

excel.close()

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

Method description

Reads data from Excel. All numeric values are returned as float. For example, if a cell contains 1, the returned value is 1.0.

Parameter description

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

only_visible <bool> Read only visible cells.

skip <int> When reading a column, skip this many rows before starting.

max <int> When reading a column or range, the number of rows must not exceed this value.

Sample call – rpa.app.wps.excel.Sheet.read

# Note: Confirm that WPS software is installed before use.
# Sample code:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.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 Excel.

Parameter description

range <str> Supports writing to rows, columns, cells, or ranges. For a range, specify only the starting cell. Example: 'A' for a column, '1' for a row, 'A1' for a cell.

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

start_row <int> When writing to a column, start at this row. This parameter applies only to column writes.

start_col <str> When writing to a row, start at this column. This parameter applies only to row writes. Accepts '1', 'A', or 'a'.

max <int> Specifies the maximum value that can be written to a column.

Sample call – rpa.app.wps.excel.Sheet.write

# Note: Confirm that WPS software is installed before use.
# Sample code:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

row_value = ["RowData1", "RPA test"]
cell_value = "RPA cell value"
column_value = ["ColumnData1", "RPA test"]
range_value = [["RangeData1", "RangeData2"], ["RangeData3", "RangeData4"]]

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 data within the specified range.

Parameter description

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

Sample call – rpa.app.wps.excel.Sheet.copy

# Note: Confirm that WPS software is installed before use.
# Sample code:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.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 the specified range.

Parameter description

range <str> Target location for pasting. Supports row, column, or starting cell of a range. 'A' for a column, '1' for a row, 'A1' for a cell.

paste_type <str> Data type to paste.

Options:

• all: Everything

• formula: formula

• Value: value

• format: format

• annotation: Comments

• verification: Validation

• sourceUnit: The units that use the source subject.

• exceptBorder: Excludes the border.

• colWidth: Column width

• FormulasNumber: Formulas and number formats

• valueNumber: Values and number formats

• mergeCondition: All merged conditional formats

retry <int> Number of retry attempts.

Sample call – rpa.app.wps.excel.Sheet.paste

# Note: Confirm that WPS software is installed before use.
# Sample code:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.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

Gets the number of rows in the specified sheet.

Return value

Returns the row count <str>.

Sample call – rpa.app.wps.excel.Sheet.row_count

# Note: Confirm that WPS software is installed before use.
# Sample code:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

count = sheet.row_count()

col_count()

Method description

Gets the number of columns in the specified sheet.

Return value

Returns the column count <str>.

Sample call – rpa.app.wps.excel.Sheet.col_count

# Note: Confirm that WPS software is installed before use.
# Sample code:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

count = sheet.col_count()

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

Method description

Sorts data.

Parameter description

sort_fields <list> Array of sort fields: sort_fields = [(<column>, <sort type>, <order>)]

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

• Sort type:

  • value: Sort by cell value

  • cell_color: Sort by cell background color

  • font_color: The font color of the cell.

• Order:

  • asc: Ascending

  • desc: Descending

range<str>The search range, such as 'A1:C5'. The default is the entire sheet.

match_case <bool> Case sensitivity.

• False: Default. Perform case-insensitive sorting.

• True: Perform case-sensitive sorting.

sort_method <str> Sorting method.

Options:

• pinyin: Default. Sort by Chinese pinyin order.

• stroke: Sort by stroke count of each character.

contains_header <bool> Whether the range includes a header row.

• False: Default. Sort the entire range.

• True: The entire range is not sorted.

Sample call – rpa.app.wps.excel.Sheet.sort

# Note: Confirm that WPS software is installed before use.
# Sample code:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()
# Sort column A in descending order by value
sort_fields1 = [('A', 'value', 'desc')]
sheet.sort(sort_fields1)
# Sort column B in ascending order by cell color
sort_fields2 = [('B', 'cell_color', 'asc')]
sheet.sort(sort_fields2)
# Sort column C in ascending order by font color
sort_fields3 = [('C', 'font_color', 'asc')]
sheet.sort(sort_fields3)

filter(col, array, delete=False)

Method description

Filters a column.

Parameter description

col <str> Column identifier.

array <list> Array of filter values.

delete <bool> Whether to delete filtered rows. This parameter is ignored in version 3.4.3 and later.

Sample call – rpa.app.wps.excel.Sheet.filter

# Note: Confirm that WPS software is installed before use.
# Sample code:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()
# Basic filter
filter_value = ['rpa_test1', 'rpa_test2']
# Advanced filter with wildcards: ? matches one character, * matches any number of characters
filter_value = ['rpa_test?']
# Filter blank cells
filter_value = ['=']

sheet.filter('A', filter_value)

remove_filter()

Method description

Removes filters.

Sample call – rpa.app.wps.excel.Sheet.remove_filter

# Note: Confirm that WPS software is installed before use.
# Sample code:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.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()

merge_cell(range, each_row=False)

Method description

Merges cells.

Parameter description

range <str> Range to merge. Must be in A1:B2 format.

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

Sample call – rpa.app.wps.excel.Sheet.merge_cell

# Note: Confirm that WPS software is installed before use.
# By default (each_row=False), the specified range merges into a single cell.
# If each_row=True, each row in the range merges separately.
# Sample code:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.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

Inserts cells.

Parameter description

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

insertDirection <str> Direction to shift existing cells.

Options:

• down: Shift down

• right: Shift right

Sample call – rpa.app.wps.excel.Sheet.insert

# Note: Confirm that WPS software is installed before use.
# This method shifts adjacent cells down or right after insertion.
# Sample code inserts an empty cell at A1. Existing data shifts down by default:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

sheet.insert('A1')

delete(range, insertDirection=None)

Method description

Deletes cells.

Parameter description

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

insertDirection <str> Direction to shift remaining cells.

Options:

• up: Shift up

• left: Shift left

Sample call – rpa.app.wps.excel.Sheet.delete

# Note: Confirm that WPS software is installed before use.
# Sample code deletes cell A1. Existing data shifts up by default:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

sheet.delete('A1')

remove_duplicated_cols(range, cols)

Method description

Column deduplication

Parameter description

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

cols <list> List of column identifiers to check for duplicates.

Sample call – rpa.app.wps.excel.Sheet.remove_duplicated_cols

# Note: Confirm that WPS software is installed before use.
# Columns used for deduplication must be within the specified range.
# For example, if the range is A1:C3, cols can only include A, B, or C.
# Sample code removes duplicates in A1:C9 where all three values in a row are identical:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

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

set_region_font_pattern_in_sheet(range, font_pattern='general')

Method description

Sets font style for a specified range.

Parameter description

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

font_pattern <str> Font style for the range.

Options:

• general: Regular

• bold: Bold

• italic: Italic

• boldAndItalic: Bold and italic

Sample call – rpa.app.wps.excel.Sheet.set_region_font_pattern_in_sheet

# Note: Confirm that WPS software is installed before use.
# Sample code:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()
# Set font to bold for range A1:B2
sheet.set_region_font_pattern_in_sheet('A1:B2', font_pattern='bold')

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

Method description

Sets border style for a specified range.

Parameter description

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

border_type <str> 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> Hex color value. Default is '#000000'.

Sample call – rpa.app.wps.excel.Sheet.set_region_border

# Note: Confirm that WPS software is installed before use.
# Sample code:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()
# Set all borders to black for range A1:B2
sheet.set_region_border('A1:B2', 'all_borders', color='000000')

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

Method description

Query

Parameter description

text <str> The content of the query.

range <str> Search range, such as 'A1:C5'. If empty, search the entire sheet (default).

count <int> Number of matches to return. 0 returns all matches (default).

Return value

Returns a two-dimensional array. Each row contains a match: first column is row number, second is column number, third is cell value. Returns 0 if no matches found <list>.

Sample call – rpa.app.wps.excel.Sheet.find

# Note: Confirm that WPS software is installed before use.
# Use the count parameter to limit results. Default (0) returns all matches.
# Setting count=1 returns the first match, scanning left to right, top to bottom.
# Sample code:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.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 the specified location.

Parameter description

file <str> Path to the image file.

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

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

width <float> Picture width.

height <float> Picture height.

Sample call – rpa.app.wps.excel.Sheet.add_picture

# Note: Confirm that WPS software is installed before use.
# Sample code:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

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

get_row_height(row)

Method description

Gets the height of a specified row.

Parameter description

row <str> Row number.

Return value

Returns row height <str>.

Sample call – rpa.app.wps.excel.Sheet.get_row_height

# Note: Confirm that WPS software is installed before use.
# Sample code:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.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.

Parameter description

row <str> Row number.

height <str> Row height.

Sample call – rpa.app.wps.excel.Sheet.set_row_height

# Note: Confirm that WPS software is installed before use.
# Sample code:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

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

get_col_width(col)

Method description

Gets the width of a specified column.

Parameter description

col <str> Column identifier.

Return value

Returns column width <str>.

Sample call – rpa.app.wps.excel.Sheet.get_col_width

# Note: Confirm that WPS software is installed before use.
# Sample code:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.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.

Parameter description

col <str> Column identifier.

width <str> Column width.

Sample call – rpa.app.wps.excel.Sheet.set_col_width

# Note: Confirm that WPS software is installed before use.
# Sample code:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

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

get_formula(range)

Method description

Gets formulas in the specified range.

Parameter description

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

Return value

Returns formulas for the specified range <str>.

Sample call – rpa.app.wps.excel.Sheet.get_formula

# Note: Confirm that WPS software is installed before use.
# Sample code:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

formula = sheet.get_formula("A1")

set_formula(range, formula)

Method description

Sets formulas in the specified range.

Parameter description

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

formula <str> Formula string.

Sample call – rpa.app.wps.excel.Sheet.set_formula

# Note: Confirm that WPS software is installed before use.
# Sample code:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.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

Gets style properties for the specified range.

Parameter description

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

style <str> Style property to retrieve.

Options:

• fontsize: Font size

• fontcolor: Font color

• fontname: Font name

• bgcolor: Background color

color_format <str> Color format. Valid only when style is fontcolor or bgcolor. Options: 'HEX' or 'RGB'.

Sample call – rpa.app.wps.excel.Sheet.get_style

# Note: Confirm that WPS software is installed before use.
# Sample code:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

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

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

Method description

Sets style properties for the specified range.

Parameter description

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

style <str> Style property to set.

Options:

• fontsize: Font size

• fontcolor: Font color

• fontname: Font name

• bgcolor: Background color

value <str> Value to set. For fontcolor or bgcolor, use '#FFFFFF' or '255,255,255'.

color_format <str> Color format. Required for fontcolor or bgcolor. Options: 'HEX' or 'RGB'.

Sample call – rpa.app.wps.excel.Sheet.set_style

# Note: Confirm that WPS software is installed before use.
# Sample code:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

sheet.set_style("A3", "bgcolor", "#FF0000", "HEX")

get_comment(range)

Method description

Gets a cell's comment.

Parameter description

range <str> Cell address, such as 'A1'.

Return value

Returns the cell's comment. Returns None if no comment exists <str>.

Sample call – rpa.app.wps.excel.Sheet.get_comment

# Note: Confirm that WPS software is installed before use.
# Returns None if the target cell has no comment.
# Sample code:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

comment = sheet.get_comment("A1")

set_comment(range, comment)

Method description

Adds a comment to a cell.

Parameter description

range <str> Cell address, such as 'A1'.

comment <str> Comment.

Sample call – rpa.app.wps.excel.Sheet.set_comment

# Note: Confirm that WPS software is installed before use.
# Sample code:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.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

Replacement

Parameter description

text <str> Text to replace.

replacement <str> Replacement text.

range <str> Search range, such as 'A1:C5'. If empty, search the entire sheet (default).

match_case <bool> Match case exactly.

Sample call – rpa.app.wps.excel.Sheet.replace

# Note: Confirm that WPS software is installed before use.
# Sample code:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

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

to_pdf(file)

Method description

Converts to PDF.

Parameter description

file <str> Path to save the PDF file.

Sample call – rpa.app.wps.excel.Sheet.to_pdf

# Note: Confirm that WPS software is installed before use.
# Sample code:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

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

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

Method description

Selects or deselects pivot table filter items.

Parameter description

index <int> Pivot table index. Specify which table if multiple exist.

name <str> Field name.

array <list> List of values to select.

select <bool> True to select, False to deselect.

Sample call – rpa.app.wps.excel.Sheet.select_pivot_field_items

# Note: Confirm that WPS software is installed before use.
# Sample code deselects values "AA" and "BB" under the "Purchaser" field.
# Corresponding data will be hidden:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet('PivotTable')

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

clear_range(range, clear_format=True)

Method description

Clears data in an Excel range.

Parameter description

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

clear_format <bool> Clear formatting as well.

Sample call – rpa.app.wps.excel.Sheet.clear_range

# Note: Confirm that WPS software is installed before use.
# Sample code:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

sheet.clear_range('A1:B2')

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

Method description

Calculates the sum of a column.

Parameter description

col <str> Column identifier.

start_row <int> Starting row number.

condition <str> Condition defining which cells to sum. Can be a number, expression, or text. Examples: '<>' (sum only numbers), '>32' (sum numbers greater than 32).

Return value

Returns column sum <float>.

Sample call – rpa.app.wps.excel.Sheet.calculate_sum_of_col

# Note: Confirm that WPS software is installed before use.
# Sample code:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.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> Excel range, such as 'A1:B2'.

condition <str> Condition defining which cells to sum. Can be a number, expression, or text. Examples: '<>' (sum only numbers), '>32' (sum numbers greater than 32).

Return value

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

Sample call – rpa.app.wps.excel.Sheet.calculate_sum_of_range

# Note: Confirm that WPS software is installed before use.
# Sample code:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

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

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

Method description

Hidden area

Parameter description

hide_region_type <str> Region type to hide.

Options:

• row: Hide one or more rows

• col: Hide one or more columns

start_row <str> Starting row number. Valid only for row hiding. Example: '1'.

end_row <str> Ending row number. Valid only for row hiding. Example: '3'.

start_col <str> Starting column identifier. Valid only for column hiding. Example: 'A' or 'a'.

end_col <str> Ending column identifier. Valid only for column hiding. Example: 'D' or 'd'.

Sample call – rpa.app.wps.excel.Sheet.hide_region

# Note: Confirm that WPS software is installed before use.
# Sample code:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.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

Parameter description

show_region_type <str> Region type to show.

Options:

• row: Show one or more rows

• col: Show one or more columns

start_row <str> Starting row number. Valid only for row showing. Example: '1'.

end_row <str> Ending row number. Valid only for row showing. Example: '3'.

start_col <str> Starting column identifier. Valid only for column showing. Example: 'A' or 'a'.

end_col <str> Ending column identifier. Valid only for column showing. Example: 'D' or 'd'.

Sample call – rpa.app.wps.excel.Sheet.show_region

# Note: Confirm that WPS software is installed before use.
# Sample code:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.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")

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

Method description

Filters multiple columns.

Parameter description

filters_val <dict> Dictionary in this format: {'A': ['testA1', 'testA2', 'testA3'], 'B': ['testB1', 'testB2'], ...}

range <str> Filter range, such as 'A1:C5'. If empty, filter the entire sheet (default).

delete <bool> Whether to delete filtered rows. This parameter is ignored in version 3.4.3 and later.

Sample call – rpa.app.wps.excel.Sheet.multi_filter

# Note: Confirm that WPS software is installed before use.
# Sample 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.wps.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()
filter_exp = {
    'A': ['test01', 'test02'],
    'B': ['1', '2']
}
sheet.multi_filter(filter_exp)

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

Method description

Creates a pivot table.

Parameter description

data_sheet <str> Name of the sheet containing source data, such as 'Sheet1'.

data_range <str> Range of source data, such as 'A1:C10'.

pivot_sheet <str> Name of the sheet where the pivot table is inserted, such as 'Sheet2'.

pivot_range <str> Insertion location for the pivot table, such as 'A1'.

pivot_settings <PivotTableSettings> Pivot table configuration.

Sample call – rpa.app.wps.excel.Sheet.create_pivot_table

# Note: Confirm that WPS software is installed before use.
# Sample code creates a pivot table that:
# - Uses Date as a filter
# - Uses "Purchaser" as row labels
# - Uses "Type" as column labels
# - Sums the "Amount" field
# Result: Total transaction amount per purchaser and type, filtered by date.
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet('ExpenseData')

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

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

excel.save()
excel.close()

Note

In the example above, pivot_settings.values defines how to process field values. The Function options are:

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

Parameter description

index <int> Pivot table index. Specify which table if multiple exist.

Sample call – rpa.app.wps.excel.Sheet.refresh_pivot_table

# Note: Confirm that WPS software is installed before use.
# Sample code updates the pivot table with current source data:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.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

Gets all items in a pivot table filter field.

Parameter description

index <int> Pivot table index. Specify which table if multiple exist.

name <str> Field name.

Return value

Returns filter results <list>.

Sample call – rpa.app.wps.excel.Sheet.get_all_pivot_field_items

# Note: Confirm that WPS software is installed before use.
# Sample code gets all unique values under the "Purchaser" field:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet('PivotTable')

options_of_field = sheet.get_all_pivot_field_items('Purchaser')

get_number_format(range, format)

Method description

Gets number format for the specified range.

Parameter description

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

Sample call – rpa.app.wps.excel.Sheet.get_number_format

# Note: Confirm that WPS software is installed before use.
# Sample code:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

sheet.get_number_format("D2")

set_number_format(range, format)

Method description

Sets number format for the specified range.

Parameter description

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

format <str> Format string, such as '@'. See Excel's custom number formats.

Sample call – rpa.app.wps.excel.Sheet.set_number_format

# Note: Confirm that WPS software is installed before use.
# Sample code:
excel_file_path = r"D:\2_TestFileArchive\TestExcel.xlsx"
excel = rpa.app.wps.excel.open(excel_file_path, visible=True)
sheet = excel.get_sheet()

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

Note

For valid format values, refer to Excel's standard custom number format syntax (Home > Number > Custom).