Worksheet

Worksheet class

Encapsulates the object that represents a single worksheet.

Methods

Name	Description
addPageBreaks(cellName)	Adds page break.
advancedFilter(isFilter, listRange, criteriaRange, copyTo, uniqueRecordOnly)	Filters data using complex criteria.
autoFitColumn(columnIndex, firstRow, lastRow)	Autofits the column width. This method autofits a row based on content in a range of cells within the row.
autoFitColumn(columnIndex)	Autofits the column width. AutoFitColumn is an imprecise function.
autoFitColumns()	Autofits all columns in this worksheet.
autoFitColumns(options)	Autofits all columns in this worksheet.
autoFitColumns(firstColumn, lastColumn)	Autofits the columns width. AutoFitColumn is an imprecise function.
autoFitColumns(firstColumn, lastColumn, options)	Autofits the columns width. AutoFitColumn is an imprecise function.
autoFitColumns(firstRow, firstColumn, lastRow, lastColumn)	Autofits the columns width. AutoFitColumn is an imprecise function.
autoFitColumns(firstRow, firstColumn, lastRow, lastColumn, options)	Autofits the columns width. AutoFitColumn is an imprecise function.
autoFitRow(rowIndex, firstColumn, lastColumn)	Autofits the row height. This method autofits a row based on content in a range of cells within the row.
autoFitRow(rowIndex, firstColumn, lastColumn, options)	Autofits the row height. This method autofits a row based on content in a range of cells within the row.
autoFitRow(startRow, endRow, startColumn, endColumn)	Autofits row height in a rectangle range.
autoFitRow(rowIndex)	Autofits the row height. AutoFitRow is an imprecise function.
autoFitRows()	Autofits all rows in this worksheet.
autoFitRows(onlyAuto)	Autofits all rows in this worksheet.
autoFitRows(options)	Autofits all rows in this worksheet.
autoFitRows(startRow, endRow)	Autofits row height in a range.
autoFitRows(startRow, endRow, options)	Autofits row height in a range.
calculateArrayFormula(formula, opts)	Calculates a formula as array formula.
calculateArrayFormula(formula, opts, maxRowCount, maxColumnCount)	Calculates a formula as array formula. The formula will be taken as dynamic array formula to calculate the dimension and
calculateArrayFormula(formula, pOpts, cOpts, baseCellRow, baseCellColumn, maxRowCount, maxColumnCount, calculationData)	Calculates a formula as array formula. The formula will be taken as dynamic array formula to calculate the dimension and
calculateFormula(formula)	Calculates a formula.
calculateFormula(formula, opts)	Calculates a formula expression directly. The formula will be calculated just like it has been set to cell A1. And the f
calculateFormula(formula, pOpts, cOpts, baseCellRow, baseCellColumn, calculationData)	Calculates a formula expression directly. The formula will be calculated just like it has been set to the specified base
calculateFormula(options, recursive)	Calculates all formulas in this worksheet.
clearComments()	Clears all comments in designer spreadsheet.
closeAccessCache(opts)	Closes the session that uses caches to access the data in this worksheet.
convertFormulaReferenceStyle(formula, toR1C1, baseCellRow, baseCellColumn)	Converts the formula reference style.
copy(sourceSheet)	Copies contents and formats from another worksheet.
copy(sourceSheet, copyOptions)	Copies contents and formats from another worksheet. You can copy data from another worksheet in the same file or another
dispose()	Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
filter()
freezePanes(row, column, freezedRows, freezedColumns)	Freezes panes at the specified cell in the worksheet. Row index and column index cannot all be zero. Number of rows and
freezePanes(cellName, freezedRows, freezedColumns)	Freezes panes at the specified cell in the worksheet. Row index and column index cannot all be zero. Number of rows and
getActiveCell()	Gets or sets the active cell in the worksheet.
getAdvancedFilter()	Gets the settings of advanced filter.
getAllPictures()
getAllowEditRanges()	Gets the allow edit range collection in the worksheet.
getAutoFilter()	Represents auto filter for the specified worksheet.
getBackgroundImage()	Gets and sets worksheet background image.
getCellWatches()	Gets collection of cells on this worksheet being watched in the ‘watch window’.
getCells()	Gets the Cells collection.
getCharts()	Gets a Chart collection
getCheckBoxes()	Gets a CheckBox collection.
getCodeName()	Gets worksheet code name.
getComments()	Gets the Comment collection.
getConditionalFormattings()	Gets the ConditionalFormattings in the worksheet.
getCustomProperties()	Gets an object representing the identifier information associated with a worksheet. Worksheet.CustomProperties provide a
getDisplayRightToLeft()	Indicates if the specified worksheet is displayed from right to left instead of from left to right. Default is false.
getDisplayZeros()	True if zero values are displayed.
getErrorCheckOptions()	Gets error check setting applied on certain ranges.
getFirstVisibleColumn()	Represents first visible column index.
getFirstVisibleRow()	Represents first visible row index.
getFreezedPanes()	Gets the freeze panes.
getGridlineColor()
getHorizontalPageBreaks()	Gets the HorizontalPageBreakCollection collection.
getHyperlinks()	Gets the HyperlinkCollection collection.
getIndex()	Gets the index of sheet in the worksheet collection.
getListObjects()	Gets all ListObjects in this worksheet.
getName()	Gets or sets the name of the worksheet. The max length of sheet name is 31. And you cannot assign same name(case insensi
getOleObjects()	Represents a collection of OleObject in a worksheet.
getOutline()	Gets the outline on this worksheet.
getPageSetup()	Represents the page setup description in this sheet.
getPaneState()	Indicates whether the pane has horizontal or vertical splits, and whether those splits are frozen. The value of the prop
getPanes()	Gets the window panes. If the window is not split or frozen.
getPictures()	Gets a Picture collection.
getPivotTables()	Gets all pivot tables in this worksheet.
getPrintingPageBreaks(options)	Gets automatic page breaks. Each cell area represents a paper.
getProtection()	Represents the various types of protection options available for a worksheet. Supports advanced protection options in Ex
getQueryTables()	Gets QueryTableCollection in the worksheet.
getScenarios()	Gets the collection of Scenario.
getSelectedAreas()
getSelectedRanges()	Gets selected ranges of cells in the designer spreadsheet.
getShapes()	Returns all drawing shapes in this worksheet.
getShowDataTypeIcons()
getShowFormulas()	Indicates whether to show formulas or their results.
getSlicers()	Get the Slicer collection in the worksheet
getSmartTagSetting()	Gets all SmartTagCollection objects of the worksheet.
getSparklineGroups()	Gets the sparkline groups in the worksheet.
getTabColor()	Represents worksheet tab color. This feature is only supported in ExcelXP(Excel2002) and later versions. If you save fil
getTabId()	Specifies the internal identifier for the sheet.
getTextBoxes()	Gets a TextBox collection.
getTimelines()	Get the Timeline collection in the worksheet
getTransitionEntry()	Indicates whether the Transition Formula Entry (Lotus compatibility) option is enabled.
getTransitionEvaluation()	Indicates whether the Transition Formula Evaluation (Lotus compatibility) option is enabled.
getType()	Represents worksheet type. The value of the property is SheetType integer constant.
getUniqueId()	Gets and sets the unique id, it is same as {15DB5C3C-A5A1-48AF-8F25-3D86AC232D4F}.
getValidations()	Gets the data validation setting collection in the worksheet.
getVerticalPageBreaks()	Gets the VerticalPageBreakCollection collection.
getViewType()	Gets and sets the view type. The value of the property is ViewType integer constant.
getVisibilityType()	Indicates the visible state for this sheet. The value of the property is VisibilityType integer constant.
getWorkbook()	Gets the workbook object which contains this sheet.
getZoom()	Represents the scaling factor in percentage. It should be between 10 and 400. Please set the view type first.
hasAutofilter()	Indicates whether this worksheet has auto filter.
isGridlinesVisible()	Gets or sets a value indicating whether the gridlines are visible.Default is true.
isOutlineShown()	Indicates whether to show outline.
isPageBreakPreview()	Indicates whether the specified worksheet is shown in normal view or page break preview.
isProtected()	Indicates if the worksheet is protected.
isRowColumnHeadersVisible()	Gets or sets a value indicating whether the worksheet will display row and column headers. Default is true.
isRulerVisible()	Indicates whether the ruler is visible. This property is only applied for page break preview.
isSelected()	Indicates whether this worksheet is selected when the workbook is opened.
isVisible()	Represents if the worksheet is visible.
moveTo(index)	Moves the sheet to another location in the spreadsheet.
protect(type)	Protects worksheet. This method protects worksheet without password. It can protect worksheet in all versions of Excel f
protect(type, password, oldPassword)	Protects worksheet. This method can protect worksheet in all versions of Excel file.
refreshPivotTables()	Refreshes all the PivotTables in this Worksheet.
refreshPivotTables(option)	Refreshes all the PivotTables in this Worksheet.
removeAllDrawingObjects()	Removes all drawing objects in this worksheet.
removeAutoFilter()	Removes the auto filter of the worksheet.
removeSplit()	Removes split window.
replace(oldString, newString)	Replaces all cells’ text with a new string.
selectRange(startRow, startColumn, totalRows, totalColumns, removeOthers)	Selects a range.
setActiveCell()	Gets or sets the active cell in the worksheet.
setBackgroundImage()	Gets and sets worksheet background image.
setCodeName()	Gets worksheet code name.
setDisplayRightToLeft()	Indicates if the specified worksheet is displayed from right to left instead of from left to right. Default is false.
setDisplayZeros()	True if zero values are displayed.
setFirstVisibleColumn()	Represents first visible column index.
setFirstVisibleRow()	Represents first visible row index.
setGridlineColor()
setGridlinesVisible()	Gets or sets a value indicating whether the gridlines are visible.Default is true.
setName()	Gets or sets the name of the worksheet. The max length of sheet name is 31. And you cannot assign same name(case insensi
setOutlineShown()	Indicates whether to show outline.
setPageBreakPreview()	Indicates whether the specified worksheet is shown in normal view or page break preview.
setRowColumnHeadersVisible()	Gets or sets a value indicating whether the worksheet will display row and column headers. Default is true.
setRulerVisible()	Indicates whether the ruler is visible. This property is only applied for page break preview.
setSelected()	Indicates whether this worksheet is selected when the workbook is opened.
setShowDataTypeIcons()
setShowFormulas()	Indicates whether to show formulas or their results.
setTabColor()	Represents worksheet tab color. This feature is only supported in ExcelXP(Excel2002) and later versions. If you save fil
setTabId()	Specifies the internal identifier for the sheet.
setTransitionEntry()	Indicates whether the Transition Formula Entry (Lotus compatibility) option is enabled.
setTransitionEvaluation()	Indicates whether the Transition Formula Evaluation (Lotus compatibility) option is enabled.
setType()	Represents worksheet type. The value of the property is SheetType integer constant.
setUniqueId()	Gets and sets the unique id, it is same as {15DB5C3C-A5A1-48AF-8F25-3D86AC232D4F}.
setViewType()	Gets and sets the view type. The value of the property is ViewType integer constant.
setVisibilityType()	Indicates the visible state for this sheet. The value of the property is VisibilityType integer constant.
setVisible()	Represents if the worksheet is visible.
setVisible(isVisible, ignoreError)	Sets the visible options.
setZoom()	Represents the scaling factor in percentage. It should be between 10 and 400. Please set the view type first.
split()	Splits window.
startAccessCache(opts)	Starts the session that uses caches to access the data in this worksheet. After finishing the access to the data, closeA
toString()	Returns a string represents the current Worksheet object.
unFreezePanes()	Unfreezes panes in the worksheet.
unprotect()	Unprotects worksheet. This method unprotects worksheet which is protected without password.
unprotect(password)	Unprotects worksheet. If the worksheet is protected without a password, you can set a null value or blank string to pass
xmlMapQuery(path, xmlMap)	Query cell areas that mapped/linked to the specific path of xml map. e.g. A xml map element structure: -RootElement |-At

addPageBreaks(cellName)

Adds page break.

Parameter	Type	Description
cellName	String

advancedFilter(isFilter, listRange, criteriaRange, copyTo, uniqueRecordOnly)

Filters data using complex criteria.

Parameter	Type	Description
isFilter	boolean	Indicates whether filtering the list in place.
listRange	String	The list range.
criteriaRange	String	The criteria range.
copyTo	String	The range where copying data to.
uniqueRecordOnly	boolean	Only displaying or copying unique rows.

autoFitColumn(columnIndex, firstRow, lastRow)

Autofits the column width. This method autofits a row based on content in a range of cells within the row.

Parameter	Type	Description
columnIndex	Number	Column index.
firstRow	Number	First row index.
lastRow	Number	Last row index.

autoFitColumn(columnIndex)

Autofits the column width. AutoFitColumn is an imprecise function.

Parameter	Type	Description
columnIndex	Number	Column index.

autoFitColumns()

Autofits all columns in this worksheet.

autoFitColumns(options)

Autofits all columns in this worksheet.

Parameter	Type	Description
options	AutoFitterOptions	The auto fitting options

autoFitColumns(firstColumn, lastColumn)

Autofits the columns width. AutoFitColumn is an imprecise function.

Parameter	Type	Description
firstColumn	Number	First column index.
lastColumn	Number	Last column index.

autoFitColumns(firstColumn, lastColumn, options)

Autofits the columns width. AutoFitColumn is an imprecise function.

Parameter	Type	Description
firstColumn	Number	First column index.
lastColumn	Number	Last column index.
options	AutoFitterOptions	The auto fitting options

autoFitColumns(firstRow, firstColumn, lastRow, lastColumn)

Autofits the columns width. AutoFitColumn is an imprecise function.

Parameter	Type	Description
firstRow	Number	First row index.
firstColumn	Number	First column index.
lastRow	Number	Last row index.
lastColumn	Number	Last column index.

autoFitColumns(firstRow, firstColumn, lastRow, lastColumn, options)

Autofits the columns width. AutoFitColumn is an imprecise function.

Parameter	Type	Description
firstRow	Number	First row index.
firstColumn	Number	First column index.
lastRow	Number	Last row index.
lastColumn	Number	Last column index.
options	AutoFitterOptions	The auto fitting options

autoFitRow(rowIndex, firstColumn, lastColumn)

Autofits the row height. This method autofits a row based on content in a range of cells within the row.

Parameter	Type	Description
rowIndex	Number	Row index.
firstColumn	Number	First column index.
lastColumn	Number	Last column index.

autoFitRow(rowIndex, firstColumn, lastColumn, options)

Autofits the row height. This method autofits a row based on content in a range of cells within the row.

Parameter	Type	Description
rowIndex	Number	Row index.
firstColumn	Number	First column index.
lastColumn	Number	Last column index.
options	AutoFitterOptions	The auto fitter options

autoFitRow(startRow, endRow, startColumn, endColumn)

Autofits row height in a rectangle range.

Parameter	Type	Description
startRow	Number	Start row index.
endRow	Number	End row index.
startColumn	Number	Start column index.
endColumn	Number	End column index.

autoFitRow(rowIndex)

Autofits the row height. AutoFitRow is an imprecise function.

Parameter	Type	Description
rowIndex	Number	Row index.

autoFitRows()

Autofits all rows in this worksheet.

autoFitRows(onlyAuto)

Autofits all rows in this worksheet.

Parameter	Type	Description
onlyAuto	boolean	True,only autofits the row height when row height is not customed.

autoFitRows(options)

Autofits all rows in this worksheet.

Parameter	Type	Description
options	AutoFitterOptions	The auto fitter options

autoFitRows(startRow, endRow)

Autofits row height in a range.

Parameter	Type	Description
startRow	Number	Start row index.
endRow	Number	End row index.

autoFitRows(startRow, endRow, options)

Autofits row height in a range.

Parameter	Type	Description
startRow	Number	Start row index.
endRow	Number	End row index.
options	AutoFitterOptions	The options of auto fitter.

calculateArrayFormula(formula, opts)

Calculates a formula as array formula.

Parameter	Type	Description
formula	String	Formula to be calculated.
opts	CalculationOptions	Options for calculating formula

calculateArrayFormula(formula, opts, maxRowCount, maxColumnCount)

Calculates a formula as array formula. The formula will be taken as dynamic array formula to calculate the dimension and result. User specified maximum dimension is used for cases that the calculated result is large data set (for example, the calculated result may correspond to a whole row or column data) but user does not need so large an array according to business requirement or for performance consideration.

Parameter	Type	Description
formula	String	Formula to be calculated.
opts	CalculationOptions	Options for calculating formula
maxRowCount	Number	the maximum row count of resultant data. If it is non-positive or greater than the actual row count, then actual row count will be used.
maxColumnCount	Number	the maximum column count of resultant data. If it is non-positive or greater than the actual row count, then actual column count will be used.

Returns: Array of Array of Object — Array of Array of Object Calculated formula result.

calculateArrayFormula(formula, pOpts, cOpts, baseCellRow, baseCellColumn, maxRowCount, maxColumnCount, calculationData)

Calculates a formula as array formula. The formula will be taken as dynamic array formula to calculate the dimension and result. User specified maximum dimension is used for cases that the calculated result is large data set (for example, the calculated result may correspond to a whole row or column data) but user does not need so large an array according to business requirement or for performance consideration.

Parameter	Type	Description
formula	String	Formula to be calculated.
pOpts	FormulaParseOptions	Options for parsing formula
cOpts	CalculationOptions	Options for calculating formula
baseCellRow	Number	The row index of the base cell.
baseCellColumn	Number	The column index of the base cell.
maxRowCount	Number	The maximum row count of resultant data. If it is non-positive or greater than the actual row count, then actual row count will be used.
maxColumnCount	Number	The maximum column count of resultant data. If it is non-positive or greater than the actual row count, then actual column count will be used.
calculationData	CalculationData	The calculation data. It is used for the situation that user needs to calculate some static formulas when implementing custom calculation engine. For such kind of situation, user needs to specify it with the calculation data provided for

Returns: Array of Array of Object — Array of Array of Object Calculated formula result.

calculateFormula(formula)

Calculates a formula.

Parameter	Type	Description
formula	String	Formula to be calculated.

Returns: Object — Object Calculated formula result.

calculateFormula(formula, opts)

Calculates a formula expression directly. The formula will be calculated just like it has been set to cell A1. And the formula will be taken as normal formula. If you need the formula be calculated as an array formula and to get an array for the calculated result, please use calculateArrayFormula(java.lang.String, com.aspose.cells.CalculationOptions) instead.

Parameter	Type	Description
formula	String	Formula to be calculated.
opts	CalculationOptions	Options for calculating formula

Returns: Object — Object Calculated result of given formula. The returned object may be of possible types of Cell.Value, or ReferredArea.

calculateFormula(formula, pOpts, cOpts, baseCellRow, baseCellColumn, calculationData)

Calculates a formula expression directly. The formula will be calculated just like it has been set to the specified base cell. And the formula will be taken as normal formula. If you need the formula be calculated as an array formula and to get an array for the calculated result, please use calculateArrayFormula(java.lang.String, com.aspose.cells.FormulaParseOptions, com.aspose.cells.CalculationOptions, int, int, int, int, com.aspose.cells.CalculationData) instead.

Parameter	Type	Description
formula	String	Formula to be calculated.
pOpts	FormulaParseOptions	Options for parsing formula.
cOpts	CalculationOptions	Options for calculating formula.
baseCellRow	Number	The row index of the base cell.
baseCellColumn	Number	The column index of the base cell.
calculationData	CalculationData	The calculation data. It is used for the situation that user needs to calculate some static formulas when implementing custom calculation engine. For such kind of situation, user needs to specify it with the calculation data provided for

Returns: Object — Object Calculated result of given formula. The returned object may be of possible types of Cell.Value, or ReferredArea.

calculateFormula(options, recursive)

Calculates all formulas in this worksheet.

Parameter	Type	Description
options	CalculationOptions	Options for calculation
recursive	boolean	True means if the worksheet’ cells depend on the cells of other worksheets, the dependent cells in other worksheets will be calculated too. False means all the formulas in the worksheet have been calculated and the values are right.

clearComments()

Clears all comments in designer spreadsheet.

closeAccessCache(opts)

Closes the session that uses caches to access the data in this worksheet.

Parameter	Type	Description
opts	Number	AccessCacheOptions

convertFormulaReferenceStyle(formula, toR1C1, baseCellRow, baseCellColumn)

Converts the formula reference style.

Parameter	Type	Description
formula	String	The formula to be converted.
toR1C1	boolean	Which reference style to convert the formula to. If the original formula is of A1 reference style, then this value should be true so the formula will be converted from A1 to R1C1 reference style; If the original formula is of R1C1 reference style, then this value should be false so the formula will be converted from R1C1 to A1 reference style;
baseCellRow	Number	The row index of the base cell.
baseCellColumn	Number	The column index of the base cell.

Returns: String — String The converted formula.

copy(sourceSheet)

Copies contents and formats from another worksheet.

Parameter	Type	Description
sourceSheet	Worksheet	Source worksheet.

copy(sourceSheet, copyOptions)

Copies contents and formats from another worksheet. You can copy data from another worksheet in the same file or another file. However, this method does not support to copy drawing objects, such as comments, images and charts.

Parameter	Type	Description
sourceSheet	Worksheet	Source worksheet.
copyOptions	CopyOptions

dispose()

Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.

filter()

freezePanes(row, column, freezedRows, freezedColumns)

Freezes panes at the specified cell in the worksheet. Row index and column index cannot all be zero. Number of rows and number of columns also cannot all be zero.The first two parameters specify the froze position and the last two parameters specify the area frozen on the left top pane.

Parameter	Type	Description
row	Number	Row index.
column	Number	Column index.
freezedRows	Number	Number of visible rows in top pane, no more than row index.
freezedColumns	Number	Number of visible columns in left pane, no more than column index.

freezePanes(cellName, freezedRows, freezedColumns)

Freezes panes at the specified cell in the worksheet. Row index and column index cannot all be zero. Number of rows and number of columns also cannot all be zero.

Parameter	Type	Description
cellName	String	Cell name.
freezedRows	Number	Number of visible rows in top pane, no more than row index.
freezedColumns	Number	Number of visible columns in left pane, no more than column index.

getActiveCell()

Gets or sets the active cell in the worksheet.

getAdvancedFilter()

Gets the settings of advanced filter.

Returns: AdvancedFilter — AdvancedFilter

getAllPictures()

getAllowEditRanges()

Gets the allow edit range collection in the worksheet.

getAutoFilter()

Represents auto filter for the specified worksheet.

getBackgroundImage()

Gets and sets worksheet background image.

getCellWatches()

Gets collection of cells on this worksheet being watched in the ‘watch window’.

getCells()

Gets the Cells collection.

getCharts()

Gets a Chart collection

getCheckBoxes()

Gets a CheckBox collection.

getCodeName()

Gets worksheet code name.

getComments()

Gets the Comment collection.

getConditionalFormattings()

Gets the ConditionalFormattings in the worksheet.

getCustomProperties()

Gets an object representing the identifier information associated with a worksheet. Worksheet.CustomProperties provide a preferred mechanism for storing arbitrary data. It supports legacy third-party document components, as well as those situations that have a stringent need for binary parts.

getDisplayRightToLeft()

Indicates if the specified worksheet is displayed from right to left instead of from left to right. Default is false.

getDisplayZeros()

True if zero values are displayed.

getErrorCheckOptions()

Gets error check setting applied on certain ranges.

getFirstVisibleColumn()

Represents first visible column index.

getFirstVisibleRow()

Represents first visible row index.

getFreezedPanes()

Gets the freeze panes.

Returns: Array of Number — Array of Number Return null means the worksheet is not frozen 0:Row index;1:column;2:freezedRows;3:freezedRows

getGridlineColor()

getHorizontalPageBreaks()

Gets the HorizontalPageBreakCollection collection.

getHyperlinks()

Gets the HyperlinkCollection collection.

getIndex()

Gets the index of sheet in the worksheet collection.

getListObjects()

Gets all ListObjects in this worksheet.

getName()

Gets or sets the name of the worksheet. The max length of sheet name is 31. And you cannot assign same name(case insensitive) to two worksheets. For example, you cannot set “SheetName1” to the first worksheet and set “SHEETNAME1” to the second worksheet.

getOleObjects()

Represents a collection of OleObject in a worksheet.

getOutline()

Gets the outline on this worksheet.

getPageSetup()

Represents the page setup description in this sheet.

getPaneState()

Indicates whether the pane has horizontal or vertical splits, and whether those splits are frozen. The value of the property is PaneStateType integer constant.

getPanes()

Gets the window panes. If the window is not split or frozen.

getPictures()

Gets a Picture collection.

getPivotTables()

Gets all pivot tables in this worksheet.

getPrintingPageBreaks(options)

Gets automatic page breaks. Each cell area represents a paper.

Parameter	Type	Description
options	ImageOrPrintOptions	The print options

Returns: Array ofCellArea — Array ofCellArea The automatic page breaks areas.

getProtection()

Represents the various types of protection options available for a worksheet. Supports advanced protection options in ExcelXP and above version. This property can protect worksheet in all versions of Excel file and support advanced protection options in ExcelXP and above version.

getQueryTables()

Gets QueryTableCollection in the worksheet.

getScenarios()

Gets the collection of Scenario.

getSelectedAreas()

getSelectedRanges()

Gets selected ranges of cells in the designer spreadsheet.

Returns: ArrayList — ArrayList An java.util.ArrayList which contains selected ranges.

getShapes()

Returns all drawing shapes in this worksheet.

getShowDataTypeIcons()

getShowFormulas()

Indicates whether to show formulas or their results.

getSlicers()

Get the Slicer collection in the worksheet

getSmartTagSetting()

Gets all SmartTagCollection objects of the worksheet.

getSparklineGroups()

Gets the sparkline groups in the worksheet.

getTabColor()

Represents worksheet tab color. This feature is only supported in ExcelXP(Excel2002) and later versions. If you save file as Excel97 or Excel2000 format, it will be omitted.

getTabId()

Specifies the internal identifier for the sheet.

getTextBoxes()

Gets a TextBox collection.

getTimelines()

Get the Timeline collection in the worksheet

getTransitionEntry()

Indicates whether the Transition Formula Entry (Lotus compatibility) option is enabled.

getTransitionEvaluation()

Indicates whether the Transition Formula Evaluation (Lotus compatibility) option is enabled.

getType()

Represents worksheet type. The value of the property is SheetType integer constant.

getUniqueId()

Gets and sets the unique id, it is same as {15DB5C3C-A5A1-48AF-8F25-3D86AC232D4F}.

getValidations()

Gets the data validation setting collection in the worksheet.

getVerticalPageBreaks()

Gets the VerticalPageBreakCollection collection.

getViewType()

Gets and sets the view type. The value of the property is ViewType integer constant.

getVisibilityType()

Indicates the visible state for this sheet. The value of the property is VisibilityType integer constant.

getWorkbook()

Gets the workbook object which contains this sheet.

getZoom()

Represents the scaling factor in percentage. It should be between 10 and 400. Please set the view type first.

hasAutofilter()

Indicates whether this worksheet has auto filter.

isGridlinesVisible()

Gets or sets a value indicating whether the gridlines are visible.Default is true.

isOutlineShown()

Indicates whether to show outline.

isPageBreakPreview()

Indicates whether the specified worksheet is shown in normal view or page break preview.

isProtected()

Indicates if the worksheet is protected.

isRowColumnHeadersVisible()

Gets or sets a value indicating whether the worksheet will display row and column headers. Default is true.

isRulerVisible()

Indicates whether the ruler is visible. This property is only applied for page break preview.

isSelected()

Indicates whether this worksheet is selected when the workbook is opened.

isVisible()

Represents if the worksheet is visible.

moveTo(index)

Moves the sheet to another location in the spreadsheet.

Parameter	Type	Description
index	Number	Destination sheet index.

protect(type)

Protects worksheet. This method protects worksheet without password. It can protect worksheet in all versions of Excel file.

Parameter	Type	Description
type	Number	ProtectionType

protect(type, password, oldPassword)

Protects worksheet. This method can protect worksheet in all versions of Excel file.

Parameter	Type	Description
type	Number	ProtectionType
password	String	Password.
oldPassword	String	If the worksheet is already protected by a password, please supply the old password. Otherwise, you can set a null value or blank string to this parameter.

Example:

//Instantiating a Workbook object
var excel = new aspose.cells.Workbook("Book2.xls");
//Accessing the first worksheet in the Excel file
var worksheet = excel.getWorksheets().get(0);
//Protecting the worksheet with a password
worksheet.protect(aspose.cells.ProtectionType.ALL, "aspose", null);
//Saving the modified Excel file in default (that is Excel 20003) format
excel.save("Book1.xls");

refreshPivotTables()

Refreshes all the PivotTables in this Worksheet.

refreshPivotTables(option)

Refreshes all the PivotTables in this Worksheet.

Parameter	Type	Description
option	PivotTableRefreshOption	The option for refreshing data source of pivot table.

removeAllDrawingObjects()

Removes all drawing objects in this worksheet.

removeAutoFilter()

Removes the auto filter of the worksheet.

removeSplit()

Removes split window.

replace(oldString, newString)

Replaces all cells’ text with a new string.

Parameter	Type	Description
oldString	String	Old string value.
newString	String	New string value.

selectRange(startRow, startColumn, totalRows, totalColumns, removeOthers)

Selects a range.

Parameter	Type	Description
startRow	Number	The start row.
startColumn	Number	The start column
totalRows	Number	The number of rows.
totalColumns	Number	The number of columns
removeOthers	boolean	True means removing other selected range and only select this range.

setActiveCell()

Gets or sets the active cell in the worksheet.

setBackgroundImage()

Gets and sets worksheet background image.

setCodeName()

Gets worksheet code name.

setDisplayRightToLeft()

Indicates if the specified worksheet is displayed from right to left instead of from left to right. Default is false.

setDisplayZeros()

True if zero values are displayed.

setFirstVisibleColumn()

Represents first visible column index.

setFirstVisibleRow()

Represents first visible row index.

setGridlineColor()

setGridlinesVisible()

Gets or sets a value indicating whether the gridlines are visible.Default is true.

setName()

Gets or sets the name of the worksheet. The max length of sheet name is 31. And you cannot assign same name(case insensitive) to two worksheets. For example, you cannot set “SheetName1” to the first worksheet and set “SHEETNAME1” to the second worksheet.

setOutlineShown()

Indicates whether to show outline.

setPageBreakPreview()

Indicates whether the specified worksheet is shown in normal view or page break preview.

setRowColumnHeadersVisible()

Gets or sets a value indicating whether the worksheet will display row and column headers. Default is true.

setRulerVisible()

Indicates whether the ruler is visible. This property is only applied for page break preview.

setSelected()

Indicates whether this worksheet is selected when the workbook is opened.

setShowDataTypeIcons()

setShowFormulas()

Indicates whether to show formulas or their results.

setTabColor()

Represents worksheet tab color. This feature is only supported in ExcelXP(Excel2002) and later versions. If you save file as Excel97 or Excel2000 format, it will be omitted.

setTabId()

Specifies the internal identifier for the sheet.

setTransitionEntry()

Indicates whether the Transition Formula Entry (Lotus compatibility) option is enabled.

setTransitionEvaluation()

Indicates whether the Transition Formula Evaluation (Lotus compatibility) option is enabled.

setType()

Represents worksheet type. The value of the property is SheetType integer constant.

setUniqueId()

Gets and sets the unique id, it is same as {15DB5C3C-A5A1-48AF-8F25-3D86AC232D4F}.

setViewType()

Gets and sets the view type. The value of the property is ViewType integer constant.

setVisibilityType()

Indicates the visible state for this sheet. The value of the property is VisibilityType integer constant.

setVisible()

Represents if the worksheet is visible.

setVisible(isVisible, ignoreError)

Sets the visible options.

Parameter	Type	Description
isVisible	boolean	Whether the worksheet is visible
ignoreError	boolean	Whether to ignore error if this option is not valid.

setZoom()

Represents the scaling factor in percentage. It should be between 10 and 400. Please set the view type first.

split()

Splits window.

startAccessCache(opts)

Starts the session that uses caches to access the data in this worksheet. After finishing the access to the data, closeAccessCache(int) should be invoked with same options to clear all caches and recover normal access mode.

Parameter	Type	Description
opts	Number	AccessCacheOptions

toString()

Returns a string represents the current Worksheet object.

Returns: String — String

unFreezePanes()

Unfreezes panes in the worksheet.

unprotect()

Unprotects worksheet. This method unprotects worksheet which is protected without password.

unprotect(password)

Unprotects worksheet. If the worksheet is protected without a password, you can set a null value or blank string to password parameter.

Parameter	Type	Description
password	String	Password

xmlMapQuery(path, xmlMap)

Query cell areas that mapped/linked to the specific path of xml map. e.g. A xml map element structure: -RootElement |-Attribute1 |-SubElement |-Attribute2 |-Attribute3 To query “Attribute1”, path is “/RootElement/@Attribute1” To query “Attribute2”, path is “/RootElement/SubElement/@Attribute2” To query whole “SubElement”, path is “/RootElement/SubElement”

Parameter	Type	Description
path	String	xml element path
xmlMap	XmlMap	Specify an xml map if you want to query for the specific path within a specific map

Returns: ArrayList — ArrayList CellArea list that mapped/linked to the specific path of xml map, an empty list is returned if nothing is mapped/linked.