Worksheet

Worksheet class

Encapsulates the object that represents a single worksheet.

class Worksheet;

Example

const { Workbook } = require("aspose.cells.node");

var workbook = new Workbook();
var sheet = workbook.getWorksheets().get(0);
//Freeze panes at "AS40" with 10 rows and 10 columns
sheet.freezePanes("AS40", 10, 10);
//Add a hyperlink in Cell A1
sheet.getHyperlinks().add("A1", 1, 1, "http://www.aspose.com");

Methods

Method	Description
getProtection()	Represents the various types of protection options available for a worksheet. Supports advanced protection options in ExcelXP and above version.
getUniqueId()	Gets and sets the unique id, it is same as {15DB5C3C-A5A1-48AF-8F25-3D86AC232D4F}.
setUniqueId(string)	Gets and sets the unique id, it is same as {15DB5C3C-A5A1-48AF-8F25-3D86AC232D4F}.
getWorkbook()	Gets the workbook object which contains this sheet.
getCells()	Gets the Cells collection.
getQueryTables()	Gets QueryTableCollection in the worksheet.
getPivotTables()	Gets all pivot tables in this worksheet.
getType()	Represents worksheet type.
setType(SheetType)	Represents worksheet type.
getName()	Gets or sets the name of the worksheet.
setName(string)	Gets or sets the name of the worksheet.
getShowFormulas()	Indicates whether to show formulas or their results.
setShowFormulas(boolean)	Indicates whether to show formulas or their results.
isGridlinesVisible()	Gets or sets a value indicating whether the gridlines are visible.Default is true.
setIsGridlinesVisible(boolean)	Gets or sets a value indicating whether the gridlines are visible.Default is true.
isRowColumnHeadersVisible()	Gets or sets a value indicating whether the worksheet will display row and column headers. Default is true.
setIsRowColumnHeadersVisible(boolean)	Gets or sets a value indicating whether the worksheet will display row and column headers. Default is true.
getPaneState()	Indicates whether the pane has horizontal or vertical splits, and whether those splits are frozen.
getDisplayZeros()	True if zero values are displayed.
setDisplayZeros(boolean)	True if zero values are displayed.
getDisplayRightToLeft()	Indicates if the specified worksheet is displayed from right to left instead of from left to right. Default is false.
setDisplayRightToLeft(boolean)	Indicates if the specified worksheet is displayed from right to left instead of from left to right. Default is false.
isOutlineShown()	Indicates whether to show outline.
setIsOutlineShown(boolean)	Indicates whether to show outline.
isSelected()	Indicates whether this worksheet is selected when the workbook is opened.
setIsSelected(boolean)	Indicates whether this worksheet is selected when the workbook is opened.
getListObjects()	Gets all ListObjects in this worksheet.
getTabId()	Specifies the internal identifier for the sheet.
setTabId(number)	Specifies the internal identifier for the sheet.
getHorizontalPageBreaks()	Gets the HorizontalPageBreakCollection collection.
getVerticalPageBreaks()	Gets the VerticalPageBreakCollection collection.
getHyperlinks()	Gets the HyperlinkCollection collection.
getPageSetup()	Represents the page setup description in this sheet.
getAutoFilter()	Represents auto filter for the specified worksheet.
getHasAutofilter()	Indicates whether this worksheet has auto filter.
getTransitionEvaluation()	Indicates whether the Transition Formula Evaluation (Lotus compatibility) option is enabled.
setTransitionEvaluation(boolean)	Indicates whether the Transition Formula Evaluation (Lotus compatibility) option is enabled.
getTransitionEntry()	Indicates whether the Transition Formula Entry (Lotus compatibility) option is enabled.
setTransitionEntry(boolean)	Indicates whether the Transition Formula Entry (Lotus compatibility) option is enabled.
getVisibilityType()	Indicates the visible state for this sheet.
setVisibilityType(VisibilityType)	Indicates the visible state for this sheet.
isVisible()	Represents if the worksheet is visible.
setIsVisible(boolean)	Represents if the worksheet is visible.
getSparklineGroups()	Gets the sparkline groups in the worksheet.
getCharts()	Gets a Chart collection
getComments()	Gets the Comment collection.
getPictures()	Gets a Picture collection.
getTextBoxes()	Gets a TextBox collection.
getCheckBoxes()	Gets a CheckBox collection.
getOleObjects()	Represents a collection of OleObject in a worksheet.
getShapes()	Returns all drawing shapes in this worksheet.
getSlicers()	Get the Slicer collection in the worksheet
getTimelines()	Get the Timeline collection in the worksheet
getIndex()	Gets the index of sheet in the worksheet collection.
isProtected()	Indicates if the worksheet is protected.
getValidations()	Gets the data validation setting collection in the worksheet.
getAllowEditRanges()	Gets the allow edit range collection in the worksheet.
getErrorCheckOptions()	Gets error check setting applied on certain ranges.
getOutline()	Gets the outline on this worksheet.
getFirstVisibleRow()	Represents first visible row index.
setFirstVisibleRow(number)	Represents first visible row index.
getFirstVisibleColumn()	Represents first visible column index.
setFirstVisibleColumn(number)	Represents first visible column index.
getZoom()	Represents the scaling factor in percentage. It should be between 10 and 400.
setZoom(number)	Represents the scaling factor in percentage. It should be between 10 and 400.
getViewType()	Gets and sets the view type.
setViewType(ViewType)	Gets and sets the view type.
isPageBreakPreview()	Indicates whether the specified worksheet is shown in normal view or page break preview.
setIsPageBreakPreview(boolean)	Indicates whether the specified worksheet is shown in normal view or page break preview.
isRulerVisible()	Indicates whether the ruler is visible. This property is only applied for page break preview.
setIsRulerVisible(boolean)	Indicates whether the ruler is visible. This property is only applied for page break preview.
getTabColor()	Represents worksheet tab color.
setTabColor(Color)	Represents worksheet tab color.
getCodeName()	Gets worksheet code name.
setCodeName(string)	Gets worksheet code name.
getBackgroundImage()	Gets and sets worksheet background image.
setBackgroundImage(number[])	Gets and sets worksheet background image.
getConditionalFormattings()	Gets the ConditionalFormattings in the worksheet.
getActiveCell()	Gets or sets the active cell in the worksheet.
setActiveCell(string)	Gets or sets the active cell in the worksheet.
getCustomProperties()	Gets an object representing the identifier information associated with a worksheet.
getSmartTagSetting()	Gets all SmartTagCollection objects of the worksheet.
getScenarios()	Gets the collection of Scenario.
getCellWatches()	Gets collection of cells on this worksheet being watched in the ‘watch window’.
dispose()	Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
getPanes()	Gets the window panes.
freezePanes(number, number, number, number)	Freezes panes at the specified cell in the worksheet.
freezePanes(string, number, number)	Freezes panes at the specified cell in the worksheet.
getFreezedPanes()	Gets the freeze panes.
split()	Splits window.
unFreezePanes()	Unfreezes panes in the worksheet.
removeSplit()	Removes split window.
addPageBreaks(string)	Adds page break.
copy(Worksheet)	Copies contents and formats from another worksheet.
copy(Worksheet, CopyOptions)	Copies contents and formats from another worksheet.
autoFitColumn(number, number, number)	Autofits the column width.
autoFitColumn(number)	Autofits the column width.
autoFitColumns()	Autofits all columns in this worksheet.
autoFitColumns(AutoFitterOptions)	Autofits all columns in this worksheet.
autoFitColumns(number, number)	Autofits the columns width.
autoFitColumns(number, number, AutoFitterOptions)	Autofits the columns width.
autoFitColumns(number, number, number, number)	Autofits the columns width.
autoFitColumns(number, number, number, number, AutoFitterOptions)	Autofits the columns width.
autoFitRow(number, number, number)	Autofits the row height.
autoFitRow(number, number, number, AutoFitterOptions)	Autofits the row height.
autoFitRow(number, number, number, number)	Autofits row height in a rectangle range.
autoFitRow(number)	Autofits the row height.
autoFitRows()	Autofits all rows in this worksheet.
autoFitRows(boolean)	Autofits all rows in this worksheet.
autoFitRows(AutoFitterOptions)	Autofits all rows in this worksheet.
autoFitRows(number, number)	Autofits row height in a range.
autoFitRows(number, number, AutoFitterOptions)	Autofits row height in a range.
getAdvancedFilter()	Gets the settings of advanced filter.
advanced_Filter(boolean, string, string, string, boolean)	Filters data using complex criteria.
removeAutoFilter()	Removes the auto filter of the worksheet.
setVisible(boolean, boolean)	Sets the visible options.
selectRange(number, number, number, number, boolean)	Selects a range.
removeAllDrawingObjects()	Removes all drawing objects in this worksheet.
clearComments()	Clears all comments in designer spreadsheet.
protect(ProtectionType)	Protects worksheet.
protect(ProtectionType, string, string)	Protects worksheet.
unprotect()	Unprotects worksheet.
unprotect(string)	Unprotects worksheet.
moveTo(number)	Moves the sheet to another location in the spreadsheet.
replace(string, string)	Replaces all cells’ text with a new string.
getPrintingPageBreaks(ImageOrPrintOptions)	Gets automatic page breaks.
toString()	Returns a string represents the current Worksheet object.
startAccessCache(AccessCacheOptions)	Starts the session that uses caches to access the data in this worksheet.
closeAccessCache(AccessCacheOptions)	Closes the session that uses caches to access the data in this worksheet.
convertFormulaReferenceStyle(string, boolean, number, number)	Converts the formula reference style.
calculateFormula(string)	Calculates a formula.
calculateFormula(string, CalculationOptions)	Calculates a formula expression directly.
calculateFormula(string, FormulaParseOptions, CalculationOptions, number, number, CalculationData)	Calculates a formula expression directly.
calculateFormula(CalculationOptions, boolean)	Calculates all formulas in this worksheet.
calculateArrayFormula(string, CalculationOptions)	Calculates a formula as array formula.
calculateArrayFormula(string, CalculationOptions, number, number)	Calculates a formula as array formula.
calculateArrayFormula(string, FormulaParseOptions, CalculationOptions, number, number, number, number, CalculationData)	Calculates a formula as array formula.
refreshPivotTables()	Refreshes all the PivotTables in this Worksheet.
refreshPivotTables(PivotTableRefreshOption)	Refreshes all the PivotTables in this Worksheet.
isNull()	Checks whether the implementation object is null.

