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.worksheets.get(0);
//Freeze panes at "AS40" with 10 rows and 10 columns
sheet.freezePanes("AS40", 10, 10);
//Add a hyperlink in Cell A1
sheet.hyperlinks.add("A1", 1, 1, "https://www.aspose.com");

Properties

PropertyTypeDescription
protectionProtectionReadonly. Represents the various types of protection options available for a worksheet. Supports advanced protection options in ExcelXP and above version.
uniqueIdstringGets and sets the unique id, it is same as {15DB5C3C-A5A1-48AF-8F25-3D86AC232D4F}.
workbookWorkbookReadonly. Gets the workbook object which contains this sheet.
cellsCellsReadonly. Gets the Cells collection.
queryTablesQueryTableCollectionReadonly. Gets QueryTableCollection in the worksheet.
typeSheetTypeRepresents worksheet type.
namestringGets or sets the name of the worksheet.
showFormulasbooleanIndicates whether to show formulas or their results.
isGridlinesVisiblebooleanGets or sets a value indicating whether the gridlines are visible.Default is true.
isRowColumnHeadersVisiblebooleanGets or sets a value indicating whether the worksheet will display row and column headers. Default is true.
paneStatePaneStateTypeReadonly. Indicates whether the pane has horizontal or vertical splits, and whether those splits are frozen.
displayZerosbooleanTrue if zero values are displayed.
displayRightToLeftbooleanIndicates if the specified worksheet is displayed from right to left instead of from left to right. Default is false.
isOutlineShownbooleanIndicates whether to show outline.
isSelectedbooleanIndicates whether this worksheet is selected when the workbook is opened.
pivotTablesPivotTableCollectionReadonly. Gets all pivot tables in this worksheet.
listObjectsListObjectCollectionReadonly. Gets all ListObjects in this worksheet.
tabIdnumberSpecifies the internal identifier for the sheet.
horizontalPageBreaksHorizontalPageBreakCollectionReadonly. Gets the HorizontalPageBreakCollection collection.
verticalPageBreaksVerticalPageBreakCollectionReadonly. Gets the VerticalPageBreakCollection collection.
hyperlinksHyperlinkCollectionReadonly. Gets the HyperlinkCollection collection.
pageSetupPageSetupReadonly. Represents the page setup description in this sheet.
autoFilterAutoFilterReadonly. Represents auto filter for the specified worksheet.
hasAutofilterbooleanReadonly. Indicates whether this worksheet has auto filter.
transitionEvaluationbooleanIndicates whether the Transition Formula Evaluation (Lotus compatibility) option is enabled.
transitionEntrybooleanIndicates whether the Transition Formula Entry (Lotus compatibility) option is enabled.
visibilityTypeVisibilityTypeIndicates the visible state for this sheet.
isVisiblebooleanRepresents if the worksheet is visible.
sparklineGroupsSparklineGroupCollectionReadonly. Gets the sparkline groups in the worksheet.
chartsChartCollectionReadonly. Gets a Chart collection
commentsCommentCollectionReadonly. Gets the Comment collection.
picturesPictureCollectionReadonly. Gets a Picture collection.
textBoxesTextBoxCollectionReadonly. Gets a TextBox collection.
checkBoxesCheckBoxCollectionReadonly. Gets a CheckBox collection.
oleObjectsOleObjectCollectionReadonly. Represents a collection of OleObject in a worksheet.
shapesShapeCollectionReadonly. Returns all drawing shapes in this worksheet.
slicersSlicerCollectionReadonly. Get the Slicer collection in the worksheet
timelinesTimelineCollectionReadonly. Get the Timeline collection in the worksheet
indexnumberReadonly. Gets the index of sheet in the worksheet collection.
isProtectedbooleanReadonly. Indicates if the worksheet is protected.
validationsValidationCollectionReadonly. Gets the data validation setting collection in the worksheet.
allowEditRangesProtectedRangeCollectionReadonly. Gets the allow edit range collection in the worksheet.
errorCheckOptionsErrorCheckOptionCollectionReadonly. Gets error check setting applied on certain ranges.
outlineOutlineReadonly. Gets the outline on this worksheet.
firstVisibleRownumberRepresents first visible row index.
firstVisibleColumnnumberRepresents first visible column index.
zoomnumberRepresents the scaling factor in percentage. It should be between 10 and 400.
viewTypeViewTypeGets and sets the view type.
isPageBreakPreviewbooleanIndicates whether the specified worksheet is shown in normal view or page break preview.
isRulerVisiblebooleanIndicates whether the ruler is visible. This property is only applied for page break preview.
tabColorColorRepresents worksheet tab color.
gridlineColorColorGets and sets the color of gridline
codeNamestringGets worksheet code name.
backgroundImageUint8ArrayGets and sets worksheet background image.
conditionalFormattingsConditionalFormattingCollectionReadonly. Gets the ConditionalFormattings in the worksheet.
activeCellstringGets or sets the active cell in the worksheet.
customPropertiesCustomPropertyCollectionReadonly. Gets an object representing the identifier information associated with a worksheet.
smartTagSettingSmartTagSettingReadonly. Gets all SmartTagCollection objects of the worksheet.
scenariosScenarioCollectionReadonly. Gets the collection of Scenario.
cellWatchesCellWatchCollectionReadonly. Gets collection of cells on this worksheet being watched in the ‘watch window’.

Methods

