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

Method	Description
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 Excel file.
refreshPivotTables(PivotTableRefreshOption option)	Refreshes all the PivotTables in the Excel file.
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:

Parameter	Type	Description
type	int	SheetType. Worksheet type.

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

add(Object o)

public int add(Object o)

Adds an item to the CollectionBase instance.

Parameters:

Parameter	Type	Description
o	java.lang.Object	The 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:

Parameter	Type	Description
sheetName	java.lang.String	Worksheet name

Returns: Worksheet - Range.getWorksheet() object.

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

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

Copy a group of worksheets.

Parameters:

Parameter	Type	Description
source	Worksheet[]	The source worksheets.
destSheetNames	java.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:

Parameter	Type	Description
sheetIndex	int	Index 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:

Parameter	Type	Description
sheetName	java.lang.String	Name 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:

Parameter	Type	Description
o	java.lang.Object	test 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:

Parameter	Type	Description
address	java.lang.String	The address of the range.
sheetIndex	int	The 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:

Parameter	Type	Description
address	java.lang.String	The address of the range.
sheetIndex	int	The sheet index.

Returns: UnionRange - A Range object

equals(Object arg0)

public boolean equals(Object arg0)

Parameters:

Parameter	Type	Description
arg0	java.lang.Object

Returns: boolean

get(int index)

public Worksheet get(int index)

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

Parameters:

Parameter	Type	Description
index	int	The 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:

Parameter	Type	Description
sheetName	java.lang.String	Worksheet 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

getExternalLinks()

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:

Parameter	Type	Description
rangeName	java.lang.String	Name 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:

Parameter	Type	Description
rangeName	java.lang.String	Name of range or table’s name.
currentSheetIndex	int	The sheet index. -1 represents global .
includeTable	boolean	Indicates 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:

Parameter	Type	Description
codeName	java.lang.String	Worksheet 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:

Parameter	Type	Description
o	java.lang.Object	Determines 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:

Parameter	Type	Description
index	int	The sheet index
sheetType	int	SheetType. 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:

Parameter	Type	Description
index	int	The sheet index
sheetType	int	SheetType. The sheet type.
sheetName	java.lang.String	The 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 Excel file.

refreshPivotTables(PivotTableRefreshOption option)

public boolean refreshPivotTables(PivotTableRefreshOption option)

Refreshes all the PivotTables in the Excel file.

Parameters:

Parameter	Type	Description
option	PivotTableRefreshOption	The option for refreshing data source of the pivot tables.

Returns: boolean

registerAddInFunction(int id, String functionName)

public String registerAddInFunction(int id, String functionName)

Adds addin function into the workbook

Parameters:

Parameter	Type	Description
id	int	ID of the data which contains addin functions, can be got by the first call of registerAddInFunction(String,String,boolean) for the same addin file.
functionName	java.lang.String	the 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:

Parameter	Type	Description
addInFile	java.lang.String	the file contains the addin functions
functionName	java.lang.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: 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:

Parameter	Type	Description
index	int	The index value of the element to remove.

removeAt(String name)

public void removeAt(String name)

Removes the element at a specified name.

Parameters:

Parameter	Type	Description
name	java.lang.String	The 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:

Parameter	Type	Description
value	int

setActiveSheetName(String value)

public void setActiveSheetName(String value)

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

Parameters:

Parameter	Type	Description
value	java.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:

Parameter	Type	Description
startRow	int	Start row index.
endRow	int	End row index.
startColumn	int	Start column index.
endColumn	int	End 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:

Parameter	Type	Description
value	java.lang.Object

setRefreshAllConnections(boolean value)

public void setRefreshAllConnections(boolean value)

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

Parameters:

Parameter	Type	Description
value	boolean

setXmlMaps(XmlMapCollection value)

public void setXmlMaps(XmlMapCollection value)

Sets the XML maps in the workbook.

Parameters:

Parameter	Type	Description
value	XmlMapCollection

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:

Parameter	Type	Description
sheetIndex1	int	The first worksheet.
sheetIndex2	int	The 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:

Parameter	Type	Description
arg0	long

wait(long arg0, int arg1)

public final void wait(long arg0, int arg1)

Parameters:

Parameter	Type	Description
arg0	long
arg1	int