getProtection()

Represents the various types of protection options available for a worksheet. Supports advanced protection options in ExcelXP and above version.

getProtection() : Protection;

Returns

Protection

Remarks

This property can protect worksheet in all versions of Excel file and support advanced protection options in ExcelXP and above version.

getUniqueId()

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

getUniqueId() : string;

setUniqueId(string)

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

setUniqueId(value: string) : void;

Parameters:

Parameter	Type	Description
value	string	The value to set.

getWorkbook()

Gets the workbook object which contains this sheet.

getWorkbook() : Workbook;

Returns

Workbook

getCells()

Gets the Cells collection.

getCells() : Cells;

Returns

Cells

getQueryTables()

Gets QueryTableCollection in the worksheet.

getQueryTables() : QueryTableCollection;

Returns

QueryTableCollection

getPivotTables()

Gets all pivot tables in this worksheet.

getPivotTables() : PivotTableCollection;

Returns

PivotTableCollection

getType()

Represents worksheet type.

getType() : SheetType;

Returns

SheetType

setType(SheetType)

Represents worksheet type.

setType(value: SheetType) : void;

Parameters:

Parameter	Type	Description
value	SheetType	The value to set.

getName()

Gets or sets the name of the worksheet.

getName() : string;

Remarks

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.

setName(string)

