WorksheetCollection

Inheritance: java.lang.Object, com.aspose.cells.CollectionBase

public class WorksheetCollection extends CollectionBase

Encapsulates a collection of Range.getWorksheet() objects.

Example

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

MethodDescription
add()Adds a worksheet to the collection.
add(int type)Adds a worksheet to the collection.
add(Object o)Adds an item to the CollectionBase instance.
add(String sheetName)Adds a worksheet to the collection.
addCopy(Worksheet[] source, String[] destSheetNames)Copy a group of worksheets.
addCopy(int sheetIndex)Adds a worksheet to the collection and copies data from an existed worksheet.
addCopy(String sheetName)Adds a worksheet to the collection and copies data from an existed worksheet.
clear()Clear all worksheets.
clearPivottables()Clears pivot tables from the spreadsheet.
contains(Object o)Return whether instance contains this object
createRange(String address, int sheetIndex)Creates a Range object from an address of the range.
createUnionRange(String address, int sheetIndex)Creates a Range object from an address of the range.
equals(Object arg0)
get(int index)Gets the Range.getWorksheet() element at the specified index.
get(String sheetName)Gets the Range.getWorksheet() element with the specified name.
getActiveSheetIndex()Represents the index of active worksheet when the spreadsheet is opened.
getActiveSheetName()Represents the name of active worksheet when the spreadsheet is opened.
getBuiltInDocumentProperties()Returns a DocumentProperty collection that represents all the built-in document properties of the spreadsheet.
getClass()
getCount()Gets the number of elements contained in the CollectionBase instance.
getCustomDocumentProperties()Returns a DocumentProperty collection that represents all the custom document properties of the spreadsheet.
getDxfs()Gets the master differential formatting records.
getExternalLinks()Represents external links in a workbook.
getNamedRanges()Gets all pre-defined named ranges in the spreadsheet.
getNamedRangesAndTables()Gets all pre-defined named ranges in the spreadsheet.
getNames()Gets the collection of all the Name objects in the spreadsheet.
getOleSize()Gets displayed size when Workbook file is used as an Ole object.
getRangeByName(String rangeName)Gets Range object by pre-defined name.
getRangeByName(String rangeName, int currentSheetIndex, boolean includeTable)Gets Range by pre-defined name or table’s name
getRevisionLogs()Represents revision logs.
getSheetByCodeName(String codeName)Gets the worksheet by the code name.
getTableStyles()Gets getTableStyles() object.
getThreadedCommentAuthors()Gets the list of threaded comment authors.
getWebExtensionTaskPanes()Gets the list of task panes.
getWebExtensions()Gets the list of task panes.
getXmlMaps()Gets the XML maps in the workbook.
hashCode()
indexOf(Object o)Determines the index of a specific item in the CollectionBase instance.
insert(int index, int sheetType)Insert a worksheet.
insert(int index, int sheetType, String sheetName)Insert a worksheet.
isRefreshAllConnections()Indicates whether refresh all connections on opening file in MS Excel.
iterator()Returns an enumerator that iterates through the CollectionBase instance.
notify()
notifyAll()
refreshAll()Refresh all pivot tables and charts with pivot source.
refreshPivotTables()Refreshes all the PivotTables in the WorksheetCollection.
registerAddInFunction(int id, String functionName)Adds addin function into the workbook
registerAddInFunction(String addInFile, String functionName, boolean lib)Adds addin function into the workbook
removeAt(int index)Removes the element at a specified index.
removeAt(String name)Removes the element at a specified name.
setActiveSheetIndex(int value)Represents the index of active worksheet when the spreadsheet is opened.
setActiveSheetName(String value)Represents the name of active worksheet when the spreadsheet is opened.
setOleSize(int startRow, int endRow, int startColumn, int endColumn)Sets displayed size when Workbook file is used as an Ole object.
setOleSize(Object value)Sets displayed size when Workbook file is used as an Ole object.
setRefreshAllConnections(boolean value)Indicates whether refresh all connections on opening file in MS Excel.
setXmlMaps(XmlMapCollection value)Sets the XML maps in the workbook.
sortNames()Sorts the defined names.
swapSheet(int sheetIndex1, int sheetIndex2)Swaps the two sheets.
toString()
wait()
wait(long arg0)
wait(long arg0, int arg1)

