Worksheet

Worksheet class

Encapsulates the object that represents a single worksheet.

class Worksheet;

Example

const { Workbook } = AsposeCells;

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
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.
autoFitColumnAsync(number, number, number)Autofits the column width.
autoFitColumnAsync(number)Autofits the column width.
autoFitColumn(number, number, number)Autofits the column width.
autoFitColumn(number)Autofits the column 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.
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.
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.
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.
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.
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.
getSelectedAreas()Gets selected ranges of cells in the designer spreadsheet.
getPrintingPageBreaks(ImageOrPrintOptions)Gets automatic page breaks.
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.
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.
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.
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.
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.
refreshPivotTablesAsync()Refreshes all the PivotTables in this Worksheet.
refreshPivotTablesAsync(PivotTableRefreshOption)Refreshes all the PivotTables in this Worksheet.
refreshPivotTables()Refreshes all the PivotTables in this Worksheet.
refreshPivotTables(PivotTableRefreshOption)Refreshes all the PivotTables in this Worksheet.
toString()Returns a string represents the current Worksheet object.

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;

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.

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.

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.

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.

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.

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.

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.

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

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.

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, SaveFormat } = AsposeCells;

//Instantiating a Workbook object
var excel = new Workbook(data);
//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
var uint8Array = excel.save(SaveFormat.Xlsx);

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.

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.

calculateFormulaAsync(string)

Calculates a formula.

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

Parameters:

ParameterTypeDescription
formulastringFormula to be calculated.

Returns

Calculated formula result.

calculateFormulaAsync(string, CalculationOptions)

Calculates a formula expression directly.

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

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<VObject>;

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

calculateFormula(string)

Calculates a formula.

calculateFormula(formula: string) : VObject;

Parameters:

ParameterTypeDescription
formulastringFormula to be calculated.

Returns

Calculated formula result.

calculateFormula(string, CalculationOptions)

Calculates a formula expression directly.

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

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) : VObject;

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.

calculateArrayFormulaAsync(string, CalculationOptions)

Calculates a formula as array formula.

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

Parameters:

ParameterTypeDescription
formulastringFormula to be calculated.
optsCalculationOptionsOptions for calculating formula

Returns

Promise<VObject[][]>

calculateArrayFormulaAsync(string, CalculationOptions, number, number)

Calculates a formula as array formula.

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

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<VObject[][]>;

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.

calculateArrayFormula(string, CalculationOptions)

Calculates a formula as array formula.

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

Parameters:

ParameterTypeDescription
formulastringFormula to be calculated.
optsCalculationOptionsOptions for calculating formula

Returns

VObject[][]

calculateArrayFormula(string, CalculationOptions, number, number)

Calculates a formula as array formula.

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

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) : VObject[][];

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.

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

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.

toString()

Returns a string represents the current Worksheet object.

toString() : string;