Worksheet class

Worksheet class

Encapsulates the object that represents a single worksheet.

The Worksheet type exposes the following members:

Properties

PropertyDescription
protectionRepresents the various types of protection options available for a worksheet. Supports advanced protection options in ExcelXP and above version.
unique_idGets and sets the unique id, it is same as {15DB5C3C-A5A1-48AF-8F25-3D86AC232D4F}.
workbookGets the workbook object which contains this sheet.
cellsGets the Worksheet.cells collection.
query_tablesGets QueryTableCollection in the worksheet.
pivot_tablesGets all pivot tables in this worksheet.
typeRepresents worksheet type.
nameGets or sets the name of the worksheet.
show_formulasIndicates whether to show formulas or their results.
is_gridlines_visibleGets or sets a value indicating whether the gridlines are visible.Default is true.
is_row_column_headers_visibleGets or sets a value indicating whether the worksheet will display row and column headers.
Default is true.
pane_stateIndicates whether the pane has horizontal or vertical splits, and whether those splits are frozen.
display_zerosTrue if zero values are displayed.
display_right_to_leftIndicates if the specified worksheet is displayed from right to left instead of from left to right.
Default is false.
is_outline_shownIndicates whether to show outline.
is_selectedIndicates whether this worksheet is selected when the workbook is opened.
list_objectsGets all ListObjects in this worksheet.
tab_idSpecifies the internal identifier for the sheet.
horizontal_page_breaksGets the HorizontalPageBreakCollection collection.
vertical_page_breaksGets the VerticalPageBreakCollection collection.
hyperlinksGets the HyperlinkCollection collection.
page_setupRepresents the page setup description in this sheet.
auto_filterRepresents auto filter for the specified worksheet.
has_autofilterIndicates whether this worksheet has auto filter.
transition_evaluationIndicates whether the Transition Formula Evaluation (Lotus compatibility) option is enabled.
transition_entryIndicates whether the Transition Formula Entry (Lotus compatibility) option is enabled.
visibility_typeIndicates the visible state for this sheet.
is_visibleRepresents if the worksheet is visible.
sparkline_groupsGets the sparkline groups in the worksheet.
chartsGets a Chart collection
commentsGets the Comment collection.
picturesGets a Picture collection.
text_boxesGets a TextBox collection.
check_boxesGets a CheckBox collection.
ole_objectsRepresents a collection of OleObject in a worksheet.
shapesReturns all drawing shapes in this worksheet.
slicersGet the Slicer collection in the worksheet
timelinesGet the Timeline collection in the worksheet
indexGets the index of sheet in the worksheet collection.
is_protectedIndicates if the worksheet is protected.
validationsGets the data validation setting collection in the worksheet.
allow_edit_rangesGets the allow edit range collection in the worksheet.
error_check_optionsGets error check setting applied on certain ranges.
outlineGets the outline on this worksheet.
first_visible_rowRepresents first visible row index.
first_visible_columnRepresents first visible column index.
zoomRepresents the scaling factor in percentage. It should be between 10 and 400.
view_typeGets and sets the view type.
is_page_break_previewIndicates whether the specified worksheet is shown in normal view or page break preview.
is_ruler_visibleIndicates whether the ruler is visible. This property is only applied for page break preview.
tab_colorRepresents worksheet tab color.
code_nameGets worksheet code name.
background_imageGets and sets worksheet background image.
conditional_formattingsGets the ConditionalFormattings in the worksheet.
active_cellGets or sets the active cell in the worksheet.
custom_propertiesGets an object representing
the identifier information associated with a worksheet.
smart_tag_settingGets all SmartTagCollection objects of the worksheet.
scenariosGets the collection of Scenario.
cell_watchesGets collection of cells on this worksheet being watched in the ‘watch window’.

Methods

MethodDescription
freeze_panesFreezes panes at the specified cell in the worksheet.
freeze_panesFreezes panes at the specified cell in the worksheet.
copyCopies contents and formats from another worksheet.
copyCopies contents and formats from another worksheet.
auto_fit_columnAutofits the column width.
auto_fit_columnAutofits the column width.
auto_fit_columnsAutofits all columns in this worksheet.
auto_fit_columnsAutofits all columns in this worksheet.
auto_fit_columnsAutofits the columns width.
auto_fit_columnsAutofits the columns width.
auto_fit_columnsAutofits the columns width.
auto_fit_columnsAutofits the columns width.
auto_fit_rowAutofits the row height.
auto_fit_rowAutofits the row height.
auto_fit_rowAutofits row height in a rectangle range.
auto_fit_rowAutofits the row height.
auto_fit_rowsAutofits all rows in this worksheet.
auto_fit_rowsAutofits all rows in this worksheet.
auto_fit_rowsAutofits all rows in this worksheet.
auto_fit_rowsAutofits row height in a range.
auto_fit_rowsAutofits row height in a range.
protectProtects worksheet.
protectProtects worksheet.
unprotectUnprotects worksheet.
unprotectUnprotects worksheet.
calculate_formulaCalculates a formula.
calculate_formulaCalculates a formula expression directly.
calculate_formulaCalculates a formula expression directly.
calculate_formulaCalculates all formulas in this worksheet.
calculate_array_formulaCalculates a formula as array formula.
calculate_array_formulaCalculates a formula as array formula.
calculate_array_formulaCalculates a formula as array formula.
refresh_pivot_tablesRefreshes all the PivotTables in this Worksheet.
refresh_pivot_tablesRefreshes all the PivotTables in this Worksheet.
get_panesGets the window panes.
get_freezed_panesGets the freeze panes.
splitSplits window.
un_freeze_panesUnfreezes panes in the worksheet.
remove_splitRemoves split window.
add_page_breaksAdds page break.
get_advanced_filterGets the settings of advanced filter.
advanced_filterFilters data using complex criteria.
remove_auto_filterRemoves the auto filter of the worksheet.
set_visibleSets the visible options.
select_rangeSelects a range.
removeRemove current sheet (CELLSPYTHONNET-192,User requests to add this API)
remove_all_drawing_objectsRemoves all drawing objects in this worksheet.
clear_commentsClears all comments in designer spreadsheet.
move_toMoves the sheet to another location in the spreadsheet.
replaceReplaces all cells’ text with a new string.
get_selected_rangesGets selected ranges of cells in the designer spreadsheet.
get_printing_page_breaksGets automatic page breaks.
start_access_cacheStarts the session that uses caches to access the data in this worksheet.
close_access_cacheCloses the session that uses caches to access the data in this worksheet.
convert_formula_reference_styleConverts the formula reference style.
xml_map_queryQuery 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")

See Also