add()

public int add()

Adds a worksheet to the collection.

Returns: int - Range.getWorksheet() object index.

add(int type)

public int add(int type)

Adds a worksheet to the collection.

Example

         Workbook workbook = new Workbook();
         workbook.getWorksheets().add(SheetType.CHART);
         	Cells 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);
         	ChartCollection charts = workbook.getWorksheets().get(1).getCharts();
         	int chartIndex = charts.add(ChartType.COLUMN, 10,10,20,20);
         	Chart chart = charts.get(chartIndex);
         	chart.getNSeries().add("Sheet1!C2:C6", true);

Parameters:

ParameterTypeDescription
typeintSheetType. Worksheet type.

Returns: int - Range.getWorksheet() object index.

add(Object o)

public int add(Object o)

Adds an item to the CollectionBase instance.

Parameters:

ParameterTypeDescription
ojava.lang.ObjectThe Object to add to the CollectionBase instance.

Returns: int - The position into which the new element was inserted.

add(String sheetName)

public Worksheet add(String sheetName)

Adds a worksheet to the collection.

Parameters:

ParameterTypeDescription
sheetNamejava.lang.StringWorksheet name

Returns: Worksheet - Range.getWorksheet() object.

addCopy(Worksheet[] source, String[] destSheetNames)

public void addCopy(Worksheet[] source, String[] destSheetNames)

Copy a group of worksheets.

Parameters:

ParameterTypeDescription
sourceWorksheet[]The source worksheets.
destSheetNamesjava.lang.String[]The names of the copied sheets.

addCopy(int sheetIndex)

public int addCopy(int sheetIndex)

Adds a worksheet to the collection and copies data from an existed worksheet.

Parameters:

ParameterTypeDescription
sheetIndexintIndex of source worksheet.

Returns: int - Range.getWorksheet() object index.

addCopy(String sheetName)

public int addCopy(String sheetName)

Adds a worksheet to the collection and copies data from an existed worksheet.

Parameters:

ParameterTypeDescription
sheetNamejava.lang.StringName of source worksheet.

Returns: int - Range.getWorksheet() object index.

clear()

public void clear()

Clear all worksheets.

Remarks

A workbook must contains a worksheet.

clearPivottables()

public void clearPivottables()

Clears pivot tables from the spreadsheet.

contains(Object o)

public boolean contains(Object o)

Return whether instance contains this object

Parameters:

ParameterTypeDescription
ojava.lang.Objecttest object

Returns: boolean - Whether instance contains this object

createRange(String address, int sheetIndex)

public Range createRange(String address, int sheetIndex)

Creates a Range object from an address of the range.

Parameters:

ParameterTypeDescription
addressjava.lang.StringThe address of the range.
sheetIndexintThe sheet index.

Returns: Range - A Range object

createUnionRange(String address, int sheetIndex)

public UnionRange createUnionRange(String address, int sheetIndex)

Creates a Range object from an address of the range.

Parameters:

ParameterTypeDescription
addressjava.lang.StringThe address of the range.
sheetIndexintThe sheet index.

Returns: UnionRange - A Range object

equals(Object arg0)

public boolean equals(Object arg0)

Parameters:

ParameterTypeDescription
arg0java.lang.Object

Returns: boolean

get(int index)

public Worksheet get(int index)

Gets the Range.getWorksheet() element at the specified index.

Parameters:

ParameterTypeDescription
indexintThe zero based index of the element.

Returns: Worksheet - The element at the specified index.

get(String sheetName)

public Worksheet get(String sheetName)

Gets the Range.getWorksheet() element with the specified name.

Parameters:

ParameterTypeDescription
sheetNamejava.lang.StringWorksheet name

Returns: Worksheet - The element with the specified name.

getActiveSheetIndex()

public int getActiveSheetIndex()

Represents the index of active worksheet when the spreadsheet is opened.

Remarks

Sheet index is zero based.

Returns: int

getActiveSheetName()

public String getActiveSheetName()

Represents the name of active worksheet when the spreadsheet is opened.

Returns: java.lang.String

getBuiltInDocumentProperties()

