Worksheet class

Encapsulates the object that represents a single worksheet.

The Worksheet type exposes the following members:

Properties

Property	Description
protection	Represents the various types of protection options available for a worksheet. Supports advanced protection options in ExcelXP and above version.
unique_id	Gets and sets the unique id, it is same as {15DB5C3C-A5A1-48AF-8F25-3D86AC232D4F}.
workbook	Gets the workbook object which contains this sheet.
cells	Gets the `Worksheet.cells` collection.
query_tables	Gets `QueryTableCollection` in the worksheet.
pivot_tables	Gets all pivot tables in this worksheet.
type	Represents worksheet type.
name	Gets or sets the name of the worksheet.
show_formulas	Indicates whether to show formulas or their results.
is_gridlines_visible	Gets or sets a value indicating whether the gridlines are visible.Default is true.
is_row_column_headers_visible	Gets or sets a value indicating whether the worksheet will display row and column headers. Default is true.
pane_state	Indicates whether the pane has horizontal or vertical splits, and whether those splits are frozen.
display_zeros	True if zero values are displayed.
display_right_to_left	Indicates if the specified worksheet is displayed from right to left instead of from left to right. Default is false.
is_outline_shown	Indicates whether to show outline.
is_selected	Indicates whether this worksheet is selected when the workbook is opened.
list_objects	Gets all ListObjects in this worksheet.
tab_id	Specifies the internal identifier for the sheet.
horizontal_page_breaks	Gets the `HorizontalPageBreakCollection` collection.
vertical_page_breaks	Gets the `VerticalPageBreakCollection` collection.
hyperlinks	Gets the `HyperlinkCollection` collection.
page_setup	Represents the page setup description in this sheet.
auto_filter	Represents auto filter for the specified worksheet.
has_autofilter	Indicates whether this worksheet has auto filter.
transition_evaluation	Indicates whether the Transition Formula Evaluation (Lotus compatibility) option is enabled.
transition_entry	Indicates whether the Transition Formula Entry (Lotus compatibility) option is enabled.
visibility_type	Indicates the visible state for this sheet.
is_visible	Represents if the worksheet is visible.
sparkline_groups	Gets the sparkline groups in the worksheet.
charts	Gets a `Chart` collection
comments	Gets the `Comment` collection.
pictures	Gets a `Picture` collection.
text_boxes	Gets a `TextBox` collection.
check_boxes	Gets a `CheckBox` collection.
ole_objects	Represents a collection of `OleObject` in a worksheet.
shapes	Returns all drawing shapes in this worksheet.
slicers	Get the Slicer collection in the worksheet
timelines	Get the Timeline collection in the worksheet
index	Gets the index of sheet in the worksheet collection.
is_protected	Indicates if the worksheet is protected.
validations	Gets the data validation setting collection in the worksheet.
allow_edit_ranges	Gets the allow edit range collection in the worksheet.
error_check_options	Gets error check setting applied on certain ranges.
outline	Gets the outline on this worksheet.
first_visible_row	Represents first visible row index.
first_visible_column	Represents first visible column index.
zoom	Represents the scaling factor in percentage. It should be between 10 and 400.
view_type	Gets and sets the view type.
is_page_break_preview	Indicates whether the specified worksheet is shown in normal view or page break preview.
is_ruler_visible	Indicates whether the ruler is visible. This property is only applied for page break preview.
tab_color	Represents worksheet tab color.
code_name	Gets worksheet code name.
background_image	Gets and sets worksheet background image.
conditional_formattings	Gets the ConditionalFormattings in the worksheet.
active_cell	Gets or sets the active cell in the worksheet.
custom_properties	Gets an object representing the identifier information associated with a worksheet.
smart_tag_setting	Gets all `SmartTagCollection` objects of the worksheet.
scenarios	Gets the collection of `Scenario`.
cell_watches	Gets collection of cells on this worksheet being watched in the ‘watch window’.

Methods

