WorksheetCollection
WorksheetCollection class
Encapsulates a collection of Worksheet objects.
class WorksheetCollection;
Example
const { Workbook } = require("aspose.cells.node");
var workbook = new Workbook();
var sheets = workbook.worksheets;
//Add a worksheet
sheets.add();
//Change the name of a worksheet
sheets.get(0).name = "First Sheet";
//Set the active sheet to the second worksheet
sheets.activeSheetIndex = 1;
Properties
| Property | Type | Description |
|---|---|---|
| webExtensionTaskPanes | WebExtensionTaskPaneCollection | Readonly. Gets the list of task panes. |
| webExtensions | WebExtensionCollection | Readonly. Gets the list of task panes. |
| threadedCommentAuthors | ThreadedCommentAuthorCollection | Readonly. Gets the list of threaded comment authors. |
| isRefreshAllConnections | boolean | Indicates whether refresh all connections on opening file in MS Excel. |
| names | NameCollection | Readonly. Gets the collection of all the Name objects in the spreadsheet. |
| activeSheetName | string | Represents the name of active worksheet when the spreadsheet is opened. |
| activeSheetIndex | number | Represents the index of active worksheet when the spreadsheet is opened. |
| dxfs | DxfCollection | Readonly. Gets the master differential formatting records. |
| xmlMaps | XmlMapCollection | Gets and sets the XML maps in the workbook. |
| builtInDocumentProperties | BuiltInDocumentPropertyCollection | Readonly. Returns a DocumentProperty collection that represents all the built-in document properties of the spreadsheet. |
| customDocumentProperties | CustomDocumentPropertyCollection | Readonly. Returns a DocumentProperty collection that represents all the custom document properties of the spreadsheet. |
| oleSize | Object | Gets and Sets displayed size when Workbook file is used as an Ole object. |
| externalLinks | ExternalLinkCollection | Readonly. Represents external links in a workbook. |
| tableStyles | TableStyleCollection | Readonly. Gets TableStyles object. |
| revisionLogs | RevisionLogCollection | Readonly. Represents revision logs. |
| sensitivityLabels | SensitivityLabelCollection | Readonly. Represents all sensitivity labels. |
Methods
| Method | Description |
|---|---|
| get(number) | Gets the Worksheet element at the specified index. |
| get(string) | Gets the Worksheet element with the specified name. |
| getWebExtensionTaskPanes() | @deprecated. Please use the ‘webExtensionTaskPanes’ property instead. Gets the list of task panes. |
| getWebExtensions() | @deprecated. Please use the ‘webExtensions’ property instead. Gets the list of task panes. |
| getThreadedCommentAuthors() | @deprecated. Please use the ’threadedCommentAuthors’ property instead. Gets the list of threaded comment authors. |
| isRefreshAllConnections() | @deprecated. Please use the ‘isRefreshAllConnections’ property instead. Indicates whether refresh all connections on opening file in MS Excel. |
| setIsRefreshAllConnections(boolean) | @deprecated. Please use the ‘isRefreshAllConnections’ property instead. Indicates whether refresh all connections on opening file in MS Excel. |
| getNames() | @deprecated. Please use the ’names’ property instead. Gets the collection of all the Name objects in the spreadsheet. |
| getActiveSheetName() | @deprecated. Please use the ‘activeSheetName’ property instead. Represents the name of active worksheet when the spreadsheet is opened. |
| setActiveSheetName(string) | @deprecated. Please use the ‘activeSheetName’ property instead. Represents the name of active worksheet when the spreadsheet is opened. |
| getActiveSheetIndex() | @deprecated. Please use the ‘activeSheetIndex’ property instead. Represents the index of active worksheet when the spreadsheet is opened. |
| setActiveSheetIndex(number) | @deprecated. Please use the ‘activeSheetIndex’ property instead. Represents the index of active worksheet when the spreadsheet is opened. |
| getDxfs() | @deprecated. Please use the ‘dxfs’ property instead. Gets the master differential formatting records. |
| getXmlMaps() | @deprecated. Please use the ‘xmlMaps’ property instead. Gets and sets the XML maps in the workbook. |
| setXmlMaps(XmlMapCollection) | @deprecated. Please use the ‘xmlMaps’ property instead. Gets and sets the XML maps in the workbook. |
| getBuiltInDocumentProperties() | @deprecated. Please use the ‘builtInDocumentProperties’ property instead. Returns a DocumentProperty collection that represents all the built-in document properties of the spreadsheet. |
| getCustomDocumentProperties() | @deprecated. Please use the ‘customDocumentProperties’ property instead. Returns a DocumentProperty collection that represents all the custom document properties of the spreadsheet. |
| getOleSize() | @deprecated. Please use the ‘oleSize’ property instead. Gets and Sets displayed size when Workbook file is used as an Ole object. |
| setOleSize(Object) | @deprecated. Please use the ‘oleSize’ property instead. Gets and Sets displayed size when Workbook file is used as an Ole object. |
| getExternalLinks() | @deprecated. Please use the ’externalLinks’ property instead. Represents external links in a workbook. |
| getTableStyles() | @deprecated. Please use the ’tableStyles’ property instead. Gets TableStyles object. |
| getRevisionLogs() | @deprecated. Please use the ‘revisionLogs’ property instead. Represents revision logs. |
| getSensitivityLabels() | @deprecated. Please use the ‘sensitivityLabels’ property instead. Represents all sensitivity labels. |
| createRange(string, number) | Creates a Range object from an address of the range. |
| createUnionRange(string, number) | Creates a Range object from an address of the range. |
| getSheetByCodeName(string) | Gets the worksheet by the code name. |
| sortNames() | Sorts the defined names. |
| insert(number, SheetType) | Insert a worksheet. |
| insert(number, SheetType, string) | Insert a worksheet. |
| add(SheetType) | Adds a worksheet to the collection. |
| add() | Adds a worksheet to the collection. |
| add(string) | Adds a worksheet to the collection. |
| swapSheet(number, number) | Swaps the two sheets. |
| registerAddInFunction(string, string, boolean) | Adds addin function into the workbook |
| registerAddInFunction(number, string) | Adds addin function into the workbook |
| removeAt(string) | Removes the element at a specified name. |
| removeAt(number) | Removes the element at a specified index. |
| clear() | Clear all worksheets. |
| addCopy(string) | Adds a worksheet to the collection and copies data from an existed worksheet. |
| addCopy(number) | Adds a worksheet to the collection and copies data from an existed worksheet. |
| addCopy(Worksheet[], string[]) | Copy a group of worksheets. |
| getRangeByName(string) | Gets Range object by pre-defined name. |
| getRangeByName(string, number, boolean) | Gets Range by pre-defined name or table’s name |
| getNamedRanges() | Gets all pre-defined named ranges in the spreadsheet. |
| getNamedRangesAndTables() | Gets all pre-defined named ranges in the spreadsheet. |
| setOleSize(number, number, number, number) | Sets displayed size when Workbook file is used as an Ole object. |
| clearPivottables() | Clears pivot tables from the spreadsheet. |
| refreshAll() | Refresh all pivot tables and charts with pivot source. |
| refreshAllAsync() | Refresh all pivot tables and charts with pivot source. |
| refreshPivotTables() | Refreshes all the PivotTables in the Excel file. |
| refreshPivotTables(PivotTableRefreshOption) | Refreshes all the PivotTables in the Excel file. |
| refreshPivotTablesAsync() | Refreshes all the PivotTables in the Excel file. |
| refreshPivotTablesAsync(PivotTableRefreshOption) | Refreshes all the PivotTables in the Excel file. |
| getCount() | @deprecated. Please use the ‘count’ property instead. Gets the number of elements contained in. |
| isNull() | Checks whether the implementation object is null. |
webExtensionTaskPanes
Readonly. Gets the list of task panes.
webExtensionTaskPanes : WebExtensionTaskPaneCollection;
webExtensions
Readonly. Gets the list of task panes.
webExtensions : WebExtensionCollection;
threadedCommentAuthors
Readonly. Gets the list of threaded comment authors.
threadedCommentAuthors : ThreadedCommentAuthorCollection;
isRefreshAllConnections
Indicates whether refresh all connections on opening file in MS Excel.
isRefreshAllConnections : boolean;
names
Readonly. Gets the collection of all the Name objects in the spreadsheet.
names : NameCollection;
activeSheetName
Represents the name of active worksheet when the spreadsheet is opened.
activeSheetName : string;
activeSheetIndex
Represents the index of active worksheet when the spreadsheet is opened.
activeSheetIndex : number;
Remarks
Sheet index is zero based.
dxfs
Readonly. Gets the master differential formatting records.
dxfs : DxfCollection;
xmlMaps
Gets and sets the XML maps in the workbook.
xmlMaps : XmlMapCollection;
builtInDocumentProperties
Readonly. Returns a DocumentProperty collection that represents all the built-in document properties of the spreadsheet.
builtInDocumentProperties : BuiltInDocumentPropertyCollection;
Remarks
A new property cannot be added to built-in document properties list. You can only get a built-in property and change its value. The following is the built-in properties name list:
Title
Subject
Author
Keywords
Comments
Template
Last Author
Revision Number
Application Name
Last Print Date
Creation Date
Last Save Time
Total Editing Time
Number of Pages
Number of Words
Number of Characters
Security
Category
Format
Manager
Company
Number of Bytes
Number of Lines
Number of Paragraphs
Number of Slides
Number of Notes
Number of Hidden Slides
Number of Multimedia Clips
Example
const { Workbook } = require("aspose.cells.node");
var workbook = new Workbook();
var doc = workbook.worksheets.builtInDocumentProperties.get("Author");
doc.value = "John Smith";
customDocumentProperties
Readonly. Returns a DocumentProperty collection that represents all the custom document properties of the spreadsheet.
customDocumentProperties : CustomDocumentPropertyCollection;
Example
const { Workbook } = require("aspose.cells.node");
var excel = new Workbook();
excel.worksheets.customDocumentProperties.add("Checked by", "Jane");
oleSize
Gets and Sets displayed size when Workbook file is used as an Ole object.
oleSize : Object;
Remarks
Null means no ole size setting.
externalLinks
Readonly. Represents external links in a workbook.
externalLinks : ExternalLinkCollection;
tableStyles
Readonly. Gets TableStyles object.
tableStyles : TableStyleCollection;
revisionLogs
Readonly. Represents revision logs.
revisionLogs : RevisionLogCollection;
sensitivityLabels
Readonly. Represents all sensitivity labels.
sensitivityLabels : SensitivityLabelCollection;
get(number)
Gets the Worksheet element at the specified index.
get(index: number) : Worksheet;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| index | number | The zero based index of the element. |
Returns
The element at the specified index.
get(string)
Gets the Worksheet element with the specified name.
get(sheetName: string) : Worksheet;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| sheetName | string | Worksheet name |
Returns
The element with the specified name.
getWebExtensionTaskPanes()
@deprecated. Please use the ‘webExtensionTaskPanes’ property instead. Gets the list of task panes.
getWebExtensionTaskPanes() : WebExtensionTaskPaneCollection;
Returns
WebExtensionTaskPaneCollection
getWebExtensions()
@deprecated. Please use the ‘webExtensions’ property instead. Gets the list of task panes.
getWebExtensions() : WebExtensionCollection;
Returns
getThreadedCommentAuthors()
@deprecated. Please use the ’threadedCommentAuthors’ property instead. Gets the list of threaded comment authors.
getThreadedCommentAuthors() : ThreadedCommentAuthorCollection;
Returns
ThreadedCommentAuthorCollection
isRefreshAllConnections()
@deprecated. Please use the ‘isRefreshAllConnections’ property instead. Indicates whether refresh all connections on opening file in MS Excel.
isRefreshAllConnections() : boolean;
setIsRefreshAllConnections(boolean)
@deprecated. Please use the ‘isRefreshAllConnections’ property instead. Indicates whether refresh all connections on opening file in MS Excel.
setIsRefreshAllConnections(value: boolean) : void;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| value | boolean | The value to set. |
getNames()
@deprecated. Please use the ’names’ property instead. Gets the collection of all the Name objects in the spreadsheet.
getNames() : NameCollection;
Returns
getActiveSheetName()
@deprecated. Please use the ‘activeSheetName’ property instead. Represents the name of active worksheet when the spreadsheet is opened.
getActiveSheetName() : string;
setActiveSheetName(string)
@deprecated. Please use the ‘activeSheetName’ property instead. Represents the name of active worksheet when the spreadsheet is opened.
setActiveSheetName(value: string) : void;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| value | string | The value to set. |
getActiveSheetIndex()
@deprecated. Please use the ‘activeSheetIndex’ property instead. Represents the index of active worksheet when the spreadsheet is opened.
getActiveSheetIndex() : number;
Remarks
Sheet index is zero based.
setActiveSheetIndex(number)
@deprecated. Please use the ‘activeSheetIndex’ property instead. Represents the index of active worksheet when the spreadsheet is opened.
setActiveSheetIndex(value: number) : void;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| value | number | The value to set. |
Remarks
Sheet index is zero based.
getDxfs()
@deprecated. Please use the ‘dxfs’ property instead. Gets the master differential formatting records.
getDxfs() : DxfCollection;
Returns
getXmlMaps()
@deprecated. Please use the ‘xmlMaps’ property instead. Gets and sets the XML maps in the workbook.
getXmlMaps() : XmlMapCollection;
Returns
setXmlMaps(XmlMapCollection)
@deprecated. Please use the ‘xmlMaps’ property instead. Gets and sets the XML maps in the workbook.
setXmlMaps(value: XmlMapCollection) : void;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| value | XmlMapCollection | The value to set. |
getBuiltInDocumentProperties()
@deprecated. Please use the ‘builtInDocumentProperties’ property instead. Returns a DocumentProperty collection that represents all the built-in document properties of the spreadsheet.
getBuiltInDocumentProperties() : BuiltInDocumentPropertyCollection;
Returns
BuiltInDocumentPropertyCollection
Remarks
A new property cannot be added to built-in document properties list. You can only get a built-in property and change its value. The following is the built-in properties name list:
Title
Subject
Author
Keywords
Comments
Template
Last Author
Revision Number
Application Name
Last Print Date
Creation Date
Last Save Time
Total Editing Time
Number of Pages
Number of Words
Number of Characters
Security
Category
Format
Manager
Company
Number of Bytes
Number of Lines
Number of Paragraphs
Number of Slides
Number of Notes
Number of Hidden Slides
Number of Multimedia Clips
getCustomDocumentProperties()
@deprecated. Please use the ‘customDocumentProperties’ property instead. Returns a DocumentProperty collection that represents all the custom document properties of the spreadsheet.
getCustomDocumentProperties() : CustomDocumentPropertyCollection;
Returns
CustomDocumentPropertyCollection
getOleSize()
@deprecated. Please use the ‘oleSize’ property instead. Gets and Sets displayed size when Workbook file is used as an Ole object.
getOleSize() : Object;
Remarks
Null means no ole size setting.
setOleSize(Object)
@deprecated. Please use the ‘oleSize’ property instead. Gets and Sets displayed size when Workbook file is used as an Ole object.
setOleSize(value: Object) : void;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| value | Object | The value to set. |
Remarks
Null means no ole size setting.
getExternalLinks()
@deprecated. Please use the ’externalLinks’ property instead. Represents external links in a workbook.
getExternalLinks() : ExternalLinkCollection;
Returns
getTableStyles()
@deprecated. Please use the ’tableStyles’ property instead. Gets TableStyles object.
getTableStyles() : TableStyleCollection;
Returns
getRevisionLogs()
@deprecated. Please use the ‘revisionLogs’ property instead. Represents revision logs.
getRevisionLogs() : RevisionLogCollection;
Returns
getSensitivityLabels()
@deprecated. Please use the ‘sensitivityLabels’ property instead. Represents all sensitivity labels.
getSensitivityLabels() : SensitivityLabelCollection;
Returns
createRange(string, number)
Creates a Range object from an address of the range.
createRange(address: string, sheetIndex: number) : Range;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| address | string | The address of the range. |
| sheetIndex | number | The sheet index. |
Returns
A Range object
createUnionRange(string, number)
Creates a Range object from an address of the range.
createUnionRange(address: string, sheetIndex: number) : UnionRange;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| address | string | The address of the range. |
| sheetIndex | number | The sheet index. |
Returns
A Range object
getSheetByCodeName(string)
Gets the worksheet by the code name.
getSheetByCodeName(codeName: string) : Worksheet;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| codeName | string | Worksheet code name. |
Returns
The element with the specified code name.
sortNames()
Sorts the defined names.
sortNames() : void;
Remarks
If you create a large amount of named ranges in the Excel file, please call this method after all named ranges are created and before saving
insert(number, SheetType)
Insert a worksheet.
insert(index: number, sheetType: SheetType) : Worksheet;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| index | number | The sheet index |
| sheetType | SheetType | The sheet type. |
Returns
Returns an inserted worksheet.
insert(number, SheetType, string)
Insert a worksheet.
insert(index: number, sheetType: SheetType, sheetName: string) : Worksheet;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| index | number | The sheet index |
| sheetType | SheetType | The sheet type. |
| sheetName | string | The sheet name. |
Returns
Returns an inserted worksheet.
add(SheetType)
Adds a worksheet to the collection.
add(type: SheetType) : number;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| type | SheetType | Worksheet type. |
Returns
Worksheet object index.
Example
const { Workbook, SheetType, ChartType } = require("aspose.cells.node");
var workbook = new Workbook();
workbook.worksheets.add(SheetType.Chart);
var cells = workbook.worksheets.get(0).cells;
cells.get("c2").putValue(5000);
cells.get("c3").putValue(3000);
cells.get("c4").putValue(4000);
cells.get("c5").putValue(5000);
cells.get("c6").putValue(6000);
var charts = workbook.worksheets.get(1).charts;
var chartIndex = charts.add(ChartType.Column, 10, 10, 20, 20);
var chart = charts.get(chartIndex);
chart.nSeries.add("Sheet1!C2:C6", true);
add()
Adds a worksheet to the collection.
add() : number;
Returns
Worksheet object index.
add(string)
Adds a worksheet to the collection.
add(sheetName: string) : Worksheet;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| sheetName | string | Worksheet name |
Returns
Worksheet object.
swapSheet(number, number)
Swaps the two sheets.
swapSheet(sheetIndex1: number, sheetIndex2: number) : void;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| sheetIndex1 | number | The first worksheet. |
| sheetIndex2 | number | The second worksheet. |
registerAddInFunction(string, string, boolean)
Adds addin function into the workbook
registerAddInFunction(addInFile: string, functionName: string, lib: boolean) : number;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| addInFile | string | the file contains the addin functions |
| functionName | string | the addin function name |
| lib | boolean | whether the given addin file is in the directory or sub-directory of Workbook Add-In library. /// This flag takes effect and makes difference when given addInFile is of relative path: /// true denotes the path is relative to Add-In library and false denotes the path is relative to this Workbook. |
Returns
ID of the data which contains given addin function
registerAddInFunction(number, string)
Adds addin function into the workbook
registerAddInFunction(id: number, functionName: string) : string;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| id | number | ID of the data which contains addin functions, /// can be got by the first call of [RegisterAddInFunction(string, string, bool)](../registeraddinfunction(string, string, bool)/) for the same addin file. |
| functionName | string | the addin function name |
Returns
URL of the addin file which contains addin functions
removeAt(string)
Removes the element at a specified name.
removeAt(name: string) : void;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| name | string | The name of the element to remove. |
removeAt(number)
Removes the element at a specified index.
removeAt(index: number) : void;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| index | number | The index value of the element to remove. |
clear()
Clear all worksheets.
clear() : void;
Remarks
A workbook must contains a worksheet.
addCopy(string)
Adds a worksheet to the collection and copies data from an existed worksheet.
addCopy(sheetName: string) : number;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| sheetName | string | Name of source worksheet. |
Returns
Worksheet object index.
addCopy(number)
Adds a worksheet to the collection and copies data from an existed worksheet.
addCopy(sheetIndex: number) : number;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| sheetIndex | number | Index of source worksheet. |
Returns
Worksheet object index.
addCopy(Worksheet[], string[])
Copy a group of worksheets.
addCopy(source: Worksheet[], destSheetNames: string[]) : void;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| source | Worksheet[] | The source worksheets. |
| destSheetNames | string[] | The names of the copied sheets. |
getRangeByName(string)
Gets Range object by pre-defined name.
getRangeByName(rangeName: string) : Range;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| rangeName | string | Name of range. |
Returns
Range object.
Returns null if the named range does not exist.getRangeByName(string, number, boolean)
Gets Range by pre-defined name or table’s name
getRangeByName(rangeName: string, currentSheetIndex: number, includeTable: boolean) : Range;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| rangeName | string | Name of range or table’s name. |
| currentSheetIndex | number | The sheet index. -1 represents global . |
| includeTable | boolean | Indicates whether checking all tables. |
Returns
getNamedRanges()
Gets all pre-defined named ranges in the spreadsheet.
getNamedRanges() : Range[];
Returns
An array of Range objects. If the defined Name’s reference is external or has multiple ranges, no Range object will be returned for this Name.
Returns null if the named range does not exist.getNamedRangesAndTables()
Gets all pre-defined named ranges in the spreadsheet.
getNamedRangesAndTables() : Range[];
Returns
An array of Range objects.
Returns null if the named range does not exist.setOleSize(number, number, number, number)
Sets displayed size when Workbook file is used as an Ole object.
setOleSize(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. |
Remarks
This method is generally used to adjust display size in ppt file or doc file.
clearPivottables()
Clears pivot tables from the spreadsheet.
clearPivottables() : void;
refreshAll()
Refresh all pivot tables and charts with pivot source.
refreshAll() : void;
refreshAllAsync()
Refresh all pivot tables and charts with pivot source.
refreshAllAsync() : Promise<void>;
Returns
refreshPivotTables()
Refreshes all the PivotTables in the Excel file.
refreshPivotTables() : void;
refreshPivotTables(PivotTableRefreshOption)
Refreshes all the PivotTables in the Excel file.
refreshPivotTables(option: PivotTableRefreshOption) : boolean;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| option | PivotTableRefreshOption | The option for refreshing data source of the pivot tables. |
refreshPivotTablesAsync()
Refreshes all the PivotTables in the Excel file.
refreshPivotTablesAsync() : Promise<void>;
Returns
refreshPivotTablesAsync(PivotTableRefreshOption)
Refreshes all the PivotTables in the Excel file.
refreshPivotTablesAsync(option: PivotTableRefreshOption) : Promise<boolean>;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| option | PivotTableRefreshOption | The option for refreshing data source of the pivot tables. |
Returns
getCount()
@deprecated. Please use the ‘count’ property instead. Gets the number of elements contained in.
getCount() : number;
isNull()
Checks whether the implementation object is null.
isNull() : boolean;