com.aspose.cells

Class Worksheet

  • java.lang.Object
    • com.aspose.cells.Worksheet
  • All Implemented Interfaces:
    com.aspose.cells.b.a.zj
    public class Worksheet 
    extends java.lang.Object

Encapsulates the object that represents a single worksheet.

Example:

Workbook workbook = new Workbook();

Worksheet sheet = workbook.getWorksheets().get(0);

//Freeze panes at "AS40" with 10 rows and 10 columns
sheet.freezePanes("AS40", 10, 10);

//Add a hyperlink in Cell A1
sheet.getHyperlinks().add("A1", 1, 1, "http://www.aspose.com");

Property Getters/Setters Summary
java.lang.StringgetActiveCell()
void
setActiveCell(java.lang.Stringvalue)
Gets or sets the active cell in the worksheet.
ProtectedRangeCollectiongetAllowEditRanges()
Gets the allow edit range collection in the worksheet.
AutoFiltergetAutoFilter()
Represents auto filter for the specified worksheet.
byte[]getBackgroundImage()
void
setBackgroundImage(byte[]value)
Gets and sets worksheet background image.
CellsgetCells()
Gets the Cells collection.
ChartCollectiongetCharts()
Gets a Chart collection
CheckBoxCollectiongetCheckBoxes()
Gets a CheckBox collection.
java.lang.StringgetCodeName()
void
setCodeName(java.lang.Stringvalue)
Gets worksheet code name.
CommentCollectiongetComments()
Gets the Comment collection.
ConditionalFormattingCollectiongetConditionalFormattings()
Gets the ConditionalFormattings in the worksheet.
CustomPropertyCollectiongetCustomProperties()
Gets an object representing the identifier information associated with a worksheet.
booleangetDisplayRightToLeft()
void
setDisplayRightToLeft(booleanvalue)
Indicates if the specified worksheet is displayed from right to left instead of from left to right. Default is false.
booleangetDisplayZeros()
void
setDisplayZeros(booleanvalue)
True if zero values are displayed.
ErrorCheckOptionCollectiongetErrorCheckOptions()
Gets error check setting applied on certain ranges.
intgetFirstVisibleColumn()
void
Represents first visible column index.
intgetFirstVisibleRow()
void
Represents first visible row index.
booleanhasAutofilter()
Indicates whether this worksheet has auto filter.
HorizontalPageBreakCollectiongetHorizontalPageBreaks()
Gets the HorizontalPageBreakCollection collection.
HyperlinkCollectiongetHyperlinks()
Gets the HyperlinkCollection collection.
intgetIndex()
Gets the index of sheet in the worksheet collection.
booleanisGridlinesVisible()
void
setGridlinesVisible(booleanvalue)
Gets or sets a value indicating whether the gridelines are visible.Default is true.
booleanisOutlineShown()
void
setOutlineShown(booleanvalue)
Indicates whether to show outline.
booleanisPageBreakPreview()
void
setPageBreakPreview(booleanvalue)
Indicates whether the specified worksheet is shown in normal view or page break preview.
booleanisProtected()
Indicates if the worksheet is protected.
booleanisRowColumnHeadersVisible()
void
Gets or sets a value indicating whether the worksheet will display row and column headers. Default is true.
booleanisRulerVisible()
void
setRulerVisible(booleanvalue)
Indicates whether the ruler is visible. This property is only applied for page break preview.
booleanisSelected()
void
setSelected(booleanvalue)
Indicates whether this worksheet is selected when the workbook is opened.
booleanisVisible()
void
setVisible(booleanvalue)
Represents if the worksheet is visible.
ListObjectCollectiongetListObjects()
Gets all ListObjects in this worksheet.
java.lang.StringgetName()
void
setName(java.lang.Stringvalue)
Gets or sets the name of the worksheet.
OleObjectCollectiongetOleObjects()
Represents a collection of OleObject in a worksheet.
OutlinegetOutline()
Gets the outline on this worksheet.
PageSetupgetPageSetup()
Represents the page setup description in this sheet.
PictureCollectiongetPictures()
Gets a Picture collection.
PivotTableCollectiongetPivotTables()
Gets all pivot tables in this worksheet.
ProtectiongetProtection()
Represents the various types of protection options available for a worksheet. Supports advanced protection options in ExcelXP and above version.
QueryTableCollectiongetQueryTables()
Gets the queryTables in the worksheet.
ScenarioCollectiongetScenarios()
Gets the collection of Scenario.
ShapeCollectiongetShapes()
Returns all drawing shapes in this worksheet.
booleangetShowFormulas()
void
setShowFormulas(booleanvalue)
Indicates whether to show formulas or their results.
SmartTagSettinggetSmartTagSetting()
Gets all SmartTagCollection objects of the worksheet.
SparklineGroupCollectiongetSparklineGroupCollection()
Gets the sparkline group collection in the worksheet.
ColorgetTabColor()
void
Represents worksheet tab color.
intgetTabId()
void
setTabId(intvalue)
Specifies the internal identifier for the sheet.
TextBoxCollectiongetTextBoxes()
Gets a #Error Cref: !:TextBoxe collection.
booleangetTransitionEntry()
void
setTransitionEntry(booleanvalue)
Indicates whether the Transition Formula Entry (Lotus compatibility) option is enabled.
booleangetTransitionEvaluation()
void
Indicates whether the Transition Formula Evaluation (Lotus compatibility) option is enabled.
intgetType()
void
setType(intvalue)
Represents worksheet type. The value of the property is SheetType integer constant.
ValidationCollectiongetValidations()
Gets the data validation setting collection in the worksheet.
VerticalPageBreakCollectiongetVerticalPageBreaks()
Gets the VerticalPageBreakCollection collection.
intgetViewType()
void
setViewType(intvalue)
Gets and sets the view type. The value of the property is ViewType integer constant.
intgetVisibilityType()
void
Indicates the visible state for this sheet. The value of the property is VisibilityType integer constant.
WorkbookgetWorkbook()
Gets the workbook object which contains this sheet.
intgetZoom()
void
setZoom(intvalue)
Represents the scaling factor in percentage. It should be between 10 and 400.
 
