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
| 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
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;
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.
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.
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.
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.
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.
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.
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.
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
autoFitRows()
Autofits all rows in this worksheet.
autoFitRows() : void;
autoFitRows(boolean)
Autofits all rows in this worksheet.
autoFitRows(onlyAuto: boolean) : void;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| onlyAuto | boolean | True,only autofits the row height when row height is not customed. |
autoFitRows(AutoFitterOptions)
Autofits all rows in this worksheet.
autoFitRows(options: AutoFitterOptions) : void;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| options | AutoFitterOptions | The auto fitter options |
autoFitRows(number, number)
Autofits row height in a range.
autoFitRows(startRow: number, endRow: number) : void;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| startRow | number | Start row index. |
| endRow | number | End row index. |
autoFitRows(number, number, AutoFitterOptions)
Autofits row height in a range.
autoFitRows(startRow: number, endRow: number, options: AutoFitterOptions) : void;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| startRow | number | Start row index. |
| endRow | number | End row index. |
| options | AutoFitterOptions | The options of auto fitter. |
getAdvancedFilter()
Gets the settings of advanced filter.
getAdvancedFilter() : AdvancedFilter;
Returns
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, 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:
| 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.
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.
calculateFormulaAsync(string)
Calculates a formula.
calculateFormulaAsync(formula: string) : Promise<VObject>;
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<VObject>;
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<VObject>;
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
calculateFormula(string)
Calculates a formula.
calculateFormula(formula: string) : VObject;
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) : VObject;
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) : VObject;
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. |
calculateArrayFormulaAsync(string, CalculationOptions)
Calculates a formula as array formula.
calculateArrayFormulaAsync(formula: string, opts: CalculationOptions) : Promise<VObject[][]>;
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<VObject[][]>;
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<VObject[][]>;
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.
calculateArrayFormula(string, CalculationOptions)
Calculates a formula as array formula.
calculateArrayFormula(formula: string, opts: CalculationOptions) : VObject[][];
Parameters:
| Parameter | Type | Description |
|---|---|---|
| formula | string | Formula to be calculated. |
| opts | CalculationOptions | Options for calculating formula |
Returns
calculateArrayFormula(string, CalculationOptions, number, number)
Calculates a formula as array formula.
calculateArrayFormula(formula: string, opts: CalculationOptions, maxRowCount: number, maxColumnCount: number) : VObject[][];
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) : VObject[][];
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.
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
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. |
toString()
Returns a string represents the current Worksheet object.
toString() : string;