aspose.cells

Class WorksheetCollection

Encapsulates a collection of Worksheet objects.

Example:

$workbook = new cells\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
functiongetActiveSheetIndex()
function
           Represents the index of active worksheet when the spreadsheet is opened.
functiongetActiveSheetName()
function
           Represents the name of active worksheet when the spreadsheet is opened.
functiongetBuiltInDocumentProperties()
Returns a DocumentProperty collection that represents all the built-in document properties of the spreadsheet.
functiongetCount()
functiongetCustomDocumentProperties()
Returns a DocumentProperty collection that represents all the custom document properties of the spreadsheet.
functiongetDxfs()
Gets the master differential formatting records.
functiongetExternalLinks()
Represents external links in a workbook.
functionisRefreshAllConnections()
function
           Indicates whether refresh all connections on opening file in MS Excel.
functiongetNames()
Gets the collection of all the Name objects in the spreadsheet.
functiongetOleSize()
function
setOleSize(value)
           Gets and Sets displayed size when Workbook file is used as an Ole object.
functiongetRevisionLogs()
Represents revision logs.
functiongetTableStyles()
Gets TableStyles object.
functiongetThreadedCommentAuthors()
Gets the list of threaded comment authors.
functiongetWebExtensions()
Gets the list of task panes.
functiongetWebExtensionTaskPanes()
Gets the list of task panes.
functiongetXmlMaps()
function
setXmlMaps(value)
           Gets and sets the XML maps in the workbook.
functionget(index)
Gets the Worksheet element at the specified index.
functionget(sheetName)
Gets the Worksheet element with the specified name.
 