MethodDescription
getProtection()@deprecated. Please use the ‘protection’ property instead. Represents the various types of protection options available for a worksheet. Supports advanced protection options in ExcelXP and above version.
getUniqueId()@deprecated. Please use the ‘uniqueId’ property instead. Gets and sets the unique id, it is same as {15DB5C3C-A5A1-48AF-8F25-3D86AC232D4F}.
setUniqueId(string)@deprecated. Please use the ‘uniqueId’ property instead. Gets and sets the unique id, it is same as {15DB5C3C-A5A1-48AF-8F25-3D86AC232D4F}.
getWorkbook()@deprecated. Please use the ‘workbook’ property instead. Gets the workbook object which contains this sheet.
getCells()@deprecated. Please use the ‘cells’ property instead. Gets the Cells collection.
getQueryTables()@deprecated. Please use the ‘queryTables’ property instead. Gets QueryTableCollection in the worksheet.
getType()@deprecated. Please use the ’type’ property instead. Represents worksheet type.
setType(SheetType)@deprecated. Please use the ’type’ property instead. Represents worksheet type.
getName()@deprecated. Please use the ’name’ property instead. Gets or sets the name of the worksheet.
setName(string)@deprecated. Please use the ’name’ property instead. Gets or sets the name of the worksheet.
getShowFormulas()@deprecated. Please use the ‘showFormulas’ property instead. Indicates whether to show formulas or their results.
setShowFormulas(boolean)@deprecated. Please use the ‘showFormulas’ property instead. Indicates whether to show formulas or their results.
isGridlinesVisible()@deprecated. Please use the ‘isGridlinesVisible’ property instead. Gets or sets a value indicating whether the gridlines are visible.Default is true.
setIsGridlinesVisible(boolean)@deprecated. Please use the ‘isGridlinesVisible’ property instead. Gets or sets a value indicating whether the gridlines are visible.Default is true.
isRowColumnHeadersVisible()@deprecated. Please use the ‘isRowColumnHeadersVisible’ property instead. Gets or sets a value indicating whether the worksheet will display row and column headers. Default is true.
setIsRowColumnHeadersVisible(boolean)@deprecated. Please use the ‘isRowColumnHeadersVisible’ property instead. Gets or sets a value indicating whether the worksheet will display row and column headers. Default is true.
getPaneState()@deprecated. Please use the ‘paneState’ property instead. Indicates whether the pane has horizontal or vertical splits, and whether those splits are frozen.
getDisplayZeros()@deprecated. Please use the ‘displayZeros’ property instead. True if zero values are displayed.
setDisplayZeros(boolean)@deprecated. Please use the ‘displayZeros’ property instead. True if zero values are displayed.
getDisplayRightToLeft()@deprecated. Please use the ‘displayRightToLeft’ property instead. Indicates if the specified worksheet is displayed from right to left instead of from left to right. Default is false.
setDisplayRightToLeft(boolean)@deprecated. Please use the ‘displayRightToLeft’ property instead. Indicates if the specified worksheet is displayed from right to left instead of from left to right. Default is false.
isOutlineShown()@deprecated. Please use the ‘isOutlineShown’ property instead. Indicates whether to show outline.
setIsOutlineShown(boolean)@deprecated. Please use the ‘isOutlineShown’ property instead. Indicates whether to show outline.
isSelected()@deprecated. Please use the ‘isSelected’ property instead. Indicates whether this worksheet is selected when the workbook is opened.
setIsSelected(boolean)@deprecated. Please use the ‘isSelected’ property instead. Indicates whether this worksheet is selected when the workbook is opened.
getPivotTables()@deprecated. Please use the ‘pivotTables’ property instead. Gets all pivot tables in this worksheet.
getListObjects()@deprecated. Please use the ’listObjects’ property instead. Gets all ListObjects in this worksheet.
getTabId()@deprecated. Please use the ’tabId’ property instead. Specifies the internal identifier for the sheet.
setTabId(number)@deprecated. Please use the ’tabId’ property instead. Specifies the internal identifier for the sheet.
getHorizontalPageBreaks()@deprecated. Please use the ‘horizontalPageBreaks’ property instead. Gets the HorizontalPageBreakCollection collection.
getVerticalPageBreaks()@deprecated. Please use the ‘verticalPageBreaks’ property instead. Gets the VerticalPageBreakCollection collection.
getHyperlinks()@deprecated. Please use the ‘hyperlinks’ property instead. Gets the HyperlinkCollection collection.
getPageSetup()@deprecated. Please use the ‘pageSetup’ property instead. Represents the page setup description in this sheet.
getAutoFilter()@deprecated. Please use the ‘autoFilter’ property instead. Represents auto filter for the specified worksheet.
getHasAutofilter()@deprecated. Please use the ‘hasAutofilter’ property instead. Indicates whether this worksheet has auto filter.
getTransitionEvaluation()@deprecated. Please use the ’transitionEvaluation’ property instead. Indicates whether the Transition Formula Evaluation (Lotus compatibility) option is enabled.
setTransitionEvaluation(boolean)@deprecated. Please use the ’transitionEvaluation’ property instead. Indicates whether the Transition Formula Evaluation (Lotus compatibility) option is enabled.
getTransitionEntry()@deprecated. Please use the ’transitionEntry’ property instead. Indicates whether the Transition Formula Entry (Lotus compatibility) option is enabled.
setTransitionEntry(boolean)@deprecated. Please use the ’transitionEntry’ property instead. Indicates whether the Transition Formula Entry (Lotus compatibility) option is enabled.
getVisibilityType()@deprecated. Please use the ‘visibilityType’ property instead. Indicates the visible state for this sheet.
setVisibilityType(VisibilityType)@deprecated. Please use the ‘visibilityType’ property instead. Indicates the visible state for this sheet.
isVisible()@deprecated. Please use the ‘isVisible’ property instead. Represents if the worksheet is visible.
setIsVisible(boolean)@deprecated. Please use the ‘isVisible’ property instead. Represents if the worksheet is visible.
getSparklineGroups()@deprecated. Please use the ‘sparklineGroups’ property instead. Gets the sparkline groups in the worksheet.
getCharts()@deprecated. Please use the ‘charts’ property instead. Gets a Chart collection
getComments()@deprecated. Please use the ‘comments’ property instead. Gets the Comment collection.
getPictures()@deprecated. Please use the ‘pictures’ property instead. Gets a Picture collection.
getTextBoxes()@deprecated. Please use the ’textBoxes’ property instead. Gets a TextBox collection.
getCheckBoxes()@deprecated. Please use the ‘checkBoxes’ property instead. Gets a CheckBox collection.
getOleObjects()@deprecated. Please use the ‘oleObjects’ property instead. Represents a collection of OleObject in a worksheet.
getShapes()@deprecated. Please use the ‘shapes’ property instead. Returns all drawing shapes in this worksheet.
getSlicers()@deprecated. Please use the ‘slicers’ property instead. Get the Slicer collection in the worksheet
getTimelines()@deprecated. Please use the ’timelines’ property instead. Get the Timeline collection in the worksheet
getIndex()@deprecated. Please use the ‘index’ property instead. Gets the index of sheet in the worksheet collection.
isProtected()@deprecated. Please use the ‘isProtected’ property instead. Indicates if the worksheet is protected.
getValidations()@deprecated. Please use the ‘validations’ property instead. Gets the data validation setting collection in the worksheet.
getAllowEditRanges()@deprecated. Please use the ‘allowEditRanges’ property instead. Gets the allow edit range collection in the worksheet.
getErrorCheckOptions()@deprecated. Please use the ’errorCheckOptions’ property instead. Gets error check setting applied on certain ranges.
getOutline()@deprecated. Please use the ‘outline’ property instead. Gets the outline on this worksheet.
getFirstVisibleRow()@deprecated. Please use the ‘firstVisibleRow’ property instead. Represents first visible row index.
setFirstVisibleRow(number)@deprecated. Please use the ‘firstVisibleRow’ property instead. Represents first visible row index.
getFirstVisibleColumn()@deprecated. Please use the ‘firstVisibleColumn’ property instead. Represents first visible column index.
setFirstVisibleColumn(number)@deprecated. Please use the ‘firstVisibleColumn’ property instead. Represents first visible column index.
getZoom()@deprecated. Please use the ‘zoom’ property instead. Represents the scaling factor in percentage. It should be between 10 and 400.
setZoom(number)@deprecated. Please use the ‘zoom’ property instead. Represents the scaling factor in percentage. It should be between 10 and 400.
getViewType()@deprecated. Please use the ‘viewType’ property instead. Gets and sets the view type.
setViewType(ViewType)@deprecated. Please use the ‘viewType’ property instead. Gets and sets the view type.
isPageBreakPreview()@deprecated. Please use the ‘isPageBreakPreview’ property instead. Indicates whether the specified worksheet is shown in normal view or page break preview.
setIsPageBreakPreview(boolean)@deprecated. Please use the ‘isPageBreakPreview’ property instead. Indicates whether the specified worksheet is shown in normal view or page break preview.
isRulerVisible()@deprecated. Please use the ‘isRulerVisible’ property instead. Indicates whether the ruler is visible. This property is only applied for page break preview.
setIsRulerVisible(boolean)@deprecated. Please use the ‘isRulerVisible’ property instead. Indicates whether the ruler is visible. This property is only applied for page break preview.
getTabColor()@deprecated. Please use the ’tabColor’ property instead. Represents worksheet tab color.
setTabColor(Color)@deprecated. Please use the ’tabColor’ property instead. Represents worksheet tab color.
getGridlineColor()@deprecated. Please use the ‘gridlineColor’ property instead. Gets and sets the color of gridline
setGridlineColor(Color)@deprecated. Please use the ‘gridlineColor’ property instead. Gets and sets the color of gridline
getCodeName()@deprecated. Please use the ‘codeName’ property instead. Gets worksheet code name.
setCodeName(string)@deprecated. Please use the ‘codeName’ property instead. Gets worksheet code name.
getBackgroundImage()@deprecated. Please use the ‘backgroundImage’ property instead. Gets and sets worksheet background image.
setBackgroundImage(Uint8Array)@deprecated. Please use the ‘backgroundImage’ property instead. Gets and sets worksheet background image.
getConditionalFormattings()@deprecated. Please use the ‘conditionalFormattings’ property instead. Gets the ConditionalFormattings in the worksheet.
getActiveCell()@deprecated. Please use the ‘activeCell’ property instead. Gets or sets the active cell in the worksheet.
setActiveCell(string)@deprecated. Please use the ‘activeCell’ property instead. Gets or sets the active cell in the worksheet.
getCustomProperties()@deprecated. Please use the ‘customProperties’ property instead. Gets an object representing the identifier information associated with a worksheet.
getSmartTagSetting()@deprecated. Please use the ‘smartTagSetting’ property instead. Gets all SmartTagCollection objects of the worksheet.
getScenarios()@deprecated. Please use the ‘scenarios’ property instead. Gets the collection of Scenario.
getCellWatches()@deprecated. Please use the ‘cellWatches’ property instead. 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.
autoFitColumnAsync(number, number, number)Autofits the column width.
autoFitColumnAsync(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.
autoFitColumnsAsync()Autofits all columns in this worksheet.
autoFitColumnsAsync(AutoFitterOptions)Autofits all columns in this worksheet.
autoFitColumnsAsync(number, number)Autofits the columns width.
autoFitColumnsAsync(number, number, AutoFitterOptions)Autofits the columns width.
autoFitColumnsAsync(number, number, number, number)Autofits the columns width.
autoFitColumnsAsync(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.
autoFitRowAsync(number, number, number)Autofits the row height.
autoFitRowAsync(number, number, number, AutoFitterOptions)Autofits the row height.
autoFitRowAsync(number, number, number, number)Autofits row height in a rectangle range.
autoFitRowAsync(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.
autoFitRowsAsync()Autofits all rows in this worksheet.
autoFitRowsAsync(boolean)Autofits all rows in this worksheet.
autoFitRowsAsync(AutoFitterOptions)Autofits all rows in this worksheet.
autoFitRowsAsync(number, number)Autofits row height in a range.
autoFitRowsAsync(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.
getSelectedAreas()Gets selected ranges of cells in the designer spreadsheet.
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.
calculateFormulaAsync(string)Calculates a formula.
calculateFormulaAsync(string, CalculationOptions)Calculates a formula expression directly.
calculateFormulaAsync(string, FormulaParseOptions, CalculationOptions, number, number, CalculationData)Calculates a formula expression directly.
calculateFormulaAsync(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.
calculateArrayFormulaAsync(string, CalculationOptions)Calculates a formula as array formula.
calculateArrayFormulaAsync(string, CalculationOptions, number, number)Calculates a formula as array formula.
calculateArrayFormulaAsync(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.
refreshPivotTablesAsync()Refreshes all the PivotTables in this Worksheet.
refreshPivotTablesAsync(PivotTableRefreshOption)Refreshes all the PivotTables in this Worksheet.
isNull()Checks whether the implementation object is null.

protection

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

protection : Protection;

Remarks

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

uniqueId

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

uniqueId : string;

workbook

Readonly. Gets the workbook object which contains this sheet.

workbook : Workbook;

cells

Readonly. Gets the Cells collection.

cells : Cells;

queryTables

Readonly. Gets QueryTableCollection in the worksheet.

queryTables : QueryTableCollection;

type

Represents worksheet type.

type : SheetType;

name

Gets or sets the name of the worksheet.

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

showFormulas

Indicates whether to show formulas or their results.

showFormulas : boolean;

isGridlinesVisible

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

isGridlinesVisible : boolean;

isRowColumnHeadersVisible

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

isRowColumnHeadersVisible : boolean;

paneState

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

paneState : PaneStateType;

displayZeros

True if zero values are displayed.

displayZeros : boolean;

displayRightToLeft

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

displayRightToLeft : boolean;

isOutlineShown

Indicates whether to show outline.

isOutlineShown : boolean;

isSelected

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

isSelected : boolean;

pivotTables

Readonly. Gets all pivot tables in this worksheet.

pivotTables : PivotTableCollection;

listObjects

Readonly. Gets all ListObjects in this worksheet.

listObjects : ListObjectCollection;

tabId

Specifies the internal identifier for the sheet.

tabId : number;

horizontalPageBreaks

Readonly. Gets the HorizontalPageBreakCollection collection.

horizontalPageBreaks : HorizontalPageBreakCollection;

verticalPageBreaks

Readonly. Gets the VerticalPageBreakCollection collection.

verticalPageBreaks : VerticalPageBreakCollection;

Readonly. Gets the HyperlinkCollection collection.

hyperlinks : HyperlinkCollection;

pageSetup

Readonly. Represents the page setup description in this sheet.

pageSetup : PageSetup;

autoFilter

Readonly. Represents auto filter for the specified worksheet.

autoFilter : AutoFilter;

hasAutofilter

Readonly. Indicates whether this worksheet has auto filter.

hasAutofilter : boolean;

transitionEvaluation

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

transitionEvaluation : boolean;

transitionEntry

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

transitionEntry : boolean;

visibilityType

Indicates the visible state for this sheet.

visibilityType : VisibilityType;

isVisible

Represents if the worksheet is visible.

isVisible : boolean;

sparklineGroups

Readonly. Gets the sparkline groups in the worksheet.

sparklineGroups : SparklineGroupCollection;

charts

Readonly. Gets a Chart collection

charts : ChartCollection;

comments

Readonly. Gets the Comment collection.

comments : CommentCollection;

pictures

Readonly. Gets a Picture collection.

pictures : PictureCollection;

textBoxes

Readonly. Gets a TextBox collection.

textBoxes : TextBoxCollection;

checkBoxes

Readonly. Gets a CheckBox collection.

checkBoxes : CheckBoxCollection;

oleObjects

Readonly. Represents a collection of OleObject in a worksheet.

oleObjects : OleObjectCollection;

shapes

Readonly. Returns all drawing shapes in this worksheet.

shapes : ShapeCollection;

slicers

Readonly. Get the Slicer collection in the worksheet

slicers : SlicerCollection;

timelines

Readonly. Get the Timeline collection in the worksheet

timelines : TimelineCollection;

index

Readonly. Gets the index of sheet in the worksheet collection.

index : number;

isProtected

Readonly. Indicates if the worksheet is protected.

isProtected : boolean;

validations

Readonly. Gets the data validation setting collection in the worksheet.

validations : ValidationCollection;

allowEditRanges

Readonly. Gets the allow edit range collection in the worksheet.

allowEditRanges : ProtectedRangeCollection;

errorCheckOptions

Readonly. Gets error check setting applied on certain ranges.

errorCheckOptions : ErrorCheckOptionCollection;

outline

Readonly. Gets the outline on this worksheet.

outline : Outline;

firstVisibleRow

Represents first visible row index.

firstVisibleRow : number;

firstVisibleColumn

Represents first visible column index.

firstVisibleColumn : number;

zoom

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

zoom : number;

Remarks

Please set the view type first.

viewType

Gets and sets the view type.

viewType : ViewType;

isPageBreakPreview

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

isPageBreakPreview : boolean;

isRulerVisible

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

isRulerVisible : boolean;

tabColor

Represents worksheet tab color.

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

gridlineColor

Gets and sets the color of gridline

gridlineColor : Color;

codeName

Gets worksheet code name.

codeName : string;

backgroundImage

Gets and sets worksheet background image.

backgroundImage : Uint8Array;

conditionalFormattings

Readonly. Gets the ConditionalFormattings in the worksheet.

conditionalFormattings : ConditionalFormattingCollection;

activeCell

Gets or sets the active cell in the worksheet.

activeCell : string;

customProperties

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

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

smartTagSetting

Readonly. Gets all SmartTagCollection objects of the worksheet.

smartTagSetting : SmartTagSetting;

scenarios

Readonly. Gets the collection of Scenario.

scenarios : ScenarioCollection;

cellWatches

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

cellWatches : CellWatchCollection;

getProtection()

@deprecated. Please use the ‘protection’ property instead. 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()

@deprecated. Please use the ‘uniqueId’ property instead. Gets and sets the unique id, it is same as {15DB5C3C-A5A1-48AF-8F25-3D86AC232D4F}.

getUniqueId() : string;

setUniqueId(string)

@deprecated. Please use the ‘uniqueId’ property instead. Gets and sets the unique id, it is same as {15DB5C3C-A5A1-48AF-8F25-3D86AC232D4F}.

setUniqueId(value: string) : void;

Parameters:

ParameterTypeDescription
valuestringThe value to set.

getWorkbook()

@deprecated. Please use the ‘workbook’ property instead. Gets the workbook object which contains this sheet.

getWorkbook() : Workbook;

Returns

Workbook

getCells()

@deprecated. Please use the ‘cells’ property instead. Gets the Cells collection.

getCells() : Cells;

Returns

Cells

getQueryTables()

@deprecated. Please use the ‘queryTables’ property instead. Gets QueryTableCollection in the worksheet.

getQueryTables() : QueryTableCollection;

Returns

QueryTableCollection

getType()

@deprecated. Please use the ’type’ property instead. Represents worksheet type.

getType() : SheetType;

Returns

SheetType

setType(SheetType)

@deprecated. Please use the ’type’ property instead. Represents worksheet type.

setType(value: SheetType) : void;

Parameters:

ParameterTypeDescription
valueSheetTypeThe value to set.

getName()

@deprecated. Please use the ’name’ property instead. 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)

@deprecated. Please use the ’name’ property instead. Gets or sets the name of the worksheet.

setName(value: string) : void;

Parameters:

ParameterTypeDescription
valuestringThe value to set.

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.

getShowFormulas()

@deprecated. Please use the ‘showFormulas’ property instead. Indicates whether to show formulas or their results.

getShowFormulas() : boolean;

setShowFormulas(boolean)

@deprecated. Please use the ‘showFormulas’ property instead. Indicates whether to show formulas or their results.

setShowFormulas(value: boolean) : void;

Parameters:

ParameterTypeDescription
valuebooleanThe value to set.

isGridlinesVisible()

@deprecated. Please use the ‘isGridlinesVisible’ property instead. Gets or sets a value indicating whether the gridlines are visible.Default is true.

isGridlinesVisible() : boolean;

setIsGridlinesVisible(boolean)

@deprecated. Please use the ‘isGridlinesVisible’ property instead. Gets or sets a value indicating whether the gridlines are visible.Default is true.

setIsGridlinesVisible(value: boolean) : void;

Parameters:

ParameterTypeDescription
valuebooleanThe value to set.

isRowColumnHeadersVisible()

@deprecated. Please use the ‘isRowColumnHeadersVisible’ property instead. Gets or sets a value indicating whether the worksheet will display row and column headers. Default is true.

isRowColumnHeadersVisible() : boolean;

setIsRowColumnHeadersVisible(boolean)

@deprecated. Please use the ‘isRowColumnHeadersVisible’ property instead. Gets or sets a value indicating whether the worksheet will display row and column headers. Default is true.

setIsRowColumnHeadersVisible(value: boolean) : void;

Parameters:

ParameterTypeDescription
valuebooleanThe value to set.

getPaneState()

@deprecated. Please use the ‘paneState’ property instead. Indicates whether the pane has horizontal or vertical splits, and whether those splits are frozen.

getPaneState() : PaneStateType;

Returns

PaneStateType

getDisplayZeros()

@deprecated. Please use the ‘displayZeros’ property instead. True if zero values are displayed.

getDisplayZeros() : boolean;

setDisplayZeros(boolean)

@deprecated. Please use the ‘displayZeros’ property instead. True if zero values are displayed.

setDisplayZeros(value: boolean) : void;

Parameters:

ParameterTypeDescription
valuebooleanThe value to set.

getDisplayRightToLeft()

@deprecated. Please use the ‘displayRightToLeft’ property instead. Indicates if the specified worksheet is displayed from right to left instead of from left to right. Default is false.

getDisplayRightToLeft() : boolean;

setDisplayRightToLeft(boolean)

@deprecated. Please use the ‘displayRightToLeft’ property instead. 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:

ParameterTypeDescription
valuebooleanThe value to set.

isOutlineShown()

@deprecated. Please use the ‘isOutlineShown’ property instead. Indicates whether to show outline.

isOutlineShown() : boolean;

setIsOutlineShown(boolean)

@deprecated. Please use the ‘isOutlineShown’ property instead. Indicates whether to show outline.

setIsOutlineShown(value: boolean) : void;

Parameters:

ParameterTypeDescription
valuebooleanThe value to set.

isSelected()

@deprecated. Please use the ‘isSelected’ property instead. Indicates whether this worksheet is selected when the workbook is opened.

isSelected() : boolean;

setIsSelected(boolean)

@deprecated. Please use the ‘isSelected’ property instead. Indicates whether this worksheet is selected when the workbook is opened.

setIsSelected(value: boolean) : void;

Parameters:

ParameterTypeDescription
valuebooleanThe value to set.

getPivotTables()

@deprecated. Please use the ‘pivotTables’ property instead. Gets all pivot tables in this worksheet.

getPivotTables() : PivotTableCollection;

Returns

PivotTableCollection

getListObjects()

@deprecated. Please use the ’listObjects’ property instead. Gets all ListObjects in this worksheet.

getListObjects() : ListObjectCollection;

Returns

ListObjectCollection

getTabId()

@deprecated. Please use the ’tabId’ property instead. Specifies the internal identifier for the sheet.

getTabId() : number;

setTabId(number)

@deprecated. Please use the ’tabId’ property instead. Specifies the internal identifier for the sheet.

setTabId(value: number) : void;

Parameters:

ParameterTypeDescription
valuenumberThe value to set.

getHorizontalPageBreaks()

@deprecated. Please use the ‘horizontalPageBreaks’ property instead. Gets the HorizontalPageBreakCollection collection.

getHorizontalPageBreaks() : HorizontalPageBreakCollection;

Returns

HorizontalPageBreakCollection

getVerticalPageBreaks()

@deprecated. Please use the ‘verticalPageBreaks’ property instead. Gets the VerticalPageBreakCollection collection.

getVerticalPageBreaks() : VerticalPageBreakCollection;

Returns

VerticalPageBreakCollection

@deprecated. Please use the ‘hyperlinks’ property instead. Gets the HyperlinkCollection collection.

getHyperlinks() : HyperlinkCollection;

Returns

HyperlinkCollection

getPageSetup()

@deprecated. Please use the ‘pageSetup’ property instead. Represents the page setup description in this sheet.

getPageSetup() : PageSetup;

Returns

PageSetup

getAutoFilter()

@deprecated. Please use the ‘autoFilter’ property instead. Represents auto filter for the specified worksheet.

getAutoFilter() : AutoFilter;

Returns

AutoFilter

getHasAutofilter()

@deprecated. Please use the ‘hasAutofilter’ property instead. Indicates whether this worksheet has auto filter.

getHasAutofilter() : boolean;

getTransitionEvaluation()

@deprecated. Please use the ’transitionEvaluation’ property instead. Indicates whether the Transition Formula Evaluation (Lotus compatibility) option is enabled.

getTransitionEvaluation() : boolean;

setTransitionEvaluation(boolean)

@deprecated. Please use the ’transitionEvaluation’ property instead. Indicates whether the Transition Formula Evaluation (Lotus compatibility) option is enabled.

setTransitionEvaluation(value: boolean) : void;

Parameters:

ParameterTypeDescription
valuebooleanThe value to set.

getTransitionEntry()

@deprecated. Please use the ’transitionEntry’ property instead. Indicates whether the Transition Formula Entry (Lotus compatibility) option is enabled.

getTransitionEntry() : boolean;

setTransitionEntry(boolean)

@deprecated. Please use the ’transitionEntry’ property instead. Indicates whether the Transition Formula Entry (Lotus compatibility) option is enabled.

setTransitionEntry(value: boolean) : void;

Parameters:

ParameterTypeDescription
valuebooleanThe value to set.

getVisibilityType()

@deprecated. Please use the ‘visibilityType’ property instead. Indicates the visible state for this sheet.

getVisibilityType() : VisibilityType;

Returns

VisibilityType

setVisibilityType(VisibilityType)

@deprecated. Please use the ‘visibilityType’ property instead. Indicates the visible state for this sheet.

setVisibilityType(value: VisibilityType) : void;

Parameters:

ParameterTypeDescription
valueVisibilityTypeThe value to set.

isVisible()

@deprecated. Please use the ‘isVisible’ property instead. Represents if the worksheet is visible.

isVisible() : boolean;

setIsVisible(boolean)

@deprecated. Please use the ‘isVisible’ property instead. Represents if the worksheet is visible.

setIsVisible(value: boolean) : void;

Parameters:

ParameterTypeDescription
valuebooleanThe value to set.

getSparklineGroups()

@deprecated. Please use the ‘sparklineGroups’ property instead. Gets the sparkline groups in the worksheet.

getSparklineGroups() : SparklineGroupCollection;

Returns

SparklineGroupCollection

getCharts()

@deprecated. Please use the ‘charts’ property instead. Gets a Chart collection

getCharts() : ChartCollection;

Returns

ChartCollection

getComments()

@deprecated. Please use the ‘comments’ property instead. Gets the Comment collection.

getComments() : CommentCollection;

Returns

CommentCollection

getPictures()

@deprecated. Please use the ‘pictures’ property instead. Gets a Picture collection.

getPictures() : PictureCollection;

Returns

PictureCollection

getTextBoxes()

@deprecated. Please use the ’textBoxes’ property instead. Gets a TextBox collection.

getTextBoxes() : TextBoxCollection;

Returns

TextBoxCollection

getCheckBoxes()

@deprecated. Please use the ‘checkBoxes’ property instead. Gets a CheckBox collection.

getCheckBoxes() : CheckBoxCollection;

Returns

CheckBoxCollection

getOleObjects()

@deprecated. Please use the ‘oleObjects’ property instead. Represents a collection of OleObject in a worksheet.

getOleObjects() : OleObjectCollection;

Returns

OleObjectCollection

getShapes()

@deprecated. Please use the ‘shapes’ property instead. Returns all drawing shapes in this worksheet.

getShapes() : ShapeCollection;

Returns

ShapeCollection

getSlicers()

@deprecated. Please use the ‘slicers’ property instead. Get the Slicer collection in the worksheet

getSlicers() : SlicerCollection;

Returns

SlicerCollection

getTimelines()

@deprecated. Please use the ’timelines’ property instead. Get the Timeline collection in the worksheet

getTimelines() : TimelineCollection;

Returns

TimelineCollection

getIndex()

@deprecated. Please use the ‘index’ property instead. Gets the index of sheet in the worksheet collection.

getIndex() : number;

isProtected()

@deprecated. Please use the ‘isProtected’ property instead. Indicates if the worksheet is protected.

isProtected() : boolean;

getValidations()

@deprecated. Please use the ‘validations’ property instead. Gets the data validation setting collection in the worksheet.

getValidations() : ValidationCollection;

Returns

ValidationCollection

getAllowEditRanges()

@deprecated. Please use the ‘allowEditRanges’ property instead. Gets the allow edit range collection in the worksheet.

getAllowEditRanges() : ProtectedRangeCollection;

Returns

ProtectedRangeCollection

getErrorCheckOptions()

@deprecated. Please use the ’errorCheckOptions’ property instead. Gets error check setting applied on certain ranges.

getErrorCheckOptions() : ErrorCheckOptionCollection;

Returns

ErrorCheckOptionCollection

getOutline()

@deprecated. Please use the ‘outline’ property instead. Gets the outline on this worksheet.

getOutline() : Outline;

Returns

Outline

getFirstVisibleRow()

@deprecated. Please use the ‘firstVisibleRow’ property instead. Represents first visible row index.

getFirstVisibleRow() : number;

setFirstVisibleRow(number)

@deprecated. Please use the ‘firstVisibleRow’ property instead. Represents first visible row index.

setFirstVisibleRow(value: number) : void;

Parameters:

ParameterTypeDescription
valuenumberThe value to set.

getFirstVisibleColumn()

@deprecated. Please use the ‘firstVisibleColumn’ property instead. Represents first visible column index.

getFirstVisibleColumn() : number;

setFirstVisibleColumn(number)

@deprecated. Please use the ‘firstVisibleColumn’ property instead. Represents first visible column index.

setFirstVisibleColumn(value: number) : void;

Parameters:

ParameterTypeDescription
valuenumberThe value to set.

getZoom()

@deprecated. Please use the ‘zoom’ property instead. Represents the scaling factor in percentage. It should be between 10 and 400.

getZoom() : number;

Remarks

Please set the view type first.

setZoom(number)

@deprecated. Please use the ‘zoom’ property instead. Represents the scaling factor in percentage. It should be between 10 and 400.

setZoom(value: number) : void;

Parameters:

ParameterTypeDescription
valuenumberThe value to set.

Remarks

Please set the view type first.

getViewType()

@deprecated. Please use the ‘viewType’ property instead. Gets and sets the view type.

getViewType() : ViewType;

Returns

ViewType

setViewType(ViewType)

@deprecated. Please use the ‘viewType’ property instead. Gets and sets the view type.

setViewType(value: ViewType) : void;

Parameters:

ParameterTypeDescription
valueViewTypeThe value to set.

isPageBreakPreview()

@deprecated. Please use the ‘isPageBreakPreview’ property instead. Indicates whether the specified worksheet is shown in normal view or page break preview.

isPageBreakPreview() : boolean;

setIsPageBreakPreview(boolean)

@deprecated. Please use the ‘isPageBreakPreview’ property instead. Indicates whether the specified worksheet is shown in normal view or page break preview.

setIsPageBreakPreview(value: boolean) : void;

Parameters:

ParameterTypeDescription
valuebooleanThe value to set.

isRulerVisible()

@deprecated. Please use the ‘isRulerVisible’ property instead. Indicates whether the ruler is visible. This property is only applied for page break preview.

isRulerVisible() : boolean;

setIsRulerVisible(boolean)

@deprecated. Please use the ‘isRulerVisible’ property instead. Indicates whether the ruler is visible. This property is only applied for page break preview.

setIsRulerVisible(value: boolean) : void;

Parameters:

ParameterTypeDescription
valuebooleanThe value to set.

getTabColor()

@deprecated. Please use the ’tabColor’ property instead. 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)

@deprecated. Please use the ’tabColor’ property instead. Represents worksheet tab color.

setTabColor(value: Color) : void;

Parameters:

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

getGridlineColor()

@deprecated. Please use the ‘gridlineColor’ property instead. Gets and sets the color of gridline

getGridlineColor() : Color;

Returns

Color

setGridlineColor(Color)

@deprecated. Please use the ‘gridlineColor’ property instead. Gets and sets the color of gridline

setGridlineColor(value: Color) : void;

Parameters:

ParameterTypeDescription
valueColorThe value to set.

getCodeName()

@deprecated. Please use the ‘codeName’ property instead. Gets worksheet code name.

getCodeName() : string;

setCodeName(string)

@deprecated. Please use the ‘codeName’ property instead. Gets worksheet code name.

setCodeName(value: string) : void;

Parameters:

ParameterTypeDescription
valuestringThe value to set.

getBackgroundImage()

@deprecated. Please use the ‘backgroundImage’ property instead. Gets and sets worksheet background image.

getBackgroundImage() : Uint8Array;

setBackgroundImage(Uint8Array)

@deprecated. Please use the ‘backgroundImage’ property instead. Gets and sets worksheet background image.

setBackgroundImage(value: Uint8Array) : void;

Parameters:

ParameterTypeDescription
valuenumber[]The value to set.

getConditionalFormattings()

@deprecated. Please use the ‘conditionalFormattings’ property instead. Gets the ConditionalFormattings in the worksheet.

getConditionalFormattings() : ConditionalFormattingCollection;

Returns

ConditionalFormattingCollection

getActiveCell()

@deprecated. Please use the ‘activeCell’ property instead. Gets or sets the active cell in the worksheet.

getActiveCell() : string;

setActiveCell(string)

@deprecated. Please use the ‘activeCell’ property instead. Gets or sets the active cell in the worksheet.

setActiveCell(value: string) : void;

Parameters:

ParameterTypeDescription
valuestringThe value to set.

getCustomProperties()

@deprecated. Please use the ‘customProperties’ property instead. 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()

@deprecated. Please use the ‘smartTagSetting’ property instead. Gets all SmartTagCollection objects of the worksheet.

getSmartTagSetting() : SmartTagSetting;

Returns

SmartTagSetting

getScenarios()

@deprecated. Please use the ‘scenarios’ property instead. Gets the collection of Scenario.

getScenarios() : ScenarioCollection;

Returns

ScenarioCollection

getCellWatches()

@deprecated. Please use the ‘cellWatches’ property instead. 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:

ParameterTypeDescription
rownumberRow index.
columnnumberColumn index.
freezedRowsnumberNumber of visible rows in top pane, no more than row index.
freezedColumnsnumberNumber 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:

ParameterTypeDescription
cellNamestringCell name.
freezedRowsnumberNumber of visible rows in top pane, no more than row index.
freezedColumnsnumberNumber 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:

ParameterTypeDescription
cellNamestring

copy(Worksheet)

Copies contents and formats from another worksheet.

copy(sourceSheet: Worksheet) : void;

Parameters:

ParameterTypeDescription
sourceSheetWorksheetSource worksheet.

copy(Worksheet, CopyOptions)

Copies contents and formats from another worksheet.

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

Parameters:

ParameterTypeDescription
sourceSheetWorksheetSource worksheet.
copyOptionsCopyOptions

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:

ParameterTypeDescription
columnIndexnumberColumn index.
firstRownumberFirst row index.
lastRownumberLast 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:

ParameterTypeDescription
columnIndexnumberColumn index.

Remarks

AutoFitColumn is an imprecise function.

autoFitColumnAsync(number, number, number)

Autofits the column width.

autoFitColumnAsync(columnIndex: number, firstRow: number, lastRow: number) : Promise<void>;

Parameters:

ParameterTypeDescription
columnIndexnumberColumn index.
firstRownumberFirst row index.
lastRownumberLast row index.

Returns

Promise

Remarks

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

autoFitColumnAsync(number)

Autofits the column width.

autoFitColumnAsync(columnIndex: number) : Promise<void>;

Parameters:

ParameterTypeDescription
columnIndexnumberColumn index.

Returns

Promise

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:

ParameterTypeDescription
optionsAutoFitterOptionsThe auto fitting options

autoFitColumns(number, number)

Autofits the columns width.

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

Parameters:

ParameterTypeDescription
firstColumnnumberFirst column index.
lastColumnnumberLast 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:

ParameterTypeDescription
firstColumnnumberFirst column index.
lastColumnnumberLast column index.
optionsAutoFitterOptionsThe 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:

ParameterTypeDescription
firstRownumberFirst row index.
firstColumnnumberFirst column index.
lastRownumberLast row index.
lastColumnnumberLast 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:

ParameterTypeDescription
firstRownumberFirst row index.
firstColumnnumberFirst column index.
lastRownumberLast row index.
lastColumnnumberLast column index.
optionsAutoFitterOptionsThe auto fitting options

Remarks

AutoFitColumn is an imprecise function.

autoFitColumnsAsync()

Autofits all columns in this worksheet.

autoFitColumnsAsync() : Promise<void>;

Returns

Promise

autoFitColumnsAsync(AutoFitterOptions)

Autofits all columns in this worksheet.

autoFitColumnsAsync(options: AutoFitterOptions) : Promise<void>;

Parameters:

ParameterTypeDescription
optionsAutoFitterOptionsThe auto fitting options

Returns

Promise

autoFitColumnsAsync(number, number)

Autofits the columns width.

autoFitColumnsAsync(firstColumn: number, lastColumn: number) : Promise<void>;

Parameters:

ParameterTypeDescription
firstColumnnumberFirst column index.
lastColumnnumberLast column index.

Returns

Promise

Remarks

AutoFitColumn is an imprecise function.

autoFitColumnsAsync(number, number, AutoFitterOptions)

Autofits the columns width.

autoFitColumnsAsync(firstColumn: number, lastColumn: number, options: AutoFitterOptions) : Promise<void>;

Parameters:

ParameterTypeDescription
firstColumnnumberFirst column index.
lastColumnnumberLast column index.
optionsAutoFitterOptionsThe auto fitting options

Returns

Promise

Remarks

AutoFitColumn is an imprecise function.

autoFitColumnsAsync(number, number, number, number)

Autofits the columns width.

autoFitColumnsAsync(firstRow: number, firstColumn: number, lastRow: number, lastColumn: number) : Promise<void>;

Parameters:

ParameterTypeDescription
firstRownumberFirst row index.
firstColumnnumberFirst column index.
lastRownumberLast row index.
lastColumnnumberLast column index.

Returns

Promise

Remarks

AutoFitColumn is an imprecise function.

autoFitColumnsAsync(number, number, number, number, AutoFitterOptions)

Autofits the columns width.

autoFitColumnsAsync(firstRow: number, firstColumn: number, lastRow: number, lastColumn: number, options: AutoFitterOptions) : Promise<void>;

Parameters:

ParameterTypeDescription
firstRownumberFirst row index.
firstColumnnumberFirst column index.
lastRownumberLast row index.
lastColumnnumberLast column index.
optionsAutoFitterOptionsThe auto fitting options

Returns

Promise

Remarks

AutoFitColumn is an imprecise function.

autoFitRow(number, number, number)

Autofits the row height.

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

Parameters:

ParameterTypeDescription
rowIndexnumberRow index.
firstColumnnumberFirst column index.
lastColumnnumberLast 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:

ParameterTypeDescription
rowIndexnumberRow index.
firstColumnnumberFirst column index.
lastColumnnumberLast column index.
optionsAutoFitterOptionsThe 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:

ParameterTypeDescription
startRownumberStart row index.
endRownumberEnd row index.
startColumnnumberStart column index.
endColumnnumberEnd column index.

autoFitRow(number)

Autofits the row height.

autoFitRow(rowIndex: number) : void;

Parameters:

ParameterTypeDescription
rowIndexnumberRow index.

Remarks

AutoFitRow is an imprecise function.

autoFitRowAsync(number, number, number)

Autofits the row height.

autoFitRowAsync(rowIndex: number, firstColumn: number, lastColumn: number) : Promise<void>;

Parameters:

ParameterTypeDescription
rowIndexnumberRow index.
firstColumnnumberFirst column index.
lastColumnnumberLast column index.

Returns

Promise

Remarks

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

autoFitRowAsync(number, number, number, AutoFitterOptions)

Autofits the row height.

autoFitRowAsync(rowIndex: number, firstColumn: number, lastColumn: number, options: AutoFitterOptions) : Promise<void>;

Parameters:

ParameterTypeDescription
rowIndexnumberRow index.
firstColumnnumberFirst column index.
lastColumnnumberLast column index.
optionsAutoFitterOptionsThe auto fitter options

Returns

Promise

Remarks

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

autoFitRowAsync(number, number, number, number)

Autofits row height in a rectangle range.

autoFitRowAsync(startRow: number, endRow: number, startColumn: number, endColumn: number) : Promise<void>;

Parameters:

ParameterTypeDescription
startRownumberStart row index.
endRownumberEnd row index.
startColumnnumberStart column index.
endColumnnumberEnd column index.

Returns

Promise

autoFitRowAsync(number)

Autofits the row height.

autoFitRowAsync(rowIndex: number) : Promise<void>;

Parameters:

ParameterTypeDescription
rowIndexnumberRow index.

Returns

Promise

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:

ParameterTypeDescription
onlyAutobooleanTrue,only autofits the row height when row height is not customed.

autoFitRows(AutoFitterOptions)

Autofits all rows in this worksheet.

autoFitRows(options: AutoFitterOptions) : void;

Parameters:

ParameterTypeDescription
optionsAutoFitterOptionsThe auto fitter options

autoFitRows(number, number)

Autofits row height in a range.

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

Parameters:

ParameterTypeDescription
startRownumberStart row index.
endRownumberEnd row index.

autoFitRows(number, number, AutoFitterOptions)

Autofits row height in a range.

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

Parameters:

ParameterTypeDescription
startRownumberStart row index.
endRownumberEnd row index.
optionsAutoFitterOptionsThe options of auto fitter.

autoFitRowsAsync()

Autofits all rows in this worksheet.

autoFitRowsAsync() : Promise<void>;

Returns

Promise

autoFitRowsAsync(boolean)

Autofits all rows in this worksheet.

autoFitRowsAsync(onlyAuto: boolean) : Promise<void>;

Parameters:

ParameterTypeDescription
onlyAutobooleanTrue,only autofits the row height when row height is not customed.

Returns

Promise

autoFitRowsAsync(AutoFitterOptions)

Autofits all rows in this worksheet.

autoFitRowsAsync(options: AutoFitterOptions) : Promise<void>;

Parameters:

ParameterTypeDescription
optionsAutoFitterOptionsThe auto fitter options

Returns

Promise

autoFitRowsAsync(number, number)

Autofits row height in a range.

autoFitRowsAsync(startRow: number, endRow: number) : Promise<void>;

Parameters:

ParameterTypeDescription
startRownumberStart row index.
endRownumberEnd row index.

Returns

Promise

autoFitRowsAsync(number, number, AutoFitterOptions)

Autofits row height in a range.

autoFitRowsAsync(startRow: number, endRow: number, options: AutoFitterOptions) : Promise<void>;

Parameters:

ParameterTypeDescription
startRownumberStart row index.
endRownumberEnd row index.
optionsAutoFitterOptionsThe options of auto fitter.

Returns

Promise

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:

ParameterTypeDescription
isFilterbooleanIndicates whether filtering the list in place.
listRangestringThe list range.
criteriaRangestringThe criteria range.
copyTostringThe range where copying data to.
uniqueRecordOnlybooleanOnly 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:

ParameterTypeDescription
isVisiblebooleanWhether the worksheet is visible
ignoreErrorbooleanWhether 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:

ParameterTypeDescription
startRownumberThe start row.
startColumnnumberThe start column
totalRowsnumberThe number of rows.
totalColumnsnumberThe number of columns
removeOthersbooleanTrue 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:

ParameterTypeDescription
typeProtectionTypeProtection 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:

ParameterTypeDescription
typeProtectionTypeProtection type.
passwordstringPassword.
oldPasswordstringIf 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.worksheets.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:

ParameterTypeDescription
passwordstringPassword

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:

ParameterTypeDescription
indexnumberDestination sheet index.

replace(string, string)

Replaces all cells’ text with a new string.

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

Parameters:

ParameterTypeDescription
oldStringstringOld string value.
newStringstringNew string value.

getSelectedAreas()

Gets selected ranges of cells in the designer spreadsheet.

getSelectedAreas() : Range[];

Returns

Returns all selected ranges.

getPrintingPageBreaks(ImageOrPrintOptions)

Gets automatic page breaks.

getPrintingPageBreaks(options: ImageOrPrintOptions) : CellArea[];

Parameters:

ParameterTypeDescription
optionsImageOrPrintOptionsThe 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:

ParameterTypeDescription
optsAccessCacheOptionsoptions 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:

ParameterTypeDescription
optsAccessCacheOptionsoptions of data access

convertFormulaReferenceStyle(string, boolean, number, number)

Converts the formula reference style.

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

Parameters:

ParameterTypeDescription
formulastringThe formula to be converted.
toR1C1booleanWhich 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;
baseCellRownumberThe row index of the base cell.
baseCellColumnnumberThe column index of the base cell.

Returns

The converted formula.

calculateFormula(string)

Calculates a formula.

calculateFormula(formula: string) : Object;

Parameters:

ParameterTypeDescription
formulastringFormula to be calculated.

Returns

Calculated formula result.

calculateFormula(string, CalculationOptions)

Calculates a formula expression directly.

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

Parameters:

ParameterTypeDescription
formulastringFormula to be calculated.
optsCalculationOptionsOptions 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:

ParameterTypeDescription
formulastringFormula to be calculated.
pOptsFormulaParseOptionsOptions for parsing formula.
cOptsCalculationOptionsOptions for calculating formula.
baseCellRownumberThe row index of the base cell.
baseCellColumnnumberThe column index of the base cell.
calculationDataCalculationDataThe 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:

ParameterTypeDescription
optionsCalculationOptionsOptions for calculation
recursivebooleanTrue 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.

calculateFormulaAsync(string)

Calculates a formula.

calculateFormulaAsync(formula: string) : Promise<Object>;

Parameters:

ParameterTypeDescription
formulastringFormula to be calculated.

Returns

Calculated formula result.

calculateFormulaAsync(string, CalculationOptions)

Calculates a formula expression directly.

calculateFormulaAsync(formula: string, opts: CalculationOptions) : Promise<Object>;

Parameters:

ParameterTypeDescription
formulastringFormula to be calculated.
optsCalculationOptionsOptions 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.

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

Calculates a formula expression directly.

calculateFormulaAsync(formula: string, pOpts: FormulaParseOptions, cOpts: CalculationOptions, baseCellRow: number, baseCellColumn: number, calculationData: CalculationData) : Promise<Object>;

Parameters:

ParameterTypeDescription
formulastringFormula to be calculated.
pOptsFormulaParseOptionsOptions for parsing formula.
cOptsCalculationOptionsOptions for calculating formula.
baseCellRownumberThe row index of the base cell.
baseCellColumnnumberThe column index of the base cell.
calculationDataCalculationDataThe 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.

calculateFormulaAsync(CalculationOptions, boolean)

Calculates all formulas in this worksheet.

calculateFormulaAsync(options: CalculationOptions, recursive: boolean) : Promise<void>;

Parameters:

ParameterTypeDescription
optionsCalculationOptionsOptions for calculation
recursivebooleanTrue 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.

Returns

Promise

calculateArrayFormula(string, CalculationOptions)

Calculates a formula as array formula.

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

Parameters:

ParameterTypeDescription
formulastringFormula to be calculated.
optsCalculationOptionsOptions 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:

ParameterTypeDescription
formulastringFormula to be calculated.
optsCalculationOptionsOptions for calculating formula
maxRowCountnumberthe 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.
maxColumnCountnumberthe 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:

ParameterTypeDescription
formulastringFormula to be calculated.
pOptsFormulaParseOptionsOptions for parsing formula
cOptsCalculationOptionsOptions for calculating formula
baseCellRownumberThe row index of the base cell.
baseCellColumnnumberThe column index of the base cell.
maxRowCountnumberThe 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.
maxColumnCountnumberThe 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.
calculationDataCalculationDataThe 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

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.

calculateArrayFormulaAsync(string, CalculationOptions)

Calculates a formula as array formula.

calculateArrayFormulaAsync(formula: string, opts: CalculationOptions) : Promise<Object[][]>;

Parameters:

ParameterTypeDescription
formulastringFormula to be calculated.
optsCalculationOptionsOptions for calculating formula

Returns

Promise<Object[][]>

calculateArrayFormulaAsync(string, CalculationOptions, number, number)

Calculates a formula as array formula.

calculateArrayFormulaAsync(formula: string, opts: CalculationOptions, maxRowCount: number, maxColumnCount: number) : Promise<Object[][]>;

Parameters:

ParameterTypeDescription
formulastringFormula to be calculated.
optsCalculationOptionsOptions for calculating formula
maxRowCountnumberthe 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.
maxColumnCountnumberthe 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.

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

Calculates a formula as array formula.

calculateArrayFormulaAsync(formula: string, pOpts: FormulaParseOptions, cOpts: CalculationOptions, baseCellRow: number, baseCellColumn: number, maxRowCount: number, maxColumnCount: number, calculationData: CalculationData) : Promise<Object[][]>;

Parameters:

ParameterTypeDescription
formulastringFormula to be calculated.
pOptsFormulaParseOptionsOptions for parsing formula
cOptsCalculationOptionsOptions for calculating formula
baseCellRownumberThe row index of the base cell.
baseCellColumnnumberThe column index of the base cell.
maxRowCountnumberThe 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.
maxColumnCountnumberThe 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.
calculationDataCalculationDataThe 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

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.

refreshPivotTables()

Refreshes all the PivotTables in this Worksheet.

refreshPivotTables() : void;

refreshPivotTables(PivotTableRefreshOption)

Refreshes all the PivotTables in this Worksheet.

refreshPivotTables(option: PivotTableRefreshOption) : boolean;

Parameters:

ParameterTypeDescription
optionPivotTableRefreshOptionThe option for refreshing data source of pivot table.

refreshPivotTablesAsync()

Refreshes all the PivotTables in this Worksheet.

refreshPivotTablesAsync() : Promise<void>;

Returns

Promise

refreshPivotTablesAsync(PivotTableRefreshOption)

Refreshes all the PivotTables in this Worksheet.

refreshPivotTablesAsync(option: PivotTableRefreshOption) : Promise<boolean>;

Parameters:

ParameterTypeDescription
optionPivotTableRefreshOptionThe option for refreshing data source of pivot table.

Returns

Promise

isNull()

Checks whether the implementation object is null.

isNull() : boolean;