public BuiltInDocumentPropertyCollection getBuiltInDocumentProperties()

Returns a DocumentProperty collection that represents all the built-in document properties of the spreadsheet.

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

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

Returns: BuiltInDocumentPropertyCollection

getClass()

public final native Class<?> getClass()

Returns: java.lang.Class

getCount()

public int getCount()

Gets the number of elements contained in the CollectionBase instance.

Returns: int - The number of elements contained in the CollectionBase instance.

getCustomDocumentProperties()

public CustomDocumentPropertyCollection getCustomDocumentProperties()

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

Example

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

Returns: CustomDocumentPropertyCollection

getDxfs()

public DxfCollection getDxfs()

Gets the master differential formatting records.

Returns: DxfCollection

public ExternalLinkCollection getExternalLinks()

Represents external links in a workbook.

Returns: ExternalLinkCollection

getNamedRanges()

public Range[] getNamedRanges()

Gets all pre-defined named ranges in the spreadsheet.

Returns: com.aspose.cells.Range[] - 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()

public Range[] getNamedRangesAndTables()

Gets all pre-defined named ranges in the spreadsheet.

Returns: com.aspose.cells.Range[] - An array of Range objects.

Returns null if the named range does not exist.

getNames()

public NameCollection getNames()

Gets the collection of all the Name objects in the spreadsheet.

Returns: NameCollection

getOleSize()

public Object getOleSize()

Gets displayed size when Workbook file is used as an Ole object.

Remarks

Null means no ole size setting.

Returns: java.lang.Object

getRangeByName(String rangeName)

public Range getRangeByName(String rangeName)

Gets Range object by pre-defined name.

Parameters:

ParameterTypeDescription
rangeNamejava.lang.StringName of range.

Returns: Range - Range object.

Returns null if the named range does not exist.

getRangeByName(String rangeName, int currentSheetIndex, boolean includeTable)

public Range getRangeByName(String rangeName, int currentSheetIndex, boolean includeTable)

Gets Range by pre-defined name or table’s name

Parameters:

ParameterTypeDescription
rangeNamejava.lang.StringName of range or table’s name.
currentSheetIndexintThe sheet index. -1 represents global .
includeTablebooleanIndicates whether checking all tables.

Returns: Range -

getRevisionLogs()

public RevisionLogCollection getRevisionLogs()

Represents revision logs.

Returns: RevisionLogCollection

getSheetByCodeName(String codeName)

public Worksheet getSheetByCodeName(String codeName)

Gets the worksheet by the code name.

Parameters:

ParameterTypeDescription
codeNamejava.lang.StringWorksheet code name.

Returns: Worksheet - The element with the specified code name.

getTableStyles()

public TableStyleCollection getTableStyles()

Gets getTableStyles() object.

Returns: TableStyleCollection

getThreadedCommentAuthors()

public ThreadedCommentAuthorCollection getThreadedCommentAuthors()

Gets the list of threaded comment authors.

Returns: ThreadedCommentAuthorCollection

getWebExtensionTaskPanes()

public WebExtensionTaskPaneCollection getWebExtensionTaskPanes()

Gets the list of task panes.

Returns: WebExtensionTaskPaneCollection

getWebExtensions()

public WebExtensionCollection getWebExtensions()

Gets the list of task panes.

Returns: WebExtensionCollection

getXmlMaps()

public XmlMapCollection getXmlMaps()

Gets the XML maps in the workbook.

Returns: XmlMapCollection

hashCode()

public native int hashCode()

Returns: int

indexOf(Object o)

public int indexOf(Object o)

Determines the index of a specific item in the CollectionBase instance.

Parameters:

ParameterTypeDescription
ojava.lang.ObjectDetermines the index of a specific item in the CollectionBase instance.

Returns: int - The index of value if found in the list; otherwise, -1.

insert(int index, int sheetType)

public Worksheet insert(int index, int sheetType)

Insert a worksheet.

Parameters:

ParameterTypeDescription
indexintThe sheet index
sheetTypeintSheetType. The sheet type.

Returns: Worksheet - Returns an inserted worksheet.

insert(int index, int sheetType, String sheetName)

public Worksheet insert(int index, int sheetType, String sheetName)