Gets or sets the name of the worksheet.

setName(value: string) : void;

Parameters:

Parameter	Type	Description
value	string	The value to set.

Remarks

getShowFormulas()

Indicates whether to show formulas or their results.

getShowFormulas() : boolean;

setShowFormulas(boolean)

Indicates whether to show formulas or their results.

setShowFormulas(value: boolean) : void;

Parameters:

Parameter	Type	Description
value	boolean	The value to set.

isGridlinesVisible()

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

isGridlinesVisible() : boolean;

setIsGridlinesVisible(boolean)

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

setIsGridlinesVisible(value: boolean) : void;

Parameters:

Parameter	Type	Description
value	boolean	The value to set.

isRowColumnHeadersVisible()

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

isRowColumnHeadersVisible() : boolean;

setIsRowColumnHeadersVisible(boolean)

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

setIsRowColumnHeadersVisible(value: boolean) : void;

Parameters:

Parameter	Type	Description
value	boolean	The value to set.

getPaneState()

Indicates whether the pane has horizontal or vertical splits, and whether those splits are frozen.

getPaneState() : PaneStateType;

Returns

PaneStateType

getDisplayZeros()

True if zero values are displayed.

getDisplayZeros() : boolean;

setDisplayZeros(boolean)

True if zero values are displayed.

