com.aspose.cells

Class WorksheetCollection

  • java.lang.Object
  • All Implemented Interfaces:
    java.lang.Iterable
    public class WorksheetCollection 
    extends CollectionBase

Encapsulates a collection of Worksheet 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);

Property Getters/Setters Summary
intgetActiveSheetIndex()
void
Represents the index of active worksheet when the spreadsheet is opened.
BuiltInDocumentPropertyCollectiongetBuiltInDocumentProperties()
Returns a DocumentProperties collection that represents all the built-in document properties of the spreadsheet.
intgetCount()
CustomDocumentPropertyCollectiongetCustomDocumentProperties()
Returns a DocumentProperties collection that represents all the custom document properties of the spreadsheet.
DxfCollectiongetDxfs()
Gets the master differential formatting records.
ExternalLinkCollectiongetExternalLinks()
Represents external links in a workbook.
booleanisRefreshAllConnections()
void
Indicates whether refresh all connections on opening file in MS Excel.
NameCollectiongetNames()
Gets the collection of all the Name objects in the spreadsheet.
java.lang.ObjectgetOleSize()
void
setOleSize(java.lang.Objectvalue)
Gets and Sets displayed size when Workbook file is used as an Ole object.
TableStyleCollectiongetTableStyles()
Gets TableStyles object.
XmlMapCollectiongetXmlMaps()
void
Gets and sets the XML maps in the workbook.
Worksheetget(intindex)
Gets the Worksheet element at the specified index.
Worksheetget(java.lang.StringsheetName)
Gets the Worksheet element with the specified name.
 
Method Summary
intadd()
Adds a worksheet to the collection.
intadd(inttype)
Adds a worksheet to the collection.
intadd(java.lang.Objectvalue)
Reserved for internal use.
Worksheetadd(java.lang.StringsheetName)
Adds a worksheet to the collection.
intaddCopy(intsheetIndex)
Adds a worksheet to the collection and copies data from an existed worksheet.
intaddCopy(java.lang.StringsheetName)
Adds a worksheet to the collection and copies data from an existed worksheet.
voidclear()
Clear all worksheets.
voidclearPivottables()
Clears pivot tables from the spreadsheet.
booleancontains(java.lang.Objectvalue)
Reserved for internal use.
RangecreateRange(java.lang.Stringaddress, intsheetIndex)
Creates a Range object from an address of the range.
java.lang.Objectget(intindex)
Reserved for internal use.
Range[]getNamedRanges()
Gets all pre-defined named ranges in the spreadsheet.
Range[]getNamedRangesAndTables()
Gets all pre-defined named ranges in the spreadsheet.
RangegetRangeByName(java.lang.StringrangeName)
Gets Range object by pre-defined name.
WorksheetgetSheetByCodeName(java.lang.StringcodeName)
Gets the worksheet by the code name.
intindexOf(java.lang.Objectvalue)
Reserved for internal use.
Worksheetinsert(intindex, intsheetType)
Insert a worksheet.
Worksheetinsert(intindex, intsheetType, java.lang.StringsheetName)
Insert a worksheet.
java.util.Iteratoriterator()
voidremoveAt(intindex)
Removes the element at a specified index.
voidremoveAt(java.lang.Stringname)
Removes the element at a specified name.
voidsetOleSize(intstartRow, intendRow, intstartColumn, intendColumn)
Sets displayed size when Workbook file is used as an Ole object.
voidsortNames()
Sorts defined names.
voidswapSheet(intsheetIndex1, intsheetIndex2)
Swaps the two sheets.
 

    • Property Getters/Setters Detail

      • isRefreshAllConnections/setRefreshAllConnections

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

        public NameCollection getNames()
        
        Gets the collection of all the Name objects in the spreadsheet.
      • getActiveSheetIndex/setActiveSheetIndex

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

        public DxfCollection getDxfs()
        
        Gets the master differential formatting records.
      • getBuiltInDocumentProperties

        public 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:

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

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

        Example:

        excel.getWorksheets().getCustomDocumentProperties().add("Checked by", "Jane");
      • getOleSize/setOleSize

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

        public int getCount()
        
      • get

        public Worksheet get(int 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

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

      • setOleSize

        public void setOleSize(int startRow, int endRow, int startColumn, int 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 - Start row index.
        endRow - End row index.
        startColumn - Start column index.
        endColumn - End column index.
      • clearPivottables

        public void clearPivottables()
        Clears pivot tables from the spreadsheet.
      • createRange

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

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

        public void sortNames()
        Sorts 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

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

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

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

        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);
      • swapSheet

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

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

        public Worksheet add(java.lang.String sheetName)
        Adds a worksheet to the collection.
        Parameters:
        sheetName - Worksheet name
        Returns:
        Worksheet object.
      • removeAt

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

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

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

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

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

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

        Returns null if the named range does not exist.

      • getNamedRanges

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

        Returns null if the named range does not exist.

      • getNamedRangesAndTables

        public 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

        public java.util.Iterator iterator()
      • get

        public java.lang.Object get(int index)
        Reserved for internal use.
      • contains

        public boolean contains(java.lang.Object value)
        Reserved for internal use.
      • add

        public int add(java.lang.Object value)
        Reserved for internal use.
      • indexOf

        public int indexOf(java.lang.Object value)
        Reserved for internal use.