Method Summary
functionadd()
Adds a worksheet to the collection.
functionadd(type)
Adds a worksheet to the collection.
functionadd(value)
Reserved for internal use.
functionadd(sheetName)
Adds a worksheet to the collection.
functionaddCopy(source, destSheetNames)
Copy a group of worksheets.
functionaddCopy(sheetIndex)
Adds a worksheet to the collection and copies data from an existed worksheet.
functionaddCopy(sheetName)
Adds a worksheet to the collection and copies data from an existed worksheet.
functionclear()
Clear all worksheets.
functionclearPivottables()
Clears pivot tables from the spreadsheet.
functioncontains(value)
Reserved for internal use.
functioncreateRange(address, sheetIndex)
Creates a Range object from an address of the range.
functioncreateUnionRange(address, sheetIndex)
Creates a Range object from an address of the range.
functionget(index)
Reserved for internal use.
functiongetNamedRanges()
Gets all pre-defined named ranges in the spreadsheet.
functiongetNamedRangesAndTables()
Gets all pre-defined named ranges in the spreadsheet.
functiongetRangeByName(rangeName)
Gets Range object by pre-defined name.
functiongetRangeByName(rangeName, currentSheetIndex, includeTable)
Gets Range by pre-defined name or table's name
functiongetSheetByCodeName(codeName)
Gets the worksheet by the code name.
functionindexOf(value)
Reserved for internal use.
functioninsert(index, sheetType)
Insert a worksheet.
functioninsert(index, sheetType, sheetName)
Insert a worksheet.
functioniterator()
functionrefreshAll()
Refresh all pivot tables and charts with pivot source.
functionrefreshPivotTables()
Refreshes all the PivotTables in the Excel file.
functionrefreshPivotTables(option)
Refreshes all the PivotTables in the Excel file.
functionregisterAddInFunction(id, functionName)
Adds addin function into the workbook
functionregisterAddInFunction(addInFile, functionName, lib)
Adds addin function into the workbook
functionremoveAt(index)
Removes the element at a specified index.
functionremoveAt(name)
Removes the element at a specified name.
functionsetOleSize(startRow, endRow, startColumn, endColumn)
Sets displayed size when Workbook file is used as an Ole object.
functionsortNames()
Sorts the defined names.
functionswapSheet(sheetIndex1, sheetIndex2)
Swaps the two sheets.
 

    • Property Getters/Setters Detail

      • isRefreshAllConnections/setRefreshAllConnections : boolean 

        function isRefreshAllConnections() / function setRefreshAllConnections(value)
        
        Indicates whether refresh all connections on opening file in MS Excel.
      • getNames : NameCollection 

        function getNames()
        
        Gets the collection of all the Name objects in the spreadsheet.
      • getActiveSheetName/setActiveSheetName : String 

        function getActiveSheetName() / function setActiveSheetName(value)
        
        Represents the name of active worksheet when the spreadsheet is opened.
      • getActiveSheetIndex/setActiveSheetIndex : Number 

        function getActiveSheetIndex() / function setActiveSheetIndex(value)
        
        Represents the index of active worksheet when the spreadsheet is opened. Sheet index is zero based.
      • getDxfs : DxfCollection 

        function getDxfs()
        
        Gets the master differential formatting records.
      • getXmlMaps/setXmlMaps : XmlMapCollection 

        function getXmlMaps() / function setXmlMaps(value)
        
        Gets and sets the XML maps in the workbook.
      • getBuiltInDocumentProperties : BuiltInDocumentPropertyCollection 

        function 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 = new cells\Workbook();
        $doc = $workbook->getWorksheets()->getBuiltInDocumentProperties()->get("Author");
        $doc->setValue("John Smith");
      • getCustomDocumentProperties : CustomDocumentPropertyCollection 

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

        Example:

        $workbook = new cells\Workbook();
        $workbook->getWorksheets()->getCustomDocumentProperties()->add("Checked by", "Jane");
      • getOleSize/setOleSize : Object 

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

        function getExternalLinks()
        
        Represents external links in a workbook.
      • getCount : Number 

        function getCount()
        
      • get : Worksheet 

        function 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 

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

      • setOleSize

        function 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: Number - Start row index.
        endRow: Number - End row index.
        startColumn: Number - Start column index.
        endColumn: Number - End column index.
      • clearPivottables

        function clearPivottables()
        Clears pivot tables from the spreadsheet.
      • refreshAll

        function refreshAll()
        Refresh all pivot tables and charts with pivot source.
      • refreshPivotTables

        function refreshPivotTables()
        Refreshes all the PivotTables in the Excel file.
      • refreshPivotTables

        function refreshPivotTables(option)
        Refreshes all the PivotTables in the Excel file.
        Parameters:
        option: PivotTableRefreshOption - The option for refreshing data source of the pivot tables.
      • createRange

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

        function createUnionRange(address, sheetIndex)
        Creates a Range object from an address of the range.
        Parameters:
        address: String - The address of the range.
        sheetIndex: Number - The sheet index.
        Returns:
        A Range object
      • getSheetByCodeName

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

        function 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

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

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

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

        Example:

        $workbook = new cells\Workbook();
        $workbook->getWorksheets()->add(cells\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(cells\ChartType::COLUMN, 10, 10, 20, 20);
        $chart = $charts->get($chartIndex);
        $chart->getNSeries()->add("Sheet1!C2:C6", true);
      • swapSheet

        function swapSheet(sheetIndex1, sheetIndex2)
        Swaps the two sheets.
        Parameters:
        sheetIndex1: Number - The first worksheet.
        sheetIndex2: Number - The second worksheet.
      • add

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

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

        function 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

        function registerAddInFunction(id, functionName)
        Adds addin function into the workbook
        Parameters:
        id: Number - 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

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

        function removeAt(index)
        Removes the element at a specified index.
        Parameters:
        index: Number - The index value of the element to remove.
      • clear

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

        function 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

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

        function addCopy(source, destSheetNames)
        Copy a group of worksheets.
        Parameters:
        source: Worksheet[] - The source worksheets.
        destSheetNames: String[] - The names of the copied sheets.
      • getRangeByName

        function 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

        function 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: Number - The sheet index. -1 represents global .
        includeTable: boolean - Indicates whether checking all tables.
        Returns:
      • getNamedRanges

        function 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

        function 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

        function iterator()
      • get

        function get(index)
        Reserved for internal use.
      • contains

        function contains(value)
        Reserved for internal use.
      • add

        function add(value)
        Reserved for internal use.
      • indexOf

        function indexOf(value)
        Reserved for internal use.