setDisplayZeros(value: boolean) : void;

Parameters:

Parameter	Type	Description
value	boolean	The value to set.

getDisplayRightToLeft()

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

getDisplayRightToLeft() : boolean;

setDisplayRightToLeft(boolean)

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

setDisplayRightToLeft(value: boolean) : void;

Parameters:

Parameter	Type	Description
value	boolean	The value to set.

isOutlineShown()

Indicates whether to show outline.

isOutlineShown() : boolean;

setIsOutlineShown(boolean)

Indicates whether to show outline.

setIsOutlineShown(value: boolean) : void;

Parameters:

Parameter	Type	Description
value	boolean	The value to set.

isSelected()

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

isSelected() : boolean;

setIsSelected(boolean)

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

setIsSelected(value: boolean) : void;

Parameters:

Parameter	Type	Description
value	boolean	The value to set.

getListObjects()

Gets all ListObjects in this worksheet.

getListObjects() : ListObjectCollection;

Returns

ListObjectCollection

getTabId()

Specifies the internal identifier for the sheet.

getTabId() : number;

setTabId(number)

Specifies the internal identifier for the sheet.

setTabId(value: number) : void;

Parameters:

Parameter	Type	Description
value	number	The value to set.

getHorizontalPageBreaks()

Gets the HorizontalPageBreakCollection collection.

getHorizontalPageBreaks() : HorizontalPageBreakCollection;

Returns

HorizontalPageBreakCollection

getVerticalPageBreaks()

Gets the VerticalPageBreakCollection collection.

getVerticalPageBreaks() : VerticalPageBreakCollection;

Returns

VerticalPageBreakCollection

getHyperlinks()

Gets the HyperlinkCollection collection.

getHyperlinks() : HyperlinkCollection;

Returns

HyperlinkCollection

getPageSetup()

Represents the page setup description in this sheet.

getPageSetup() : PageSetup;

Returns

PageSetup

getAutoFilter()

Represents auto filter for the specified worksheet.

getAutoFilter() : AutoFilter;

Returns

AutoFilter

getHasAutofilter()

Indicates whether this worksheet has auto filter.

getHasAutofilter() : boolean;

getTransitionEvaluation()

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

getTransitionEvaluation() : boolean;

setTransitionEvaluation(boolean)

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

setTransitionEvaluation(value: boolean) : void;

Parameters:

Parameter	Type	Description
value	boolean	The value to set.

getTransitionEntry()

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

getTransitionEntry() : boolean;

setTransitionEntry(boolean)

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

setTransitionEntry(value: boolean) : void;

Parameters:

Parameter	Type	Description
value	boolean	The value to set.

getVisibilityType()

Indicates the visible state for this sheet.

getVisibilityType() : VisibilityType;

Returns

VisibilityType

setVisibilityType(VisibilityType)

Indicates the visible state for this sheet.

setVisibilityType(value: VisibilityType) : void;

Parameters:

Parameter	Type	Description
value	VisibilityType	The value to set.

isVisible()

Represents if the worksheet is visible.

isVisible() : boolean;

setIsVisible(boolean)

Represents if the worksheet is visible.

setIsVisible(value: boolean) : void;

Parameters:

Parameter	Type	Description
value	boolean	The value to set.

getSparklineGroups()

Gets the sparkline groups in the worksheet.

getSparklineGroups() : SparklineGroupCollection;

Returns

SparklineGroupCollection

