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
Property | Type | Description |
---|---|---|
protection | Protection | Readonly. Represents the various types of protection options available for a worksheet. Supports advanced protection options in ExcelXP and above version. |
uniqueId | string | Gets and sets the unique id, it is same as {15DB5C3C-A5A1-48AF-8F25-3D86AC232D4F}. |
workbook | Workbook | Readonly. Gets the workbook object which contains this sheet. |
cells | Cells | Readonly. Gets the Cells collection. |
queryTables | QueryTableCollection | Readonly. Gets QueryTableCollection in the worksheet. |
type | SheetType | Represents worksheet type. |
name | string | Gets or sets the name of the worksheet. |
showFormulas | boolean | Indicates whether to show formulas or their results. |
isGridlinesVisible | boolean | Gets or sets a value indicating whether the gridlines are visible.Default is true. |
isRowColumnHeadersVisible | boolean | Gets or sets a value indicating whether the worksheet will display row and column headers. Default is true. |
paneState | PaneStateType | Readonly. Indicates whether the pane has horizontal or vertical splits, and whether those splits are frozen. |
displayZeros | boolean | True if zero values are displayed. |
displayRightToLeft | boolean | Indicates if the specified worksheet is displayed from right to left instead of from left to right. Default is false. |
isOutlineShown | boolean | Indicates whether to show outline. |
isSelected | boolean | Indicates whether this worksheet is selected when the workbook is opened. |
pivotTables | PivotTableCollection | Readonly. Gets all pivot tables in this worksheet. |
listObjects | ListObjectCollection | Readonly. Gets all ListObjects in this worksheet. |
tabId | number | Specifies the internal identifier for the sheet. |
horizontalPageBreaks | HorizontalPageBreakCollection | Readonly. Gets the HorizontalPageBreakCollection collection. |
verticalPageBreaks | VerticalPageBreakCollection | Readonly. Gets the VerticalPageBreakCollection collection. |
hyperlinks | HyperlinkCollection | Readonly. Gets the HyperlinkCollection collection. |
pageSetup | PageSetup | Readonly. Represents the page setup description in this sheet. |
autoFilter | AutoFilter | Readonly. Represents auto filter for the specified worksheet. |
hasAutofilter | boolean | Readonly. Indicates whether this worksheet has auto filter. |
transitionEvaluation | boolean | Indicates whether the Transition Formula Evaluation (Lotus compatibility) option is enabled. |
transitionEntry | boolean | Indicates whether the Transition Formula Entry (Lotus compatibility) option is enabled. |
visibilityType | VisibilityType | Indicates the visible state for this sheet. |
isVisible | boolean | Represents if the worksheet is visible. |
sparklineGroups | SparklineGroupCollection | Readonly. Gets the sparkline groups in the worksheet. |
charts | ChartCollection | Readonly. Gets a Chart collection |
comments | CommentCollection | Readonly. Gets the Comment collection. |
pictures | PictureCollection | Readonly. Gets a Picture collection. |
textBoxes | TextBoxCollection | Readonly. Gets a TextBox collection. |
checkBoxes | CheckBoxCollection | Readonly. Gets a CheckBox collection. |
oleObjects | OleObjectCollection | Readonly. Represents a collection of OleObject in a worksheet. |
shapes | ShapeCollection | Readonly. Returns all drawing shapes in this worksheet. |
slicers | SlicerCollection | Readonly. Get the Slicer collection in the worksheet |
timelines | TimelineCollection | Readonly. Get the Timeline collection in the worksheet |
index | number | Readonly. Gets the index of sheet in the worksheet collection. |
isProtected | boolean | Readonly. Indicates if the worksheet is protected. |
validations | ValidationCollection | Readonly. Gets the data validation setting collection in the worksheet. |
allowEditRanges | ProtectedRangeCollection | Readonly. Gets the allow edit range collection in the worksheet. |
errorCheckOptions | ErrorCheckOptionCollection | Readonly. Gets error check setting applied on certain ranges. |
outline | Outline | Readonly. Gets the outline on this worksheet. |
firstVisibleRow | number | Represents first visible row index. |
firstVisibleColumn | number | Represents first visible column index. |
zoom | number | Represents the scaling factor in percentage. It should be between 10 and 400. |
viewType | ViewType | Gets and sets the view type. |
isPageBreakPreview | boolean | Indicates whether the specified worksheet is shown in normal view or page break preview. |
isRulerVisible | boolean | Indicates whether the ruler is visible. This property is only applied for page break preview. |
tabColor | Color | Represents worksheet tab color. |
gridlineColor | Color | Gets and sets the color of gridline |
codeName | string | Gets worksheet code name. |
backgroundImage | Uint8Array | Gets and sets worksheet background image. |
conditionalFormattings | ConditionalFormattingCollection | Readonly. Gets the ConditionalFormattings in the worksheet. |
activeCell | string | Gets or sets the active cell in the worksheet. |
customProperties | CustomPropertyCollection | Readonly. Gets an object representing the identifier information associated with a worksheet. |
smartTagSetting | SmartTagSetting | Readonly. Gets all SmartTagCollection objects of the worksheet. |
scenarios | ScenarioCollection | Readonly. Gets the collection of Scenario. |
cellWatches | CellWatchCollection | Readonly. Gets collection of cells on this worksheet being watched in the ‘watch window’. |
Methods
Method | Description |
---|---|
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;
hyperlinks
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
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:
Parameter | Type | Description |
---|---|---|
value | string | The value to set. |
getWorkbook()
@deprecated. Please use the ‘workbook’ property instead. Gets the workbook object which contains this sheet.
getWorkbook() : Workbook;
Returns
getCells()
@deprecated. Please use the ‘cells’ property instead. Gets the Cells collection.
getCells() : Cells;
Returns
getQueryTables()
@deprecated. Please use the ‘queryTables’ property instead. Gets QueryTableCollection in the worksheet.
getQueryTables() : QueryTableCollection;
Returns
getType()
@deprecated. Please use the ’type’ property instead. Represents worksheet type.
getType() : SheetType;
Returns
setType(SheetType)
@deprecated. Please use the ’type’ property instead. Represents worksheet type.
setType(value: SheetType) : void;
Parameters:
Parameter | Type | Description |
---|---|---|
value | SheetType | The 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:
Parameter | Type | Description |
---|---|---|
value | string | The 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:
Parameter | Type | Description |
---|---|---|
value | boolean | The 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:
Parameter | Type | Description |
---|---|---|
value | boolean | The 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:
Parameter | Type | Description |
---|---|---|
value | boolean | The 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
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:
Parameter | Type | Description |
---|---|---|
value | boolean | The 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:
Parameter | Type | Description |
---|---|---|
value | boolean | The 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:
Parameter | Type | Description |
---|---|---|
value | boolean | The 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:
Parameter | Type | Description |
---|---|---|
value | boolean | The value to set. |
getPivotTables()
@deprecated. Please use the ‘pivotTables’ property instead. Gets all pivot tables in this worksheet.
getPivotTables() : PivotTableCollection;
Returns
getListObjects()
@deprecated. Please use the ’listObjects’ property instead. Gets all ListObjects in this worksheet.
getListObjects() : ListObjectCollection;
Returns
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:
Parameter | Type | Description |
---|---|---|
value | number | The value to set. |
getHorizontalPageBreaks()
@deprecated. Please use the ‘horizontalPageBreaks’ property instead. Gets the HorizontalPageBreakCollection collection.
getHorizontalPageBreaks() : HorizontalPageBreakCollection;
Returns
getVerticalPageBreaks()
@deprecated. Please use the ‘verticalPageBreaks’ property instead. Gets the VerticalPageBreakCollection collection.
getVerticalPageBreaks() : VerticalPageBreakCollection;
Returns
getHyperlinks()
@deprecated. Please use the ‘hyperlinks’ property instead. Gets the HyperlinkCollection collection.
getHyperlinks() : HyperlinkCollection;
Returns
getPageSetup()
@deprecated. Please use the ‘pageSetup’ property instead. Represents the page setup description in this sheet.
getPageSetup() : PageSetup;
Returns
getAutoFilter()
@deprecated. Please use the ‘autoFilter’ property instead. Represents auto filter for the specified worksheet.
getAutoFilter() : AutoFilter;
Returns
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:
Parameter | Type | Description |
---|---|---|
value | boolean | The 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:
Parameter | Type | Description |
---|---|---|
value | boolean | The value to set. |
getVisibilityType()
@deprecated. Please use the ‘visibilityType’ property instead. Indicates the visible state for this sheet.
getVisibilityType() : VisibilityType;
Returns
setVisibilityType(VisibilityType)
@deprecated. Please use the ‘visibilityType’ property instead. Indicates the visible state for this sheet.
setVisibilityType(value: VisibilityType) : void;
Parameters:
Parameter | Type | Description |
---|---|---|
value | VisibilityType | The 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:
Parameter | Type | Description |
---|---|---|
value | boolean | The value to set. |
getSparklineGroups()
@deprecated. Please use the ‘sparklineGroups’ property instead. Gets the sparkline groups in the worksheet.
getSparklineGroups() : SparklineGroupCollection;
Returns
getCharts()
@deprecated. Please use the ‘charts’ property instead. Gets a Chart collection
getCharts() : ChartCollection;
Returns
getComments()
@deprecated. Please use the ‘comments’ property instead. Gets the Comment collection.
getComments() : CommentCollection;
Returns
getPictures()
@deprecated. Please use the ‘pictures’ property instead. Gets a Picture collection.
getPictures() : PictureCollection;
Returns
getTextBoxes()
@deprecated. Please use the ’textBoxes’ property instead. Gets a TextBox collection.
getTextBoxes() : TextBoxCollection;
Returns
getCheckBoxes()
@deprecated. Please use the ‘checkBoxes’ property instead. Gets a CheckBox collection.
getCheckBoxes() : CheckBoxCollection;
Returns
getOleObjects()
@deprecated. Please use the ‘oleObjects’ property instead. Represents a collection of OleObject in a worksheet.
getOleObjects() : OleObjectCollection;
Returns
getShapes()
@deprecated. Please use the ‘shapes’ property instead. Returns all drawing shapes in this worksheet.
getShapes() : ShapeCollection;
Returns
getSlicers()
@deprecated. Please use the ‘slicers’ property instead. Get the Slicer collection in the worksheet
getSlicers() : SlicerCollection;
Returns
getTimelines()
@deprecated. Please use the ’timelines’ property instead. Get the Timeline collection in the worksheet
getTimelines() : TimelineCollection;
Returns
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
getAllowEditRanges()
@deprecated. Please use the ‘allowEditRanges’ property instead. Gets the allow edit range collection in the worksheet.
getAllowEditRanges() : ProtectedRangeCollection;
Returns
getErrorCheckOptions()
@deprecated. Please use the ’errorCheckOptions’ property instead. Gets error check setting applied on certain ranges.
getErrorCheckOptions() : ErrorCheckOptionCollection;
Returns
getOutline()
@deprecated. Please use the ‘outline’ property instead. Gets the outline on this worksheet.
getOutline() : Outline;
Returns
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:
Parameter | Type | Description |
---|---|---|
value | number | The 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:
Parameter | Type | Description |
---|---|---|
value | number | The 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:
Parameter | Type | Description |
---|---|---|
value | number | The 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
setViewType(ViewType)
@deprecated. Please use the ‘viewType’ property instead. Gets and sets the view type.
setViewType(value: ViewType) : void;
Parameters:
Parameter | Type | Description |
---|---|---|
value | ViewType | The 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:
Parameter | Type | Description |
---|---|---|
value | boolean | The 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:
Parameter | Type | Description |
---|---|---|
value | boolean | The value to set. |
getTabColor()
@deprecated. Please use the ’tabColor’ property instead. Represents worksheet tab color.
getTabColor() : Color;
Returns
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:
Parameter | Type | Description |
---|---|---|
value | Color | The value to set. |
Remarks
This feature is only supported in ExcelXP(Excel2002) and later versions. If you save file as Excel97 or Excel2000 format, it will be omitted.
getGridlineColor()
@deprecated. Please use the ‘gridlineColor’ property instead. Gets and sets the color of gridline
getGridlineColor() : Color;
Returns
setGridlineColor(Color)
@deprecated. Please use the ‘gridlineColor’ property instead. Gets and sets the color of gridline
setGridlineColor(value: Color) : void;
Parameters:
Parameter | Type | Description |
---|---|---|
value | Color | The 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:
Parameter | Type | Description |
---|---|---|
value | string | The 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:
Parameter | Type | Description |
---|---|---|
value | number[] | 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:
Parameter | Type | Description |
---|---|---|
value | string | The 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
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
getScenarios()
@deprecated. Please use the ‘scenarios’ property instead. Gets the collection of Scenario.
getScenarios() : ScenarioCollection;
Returns
getCellWatches()
@deprecated. Please use the ‘cellWatches’ property instead. Gets collection of cells on this worksheet being watched in the ‘watch window’.
getCellWatches() : CellWatchCollection;
Returns
dispose()
Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
dispose() : void;
getPanes()
Gets the window panes.
getPanes() : PaneCollection;
Returns
Remarks
If the window is not split or frozen.
freezePanes(number, number, number, number)
Freezes panes at the specified cell in the worksheet.
freezePanes(row: number, column: number, freezedRows: number, freezedColumns: number) : void;
Parameters:
Parameter | Type | Description |
---|---|---|
row | number | Row index. |
column | number | Column index. |
freezedRows | number | Number of visible rows in top pane, no more than row index. |
freezedColumns | number | Number of visible columns in left pane, no more than column index. |
Remarks
Row index and column index cannot all be zero. Number of rows and number of columns also cannot all be zero.
The first two parameters specify the froze position and the last two parameters specify the area frozen on the left top pane.
freezePanes(string, number, number)
Freezes panes at the specified cell in the worksheet.
freezePanes(cellName: string, freezedRows: number, freezedColumns: number) : void;
Parameters:
Parameter | Type | Description |
---|---|---|
cellName | string | Cell name. |
freezedRows | number | Number of visible rows in top pane, no more than row index. |
freezedColumns | number | Number of visible columns in left pane, no more than column index. |
Remarks
Row index and column index cannot all be zero. Number of rows and number of columns also cannot all be zero.
getFreezedPanes()
Gets the freeze panes.
getFreezedPanes() : number[];
Returns
Return null means the worksheet is not frozen 0:Row index;1:column;2:freezedRows;3:freezedRows
split()
Splits window.
split() : void;
unFreezePanes()
Unfreezes panes in the worksheet.
unFreezePanes() : void;
removeSplit()
Removes split window.
removeSplit() : void;
addPageBreaks(string)
Adds page break.
addPageBreaks(cellName: string) : void;
Parameters:
Parameter | Type | Description |
---|---|---|
cellName | string |
copy(Worksheet)
Copies contents and formats from another worksheet.
copy(sourceSheet: Worksheet) : void;
Parameters:
Parameter | Type | Description |
---|---|---|
sourceSheet | Worksheet | Source worksheet. |
copy(Worksheet, CopyOptions)
Copies contents and formats from another worksheet.
copy(sourceSheet: Worksheet, copyOptions: CopyOptions) : void;
Parameters:
Parameter | Type | Description |
---|---|---|
sourceSheet | Worksheet | Source worksheet. |
copyOptions | CopyOptions |
Remarks
You can copy data from another worksheet in the same file or another file. However, this method does not support to copy drawing objects, such as comments, images and charts.
autoFitColumn(number, number, number)
Autofits the column width.
autoFitColumn(columnIndex: number, firstRow: number, lastRow: number) : void;
Parameters:
Parameter | Type | Description |
---|---|---|
columnIndex | number | Column index. |
firstRow | number | First row index. |
lastRow | number | Last row index. |
Remarks
This method autofits a row based on content in a range of cells within the row.
autoFitColumn(number)
Autofits the column width.
autoFitColumn(columnIndex: number) : void;
Parameters:
Parameter | Type | Description |
---|---|---|
columnIndex | number | Column index. |
Remarks
AutoFitColumn is an imprecise function.
autoFitColumnAsync(number, number, number)
Autofits the column width.
autoFitColumnAsync(columnIndex: number, firstRow: number, lastRow: number) : Promise<void>;
Parameters:
Parameter | Type | Description |
---|---|---|
columnIndex | number | Column index. |
firstRow | number | First row index. |
lastRow | number | Last row index. |
Returns
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:
Parameter | Type | Description |
---|---|---|
columnIndex | number | Column index. |
Returns
Remarks
AutoFitColumn is an imprecise function.
autoFitColumns()
Autofits all columns in this worksheet.
autoFitColumns() : void;
autoFitColumns(AutoFitterOptions)
Autofits all columns in this worksheet.
autoFitColumns(options: AutoFitterOptions) : void;
Parameters:
Parameter | Type | Description |
---|---|---|
options | AutoFitterOptions | The auto fitting options |
autoFitColumns(number, number)
Autofits the columns width.
autoFitColumns(firstColumn: number, lastColumn: number) : void;
Parameters:
Parameter | Type | Description |
---|---|---|
firstColumn | number | First column index. |
lastColumn | number | Last column index. |
Remarks
AutoFitColumn is an imprecise function.
autoFitColumns(number, number, AutoFitterOptions)
Autofits the columns width.
autoFitColumns(firstColumn: number, lastColumn: number, options: AutoFitterOptions) : void;
Parameters:
Parameter | Type | Description |
---|---|---|
firstColumn | number | First column index. |
lastColumn | number | Last column index. |
options | AutoFitterOptions | The auto fitting options |
Remarks
AutoFitColumn is an imprecise function.
autoFitColumns(number, number, number, number)
Autofits the columns width.
autoFitColumns(firstRow: number, firstColumn: number, lastRow: number, lastColumn: number) : void;
Parameters:
Parameter | Type | Description |
---|---|---|
firstRow | number | First row index. |
firstColumn | number | First column index. |
lastRow | number | Last row index. |
lastColumn | number | Last column index. |
Remarks
AutoFitColumn is an imprecise function.
autoFitColumns(number, number, number, number, AutoFitterOptions)
Autofits the columns width.
autoFitColumns(firstRow: number, firstColumn: number, lastRow: number, lastColumn: number, options: AutoFitterOptions) : void;
Parameters:
Parameter | Type | Description |
---|---|---|
firstRow | number | First row index. |
firstColumn | number | First column index. |
lastRow | number | Last row index. |
lastColumn | number | Last column index. |
options | AutoFitterOptions | The auto fitting options |
Remarks
AutoFitColumn is an imprecise function.
autoFitColumnsAsync()
Autofits all columns in this worksheet.
autoFitColumnsAsync() : Promise<void>;
Returns
autoFitColumnsAsync(AutoFitterOptions)
Autofits all columns in this worksheet.
autoFitColumnsAsync(options: AutoFitterOptions) : Promise<void>;
Parameters:
Parameter | Type | Description |
---|---|---|
options | AutoFitterOptions | The auto fitting options |
Returns
autoFitColumnsAsync(number, number)
Autofits the columns width.
autoFitColumnsAsync(firstColumn: number, lastColumn: number) : Promise<void>;
Parameters:
Parameter | Type | Description |
---|---|---|
firstColumn | number | First column index. |
lastColumn | number | Last column index. |
Returns
Remarks
AutoFitColumn is an imprecise function.
autoFitColumnsAsync(number, number, AutoFitterOptions)
Autofits the columns width.
autoFitColumnsAsync(firstColumn: number, lastColumn: number, options: AutoFitterOptions) : Promise<void>;
Parameters:
Parameter | Type | Description |
---|---|---|
firstColumn | number | First column index. |
lastColumn | number | Last column index. |
options | AutoFitterOptions | The auto fitting options |
Returns
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:
Parameter | Type | Description |
---|---|---|
firstRow | number | First row index. |
firstColumn | number | First column index. |
lastRow | number | Last row index. |
lastColumn | number | Last column index. |
Returns
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:
Parameter | Type | Description |
---|---|---|
firstRow | number | First row index. |
firstColumn | number | First column index. |
lastRow | number | Last row index. |
lastColumn | number | Last column index. |
options | AutoFitterOptions | The auto fitting options |
Returns
Remarks
AutoFitColumn is an imprecise function.
autoFitRow(number, number, number)
Autofits the row height.
autoFitRow(rowIndex: number, firstColumn: number, lastColumn: number) : void;
Parameters:
Parameter | Type | Description |
---|---|---|
rowIndex | number | Row index. |
firstColumn | number | First column index. |
lastColumn | number | Last column index. |
Remarks
This method autofits a row based on content in a range of cells within the row.
autoFitRow(number, number, number, AutoFitterOptions)
Autofits the row height.
autoFitRow(rowIndex: number, firstColumn: number, lastColumn: number, options: AutoFitterOptions) : void;
Parameters:
Parameter | Type | Description |
---|---|---|
rowIndex | number | Row index. |
firstColumn | number | First column index. |
lastColumn | number | Last column index. |
options | AutoFitterOptions | The auto fitter options |
Remarks
This method autofits a row based on content in a range of cells within the row.
autoFitRow(number, number, number, number)
Autofits row height in a rectangle range.
autoFitRow(startRow: number, endRow: number, startColumn: number, endColumn: number) : void;
Parameters:
Parameter | Type | Description |
---|---|---|
startRow | number | Start row index. |
endRow | number | End row index. |
startColumn | number | Start column index. |
endColumn | number | End column index. |
autoFitRow(number)
Autofits the row height.
autoFitRow(rowIndex: number) : void;
Parameters:
Parameter | Type | Description |
---|---|---|
rowIndex | number | Row index. |
Remarks
AutoFitRow is an imprecise function.
autoFitRowAsync(number, number, number)
Autofits the row height.
autoFitRowAsync(rowIndex: number, firstColumn: number, lastColumn: number) : Promise<void>;
Parameters:
Parameter | Type | Description |
---|---|---|
rowIndex | number | Row index. |
firstColumn | number | First column index. |
lastColumn | number | Last column index. |
Returns
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:
Parameter | Type | Description |
---|---|---|
rowIndex | number | Row index. |
firstColumn | number | First column index. |
lastColumn | number | Last column index. |
options | AutoFitterOptions | The auto fitter options |
Returns
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:
Parameter | Type | Description |
---|---|---|
startRow | number | Start row index. |
endRow | number | End row index. |
startColumn | number | Start column index. |
endColumn | number | End column index. |
Returns
autoFitRowAsync(number)
Autofits the row height.
autoFitRowAsync(rowIndex: number) : Promise<void>;
Parameters:
Parameter | Type | Description |
---|---|---|
rowIndex | number | Row index. |
Returns
Remarks
AutoFitRow is an imprecise function.
autoFitRows()
Autofits all rows in this worksheet.
autoFitRows() : void;
autoFitRows(boolean)
Autofits all rows in this worksheet.
autoFitRows(onlyAuto: boolean) : void;
Parameters:
Parameter | Type | Description |
---|---|---|
onlyAuto | boolean | True,only autofits the row height when row height is not customed. |
autoFitRows(AutoFitterOptions)
Autofits all rows in this worksheet.
autoFitRows(options: AutoFitterOptions) : void;
Parameters:
Parameter | Type | Description |
---|---|---|
options | AutoFitterOptions | The auto fitter options |
autoFitRows(number, number)
Autofits row height in a range.
autoFitRows(startRow: number, endRow: number) : void;
Parameters:
Parameter | Type | Description |
---|---|---|
startRow | number | Start row index. |
endRow | number | End row index. |
autoFitRows(number, number, AutoFitterOptions)
Autofits row height in a range.
autoFitRows(startRow: number, endRow: number, options: AutoFitterOptions) : void;
Parameters:
Parameter | Type | Description |
---|---|---|
startRow | number | Start row index. |
endRow | number | End row index. |
options | AutoFitterOptions | The options of auto fitter. |
autoFitRowsAsync()
Autofits all rows in this worksheet.
autoFitRowsAsync() : Promise<void>;
Returns
autoFitRowsAsync(boolean)
Autofits all rows in this worksheet.
autoFitRowsAsync(onlyAuto: boolean) : Promise<void>;
Parameters:
Parameter | Type | Description |
---|---|---|
onlyAuto | boolean | True,only autofits the row height when row height is not customed. |
Returns
autoFitRowsAsync(AutoFitterOptions)
Autofits all rows in this worksheet.
autoFitRowsAsync(options: AutoFitterOptions) : Promise<void>;
Parameters:
Parameter | Type | Description |
---|---|---|
options | AutoFitterOptions | The auto fitter options |
Returns
autoFitRowsAsync(number, number)
Autofits row height in a range.
autoFitRowsAsync(startRow: number, endRow: number) : Promise<void>;
Parameters:
Parameter | Type | Description |
---|---|---|
startRow | number | Start row index. |
endRow | number | End row index. |
Returns
autoFitRowsAsync(number, number, AutoFitterOptions)
Autofits row height in a range.
autoFitRowsAsync(startRow: number, endRow: number, options: AutoFitterOptions) : Promise<void>;
Parameters:
Parameter | Type | Description |
---|---|---|
startRow | number | Start row index. |
endRow | number | End row index. |
options | AutoFitterOptions | The options of auto fitter. |
Returns
getAdvancedFilter()
Gets the settings of advanced filter.
getAdvancedFilter() : AdvancedFilter;
Returns
advanced_Filter(boolean, string, string, string, boolean)
Filters data using complex criteria.
advanced_Filter(isFilter: boolean, listRange: string, criteriaRange: string, copyTo: string, uniqueRecordOnly: boolean) : void;
Parameters:
Parameter | Type | Description |
---|---|---|
isFilter | boolean | Indicates whether filtering the list in place. |
listRange | string | The list range. |
criteriaRange | string | The criteria range. |
copyTo | string | The range where copying data to. |
uniqueRecordOnly | boolean | Only displaying or copying unique rows. |
removeAutoFilter()
Removes the auto filter of the worksheet.
removeAutoFilter() : void;
setVisible(boolean, boolean)
Sets the visible options.
setVisible(isVisible: boolean, ignoreError: boolean) : void;
Parameters:
Parameter | Type | Description |
---|---|---|
isVisible | boolean | Whether the worksheet is visible |
ignoreError | boolean | Whether to ignore error if this option is not valid. |
selectRange(number, number, number, number, boolean)
Selects a range.
selectRange(startRow: number, startColumn: number, totalRows: number, totalColumns: number, removeOthers: boolean) : void;
Parameters:
Parameter | Type | Description |
---|---|---|
startRow | number | The start row. |
startColumn | number | The start column |
totalRows | number | The number of rows. |
totalColumns | number | The number of columns |
removeOthers | boolean | True means removing other selected range and only select this range. |
removeAllDrawingObjects()
Removes all drawing objects in this worksheet.
removeAllDrawingObjects() : void;
clearComments()
Clears all comments in designer spreadsheet.
clearComments() : void;
protect(ProtectionType)
Protects worksheet.
protect(type: ProtectionType) : void;
Parameters:
Parameter | Type | Description |
---|---|---|
type | ProtectionType | Protection type. |
Remarks
This method protects worksheet without password. It can protect worksheet in all versions of Excel file.
protect(ProtectionType, string, string)
Protects worksheet.
protect(type: ProtectionType, password: string, oldPassword: string) : void;
Parameters:
Parameter | Type | Description |
---|---|---|
type | ProtectionType | Protection type. |
password | string | Password. |
oldPassword | string | If the worksheet is already protected by a password, please supply the old password. /// Otherwise, you can set a null value or blank string to this parameter. |
Remarks
This method can protect worksheet in all versions of Excel file.
Example
const { Workbook, ProtectionType } = require("aspose.cells.node");
//Instantiating a Workbook object
var excel = new Workbook("input/Book1.xls");
//Accessing the first worksheet in the Excel file
var worksheet = excel.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:
Parameter | Type | Description |
---|---|---|
password | string | Password |
Remarks
If the worksheet is protected without a password, you can set a null value or blank string to password parameter.
moveTo(number)
Moves the sheet to another location in the spreadsheet.
moveTo(index: number) : void;
Parameters:
Parameter | Type | Description |
---|---|---|
index | number | Destination sheet index. |
replace(string, string)
Replaces all cells’ text with a new string.
replace(oldString: string, newString: string) : number;
Parameters:
Parameter | Type | Description |
---|---|---|
oldString | string | Old string value. |
newString | string | New string value. |
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:
Parameter | Type | Description |
---|---|---|
options | ImageOrPrintOptions | The print options |
Returns
The automatic page breaks areas.
Remarks
Each cell area represents a paper.
toString()
Returns a string represents the current Worksheet object.
toString() : string;
startAccessCache(AccessCacheOptions)
Starts the session that uses caches to access the data in this worksheet.
startAccessCache(opts: AccessCacheOptions) : void;
Parameters:
Parameter | Type | Description |
---|---|---|
opts | AccessCacheOptions | options of data access |
Remarks
After finishing the access to the data, CloseAccessCache(AccessCacheOptions) should be invoked with same options to clear all caches and recover normal access mode.
closeAccessCache(AccessCacheOptions)
Closes the session that uses caches to access the data in this worksheet.
closeAccessCache(opts: AccessCacheOptions) : void;
Parameters:
Parameter | Type | Description |
---|---|---|
opts | AccessCacheOptions | options of data access |
convertFormulaReferenceStyle(string, boolean, number, number)
Converts the formula reference style.
convertFormulaReferenceStyle(formula: string, toR1C1: boolean, baseCellRow: number, baseCellColumn: number) : string;
Parameters:
Parameter | Type | Description |
---|---|---|
formula | string | The formula to be converted. |
toR1C1 | boolean | Which reference style to convert the formula to. /// If the original formula is of A1 reference style, /// then this value should be true so the formula will be converted from A1 to R1C1 reference style; /// If the original formula is of R1C1 reference style, /// then this value should be false so the formula will be converted from R1C1 to A1 reference style; |
baseCellRow | number | The row index of the base cell. |
baseCellColumn | number | The column index of the base cell. |
Returns
The converted formula.
calculateFormula(string)
Calculates a formula.
calculateFormula(formula: string) : Object;
Parameters:
Parameter | Type | Description |
---|---|---|
formula | string | Formula to be calculated. |
Returns
Calculated formula result.
calculateFormula(string, CalculationOptions)
Calculates a formula expression directly.
calculateFormula(formula: string, opts: CalculationOptions) : Object;
Parameters:
Parameter | Type | Description |
---|---|---|
formula | string | Formula to be calculated. |
opts | CalculationOptions | Options for calculating formula |
Returns
Calculated result of given formula. The returned object may be of possible types of Cell.Value, or ReferredArea.
Remarks
The formula will be calculated just like it has been set to cell A1. And the formula will be taken as normal formula. If you need the formula be calculated as an array formula and to get an array for the calculated result, please use [CalculateArrayFormula(string, CalculationOptions)](../calculatearrayformula(string, calculationoptions)/) instead.
calculateFormula(string, FormulaParseOptions, CalculationOptions, number, number, CalculationData)
Calculates a formula expression directly.
calculateFormula(formula: string, pOpts: FormulaParseOptions, cOpts: CalculationOptions, baseCellRow: number, baseCellColumn: number, calculationData: CalculationData) : Object;
Parameters:
Parameter | Type | Description |
---|---|---|
formula | string | Formula to be calculated. |
pOpts | FormulaParseOptions | Options for parsing formula. |
cOpts | CalculationOptions | Options for calculating formula. |
baseCellRow | number | The row index of the base cell. |
baseCellColumn | number | The column index of the base cell. |
calculationData | CalculationData | The calculation data. It is used for the situation /// that user needs to calculate some static formulas when implementing custom calculation engine. /// For such kind of situation, user needs to specify it with the calculation data provided /// for AbstractCalculationEngine.Calculate(CalculationData). |
Returns
Calculated result of given formula. The returned object may be of possible types of Cell.Value, or ReferredArea.
Remarks
The formula will be calculated just like it has been set to the specified base cell. And the formula will be taken as normal formula. If you need the formula be calculated as an array formula and to get an array for the calculated result, please use [CalculateArrayFormula(string, FormulaParseOptions, CalculationOptions, int, int, int, int, CalculationData)](../calculatearrayformula(string, formulaparseoptions, calculationoptions, int, int, int, int, calculationdata)/) instead.
calculateFormula(CalculationOptions, boolean)
Calculates all formulas in this worksheet.
calculateFormula(options: CalculationOptions, recursive: boolean) : void;
Parameters:
Parameter | Type | Description |
---|---|---|
options | CalculationOptions | Options for calculation |
recursive | boolean | True means if the worksheet’ cells depend on the cells of other worksheets, /// the dependent cells in other worksheets will be calculated too. /// False means all the formulas in the worksheet have been calculated and the values are right. |
calculateFormulaAsync(string)
Calculates a formula.
calculateFormulaAsync(formula: string) : Promise<Object>;
Parameters:
Parameter | Type | Description |
---|---|---|
formula | string | Formula to be calculated. |
Returns
Calculated formula result.
calculateFormulaAsync(string, CalculationOptions)
Calculates a formula expression directly.
calculateFormulaAsync(formula: string, opts: CalculationOptions) : Promise<Object>;
Parameters:
Parameter | Type | Description |
---|---|---|
formula | string | Formula to be calculated. |
opts | CalculationOptions | Options for calculating formula |
Returns
Calculated result of given formula. The returned object may be of possible types of Cell.Value, or ReferredArea.
Remarks
The formula will be calculated just like it has been set to cell A1. And the formula will be taken as normal formula. If you need the formula be calculated as an array formula and to get an array for the calculated result, please use [CalculateArrayFormula(string, CalculationOptions)](../calculatearrayformula(string, calculationoptions)/) instead.
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:
Parameter | Type | Description |
---|---|---|
formula | string | Formula to be calculated. |
pOpts | FormulaParseOptions | Options for parsing formula. |
cOpts | CalculationOptions | Options for calculating formula. |
baseCellRow | number | The row index of the base cell. |
baseCellColumn | number | The column index of the base cell. |
calculationData | CalculationData | The calculation data. It is used for the situation /// that user needs to calculate some static formulas when implementing custom calculation engine. /// For such kind of situation, user needs to specify it with the calculation data provided /// for AbstractCalculationEngine.Calculate(CalculationData). |
Returns
Calculated result of given formula. The returned object may be of possible types of Cell.Value, or ReferredArea.
Remarks
The formula will be calculated just like it has been set to the specified base cell. And the formula will be taken as normal formula. If you need the formula be calculated as an array formula and to get an array for the calculated result, please use [CalculateArrayFormula(string, FormulaParseOptions, CalculationOptions, int, int, int, int, CalculationData)](../calculatearrayformula(string, formulaparseoptions, calculationoptions, int, int, int, int, calculationdata)/) instead.
calculateFormulaAsync(CalculationOptions, boolean)
Calculates all formulas in this worksheet.
calculateFormulaAsync(options: CalculationOptions, recursive: boolean) : Promise<void>;
Parameters:
Parameter | Type | Description |
---|---|---|
options | CalculationOptions | Options for calculation |
recursive | boolean | True means if the worksheet’ cells depend on the cells of other worksheets, /// the dependent cells in other worksheets will be calculated too. /// False means all the formulas in the worksheet have been calculated and the values are right. |
Returns
calculateArrayFormula(string, CalculationOptions)
Calculates a formula as array formula.
calculateArrayFormula(formula: string, opts: CalculationOptions) : Object[][];
Parameters:
Parameter | Type | Description |
---|---|---|
formula | string | Formula to be calculated. |
opts | CalculationOptions | Options for calculating formula |
Returns
Object[][]
calculateArrayFormula(string, CalculationOptions, number, number)
Calculates a formula as array formula.
calculateArrayFormula(formula: string, opts: CalculationOptions, maxRowCount: number, maxColumnCount: number) : Object[][];
Parameters:
Parameter | Type | Description |
---|---|---|
formula | string | Formula to be calculated. |
opts | CalculationOptions | Options for calculating formula |
maxRowCount | number | the maximum row count of resultant data. /// If it is non-positive or greater than the actual row count, then actual row count will be used. |
maxColumnCount | number | the maximum column count of resultant data. /// If it is non-positive or greater than the actual row count, then actual column count will be used. |
Returns
Calculated formula result.
Remarks
The formula will be taken as dynamic array formula to calculate the dimension and result. User specified maximum dimension is used for cases that the calculated result is large data set (for example, the calculated result may correspond to a whole row or column data) but user does not need so large an array according to business requirement or for performance consideration.
calculateArrayFormula(string, FormulaParseOptions, CalculationOptions, number, number, number, number, CalculationData)
Calculates a formula as array formula.
calculateArrayFormula(formula: string, pOpts: FormulaParseOptions, cOpts: CalculationOptions, baseCellRow: number, baseCellColumn: number, maxRowCount: number, maxColumnCount: number, calculationData: CalculationData) : Object[][];
Parameters:
Parameter | Type | Description |
---|---|---|
formula | string | Formula to be calculated. |
pOpts | FormulaParseOptions | Options for parsing formula |
cOpts | CalculationOptions | Options for calculating formula |
baseCellRow | number | The row index of the base cell. |
baseCellColumn | number | The column index of the base cell. |
maxRowCount | number | The maximum row count of resultant data. /// If it is non-positive or greater than the actual row count, then actual row count will be used. |
maxColumnCount | number | The maximum column count of resultant data. /// If it is non-positive or greater than the actual row count, then actual column count will be used. |
calculationData | CalculationData | The calculation data. It is used for the situation /// that user needs to calculate some static formulas when implementing custom calculation engine. /// For such kind of situation, user needs to specify it with the calculation data provided /// for AbstractCalculationEngine.Calculate(CalculationData). |
Returns
Calculated formula result.
Remarks
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:
Parameter | Type | Description |
---|---|---|
formula | string | Formula to be calculated. |
opts | CalculationOptions | Options for calculating formula |
Returns
calculateArrayFormulaAsync(string, CalculationOptions, number, number)
Calculates a formula as array formula.
calculateArrayFormulaAsync(formula: string, opts: CalculationOptions, maxRowCount: number, maxColumnCount: number) : Promise<Object[][]>;
Parameters:
Parameter | Type | Description |
---|---|---|
formula | string | Formula to be calculated. |
opts | CalculationOptions | Options for calculating formula |
maxRowCount | number | the maximum row count of resultant data. /// If it is non-positive or greater than the actual row count, then actual row count will be used. |
maxColumnCount | number | the maximum column count of resultant data. /// If it is non-positive or greater than the actual row count, then actual column count will be used. |
Returns
Calculated formula result.
Remarks
The formula will be taken as dynamic array formula to calculate the dimension and result. User specified maximum dimension is used for cases that the calculated result is large data set (for example, the calculated result may correspond to a whole row or column data) but user does not need so large an array according to business requirement or for performance consideration.
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:
Parameter | Type | Description |
---|---|---|
formula | string | Formula to be calculated. |
pOpts | FormulaParseOptions | Options for parsing formula |
cOpts | CalculationOptions | Options for calculating formula |
baseCellRow | number | The row index of the base cell. |
baseCellColumn | number | The column index of the base cell. |
maxRowCount | number | The maximum row count of resultant data. /// If it is non-positive or greater than the actual row count, then actual row count will be used. |
maxColumnCount | number | The maximum column count of resultant data. /// If it is non-positive or greater than the actual row count, then actual column count will be used. |
calculationData | CalculationData | The calculation data. It is used for the situation /// that user needs to calculate some static formulas when implementing custom calculation engine. /// For such kind of situation, user needs to specify it with the calculation data provided /// for AbstractCalculationEngine.Calculate(CalculationData). |
Returns
Calculated formula result.
Remarks
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:
Parameter | Type | Description |
---|---|---|
option | PivotTableRefreshOption | The option for refreshing data source of pivot table. |
refreshPivotTablesAsync()
Refreshes all the PivotTables in this Worksheet.
refreshPivotTablesAsync() : Promise<void>;
Returns
refreshPivotTablesAsync(PivotTableRefreshOption)
Refreshes all the PivotTables in this Worksheet.
refreshPivotTablesAsync(option: PivotTableRefreshOption) : Promise<boolean>;
Parameters:
Parameter | Type | Description |
---|---|---|
option | PivotTableRefreshOption | The option for refreshing data source of pivot table. |
Returns
isNull()
Checks whether the implementation object is null.
isNull() : boolean;