Method Summary
voidaddPageBreaks(java.lang.StringcellName)
Adds page break.
voidadvancedFilter(booleanisFilter, java.lang.StringlistRange, java.lang.StringcriteriaRange, java.lang.StringcopyTo, booleanuniqueRecordOnly)
Filters data using complex criteria.
voidautoFitColumn(intcolumnIndex)
Autofits the column width.
voidautoFitColumn(intcolumnIndex, intfirstRow, intlastRow)
Autofits the column width.
voidautoFitColumns()
Autofits all columns in this worksheet.
voidautoFitColumns(AutoFitterOptions options)
Autofits all columns in this worksheet.
voidautoFitColumns(intfirstColumn, intlastColumn)
Autofits the columns width.
voidautoFitColumns(intfirstColumn, intlastColumn, AutoFitterOptions options)
Autofits the columns width.
voidautoFitColumns(intfirstRow, intfirstColumn, intlastRow, intlastColumn)
Autofits the columns width.
voidautoFitColumns(intfirstRow, intfirstColumn, intlastRow, intlastColumn, AutoFitterOptions options)
Autofits the columns width.
voidautoFitRow(introwIndex)
Autofits the row height.
voidautoFitRow(introwIndex, intfirstColumn, intlastColumn)
Autofits the row height.
voidautoFitRow(introwIndex, intfirstColumn, intlastColumn, AutoFitterOptions options)
Autofits the row height.
voidautoFitRow(intstartRow, intendRow, intstartColumn, intendColumn)
Autofits row height in a rectangle range.
voidautoFitRows()
Autofits all rows in this worksheet.
voidautoFitRows(booleanonlyAuto)
Autofits all rows in this worksheet.
voidautoFitRows(AutoFitterOptions options)
Autofits all rows in this worksheet.
voidautoFitRows(intstartRow, intendRow)
Autofits row height in a range.
voidautoFitRows(intstartRow, intendRow, AutoFitterOptions options)
Autofits row height in a range.
voidcalculateFormula(booleanrecursive, booleanignoreError, ICustomFunction customFunction)
Calculates all formulas in this worksheet.
voidcalculateFormula(CalculationOptions options, booleanrecursive)
Calculates all formulas in this worksheet.
java.lang.ObjectcalculateFormula(java.lang.Stringformula)
Calculates a formula.
java.lang.ObjectcalculateFormula(java.lang.Stringformula, CalculationOptions opts)
Calculates a formula.
voidclearComments()
Clears all comments in designer spreadsheet.
voidcopy(Worksheet sourceSheet)
Copies contents and formats from another worksheet.
voidcopy(Worksheet sourceSheet, CopyOptions copyOptions)
Copies contents and formats from another worksheet.
voiddispose()
Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
voidfreezePanes(introw, intcolumn, intfreezedRows, intfreezedColumns)
Freezes panes at the specified cell in the worksheet.
voidfreezePanes(java.lang.StringcellName, intfreezedRows, intfreezedColumns)
Freezes panes at the specified cell in the worksheet.
int[]getFreezedPanes()
Gets the freeze panes.
PaneCollectiongetPanes()
Gets the window panes.
CellArea[]getPrintingPageBreaks(ImageOrPrintOptions options)
Gets automatic page breaks.
java.util.ArrayListgetSelectedRanges()
Gets selected ranges of cells in the designer spreadsheet.
voidmoveTo(intindex)
Moves the sheet to another location in the spreadsheet.
voidprotect(inttype)
Protects worksheet.
voidprotect(inttype, java.lang.Stringpassword, java.lang.StringoldPassword)
Protects worksheet.
voidrefreshPivotTables()
Refreshes all PivotTables in this Worksheet.
voidremoveAllDrawingObjects()
Removes all drawing objects in this worksheet.
voidremoveAutoFilter()
Removes the auto filter of the worksheet.
voidremoveSplit()
Removes splitted window.
intreplace(java.lang.StringoldString, java.lang.StringnewString)
Replaces all cells' text with a new string.
voidselectRange(intstartRow, intstartColumn, inttotalRows, inttotalColumns, booleanremoveOthers)
Selects a range.
voidsetBackground(byte[]pictureData)
Sets worksheet background image.
voidsetVisible(booleanisVisible, booleanignoreError)
Sets the visible options.
voidsplit()
Splits window.
java.lang.StringtoString()
Returns a string represents the current Worksheet object.
voidunFreezePanes()
Unfreezes panes in the worksheet.
voidunprotect()
Unprotects worksheet.
voidunprotect(java.lang.Stringpassword)
Unprotects worksheet.
java.util.ArrayListxmlMapQuery(java.lang.Stringpath, XmlMap xmlMap)
Query cell areas that mapped/linked to the specific path of xml map.
 

    • Property Getters/Setters Detail

      • getProtection

        public Protection getProtection()
        
        Represents the various types of protection options available for a worksheet. Supports advanced protection options in ExcelXP and above version. This property can protect worksheet in all versions of Excel file and support advanced protection options in ExcelXP and above version.
      • getWorkbook

        public Workbook getWorkbook()
        
        Gets the workbook object which contains this sheet.
      • getCells

        public Cells getCells()
        
        Gets the Cells collection.
      • getPivotTables

        public PivotTableCollection getPivotTables()
        
        Gets all pivot tables in this worksheet.
      • getType/setType

        public int getType() / public void setType(int value)
        
        Represents worksheet type. The value of the property is SheetType integer constant.
      • getName/setName

        public java.lang.String getName() / public void setName(java.lang.String value)
        
        Gets or sets the name of the worksheet. The max length of sheet name is 31. And you cannot assign same name(case insensitive) to two worksheets. For example, you cannot set "SheetName1" to the first worksheet and set "SHEETNAME1" to the second worksheet.
      • getShowFormulas/setShowFormulas

        public boolean getShowFormulas() / public void setShowFormulas(boolean value)
        
        Indicates whether to show formulas or their results.
      • isGridlinesVisible/setGridlinesVisible

        public boolean isGridlinesVisible() / public void setGridlinesVisible(boolean value)
        
        Gets or sets a value indicating whether the gridelines are visible.Default is true.
      • isRowColumnHeadersVisible/setRowColumnHeadersVisible

        public boolean isRowColumnHeadersVisible() / public void setRowColumnHeadersVisible(boolean value)
        
        Gets or sets a value indicating whether the worksheet will display row and column headers. Default is true.
      • getDisplayZeros/setDisplayZeros

        public boolean getDisplayZeros() / public void setDisplayZeros(boolean value)
        
        True if zero values are displayed.
      • getDisplayRightToLeft/setDisplayRightToLeft

        public boolean getDisplayRightToLeft() / public void setDisplayRightToLeft(boolean value)
        
        Indicates if the specified worksheet is displayed from right to left instead of from left to right. Default is false.
      • isOutlineShown/setOutlineShown

        public boolean isOutlineShown() / public void setOutlineShown(boolean value)
        
        Indicates whether to show outline.
      • isSelected/setSelected

        public boolean isSelected() / public void setSelected(boolean value)
        
        Indicates whether this worksheet is selected when the workbook is opened.
      • getListObjects

        public ListObjectCollection getListObjects()
        
        Gets all ListObjects in this worksheet.
      • getTabId/setTabId

        public int getTabId() / public void setTabId(int value)
        
        Specifies the internal identifier for the sheet.
      • getPageSetup

        public PageSetup getPageSetup()
        
        Represents the page setup description in this sheet.
      • getAutoFilter

        public AutoFilter getAutoFilter()
        
        Represents auto filter for the specified worksheet.
      • hasAutofilter

        public boolean hasAutofilter()
        
        Indicates whether this worksheet has auto filter.
      • getTransitionEvaluation/setTransitionEvaluation

        public boolean getTransitionEvaluation() / public void setTransitionEvaluation(boolean value)
        
        Indicates whether the Transition Formula Evaluation (Lotus compatibility) option is enabled.
      • getTransitionEntry/setTransitionEntry

        public boolean getTransitionEntry() / public void setTransitionEntry(boolean value)
        
        Indicates whether the Transition Formula Entry (Lotus compatibility) option is enabled.
      • getVisibilityType/setVisibilityType

        public int getVisibilityType() / public void setVisibilityType(int value)
        
        Indicates the visible state for this sheet. The value of the property is VisibilityType integer constant.
      • isVisible/setVisible

        public boolean isVisible() / public void setVisible(boolean value)
        
        Represents if the worksheet is visible.
      • getSparklineGroupCollection

        public SparklineGroupCollection getSparklineGroupCollection()
        
        Gets the sparkline group collection in the worksheet.
      • getTextBoxes

        public TextBoxCollection getTextBoxes()
        
        Gets a #Error Cref: !:TextBoxe collection.
      • getShapes

        public ShapeCollection getShapes()
        
        Returns all drawing shapes in this worksheet.
      • getIndex

        public int getIndex()
        
        Gets the index of sheet in the worksheet collection.
      • isProtected

        public boolean isProtected()
        
        Indicates if the worksheet is protected.
      • getValidations

        public ValidationCollection getValidations()
        
        Gets the data validation setting collection in the worksheet.
      • getAllowEditRanges

        public ProtectedRangeCollection getAllowEditRanges()
        
        Gets the allow edit range collection in the worksheet.
      • getErrorCheckOptions

        public ErrorCheckOptionCollection getErrorCheckOptions()
        
        Gets error check setting applied on certain ranges.
      • getOutline

        public Outline getOutline()
        
        Gets the outline on this worksheet.
      • getFirstVisibleRow/setFirstVisibleRow

        public int getFirstVisibleRow() / public void setFirstVisibleRow(int value)
        
        Represents first visible row index.
      • getFirstVisibleColumn/setFirstVisibleColumn

        public int getFirstVisibleColumn() / public void setFirstVisibleColumn(int value)
        
        Represents first visible column index.
      • getZoom/setZoom

        public int getZoom() / public void setZoom(int value)
        
        Represents the scaling factor in percentage. It should be between 10 and 400. Please set the view type first.
      • getViewType/setViewType

        public int getViewType() / public void setViewType(int value)
        
        Gets and sets the view type. The value of the property is ViewType integer constant.
      • isPageBreakPreview/setPageBreakPreview

        public boolean isPageBreakPreview() / public void setPageBreakPreview(boolean value)
        
        Indicates whether the specified worksheet is shown in normal view or page break preview.
      • isRulerVisible/setRulerVisible

        public boolean isRulerVisible() / public void setRulerVisible(boolean value)
        
        Indicates whether the ruler is visible. This property is only applied for page break preview.
      • getTabColor/setTabColor

        public Color getTabColor() / public void setTabColor(Color value)
        
        Represents worksheet tab color. This feature is only supported in ExcelXP(Excel2002) and later versions. If you save file as Excel97 or Excel2000 format, it will be omitted.
      • getCodeName/setCodeName

        public java.lang.String getCodeName() / public void setCodeName(java.lang.String value)
        
        Gets worksheet code name.
      • getBackgroundImage/setBackgroundImage

        public byte[] getBackgroundImage() / public void setBackgroundImage(byte[] value)
        
        Gets and sets worksheet background image.
      • getActiveCell/setActiveCell

        public java.lang.String getActiveCell() / public void setActiveCell(java.lang.String value)
        
        Gets or sets the active cell in the worksheet.
      • getCustomProperties

        public CustomPropertyCollection getCustomProperties()
        
        Gets an object representing the identifier information associated with a worksheet. Worksheet.CustomProperties provide a preferred mechanism for storing arbitrary data. It supports legacy third-party document components, as well as those situations that have a stringent need for binary parts.
    • Method Detail

      • unprotect

        public void unprotect()
        Unprotects worksheet. This method unprotects worksheet which is protected without password.
      • unprotect

        public void unprotect(java.lang.String password)
        Unprotects worksheet. If the worksheet is protected without a password, you can set a null value or blank string to password parameter.
        Parameters:
        password - Password
      • moveTo

        public void moveTo(int index)
        Moves the sheet to another location in the spreadsheet.
        Parameters:
        index - Destination sheet index.
      • replace

        public int replace(java.lang.String oldString, java.lang.String newString)
        Replaces all cells' text with a new string.
        Parameters:
        oldString - Old string value.
        newString - New string value.
      • getSelectedRanges

        public java.util.ArrayList getSelectedRanges()
        Gets selected ranges of cells in the designer spreadsheet.
        Returns:
        An java.util.ArrayList which contains selected ranges.
      • setBackground

        public void setBackground(byte[] pictureData)
        Sets worksheet background image. NOTE: This member is now obsolete. Instead, please use Worksheet.BackgroundImage property. This property will be removed 12 months later since August 2016. Aspose apologizes for any inconvenience you may have experienced.
        Parameters:
        pictureData - Picture data.
      • getPrintingPageBreaks

        public CellArea[] getPrintingPageBreaks(ImageOrPrintOptions options)
                                        throws java.lang.Exception
        Gets automatic page breaks. Each cell area represents a paper.
        Parameters:
        options - The print options
        Returns:
        The automatic page breaks areas.
      • toString

        public java.lang.String toString()
        Returns a string represents the current Worksheet object.
        Returns:
      • calculateFormula

        public java.lang.Object calculateFormula(java.lang.String formula)
        Calculates a formula.
        Parameters:
        formula - Formula to be calculated.
        Returns:
        Calculated formula result.
      • calculateFormula

        public java.lang.Object calculateFormula(java.lang.String formula, CalculationOptions opts)
        Calculates a formula.
        Parameters:
        formula - Formula to be calculated.
        opts - Options for calculating formula
        Returns:
        Calculated formula result.
      • refreshPivotTables

        public void refreshPivotTables()
                               throws java.lang.Exception
        Refreshes all PivotTables in this Worksheet.
      • calculateFormula

        public void calculateFormula(boolean recursive, boolean ignoreError, ICustomFunction customFunction)
        Calculates all formulas in this worksheet.
        Parameters:
        recursive - True means if the worksheet' cells depend on the cells of other worksheets, the dependant cells in other worksheets will be calculated too. False means all the formulas in the worksheet have been calculated and the values are right.
        ignoreError - Indicates if hide the error in calculating formulas. The error may be unsupported function, external links, etc.
        customFunction - The custom formula calculation functions to extend the calculation engine.
      • calculateFormula

        public void calculateFormula(CalculationOptions options, boolean recursive)
        Calculates all formulas in this worksheet.
        Parameters:
        options - Options for calculation
        recursive - True means if the worksheet' cells depend on the cells of other worksheets, the dependant cells in other worksheets will be calculated too. False means all the formulas in the worksheet have been calculated and the values are right.
      • xmlMapQuery

        public java.util.ArrayList xmlMapQuery(java.lang.String path, XmlMap xmlMap)
                             throws java.lang.Exception
        Query cell areas that mapped/linked to the specific path of xml map. e.g. A xml map element structure: -RootElement |-Attribute1 |-SubElement |-Attribute2 |-Attribute3 To query "Attribute1", path is "/RootElement/@Attribute1" To query "Attribute2", path is "/RootElement/SubElement/@Attribute2" To query whole "SubElement", path is "/RootElement/SubElement"
        Parameters:
        path - xml element path
        xmlMap - Specify an xml map if you want to query for the specific path within a specific map
        Returns:
        CellArea list that mapped/linked to the specific path of xml map, an empty list is returned if nothing is mapped/linked.
      • dispose

        public void dispose()
        Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
      • getPanes

        public PaneCollection getPanes()
        Gets the window panes. If the window is not splitted or frozen.
      • freezePanes

        public void freezePanes(int row, int column, int freezedRows, int freezedColumns)
        Freezes panes at the specified cell in the worksheet.

        Row index and column index cannot all be zero. Number of rows and number of columns also cannot all be zero.

        The first two parameters specify the freezed position and the last two parameters specify the area freezed on the left top pane.

        Parameters:
        row - Row index.
        column - Column index.
        freezedRows - Number of visible rows in top pane, no more than row index.
        freezedColumns - Number of visible columns in left pane, no more than column index.
      • getFreezedPanes

        public int[] getFreezedPanes()
        Gets the freeze panes.
        Returns:
        Return null means the worksheet is not frozen 0:Row index;1:column;2:freezedRows;3:freezedRows
      • split

        public void split()
        Splits window.
      • freezePanes

        public void freezePanes(java.lang.String cellName, int freezedRows, int freezedColumns)
        Freezes panes at the specified cell in the worksheet. Row index and column index cannot all be zero. Number of rows and number of columns also cannot all be zero.
        Parameters:
        cellName - Cell name.
        freezedRows - Number of visible rows in top pane, no more than row index.
        freezedColumns - Number of visible columns in left pane, no more than column index.
      • unFreezePanes

        public void unFreezePanes()
        Unfreezes panes in the worksheet.
      • removeSplit

        public void removeSplit()
        Removes splitted window.
      • addPageBreaks

        public void addPageBreaks(java.lang.String cellName)
        Adds page break.
        Parameters:
        cellName -
      • copy

        public void copy(Worksheet sourceSheet)
                 throws java.lang.Exception
        Copies contents and formats from another worksheet.
        Parameters:
        sourceSheet - Source worksheet.
      • copy

        public void copy(Worksheet sourceSheet, CopyOptions copyOptions)
                 throws java.lang.Exception
        Copies contents and formats from another worksheet. You can copy data from another worksheet in the same file or another file. However, this method does not support to copy drawing objects, such as comments, images and charts.
        Parameters:
        sourceSheet - Source worksheet.
        copyOptions -
      • autoFitColumn

        public void autoFitColumn(int columnIndex, int firstRow, int lastRow)
                          throws java.lang.Exception
        Autofits the column width. This method autofits a row based on content in a range of cells within the row.
        Parameters:
        columnIndex - Column index.
        firstRow - First row index.
        lastRow - Last row index.
      • autoFitColumns

        public void autoFitColumns()
                           throws java.lang.Exception
        Autofits all columns in this worksheet.
      • autoFitColumns

        public void autoFitColumns(AutoFitterOptions options)
                           throws java.lang.Exception
        Autofits all columns in this worksheet.
        Parameters:
        options - The auto fitting options
      • autoFitColumn

        public void autoFitColumn(int columnIndex)
                          throws java.lang.Exception
        Autofits the column width. AutoFitColumn is an imprecise function.
        Parameters:
        columnIndex - Column index.
      • autoFitColumns

        public void autoFitColumns(int firstColumn, int lastColumn)
                           throws java.lang.Exception
        Autofits the columns width. AutoFitColumn is an imprecise function.
        Parameters:
        firstColumn - First column index.
        lastColumn - Last column index.
      • autoFitColumns

        public void autoFitColumns(int firstColumn, int lastColumn, AutoFitterOptions options)
                           throws java.lang.Exception
        Autofits the columns width. AutoFitColumn is an imprecise function.
        Parameters:
        firstColumn - First column index.
        lastColumn - Last column index.
        options - The auto fitting options
      • autoFitColumns

        public void autoFitColumns(int firstRow, int firstColumn, int lastRow, int lastColumn)
                           throws java.lang.Exception
        Autofits the columns width. AutoFitColumn is an imprecise function.
        Parameters:
        firstRow - First row index.
        firstColumn - First column index.
        lastRow - Last row index.
        lastColumn - Last column index.
      • autoFitColumns

        public void autoFitColumns(int firstRow, int firstColumn, int lastRow, int lastColumn, AutoFitterOptions options)
                           throws java.lang.Exception
        Autofits the columns width. AutoFitColumn is an imprecise function.
        Parameters:
        firstRow - First row index.
        firstColumn - First column index.
        lastRow - Last row index.
        lastColumn - Last column index.
        options - The auto fitting options
      • autoFitRow

        public void autoFitRow(int rowIndex, int firstColumn, int lastColumn)
                       throws java.lang.Exception
        Autofits the row height. This method autofits a row based on content in a range of cells within the row.
        Parameters:
        rowIndex - Row index.
        firstColumn - First column index.
        lastColumn - Last column index.
      • autoFitRow

        public void autoFitRow(int rowIndex, int firstColumn, int lastColumn, AutoFitterOptions options)
                       throws java.lang.Exception
        Autofits the row height. This method autofits a row based on content in a range of cells within the row.
        Parameters:
        rowIndex - Row index.
        firstColumn - First column index.
        lastColumn - Last column index.
        options - The auto fitter options
      • autoFitRows

        public void autoFitRows()
                        throws java.lang.Exception
        Autofits all rows in this worksheet.
      • autoFitRows

        public void autoFitRows(boolean onlyAuto)
                        throws java.lang.Exception
        Autofits all rows in this worksheet.
        Parameters:
        onlyAuto - True,only autofits the row height when row height is not customed.
      • autoFitRows

        public void autoFitRows(AutoFitterOptions options)
                        throws java.lang.Exception
        Autofits all rows in this worksheet.
        Parameters:
        options - The auto fitter options
      • autoFitRows

        public void autoFitRows(int startRow, int endRow)
                        throws java.lang.Exception
        Autofits row height in a range.
        Parameters:
        startRow - Start row index.
        endRow - End row index.
      • autoFitRows

        public void autoFitRows(int startRow, int endRow, AutoFitterOptions options)
                        throws java.lang.Exception
        Autofits row height in a range.
        Parameters:
        startRow - Start row index.
        endRow - End row index.
        options - The options of auto fitter.
      • autoFitRow

        public void autoFitRow(int startRow, int endRow, int startColumn, int endColumn)
                       throws java.lang.Exception
        Autofits row height in a rectangle range.
        Parameters:
        startRow - Start row index.
        endRow - End row index.
        startColumn - Start column index.
        endColumn - End column index.
      • autoFitRow

        public void autoFitRow(int rowIndex)
                       throws java.lang.Exception
        Autofits the row height. AutoFitRow is an imprecise function.
        Parameters:
        rowIndex - Row index.
      • advancedFilter

        public void advancedFilter(boolean isFilter, java.lang.String listRange, java.lang.String criteriaRange, java.lang.String copyTo, boolean uniqueRecordOnly)
        Filters data using complex criteria.
        Parameters:
        isFilter - Inidicates whether filtering the list in place.
        listRange - The list range.
        criteriaRange - The criteria range.
        copyTo - The range where copying data to.
        uniqueRecordOnly - Only displaying or copying unique rows.
      • removeAutoFilter

        public void removeAutoFilter()
        Removes the auto filter of the worksheet.
      • setVisible

        public void setVisible(boolean isVisible, boolean ignoreError)
        Sets the visible options.
        Parameters:
        isVisible - Whether the worksheet is visible
        ignoreError - Whether to ignore error if this option is not valid.
      • selectRange

        public void selectRange(int startRow, int startColumn, int totalRows, int totalColumns, boolean removeOthers)
        Selects a range.
        Parameters:
        startRow - The start row.
        startColumn - The start column
        totalRows - The number of rows.
        totalColumns - The number of columns
        removeOthers - True means removing other selected range and only select this range.
      • removeAllDrawingObjects

        public void removeAllDrawingObjects()
        Removes all drawing objects in this worksheet.
      • clearComments

        public void clearComments()
        Clears all comments in designer spreadsheet.
      • protect

        public void protect(int type)
        Protects worksheet. This method protects worksheet without password. It can protect worksheet in all versions of Excel file.
        Parameters:
        type - A ProtectionType value. Protection type.
      • protect

        public void protect(int type, java.lang.String password, java.lang.String oldPassword)
        Protects worksheet. This method can protect worksheet in all versions of Excel file.
        Parameters:
        type - A ProtectionType value. Protection type.
        password - Password.
        oldPassword - If the worksheet is already protected by a password, please supply the old password. Otherwise, you can set a null value or blank string to this parameter.

        Example:

        //Instantiating a Workbook object
        Workbook excel = new Workbook("C:\\book1.xls");
        //Accessing the first worksheet in the Excel file
        Worksheet worksheet = excel.getWorksheets().get(0);
        //Protecting the worksheet with a password
        worksheet.protect(ProtectionType.ALL, "aspose", null);
        //Saving the modified Excel file in default (that is Excel 20003) format
        excel.save("C:\\output.xls");