getCharts()

Gets a Chart collection

getCharts() : ChartCollection;

Returns

ChartCollection

getComments()

Gets the Comment collection.

getComments() : CommentCollection;

Returns

CommentCollection

getPictures()

Gets a Picture collection.

getPictures() : PictureCollection;

Returns

PictureCollection

getTextBoxes()

Gets a TextBox collection.

getTextBoxes() : TextBoxCollection;

Returns

TextBoxCollection

getCheckBoxes()

Gets a CheckBox collection.

getCheckBoxes() : CheckBoxCollection;

Returns

CheckBoxCollection

getOleObjects()

Represents a collection of OleObject in a worksheet.

getOleObjects() : OleObjectCollection;

Returns

OleObjectCollection

getShapes()

Returns all drawing shapes in this worksheet.

getShapes() : ShapeCollection;

Returns

ShapeCollection

getSlicers()

Get the Slicer collection in the worksheet

getSlicers() : SlicerCollection;

Returns

SlicerCollection

getTimelines()

Get the Timeline collection in the worksheet

getTimelines() : TimelineCollection;

Returns

TimelineCollection

getIndex()

Gets the index of sheet in the worksheet collection.

getIndex() : number;

isProtected()

Indicates if the worksheet is protected.

isProtected() : boolean;

getValidations()

Gets the data validation setting collection in the worksheet.

getValidations() : ValidationCollection;

Returns

ValidationCollection

getAllowEditRanges()

Gets the allow edit range collection in the worksheet.

getAllowEditRanges() : ProtectedRangeCollection;

Returns

ProtectedRangeCollection

getErrorCheckOptions()

Gets error check setting applied on certain ranges.

getErrorCheckOptions() : ErrorCheckOptionCollection;

Returns

ErrorCheckOptionCollection

getOutline()

Gets the outline on this worksheet.

getOutline() : Outline;

Returns

Outline

getFirstVisibleRow()

Represents first visible row index.

getFirstVisibleRow() : number;

setFirstVisibleRow(number)

Represents first visible row index.

setFirstVisibleRow(value: number) : void;

Parameters:

Parameter	Type	Description
value	number	The value to set.

getFirstVisibleColumn()

Represents first visible column index.

getFirstVisibleColumn() : number;

setFirstVisibleColumn(number)

Represents first visible column index.

setFirstVisibleColumn(value: number) : void;

Parameters:

Parameter	Type	Description
value	number	The value to set.

getZoom()

Represents the scaling factor in percentage. It should be between 10 and 400.

getZoom() : number;

Remarks

Please set the view type first.

setZoom(number)

Represents the scaling factor in percentage. It should be between 10 and 400.

setZoom(value: number) : void;

Parameters:

Parameter	Type	Description
value	number	The value to set.

Remarks

Please set the view type first.

getViewType()

Gets and sets the view type.

getViewType() : ViewType;

Returns

ViewType

setViewType(ViewType)

Gets and sets the view type.

setViewType(value: ViewType) : void;

Parameters:

Parameter	Type	Description
value	ViewType	The value to set.

isPageBreakPreview()

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

isPageBreakPreview() : boolean;

setIsPageBreakPreview(boolean)

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

setIsPageBreakPreview(value: boolean) : void;

Parameters:

Parameter	Type	Description
value	boolean	The value to set.

isRulerVisible()

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

isRulerVisible() : boolean;

setIsRulerVisible(boolean)

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

setIsRulerVisible(value: boolean) : void;

Parameters:

Parameter	Type	Description
value	boolean	The value to set.

getTabColor()

Represents worksheet tab color.

getTabColor() : Color;

Returns

Color

Remarks

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

setTabColor(Color)

Represents worksheet tab color.

setTabColor(value: Color) : void;

Parameters:

Parameter	Type	Description
value	Color	The value to set.

Remarks

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

getCodeName()

Gets worksheet code name.

getCodeName() : string;

setCodeName(string)

Gets worksheet code name.

setCodeName(value: string) : void;

Parameters:

Parameter	Type	Description
value	string	The value to set.

getBackgroundImage()

Gets and sets worksheet background image.

getBackgroundImage() : number[];

Returns

number[]