Method	Description
`freeze_panes(self, row, column, freezed_rows, freezed_columns)`	Freezes panes at the specified cell in the worksheet.
`freeze_panes(self, cell_name, freezed_rows, freezed_columns)`	Freezes panes at the specified cell in the worksheet.
`copy(self, source_sheet)`	Copies contents and formats from another worksheet.
`copy(self, source_sheet, copy_options)`	Copies contents and formats from another worksheet.
`auto_fit_column(self, column_index, first_row, last_row)`	Autofits the column width.
`auto_fit_column(self, column_index)`	Autofits the column width.
`auto_fit_columns(self)`	Autofits all columns in this worksheet.
`auto_fit_columns(self, options)`	Autofits all columns in this worksheet.
`auto_fit_columns(self, first_column, last_column)`	Autofits the columns width.
`auto_fit_columns(self, first_column, last_column, options)`	Autofits the columns width.
`auto_fit_columns(self, first_row, first_column, last_row, last_column)`	Autofits the columns width.
`auto_fit_columns(self, first_row, first_column, last_row, last_column, options)`	Autofits the columns width.
`auto_fit_row(self, row_index, first_column, last_column)`	Autofits the row height.
`auto_fit_row(self, row_index, first_column, last_column, options)`	Autofits the row height.
`auto_fit_row(self, start_row, end_row, start_column, end_column)`	Autofits row height in a rectangle range.
`auto_fit_row(self, row_index)`	Autofits the row height.
`auto_fit_rows(self)`	Autofits all rows in this worksheet.
`auto_fit_rows(self, only_auto)`	Autofits all rows in this worksheet.
`auto_fit_rows(self, options)`	Autofits all rows in this worksheet.
`auto_fit_rows(self, start_row, end_row)`	Autofits row height in a range.
`auto_fit_rows(self, start_row, end_row, options)`	Autofits row height in a range.
`protect(self, type)`	Protects worksheet.
`protect(self, type, password, old_password)`	Protects worksheet.
`unprotect(self)`	Unprotects worksheet.
`unprotect(self, password)`	Unprotects worksheet.
`calculate_formula(self, formula)`	Calculates a formula.
`calculate_formula(self, formula, opts)`	Calculates a formula expression directly.
`calculate_formula(self, formula, p_opts, c_opts, base_cell_row, base_cell_column, calculation_data)`	Calculates a formula expression directly.
`calculate_formula(self, options, recursive)`	Calculates all formulas in this worksheet.
`calculate_array_formula(self, formula, opts)`	Calculates a formula as array formula.
`calculate_array_formula(self, formula, opts, max_row_count, max_column_count)`	Calculates a formula as array formula.
`calculate_array_formula(self, formula, p_opts, c_opts, base_cell_row, base_cell_column, max_row_count, max_column_count, calculation_data)`	Calculates a formula as array formula.
`refresh_pivot_tables(self)`	Refreshes all the PivotTables in this Worksheet.
`refresh_pivot_tables(self, option)`	Refreshes all the PivotTables in this Worksheet.
`get_panes(self)`	Gets the window panes.
`get_freezed_panes(self, row, column, freezed_rows, freezed_columns)`	Gets the freeze panes.
`split(self)`	Splits window.
`un_freeze_panes(self)`	Unfreezes panes in the worksheet.
`remove_split(self)`	Removes split window.
`add_page_breaks(self, cell_name)`	Adds page break.
`get_advanced_filter(self)`	Gets the settings of advanced filter.
`advanced_filter(self, is_filter, list_range, criteria_range, copy_to, unique_record_only)`	Filters data using complex criteria.
`remove_auto_filter(self)`	Removes the auto filter of the worksheet.
`set_visible(self, is_visible, ignore_error)`	Sets the visible options.
`select_range(self, start_row, start_column, total_rows, total_columns, remove_others)`	Selects a range.
`remove(self)`	Remove current sheet (CELLSPYTHONNET-192,User requests to add this API)
`remove_all_drawing_objects(self)`	Removes all drawing objects in this worksheet.
`clear_comments(self)`	Clears all comments in designer spreadsheet.
`move_to(self, index)`	Moves the sheet to another location in the spreadsheet.
`replace(self, old_string, new_string)`	Replaces all cells’ text with a new string.
`get_selected_ranges(self)`	Gets selected ranges of cells in the designer spreadsheet.
`get_printing_page_breaks(self, options)`	Gets automatic page breaks.
`start_access_cache(self, opts)`	Starts the session that uses caches to access the data in this worksheet.
`close_access_cache(self, opts)`	Closes the session that uses caches to access the data in this worksheet.
`convert_formula_reference_style(self, formula, to_r1c1, base_cell_row, base_cell_column)`	Converts the formula reference style.
`xml_map_query(self, path, xml_map)`	Query cell areas that mapped/linked to the specific path of xml map.

Example

The following example shows how to freeze panes and insert hyperlink to worksheet with .Net or VB.

from aspose.cells import Workbook

workbook = Workbook()
sheet = workbook.worksheets[0]
# Freeze panes at "AS40" with 10 rows and 10 columns
sheet.freeze_panes("AS40", 10, 10)
# Add a hyperlink in Cell A1
sheet.hyperlinks.add("A1", 1, 1, "http://www.aspose.com")

Worksheet class

Worksheet class

Properties

Methods

Example

See Also