WorksheetCollection

WorksheetCollection class

Encapsulates a collection of Worksheet objects.

class WorksheetCollection;

Methods

Method	Description
get(number)	Gets the Worksheet element at the specified index.
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.
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.

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.

getWebExtensionTaskPanes()

Gets the list of task panes.

getWebExtensionTaskPanes() : WebExtensionTaskPaneCollection;

Returns

WebExtensionTaskPaneCollection

getWebExtensions()

Gets the list of task panes.

getWebExtensions() : WebExtensionCollection;

Returns

WebExtensionCollection

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

NameCollection

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

DxfCollection

getXmlMaps()

Gets and sets the XML maps in the workbook.

getXmlMaps() : XmlMapCollection;

Returns

XmlMapCollection

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

getCustomDocumentProperties()

Returns a DocumentProperty collection that represents all the custom document properties of the spreadsheet.

getCustomDocumentProperties() : CustomDocumentPropertyCollection;

Returns

CustomDocumentPropertyCollection

getExternalLinks()

Represents external links in a workbook.

getExternalLinks() : ExternalLinkCollection;

Returns

ExternalLinkCollection

getTableStyles()

Gets TableStyles object.

getTableStyles() : TableStyleCollection;

Returns

TableStyleCollection

getRevisionLogs()

Represents revision logs.

getRevisionLogs() : RevisionLogCollection;

Returns

RevisionLogCollection

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.

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

Range

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

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;