setBackgroundImage(number[])

Gets and sets worksheet background image.

setBackgroundImage(value: number[]) : void;

Parameters:

Parameter	Type	Description
value	number[]	The value to set.

getConditionalFormattings()

Gets the ConditionalFormattings in the worksheet.

getConditionalFormattings() : ConditionalFormattingCollection;

Returns

ConditionalFormattingCollection

getActiveCell()

Gets or sets the active cell in the worksheet.

getActiveCell() : string;

setActiveCell(string)

Gets or sets the active cell in the worksheet.

setActiveCell(value: string) : void;

Parameters:

Parameter	Type	Description
value	string	The value to set.

getCustomProperties()

Gets an object representing the identifier information associated with a worksheet.

getCustomProperties() : CustomPropertyCollection;

Returns

CustomPropertyCollection

Remarks

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.

getSmartTagSetting()

Gets all SmartTagCollection objects of the worksheet.

getSmartTagSetting() : SmartTagSetting;

Returns

SmartTagSetting

getScenarios()

Gets the collection of Scenario.

getScenarios() : ScenarioCollection;

Returns

ScenarioCollection

getCellWatches()

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

getCellWatches() : CellWatchCollection;

Returns

CellWatchCollection

dispose()

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

dispose() : void;

getPanes()

Gets the window panes.

getPanes() : PaneCollection;

Returns

PaneCollection

Remarks

If the window is not split or frozen.

freezePanes(number, number, number, number)

Freezes panes at the specified cell in the worksheet.

freezePanes(row: number, column: number, freezedRows: number, freezedColumns: number) : void;

Parameters:

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.

Remarks

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.

freezePanes(string, number, number)

Freezes panes at the specified cell in the worksheet.

freezePanes(cellName: string, freezedRows: number, freezedColumns: number) : void;

Parameters:

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.

Remarks

Row index and column index cannot all be zero. Number of rows and number of columns also cannot all be zero.

getFreezedPanes()

Gets the freeze panes.

getFreezedPanes() : number[];

Returns

Return null means the worksheet is not frozen 0:Row index;1:column;2:freezedRows;3:freezedRows

split()

Splits window.

split() : void;

unFreezePanes()

Unfreezes panes in the worksheet.

unFreezePanes() : void;

removeSplit()

Removes split window.

removeSplit() : void;

addPageBreaks(string)

Adds page break.

addPageBreaks(cellName: string) : void;

Parameters:

Parameter	Type	Description
cellName	string

copy(Worksheet)

Copies contents and formats from another worksheet.

copy(sourceSheet: Worksheet) : void;

Parameters:

Parameter	Type	Description
sourceSheet	Worksheet	Source worksheet.

copy(Worksheet, CopyOptions)

Copies contents and formats from another worksheet.

copy(sourceSheet: Worksheet, copyOptions: CopyOptions) : void;

Parameters:

Parameter	Type	Description
sourceSheet	Worksheet	Source worksheet.
copyOptions	CopyOptions

Remarks

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.

autoFitColumn(number, number, number)

Autofits the column width.

autoFitColumn(columnIndex: number, firstRow: number, lastRow: number) : void;

Parameters:

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

Remarks

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

autoFitColumn(number)

Autofits the column width.

autoFitColumn(columnIndex: number) : void;

Parameters:

Parameter	Type	Description
columnIndex	number	Column index.

Remarks

AutoFitColumn is an imprecise function.

autoFitColumns()

Autofits all columns in this worksheet.

autoFitColumns() : void;

autoFitColumns(AutoFitterOptions)

Autofits all columns in this worksheet.

autoFitColumns(options: AutoFitterOptions) : void;

Parameters:

Parameter	Type	Description
options	AutoFitterOptions	The auto fitting options

autoFitColumns(number, number)

Autofits the columns width.

autoFitColumns(firstColumn: number, lastColumn: number) : void;

Parameters:

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

Remarks

AutoFitColumn is an imprecise function.

autoFitColumns(number, number, AutoFitterOptions)

Autofits the columns width.

autoFitColumns(firstColumn: number, lastColumn: number, options: AutoFitterOptions) : void;

Parameters:

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

Remarks