Insert a worksheet.

Parameters:

ParameterTypeDescription
indexintThe sheet index
sheetTypeintSheetType. The sheet type.
sheetNamejava.lang.StringThe sheet name.

Returns: Worksheet - Returns an inserted worksheet.

isRefreshAllConnections()

public boolean isRefreshAllConnections()

Indicates whether refresh all connections on opening file in MS Excel.

Returns: boolean

iterator()

public Iterator iterator()

Returns an enumerator that iterates through the CollectionBase instance.

Returns: java.util.Iterator - An iterator for the CollectionBase instance.

notify()

public final native void notify()

notifyAll()

public final native void notifyAll()

refreshAll()

public void refreshAll()

Refresh all pivot tables and charts with pivot source.

refreshPivotTables()

public void refreshPivotTables()

Refreshes all the PivotTables in the WorksheetCollection.

registerAddInFunction(int id, String functionName)

public String registerAddInFunction(int id, String functionName)

Adds addin function into the workbook

Parameters:

ParameterTypeDescription
idintID of the data which contains addin functions, can be got by the first call of registerAddInFunction(String,String,boolean) for the same addin file.
functionNamejava.lang.Stringthe addin function name

Returns: java.lang.String - URL of the addin file which contains addin functions

registerAddInFunction(String addInFile, String functionName, boolean lib)

public int registerAddInFunction(String addInFile, String functionName, boolean lib)

Adds addin function into the workbook

Parameters:

ParameterTypeDescription
addInFilejava.lang.Stringthe file contains the addin functions
functionNamejava.lang.Stringthe addin function name
libbooleanwhether 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: int - ID of the data which contains given addin function

removeAt(int index)

public void removeAt(int index)

Removes the element at a specified index.

Parameters:

ParameterTypeDescription
indexintThe index value of the element to remove.

removeAt(String name)

public void removeAt(String name)

Removes the element at a specified name.

Parameters:

ParameterTypeDescription
namejava.lang.StringThe name of the element to remove.

setActiveSheetIndex(int value)

public void setActiveSheetIndex(int value)

Represents the index of active worksheet when the spreadsheet is opened.

Remarks

Sheet index is zero based.

Parameters:

ParameterTypeDescription
valueint

setActiveSheetName(String value)

public void setActiveSheetName(String value)

Represents the name of active worksheet when the spreadsheet is opened.

Parameters:

ParameterTypeDescription
valuejava.lang.String

setOleSize(int startRow, int endRow, int startColumn, int endColumn)

public void setOleSize(int startRow, int endRow, int startColumn, int endColumn)

Sets displayed size when Workbook file is used as an Ole object.

Remarks

This method is generally used to adjust display size in ppt file or doc file.

Parameters:

ParameterTypeDescription
startRowintStart row index.
endRowintEnd row index.
startColumnintStart column index.
endColumnintEnd column index.

setOleSize(Object value)

public void setOleSize(Object value)

Sets displayed size when Workbook file is used as an Ole object.

Remarks

Null means no ole size setting.

Parameters:

ParameterTypeDescription
valuejava.lang.Object

setRefreshAllConnections(boolean value)

public void setRefreshAllConnections(boolean value)

Indicates whether refresh all connections on opening file in MS Excel.

Parameters:

ParameterTypeDescription
valueboolean

setXmlMaps(XmlMapCollection value)

public void setXmlMaps(XmlMapCollection value)

Sets the XML maps in the workbook.

Parameters:

ParameterTypeDescription
valueXmlMapCollection

sortNames()

public void sortNames()

Sorts the defined names.

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

swapSheet(int sheetIndex1, int sheetIndex2)

public void swapSheet(int sheetIndex1, int sheetIndex2)

Swaps the two sheets.

Parameters:

ParameterTypeDescription
sheetIndex1intThe first worksheet.
sheetIndex2intThe second worksheet.

toString()

public String toString()

Returns: java.lang.String

wait()

public final void wait()

wait(long arg0)

public final native void wait(long arg0)

Parameters:

ParameterTypeDescription
arg0long

wait(long arg0, int arg1)

public final void wait(long arg0, int arg1)

Parameters:

ParameterTypeDescription
arg0long
arg1int