asposecells.api
Class WorksheetCollection

Encapsulates a collection of Worksheet objects.

Example:

workbook = Workbook()
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)

Property Getters/Setters Summary
methodgetActiveSheetIndex()
methodsetActiveSheetIndex(value)
           Represents the index of active worksheet when the spreadsheet is opened.
methodgetBuiltInDocumentProperties()
           Returns a DocumentProperties collection that represents all the built-in document properties of the spreadsheet.
methodgetCount()
          
methodgetCustomDocumentProperties()
           Returns a DocumentProperties collection that represents all the custom document properties of the spreadsheet.
methodgetDxfs()
           Gets the master differential formatting records.
methodgetExternalLinks()
           Represents external links in a workbook.
methodisRefreshAllConnections()
methodsetRefreshAllConnections(value)
           Indicates whether refresh all connections on opening file in MS Excel.
methodgetNames()
           Gets the collection of all the Name objects in the spreadsheet.
methodgetOleSize()
methodsetOleSize(value)
           Gets and Sets displayed size when Workbook file is used as an Ole object.
methodgetRevisionLogs()
           Represents revision logs.
methodgetTableStyles()
           Gets TableStyles object.
methodgetThreadedCommentAuthors()
           Gets the list of threaded comment authors.
methodgetWebExtensions()
           Gets the list of task panes.
methodgetWebExtensionTaskPanes()
           Gets the list of task panes.
methodgetXmlMaps()
methodsetXmlMaps(value)
           Gets and sets the XML maps in the workbook.
methodget(index)
           Gets the Worksheet element at the specified index.
methodget(sheetName)
           Gets the Worksheet element with the specified name.
 
Method Summary
methodadd()
           Adds a worksheet to the collection.
methodadd(type)
           Adds a worksheet to the collection.
methodadd(value)→ inherited from CollectionBase
          Reserved for internal use.
methodadd(sheetName)
           Adds a worksheet to the collection.
methodaddCopy(sheetIndex)
           Adds a worksheet to the collection and copies data from an existed worksheet.
methodaddCopy(sheetName)
           Adds a worksheet to the collection and copies data from an existed worksheet.
methodclear()
           Clear all worksheets.
methodclearPivottables()
           Clears pivot tables from the spreadsheet.
methodcontains(value)→ inherited from CollectionBase
          Reserved for internal use.
methodcreateRange(address, sheetIndex)
           Creates a Range object from an address of the range.
methodget(index)→ inherited from CollectionBase
          Reserved for internal use.
methodgetNamedRanges()
           Gets all pre-defined named ranges in the spreadsheet.
methodgetNamedRangesAndTables()
           Gets all pre-defined named ranges in the spreadsheet.
methodgetRangeByName(rangeName)
           Gets Range object by pre-defined name.
methodgetSheetByCodeName(codeName)
           Gets the worksheet by the code name.
methodindexOf(value)→ inherited from CollectionBase
          Reserved for internal use.
methodinsert(index, sheetType)
           Insert a worksheet.
methodinsert(index, sheetType, sheetName)
           Insert a worksheet.
methoditerator()→ inherited from CollectionBase
          
methodregisterAddInFunction(id, functionName)
           Adds addin function into the workbook
methodregisterAddInFunction(addInFile, functionName, lib)
           Adds addin function into the workbook
methodremoveAt(index)
           Removes the element at a specified index.
methodremoveAt(name)
           Removes the element at a specified name.
methodsetOleSize(startRow, endRow, startColumn, endColumn)
           Sets displayed size when Workbook file is used as an Ole object.
methodsortNames()
           Sorts the defined names.
methodswapSheet(sheetIndex1, sheetIndex2)
           Swaps the two sheets.
 

Property Getters/Setters Detail

getWebExtensionTaskPanes : WebExtensionTaskPaneCollection 

WebExtensionTaskPaneCollection getWebExtensionTaskPanes()
Gets the list of task panes.

getWebExtensions : WebExtensionCollection 

WebExtensionCollection getWebExtensions()
Gets the list of task panes.

getThreadedCommentAuthors : ThreadedCommentAuthorCollection 

ThreadedCommentAuthorCollection getThreadedCommentAuthors()
Gets the list of threaded comment authors.

isRefreshAllConnections/setRefreshAllConnections : boolean 

boolean isRefreshAllConnections() / setRefreshAllConnections(value)
Indicates whether refresh all connections on opening file in MS Excel.

getNames : NameCollection 

NameCollection getNames()
Gets the collection of all the Name objects in the spreadsheet.

getActiveSheetIndex/setActiveSheetIndex : int 

int getActiveSheetIndex() / setActiveSheetIndex(value)
Represents the index of active worksheet when the spreadsheet is opened. Sheet index is zero based.

getDxfs : DxfCollection 

DxfCollection getDxfs()
Gets the master differential formatting records.

getXmlMaps/setXmlMaps : XmlMapCollection 

XmlMapCollection getXmlMaps() / setXmlMaps(value)
Gets and sets the XML maps in the workbook.

getBuiltInDocumentProperties : BuiltInDocumentPropertyCollection 

BuiltInDocumentPropertyCollection getBuiltInDocumentProperties()
Returns a DocumentProperties collection that represents all the built-in document properties of the spreadsheet. 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:

workbook = Workbook()
doc = workbook.getWorksheets().getBuiltInDocumentProperties().get("Author")
doc.setValue("John Smith")

getCustomDocumentProperties : CustomDocumentPropertyCollection 

