asposecells.api

Classes

Constants

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
`method`	`getActiveSheetIndex()`
`method`	`setActiveSheetIndex(value)`
	Represents the index of active worksheet when the spreadsheet is opened.
`method`	`getActiveSheetName()`
`method`	`setActiveSheetName(value)`
	Represents the name of active worksheet when the spreadsheet is opened.
`method`	`getBuiltInDocumentProperties()`
	Returns a DocumentProperty collection that represents all the built-in document properties of the spreadsheet.
`method`	`getCount()`

`method`	`getCustomDocumentProperties()`
	Returns a DocumentProperty collection that represents all the custom document properties of the spreadsheet.
`method`	`getDxfs()`
	Gets the master differential formatting records.
`method`	`getExternalLinks()`
	Represents external links in a workbook.
`method`	`isRefreshAllConnections()`
`method`	`setRefreshAllConnections(value)`
	Indicates whether refresh all connections on opening file in MS Excel.
`method`	`getNames()`
	Gets the collection of all the Name objects in the spreadsheet.
`method`	`getOleSize()`
`method`	`setOleSize(value)`
	Gets and Sets displayed size when Workbook file is used as an Ole object.
`method`	`getRevisionLogs()`
	Represents revision logs.
`method`	`getTableStyles()`
	Gets TableStyles object.
`method`	`getThreadedCommentAuthors()`
	Gets the list of threaded comment authors.
`method`	`getWebExtensions()`
	Gets the list of task panes.
`method`	`getWebExtensionTaskPanes()`
	Gets the list of task panes.
`method`	`getXmlMaps()`
`method`	`setXmlMaps(value)`
	Gets and sets the XML maps in the workbook.
`method`	`get(index)`
	Gets the Worksheet element at the specified index.
`method`	`get(sheetName)`
	Gets the Worksheet element with the specified name.

Method Summary
`method`	`add()`
`method`	Adds a worksheet to the collection.
`method`	`add(type)`
`method`	Adds a worksheet to the collection.
`method`	`add(value)`
`method`	Reserved for internal use.
`method`	`add(sheetName)`
`method`	Adds a worksheet to the collection.
`method`	`addCopy(source, destSheetNames)`
`method`	Copy a group of worksheets.
`method`	`addCopy(sheetIndex)`
`method`	Adds a worksheet to the collection and copies data from an existed worksheet.
`method`	`addCopy(sheetName)`
`method`	Adds a worksheet to the collection and copies data from an existed worksheet.
`method`	`clear()`
`method`	Clear all worksheets.
`method`	`clearPivottables()`
`method`	Clears pivot tables from the spreadsheet.
`method`	`contains(value)`
`method`	Reserved for internal use.
`method`	`createRange(address, sheetIndex)`
`method`	Creates a Range object from an address of the range.
`method`	`createUnionRange(address, sheetIndex)`
`method`	Creates a Range object from an address of the range.
`method`	`get(index)`
`method`	Reserved for internal use.
`method`	`getNamedRanges()`
`method`	Gets all pre-defined named ranges in the spreadsheet.
`method`	`getNamedRangesAndTables()`
`method`	Gets all pre-defined named ranges in the spreadsheet.
`method`	`getRangeByName(rangeName)`
`method`	Gets Range object by pre-defined name.
`method`	`getRangeByName(rangeName, currentSheetIndex, includeTable)`
`method`	Gets Range by pre-defined name or table's name
`method`	`getSheetByCodeName(codeName)`
`method`	Gets the worksheet by the code name.
`method`	`indexOf(value)`
`method`	Reserved for internal use.
`method`	`insert(index, sheetType)`
`method`	Insert a worksheet.
`method`	`insert(index, sheetType, sheetName)`
`method`	Insert a worksheet.
`method`	`iterator()`
`method`
`method`	`refreshAll()`
`method`	Refresh all pivot tables and charts with pivot source.
`method`	`refreshPivotTables()`
`method`	Refreshes all the PivotTables in the Excel file.
`method`	`refreshPivotTables(option)`
`method`	Refreshes all the PivotTables in the Excel file.
`method`	`registerAddInFunction(id, functionName)`
`method`	Adds addin function into the workbook
`method`	`registerAddInFunction(addInFile, functionName, lib)`
`method`	Adds addin function into the workbook
`method`	`removeAt(index)`
`method`	Removes the element at a specified index.
`method`	`removeAt(name)`
`method`	Removes the element at a specified name.
`method`	`setOleSize(startRow, endRow, startColumn, endColumn)`
`method`	Sets displayed size when Workbook file is used as an Ole object.
`method`	`sortNames()`
`method`	Sorts the defined names.
`method`	`swapSheet(sheetIndex1, sheetIndex2)`
`method`	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.
  - getActiveSheetName/setActiveSheetName : String
```
String getActiveSheetName() / setActiveSheetName(value)
```
    Represents the name of active worksheet when the spreadsheet is opened.
  - 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 DocumentProperty 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 DocumentProperty 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.
  - refreshAll
```
 refreshAll()
```
    Refresh all pivot tables and charts with pivot source.
  - refreshPivotTables
```
 refreshPivotTables()
```
    Refreshes all the PivotTables in the Excel file.
  - refreshPivotTables
```
 refreshPivotTables(option)
```
    Refreshes all the PivotTables in the Excel file.
    Parameters:
    option: PivotTableRefreshOption - The option for refreshing data source of the pivot tables.
  - 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
  - createUnionRange
```
UnionRange createUnionRange(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 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
```
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.
  - addCopy
```
 addCopy(source, destSheetNames)
```
    Copy a group of worksheets.
    Parameters:
    source: Worksheet[] - The source worksheets.
    destSheetNames: String[] - The names of the copied sheets.
  - 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.
  - getRangeByName
```
Range getRangeByName(rangeName, currentSheetIndex, includeTable)
```
    Gets Range by pre-defined name or table's name
    Parameters:
    rangeName: String - Name of range or table's name.
    currentSheetIndex: int - The sheet index. -1 represents global .
    includeTable: boolean - Indicates whether checking all tables.
    Returns:
  - 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.

asposecells.api

Classes

Constants

Class WorksheetCollection

Property Getters/Setters Detail

getWebExtensionTaskPanes : WebExtensionTaskPaneCollection

getWebExtensions : WebExtensionCollection

getThreadedCommentAuthors : ThreadedCommentAuthorCollection

isRefreshAllConnections/setRefreshAllConnections : boolean

getNames : NameCollection

getActiveSheetName/setActiveSheetName : String

getActiveSheetIndex/setActiveSheetIndex : int

getDxfs : DxfCollection

getXmlMaps/setXmlMaps : XmlMapCollection

getBuiltInDocumentProperties : BuiltInDocumentPropertyCollection

getCustomDocumentProperties : CustomDocumentPropertyCollection

getOleSize/setOleSize : Object

getExternalLinks : ExternalLinkCollection

getTableStyles : TableStyleCollection

getRevisionLogs : RevisionLogCollection

getCount : int

get : Worksheet

get : Worksheet

Method Detail

setOleSize

clearPivottables

refreshAll

refreshPivotTables

refreshPivotTables

createRange

createUnionRange

getSheetByCodeName

sortNames

insert

insert

add

swapSheet

add

add

registerAddInFunction

registerAddInFunction

removeAt

removeAt

clear

addCopy

addCopy

addCopy

getRangeByName

getRangeByName

getNamedRanges

getNamedRangesAndTables

iterator

get

contains

add

indexOf