AutoFitColumn is an imprecise function.

autoFitColumns(number, number, number, number)

Autofits the columns width.

autoFitColumns(firstRow: number, firstColumn: number, lastRow: number, lastColumn: number) : void;

Parameters:

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

Remarks

AutoFitColumn is an imprecise function.

autoFitColumns(number, number, number, number, AutoFitterOptions)

Autofits the columns width.

autoFitColumns(firstRow: number, firstColumn: number, lastRow: number, lastColumn: number, options: AutoFitterOptions) : void;

Parameters:

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

Remarks

AutoFitColumn is an imprecise function.

autoFitRow(number, number, number)

Autofits the row height.

autoFitRow(rowIndex: number, firstColumn: number, lastColumn: number) : void;

Parameters:

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

Remarks

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

autoFitRow(number, number, number, AutoFitterOptions)

Autofits the row height.

autoFitRow(rowIndex: number, firstColumn: number, lastColumn: number, options: AutoFitterOptions) : void;

Parameters:

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

Remarks

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

autoFitRow(number, number, number, number)

Autofits row height in a rectangle range.

autoFitRow(startRow: number, endRow: number, startColumn: number, endColumn: number) : void;

Parameters:

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

autoFitRow(number)

Autofits the row height.

autoFitRow(rowIndex: number) : void;

Parameters:

Parameter	Type	Description
rowIndex	number	Row index.

Remarks

AutoFitRow is an imprecise function.

autoFitRows()

Autofits all rows in this worksheet.

autoFitRows() : void;

autoFitRows(boolean)

Autofits all rows in this worksheet.

autoFitRows(onlyAuto: boolean) : void;

Parameters:

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

autoFitRows(AutoFitterOptions)

Autofits all rows in this worksheet.

autoFitRows(options: AutoFitterOptions) : void;

Parameters:

Parameter	Type	Description
options	AutoFitterOptions	The auto fitter options

autoFitRows(number, number)

Autofits row height in a range.

autoFitRows(startRow: number, endRow: number) : void;

Parameters:

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

autoFitRows(number, number, AutoFitterOptions)

Autofits row height in a range.

autoFitRows(startRow: number, endRow: number, options: AutoFitterOptions) : void;

Parameters:

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

getAdvancedFilter()

Gets the settings of advanced filter.

getAdvancedFilter() : AdvancedFilter;

Returns

AdvancedFilter

advanced_Filter(boolean, string, string, string, boolean)

Filters data using complex criteria.

advanced_Filter(isFilter: boolean, listRange: string, criteriaRange: string, copyTo: string, uniqueRecordOnly: boolean) : void;

Parameters:

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.

removeAutoFilter()

Removes the auto filter of the worksheet.

removeAutoFilter() : void;

setVisible(boolean, boolean)

Sets the visible options.

setVisible(isVisible: boolean, ignoreError: boolean) : void;

Parameters:

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

selectRange(number, number, number, number, boolean)

Selects a range.

selectRange(startRow: number, startColumn: number, totalRows: number, totalColumns: number, removeOthers: boolean) : void;

Parameters:

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.

removeAllDrawingObjects()

Removes all drawing objects in this worksheet.

removeAllDrawingObjects() : void;

clearComments()

Clears all comments in designer spreadsheet.

clearComments() : void;

protect(ProtectionType)

Protects worksheet.

protect(type: ProtectionType) : void;

Parameters:

Parameter	Type	Description
type	ProtectionType	Protection type.

Remarks

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

protect(ProtectionType, string, string)

Protects worksheet.

protect(type: ProtectionType, password: string, oldPassword: string) : void;

Parameters:

Parameter	Type	Description
type	ProtectionType	Protection type.
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.

Remarks

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

Example

const { Workbook, ProtectionType } = require("aspose.cells.node");

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

unprotect()

Unprotects worksheet.

unprotect() : void;

Remarks

This method unprotects worksheet which is protected without password.

unprotect(string)

Unprotects worksheet.

unprotect(password: string) : void;

Parameters:

Parameter	Type	Description
password	string	Password

Remarks

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

moveTo(number)

Moves the sheet to another location in the spreadsheet.

moveTo(index: number) : void;