CustomDocumentPropertyCollection getCustomDocumentProperties()
Returns a DocumentProperties collection that represents all the custom document properties of the spreadsheet.

Example:

workbook = Workbook()
workbook.getWorksheets().getCustomDocumentProperties().add("Checked by", "Jane")

getOleSize/setOleSize : Object 

Object getOleSize() / setOleSize(value)
Gets and Sets displayed size when Workbook file is used as an Ole object. Null means no ole size setting.

getExternalLinks : ExternalLinkCollection 

ExternalLinkCollection getExternalLinks()
Represents external links in a workbook.

getTableStyles : TableStyleCollection 

TableStyleCollection getTableStyles()
Gets TableStyles object.

getRevisionLogs : RevisionLogCollection 

RevisionLogCollection getRevisionLogs()
Represents revision logs.

getCount : int 

int getCount()

get : Worksheet 

Worksheet get(index)
Gets the Worksheet element at the specified index.
Parameters:
index - The zero based index of the element.
Returns:
The element at the specified index.

get : Worksheet 

Worksheet get(sheetName)
Gets the Worksheet element with the specified name.
Parameters:
sheetName - Worksheet name
Returns:
The element with the specified name.

Method Detail

setOleSize

 setOleSize(startRow, endRow, startColumn, endColumn)
Sets displayed size when Workbook file is used as an Ole object. This method is generally used to adjust display size in ppt file or doc file.
Parameters:
startRow: int - Start row index.
endRow: int - End row index.
startColumn: int - Start column index.
endColumn: int - End column index.

clearPivottables

 clearPivottables()
Clears pivot tables from the spreadsheet.

createRange

Range createRange(address, sheetIndex)
Creates a Range object from an address of the range.
Parameters:
address: String - The address of the range.
sheetIndex: int - The sheet index.
Returns:
A Range object

getSheetByCodeName

Worksheet getSheetByCodeName(codeName)
Gets the worksheet by the code name.
Parameters:
codeName: String - Worksheet code name.
Returns:
The element with the specified code name.

sortNames

 sortNames()
Sorts the defined names. 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

Worksheet insert(index, sheetType)
Insert a worksheet.
Parameters:
index: int - The sheet index
sheetType: int - A SheetType value. The sheet type.
Returns:
Returns an inserted worksheet.

insert

Worksheet insert(index, sheetType, sheetName)
Insert a worksheet.
Parameters:
index: int - The sheet index
sheetType: int - A SheetType value. The sheet type.
sheetName: String - The sheet name.
Returns:
Returns an inserted worksheet.

add

int add(type)
Adds a worksheet to the collection.
Parameters:
type: int - A SheetType value. Worksheet type.
Returns:
Worksheet object index.

Example:

workbook = Workbook()
workbook.getWorksheets().add(SheetType.CHART)
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)
charts = workbook.getWorksheets().get(1).getCharts()
chartIndex = charts.add(ChartType.COLUMN, 10, 10, 20, 20)
chart = charts.get(chartIndex)
chart.getNSeries().add("Sheet1!C2:C6", True)
workbook.save("Book1.xlsx")

swapSheet

 swapSheet(sheetIndex1, sheetIndex2)
Swaps the two sheets.
Parameters:
sheetIndex1: int - The first worksheet.
sheetIndex2: int - The second worksheet.

add

int add()
Adds a worksheet to the collection.
Returns:
Worksheet object index.

add

Worksheet add(sheetName)
Adds a worksheet to the collection.
Parameters:
sheetName: String - Worksheet name
Returns:
Worksheet object.

registerAddInFunction

int registerAddInFunction(addInFile, functionName, lib)
Adds addin function into the workbook
Parameters:
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 mkes 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

String registerAddInFunction(id, functionName)
Adds addin function into the workbook
Parameters:
id: int - ID of the data which contains addin functions, can be got by the first call of registerAddInFunction(java.lang.String, java.lang.String, boolean) for the same addin file.
functionName: String - the addin function name
Returns:
URL of the addin file which contains addin functions

removeAt

 removeAt(name)
Removes the element at a specified name.
Parameters:
name: String - The name of the element to remove.

removeAt

 removeAt(index)
Removes the element at a specified index.
Parameters:
index: int - The index value of the element to remove.

clear

 clear()
Clear all worksheets. A workbook must contains a worksheet.

addCopy

int addCopy(sheetName)
Adds a worksheet to the collection and copies data from an existed worksheet.
Parameters:
sheetName: String - Name of source worksheet.
Returns:
Worksheet object index.

addCopy

int addCopy(sheetIndex)
Adds a worksheet to the collection and copies data from an existed worksheet.
Parameters:
sheetIndex: int - Index of source worksheet.
Returns:
Worksheet object index.

getRangeByName

Range getRangeByName(rangeName)
Gets Range object by pre-defined name.
Parameters:
rangeName: String - Name of range.
Returns:
Range object.

Returns null if the named range does not exist.


getNamedRanges

Range[] getNamedRanges()
Gets all pre-defined named ranges in the spreadsheet.
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

Range[] getNamedRangesAndTables()
Gets all pre-defined named ranges in the spreadsheet.
Returns:
An array of Range objects.

Returns null if the named range does not exist.


iterator

Iterator iterator()

get

Object get(index)
Reserved for internal use.

contains

boolean contains(value)
Reserved for internal use.

add

int add(value)
Reserved for internal use.

indexOf

int indexOf(value)
Reserved for internal use.

See Also:
          Aspose.Cells Documentation - the home page for the Aspose.Cellss Product Documentation.
          Aspose.Cells Support Forum - our preferred method of support.