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.getWorksheets();
//Add a worksheet
sheets.add();
//Change the name of a worksheet
sheets.get(0).setName("First Sheet");
//Set the active sheet to the second worksheet
sheets.setActiveSheetIndex(1);
Methods
| Method | Description |
|---|---|
| get(number) | Gets the Worksheet element at the specified index. |
| get(string) | Gets the Worksheet element with the specified name. |
| getWebExtensionTaskPanes() | Gets the list of task panes. |
| getWebExtensions() | Gets the list of task panes. |
| getThreadedCommentAuthors() | Gets the list of threaded comment authors. |
| isRefreshAllConnections() | Indicates whether refresh all connections on opening file in MS Excel. |
| setIsRefreshAllConnections(boolean) | Indicates whether refresh all connections on opening file in MS Excel. |
| getNames() | Gets the collection of all the Name objects in the spreadsheet. |
| getActiveSheetName() | Represents the name of active worksheet when the spreadsheet is opened. |
| setActiveSheetName(string) | Represents the name of active worksheet when the spreadsheet is opened. |
| getActiveSheetIndex() | Represents the index of active worksheet when the spreadsheet is opened. |
| setActiveSheetIndex(number) | Represents the index of active worksheet when the spreadsheet is opened. |
| getDxfs() | Gets the master differential formatting records. |
| getXmlMaps() | Gets and sets the XML maps in the workbook. |
| setXmlMaps(XmlMapCollection) | Gets and sets the XML maps in the workbook. |
| getBuiltInDocumentProperties() | Returns a DocumentProperty collection that represents all the built-in document properties of the spreadsheet. |
| getCustomDocumentProperties() | Returns a DocumentProperty collection that represents all the custom document properties of the spreadsheet. |
| getOleSize() | Gets and Sets displayed size when Workbook file is used as an Ole object. |
| setOleSize(object) | Gets and Sets displayed size when Workbook file is used as an Ole object. |
| getExternalLinks() | Represents external links in a workbook. |
| getTableStyles() | Gets TableStyles object. |
| getRevisionLogs() | Represents revision logs. |
| 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. |
| refreshPivotTables() | Refreshes all the PivotTables in the Excel file. |
| refreshPivotTables(PivotTableRefreshOption) | Refreshes all the PivotTables in the Excel file. |
| getCount() | Gets the number of elements contained in. |
| isNull() | Checks whether the implementation object is null. |
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()
Gets the list of task panes.
getWebExtensionTaskPanes() : WebExtensionTaskPaneCollection;
Returns
WebExtensionTaskPaneCollection
getWebExtensions()
Gets the list of task panes.
getWebExtensions() : WebExtensionCollection;
Returns
getThreadedCommentAuthors()
Gets the list of threaded comment authors.
getThreadedCommentAuthors() : ThreadedCommentAuthorCollection;
Returns
ThreadedCommentAuthorCollection
isRefreshAllConnections()
Indicates whether refresh all connections on opening file in MS Excel.
isRefreshAllConnections() : boolean;
setIsRefreshAllConnections(boolean)
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()
Gets the collection of all the Name objects in the spreadsheet.
getNames() : NameCollection;
Returns
getActiveSheetName()
Represents the name of active worksheet when the spreadsheet is opened.
getActiveSheetName() : string;
setActiveSheetName(string)
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()
Represents the index of active worksheet when the spreadsheet is opened.
getActiveSheetIndex() : number;
Remarks
Sheet index is zero based.
setActiveSheetIndex(number)
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()
Gets the master differential formatting records.
getDxfs() : DxfCollection;
Returns
getXmlMaps()
Gets and sets the XML maps in the workbook.
getXmlMaps() : XmlMapCollection;
Returns
setXmlMaps(XmlMapCollection)
Gets and sets the XML maps in the workbook.
setXmlMaps(value: XmlMapCollection) : void;
Parameters:
| Parameter | Type | Description |
|---|---|---|
| value | XmlMapCollection | The value to set. |
getBuiltInDocumentProperties()
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
Example
const { Workbook } = require("aspose.cells.node");
var workbook = new Workbook();
var doc = workbook.getWorksheets().getBuiltInDocumentProperties().get("Author");
doc.setValue("John Smith");
getCustomDocumentProperties()
Returns a DocumentProperty collection that represents all the custom document properties of the spreadsheet.
getCustomDocumentProperties() : CustomDocumentPropertyCollection;
Returns
CustomDocumentPropertyCollection
Example
const { Workbook } = require("aspose.cells.node");
var excel = new Workbook();
excel.getWorksheets().getCustomDocumentProperties().add("Checked by", "Jane");
getOleSize()
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)
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()
Represents external links in a workbook.
getExternalLinks() : ExternalLinkCollection;
Returns
getTableStyles()
Gets TableStyles object.
getTableStyles() : TableStyleCollection;
Returns
getRevisionLogs()
Represents revision logs.
getRevisionLogs() : RevisionLogCollection;
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.getWorksheets().add(SheetType.Chart);
var cells = workbook.getWorksheets().get(0).getCells();
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.getWorksheets().get(1).getCharts();
var chartIndex = charts.add(ChartType.Column, 10, 10, 20, 20);
var chart = charts.get(chartIndex);
chart.getNSeries().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;
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. |
getCount()
Gets the number of elements contained in.
getCount() : number;
isNull()
Checks whether the implementation object is null.
isNull() : boolean;