Parameters:

Parameter	Type	Description
index	number	Destination sheet index.

replace(string, string)

Replaces all cells’ text with a new string.

replace(oldString: string, newString: string) : number;

Parameters:

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

getPrintingPageBreaks(ImageOrPrintOptions)

Gets automatic page breaks.

getPrintingPageBreaks(options: ImageOrPrintOptions) : CellArea[];

Parameters:

Parameter	Type	Description
options	ImageOrPrintOptions	The print options

Returns

The automatic page breaks areas.

Remarks

Each cell area represents a paper.

toString()

Returns a string represents the current Worksheet object.

toString() : string;

startAccessCache(AccessCacheOptions)

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

startAccessCache(opts: AccessCacheOptions) : void;

Parameters:

Parameter	Type	Description
opts	AccessCacheOptions	options of data access

Remarks

After finishing the access to the data, CloseAccessCache(AccessCacheOptions) should be invoked with same options to clear all caches and recover normal access mode.

closeAccessCache(AccessCacheOptions)

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

closeAccessCache(opts: AccessCacheOptions) : void;

Parameters:

Parameter	Type	Description
opts	AccessCacheOptions	options of data access

convertFormulaReferenceStyle(string, boolean, number, number)

Converts the formula reference style.

convertFormulaReferenceStyle(formula: string, toR1C1: boolean, baseCellRow: number, baseCellColumn: number) : string;

Parameters:

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

The converted formula.

calculateFormula(string)

Calculates a formula.

calculateFormula(formula: string) : object;

Parameters:

Parameter	Type	Description
formula	string	Formula to be calculated.

Returns

Calculated formula result.

calculateFormula(string, CalculationOptions)

Calculates a formula expression directly.

calculateFormula(formula: string, opts: CalculationOptions) : object;

Parameters:

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

Returns

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

Remarks

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(string, CalculationOptions)](../calculatearrayformula(string, calculationoptions)/) instead.

calculateFormula(string, FormulaParseOptions, CalculationOptions, number, number, CalculationData)

Calculates a formula expression directly.

calculateFormula(formula: string, pOpts: FormulaParseOptions, cOpts: CalculationOptions, baseCellRow: number, baseCellColumn: number, calculationData: CalculationData) : object;

Parameters:

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 AbstractCalculationEngine.Calculate(CalculationData).

Returns

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

Remarks

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(string, FormulaParseOptions, CalculationOptions, int, int, int, int, CalculationData)](../calculatearrayformula(string, formulaparseoptions, calculationoptions, int, int, int, int, calculationdata)/) instead.

calculateFormula(CalculationOptions, boolean)

Calculates all formulas in this worksheet.

calculateFormula(options: CalculationOptions, recursive: boolean) : void;

Parameters:

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.

calculateArrayFormula(string, CalculationOptions)

Calculates a formula as array formula.

calculateArrayFormula(formula: string, opts: CalculationOptions) : object[][];

Parameters:

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

Returns

object[][]

calculateArrayFormula(string, CalculationOptions, number, number)

Calculates a formula as array formula.

calculateArrayFormula(formula: string, opts: CalculationOptions, maxRowCount: number, maxColumnCount: number) : object[][];

Parameters:

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

Calculated formula result.

Remarks

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.

calculateArrayFormula(string, FormulaParseOptions, CalculationOptions, number, number, number, number, CalculationData)

Calculates a formula as array formula.

calculateArrayFormula(formula: string, pOpts: FormulaParseOptions, cOpts: CalculationOptions, baseCellRow: number, baseCellColumn: number, maxRowCount: number, maxColumnCount: number, calculationData: CalculationData) : object[][];

Parameters:

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 AbstractCalculationEngine.Calculate(CalculationData).

Returns

Calculated formula result.

Remarks

refreshPivotTables()

Refreshes all the PivotTables in this Worksheet.

refreshPivotTables() : void;

refreshPivotTables(PivotTableRefreshOption)

Refreshes all the PivotTables in this Worksheet.

refreshPivotTables(option: PivotTableRefreshOption) : boolean;

Parameters:

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

isNull()

Checks whether the implementation object is null.

isNull() : boolean;