aspose.cells

Classes

Constants

Class Worksheet

Encapsulates the object that represents a single worksheet.

Example:

$workbook = new cells\Workbook();
$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
`function`	`getActiveCell()`
`function`	`setActiveCell(value)`
	Gets or sets the active cell in the worksheet.
`function`	`getAllowEditRanges()`
	Gets the allow edit range collection in the worksheet.
`function`	`getAutoFilter()`
	Represents auto filter for the specified worksheet.
`function`	`getBackgroundImage()`
`function`	`setBackgroundImage(value)`
	Gets and sets worksheet background image.
`function`	`getCells()`
	Gets the Cells collection.
`function`	`getCellWatches()`
	Gets collection of cells on this worksheet being watched in the 'watch window'.
`function`	`getCharts()`
	Gets a Chart collection
`function`	`getCheckBoxes()`
	Gets a CheckBox collection.
`function`	`getCodeName()`
`function`	`setCodeName(value)`
	Gets worksheet code name.
`function`	`getComments()`
	Gets the Comment collection.
`function`	`getConditionalFormattings()`
	Gets the ConditionalFormattings in the worksheet.
`function`	`getCustomProperties()`
	Gets an object representing the identifier information associated with a worksheet.
`function`	`getDisplayRightToLeft()`
`function`	`setDisplayRightToLeft(value)`
	Indicates if the specified worksheet is displayed from right to left instead of from left to right. Default is false.
`function`	`getDisplayZeros()`
`function`	`setDisplayZeros(value)`
	True if zero values are displayed.
`function`	`getErrorCheckOptions()`
	Gets error check setting applied on certain ranges.
`function`	`getFirstVisibleColumn()`
`function`	`setFirstVisibleColumn(value)`
	Represents first visible column index.
`function`	`getFirstVisibleRow()`
`function`	`setFirstVisibleRow(value)`
	Represents first visible row index.
`function`	`hasAutofilter()`
	Indicates whether this worksheet has auto filter.
`function`	`getHorizontalPageBreaks()`
	Gets the HorizontalPageBreakCollection collection.
`function`	`getHyperlinks()`
	Gets the HyperlinkCollection collection.
`function`	`getIndex()`
	Gets the index of sheet in the worksheet collection.
`function`	`isGridlinesVisible()`
`function`	`setGridlinesVisible(value)`
	Gets or sets a value indicating whether the gridlines are visible.Default is true.
`function`	`isOutlineShown()`
`function`	`setOutlineShown(value)`
	Indicates whether to show outline.
`function`	`isPageBreakPreview()`
`function`	`setPageBreakPreview(value)`
	Indicates whether the specified worksheet is shown in normal view or page break preview.
`function`	`isProtected()`
	Indicates if the worksheet is protected.
`function`	`isRowColumnHeadersVisible()`
`function`	`setRowColumnHeadersVisible(value)`
	Gets or sets a value indicating whether the worksheet will display row and column headers. Default is true.
`function`	`isRulerVisible()`
`function`	`setRulerVisible(value)`
	Indicates whether the ruler is visible. This property is only applied for page break preview.
`function`	`isSelected()`
`function`	`setSelected(value)`
	Indicates whether this worksheet is selected when the workbook is opened.
`function`	`isVisible()`
`function`	`setVisible(value)`
	Represents if the worksheet is visible.
`function`	`getListObjects()`
	Gets all ListObjects in this worksheet.
`function`	`getName()`
`function`	`setName(value)`
	Gets or sets the name of the worksheet.
`function`	`getOleObjects()`
	Represents a collection of OleObject in a worksheet.
`function`	`getOutline()`
	Gets the outline on this worksheet.
`function`	`getPageSetup()`
	Represents the page setup description in this sheet.
`function`	`getPaneState()`
	Indicates whether the pane has horizontal or vertical splits, and whether those splits are frozen. The value of the property is PaneStateType integer constant.
`function`	`getPictures()`
	Gets a Picture collection.
`function`	`getPivotTables()`
	Gets all pivot tables in this worksheet.
`function`	`getProtection()`
	Represents the various types of protection options available for a worksheet. Supports advanced protection options in ExcelXP and above version.
`function`	`getQueryTables()`
	Gets QueryTableCollection in the worksheet.
`function`	`getScenarios()`
	Gets the collection of Scenario.
`function`	`getShapes()`
	Returns all drawing shapes in this worksheet.
`function`	`getShowFormulas()`
`function`	`setShowFormulas(value)`
	Indicates whether to show formulas or their results.
`function`	`getSlicers()`
	Get the Slicer collection in the worksheet
`function`	`getSmartTagSetting()`
	Gets all SmartTagCollection objects of the worksheet.
`function`	`getSparklineGroups()`
	Gets the sparkline groups in the worksheet.
`function`	`getTabColor()`
`function`	`setTabColor(value)`
	Represents worksheet tab color.
`function`	`getTabId()`
`function`	`setTabId(value)`
	Specifies the internal identifier for the sheet.
`function`	`getTextBoxes()`
	Gets a TextBox collection.
`function`	`getTimelines()`
	Get the Timeline collection in the worksheet
`function`	`getTransitionEntry()`
`function`	`setTransitionEntry(value)`
	Indicates whether the Transition Formula Entry (Lotus compatibility) option is enabled.
`function`	`getTransitionEvaluation()`
`function`	`setTransitionEvaluation(value)`
	Indicates whether the Transition Formula Evaluation (Lotus compatibility) option is enabled.
`function`	`getType()`
`function`	`setType(value)`
	Represents worksheet type. The value of the property is SheetType integer constant.
`function`	`getUniqueId()`
`function`	`setUniqueId(value)`
	Gets and sets the unique id, it is same as {15DB5C3C-A5A1-48AF-8F25-3D86AC232D4F}.
`function`	`getValidations()`
	Gets the data validation setting collection in the worksheet.
`function`	`getVerticalPageBreaks()`
	Gets the VerticalPageBreakCollection collection.
`function`	`getViewType()`
`function`	`setViewType(value)`
	Gets and sets the view type. The value of the property is ViewType integer constant.
`function`	`getVisibilityType()`
`function`	`setVisibilityType(value)`
	Indicates the visible state for this sheet. The value of the property is VisibilityType integer constant.
`function`	`getWorkbook()`
	Gets the workbook object which contains this sheet.
`function`	`getZoom()`
`function`	`setZoom(value)`
	Represents the scaling factor in percentage. It should be between 10 and 400.

Method Summary
`function`	`addPageBreaks(cellName)`
`function`	Adds page break.
`function`	`advancedFilter(isFilter, listRange, criteriaRange, copyTo, uniqueRecordOnly)`
`function`	Filters data using complex criteria.
`function`	`autoFitColumn(columnIndex)`
`function`	Autofits the column width.
`function`	`autoFitColumn(columnIndex, firstRow, lastRow)`
`function`	Autofits the column width.
`function`	`autoFitColumns()`
`function`	Autofits all columns in this worksheet.
`function`	`autoFitColumns(options)`
`function`	Autofits all columns in this worksheet.
`function`	`autoFitColumns(firstColumn, lastColumn)`
`function`	Autofits the columns width.
`function`	`autoFitColumns(firstColumn, lastColumn, options)`
`function`	Autofits the columns width.
`function`	`autoFitColumns(firstRow, firstColumn, lastRow, lastColumn)`
`function`	Autofits the columns width.
`function`	`autoFitColumns(firstRow, firstColumn, lastRow, lastColumn, options)`
`function`	Autofits the columns width.
`function`	`autoFitRow(rowIndex)`
`function`	Autofits the row height.
`function`	`autoFitRow(rowIndex, firstColumn, lastColumn)`
`function`	Autofits the row height.
`function`	`autoFitRow(rowIndex, firstColumn, lastColumn, options)`
`function`	Autofits the row height.
`function`	`autoFitRow(startRow, endRow, startColumn, endColumn)`
`function`	Autofits row height in a rectangle range.
`function`	`autoFitRows()`
`function`	Autofits all rows in this worksheet.
`function`	`autoFitRows(onlyAuto)`
`function`	Autofits all rows in this worksheet.
`function`	`autoFitRows(options)`
`function`	Autofits all rows in this worksheet.
`function`	`autoFitRows(startRow, endRow)`
`function`	Autofits row height in a range.
`function`	`autoFitRows(startRow, endRow, options)`
`function`	Autofits row height in a range.
`function`	`calculateArrayFormula(formula, opts)`
`function`	Calculates a formula as array formula.
`function`	`calculateArrayFormula(formula, opts, maxRowCount, maxColumnCount)`
`function`	Calculates a formula as array formula.
`function`	`calculateArrayFormula(formula, pOpts, cOpts, baseCellRow, baseCellColumn, maxRowCount, maxColumnCount, calculationData)`
`function`	Calculates a formula as array formula.
`function`	`calculateFormula(options, recursive)`
`function`	Calculates all formulas in this worksheet.
`function`	`calculateFormula(formula)`
`function`	Calculates a formula.
`function`	`calculateFormula(formula, opts)`
`function`	Calculates a formula expression directly.
`function`	`calculateFormula(formula, pOpts, cOpts, baseCellRow, baseCellColumn, calculationData)`
`function`	Calculates a formula expression directly.
`function`	`clearComments()`
`function`	Clears all comments in designer spreadsheet.
`function`	`closeAccessCache(opts)`
`function`	Closes the session that uses caches to access the data in this worksheet.
`function`	`convertFormulaReferenceStyle(formula, toR1C1, baseCellRow, baseCellColumn)`
`function`	Converts the formula reference style.
`function`	`copy(sourceSheet)`
`function`	Copies contents and formats from another worksheet.
`function`	`copy(sourceSheet, copyOptions)`
`function`	Copies contents and formats from another worksheet.
`function`	`dispose()`
`function`	Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
`function`	`freezePanes(row, column, freezedRows, freezedColumns)`
`function`	Freezes panes at the specified cell in the worksheet.
`function`	`freezePanes(cellName, freezedRows, freezedColumns)`
`function`	Freezes panes at the specified cell in the worksheet.
`function`	`getAdvancedFilter()`
`function`	Gets the settings of advanced filter.
`function`	`getFreezedPanes()`
`function`	Gets the freeze panes.
`function`	`getPanes()`
`function`	Gets the window panes.
`function`	`getPrintingPageBreaks(options)`
`function`	Gets automatic page breaks.
`function`	`getSelectedRanges()`
`function`	Gets selected ranges of cells in the designer spreadsheet.
`function`	`moveTo(index)`
`function`	Moves the sheet to another location in the spreadsheet.
`function`	`protect(type)`
`function`	Protects worksheet.
`function`	`protect(type, password, oldPassword)`
`function`	Protects worksheet.
`function`	`refreshPivotTables()`
`function`	Refreshes all the PivotTables in this Worksheet.
`function`	`refreshPivotTables(option)`
`function`	Refreshes all the PivotTables in this Worksheet.
`function`	`removeAllDrawingObjects()`
`function`	Removes all drawing objects in this worksheet.
`function`	`removeAutoFilter()`
`function`	Removes the auto filter of the worksheet.
`function`	`removeSplit()`
`function`	Removes split window.
`function`	`replace(oldString, newString)`
`function`	Replaces all cells' text with a new string.
`function`	`selectRange(startRow, startColumn, totalRows, totalColumns, removeOthers)`
`function`	Selects a range.
`function`	`setVisible(isVisible, ignoreError)`
`function`	Sets the visible options.
`function`	`split()`
`function`	Splits window.
`function`	`startAccessCache(opts)`
`function`	Starts the session that uses caches to access the data in this worksheet.
`function`	`toString()`
`function`	Returns a string represents the current Worksheet object.
`function`	`unFreezePanes()`
`function`	Unfreezes panes in the worksheet.
`function`	`unprotect()`
`function`	Unprotects worksheet.
`function`	`unprotect(password)`
`function`	Unprotects worksheet.
`function`	`xmlMapQuery(path, xmlMap)`
`function`	Query cell areas that mapped/linked to the specific path of xml map.

- Property Getters/Setters Detail
  - getProtection : Protection
```
function 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.
  - getUniqueId/setUniqueId : String
```
function getUniqueId() / function setUniqueId(value)
```
    Gets and sets the unique id, it is same as {15DB5C3C-A5A1-48AF-8F25-3D86AC232D4F}.
  - getWorkbook : Workbook
```
function getWorkbook()
```
    Gets the workbook object which contains this sheet.
  - getCells : Cells
```
function getCells()
```
    Gets the Cells collection.
  - getQueryTables : QueryTableCollection
```
function getQueryTables()
```
    Gets QueryTableCollection in the worksheet.
  - getPivotTables : PivotTableCollection
```
function getPivotTables()
```
    Gets all pivot tables in this worksheet.
  - getType/setType : Number
```
function getType() / function setType(value)
```
    Represents worksheet type. The value of the property is SheetType integer constant.
  - getName/setName : String
```
function getName() / function setName(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 : boolean
```
function getShowFormulas() / function setShowFormulas(value)
```
    Indicates whether to show formulas or their results.
  - isGridlinesVisible/setGridlinesVisible : boolean
```
function isGridlinesVisible() / function setGridlinesVisible(value)
```
    Gets or sets a value indicating whether the gridlines are visible.Default is true.
  - isRowColumnHeadersVisible/setRowColumnHeadersVisible : boolean
```
function isRowColumnHeadersVisible() / function setRowColumnHeadersVisible(value)
```
    Gets or sets a value indicating whether the worksheet will display row and column headers. Default is true.
  - getPaneState : Number
```
function getPaneState()
```
    Indicates whether the pane has horizontal or vertical splits, and whether those splits are frozen. The value of the property is PaneStateType integer constant.
  - getDisplayZeros/setDisplayZeros : boolean
```
function getDisplayZeros() / function setDisplayZeros(value)
```
    True if zero values are displayed.
  - getDisplayRightToLeft/setDisplayRightToLeft : boolean
```
function getDisplayRightToLeft() / function setDisplayRightToLeft(value)
```
    Indicates if the specified worksheet is displayed from right to left instead of from left to right. Default is false.
  - isOutlineShown/setOutlineShown : boolean
```
function isOutlineShown() / function setOutlineShown(value)
```
    Indicates whether to show outline.
  - isSelected/setSelected : boolean
```
function isSelected() / function setSelected(value)
```
    Indicates whether this worksheet is selected when the workbook is opened.
  - getListObjects : ListObjectCollection
```
function getListObjects()
```
    Gets all ListObjects in this worksheet.
  - getTabId/setTabId : Number
```
function getTabId() / function setTabId(value)
```
    Specifies the internal identifier for the sheet.
  - getHorizontalPageBreaks : HorizontalPageBreakCollection
```
function getHorizontalPageBreaks()
```
    Gets the HorizontalPageBreakCollection collection.
  - getVerticalPageBreaks : VerticalPageBreakCollection
```
function getVerticalPageBreaks()
```
    Gets the VerticalPageBreakCollection collection.
  - getHyperlinks : HyperlinkCollection
```
function getHyperlinks()
```
    Gets the HyperlinkCollection collection.
  - getPageSetup : PageSetup
```
function getPageSetup()
```
    Represents the page setup description in this sheet.
  - getAutoFilter : AutoFilter
```
function getAutoFilter()
```
    Represents auto filter for the specified worksheet.
  - hasAutofilter : boolean
```
function hasAutofilter()
```
    Indicates whether this worksheet has auto filter.
  - getTransitionEvaluation/setTransitionEvaluation : boolean
```
function getTransitionEvaluation() / function setTransitionEvaluation(value)
```
    Indicates whether the Transition Formula Evaluation (Lotus compatibility) option is enabled.
  - getTransitionEntry/setTransitionEntry : boolean
```
function getTransitionEntry() / function setTransitionEntry(value)
```
    Indicates whether the Transition Formula Entry (Lotus compatibility) option is enabled.
  - getVisibilityType/setVisibilityType : Number
```
function getVisibilityType() / function setVisibilityType(value)
```
    Indicates the visible state for this sheet. The value of the property is VisibilityType integer constant.
  - isVisible/setVisible : boolean
```
function isVisible() / function setVisible(value)
```
    Represents if the worksheet is visible.
  - getSparklineGroups : SparklineGroupCollection
```
function getSparklineGroups()
```
    Gets the sparkline groups in the worksheet.
  - getCharts : ChartCollection
```
function getCharts()
```
    Gets a Chart collection
  - getComments : CommentCollection
```
function getComments()
```
    Gets the Comment collection.
  - getPictures : PictureCollection
```
function getPictures()
```
    Gets a Picture collection.
  - getTextBoxes : TextBoxCollection
```
function getTextBoxes()
```
    Gets a TextBox collection.
  - getCheckBoxes : CheckBoxCollection
```
function getCheckBoxes()
```
    Gets a CheckBox collection.
  - getOleObjects : OleObjectCollection
```
function getOleObjects()
```
    Represents a collection of OleObject in a worksheet.
  - getShapes : ShapeCollection
```
function getShapes()
```
    Returns all drawing shapes in this worksheet.
  - getSlicers : SlicerCollection
```
function getSlicers()
```
    Get the Slicer collection in the worksheet
  - getTimelines : TimelineCollection
```
function getTimelines()
```
    Get the Timeline collection in the worksheet
  - getIndex : Number
```
function getIndex()
```
    Gets the index of sheet in the worksheet collection.
  - isProtected : boolean
```
function isProtected()
```
    Indicates if the worksheet is protected.
  - getValidations : ValidationCollection
```
function getValidations()
```
    Gets the data validation setting collection in the worksheet.
  - getAllowEditRanges : ProtectedRangeCollection
```
function getAllowEditRanges()
```
    Gets the allow edit range collection in the worksheet.
  - getErrorCheckOptions : ErrorCheckOptionCollection
```
function getErrorCheckOptions()
```
    Gets error check setting applied on certain ranges.
  - getOutline : Outline
```
function getOutline()
```
    Gets the outline on this worksheet.
  - getFirstVisibleRow/setFirstVisibleRow : Number
```
function getFirstVisibleRow() / function setFirstVisibleRow(value)
```
    Represents first visible row index.
  - getFirstVisibleColumn/setFirstVisibleColumn : Number
```
function getFirstVisibleColumn() / function setFirstVisibleColumn(value)
```
    Represents first visible column index.
  - getZoom/setZoom : Number
```
function getZoom() / function setZoom(value)
```
    Represents the scaling factor in percentage. It should be between 10 and 400.
    Please set the view type first.
  - getViewType/setViewType : Number
```
function getViewType() / function setViewType(value)
```
    Gets and sets the view type. The value of the property is ViewType integer constant.
  - isPageBreakPreview/setPageBreakPreview : boolean
```
function isPageBreakPreview() / function setPageBreakPreview(value)
```
    Indicates whether the specified worksheet is shown in normal view or page break preview.
  - isRulerVisible/setRulerVisible : boolean
```
function isRulerVisible() / function setRulerVisible(value)
```
    Indicates whether the ruler is visible. This property is only applied for page break preview.
  - getTabColor/setTabColor : Color
```
function getTabColor() / function setTabColor(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 : String
```
function getCodeName() / function setCodeName(value)
```
    Gets worksheet code name.
  - getBackgroundImage/setBackgroundImage : byte[]
```
function getBackgroundImage() / function setBackgroundImage(value)
```
    Gets and sets worksheet background image.
  - getConditionalFormattings : ConditionalFormattingCollection
```
function getConditionalFormattings()
```
    Gets the ConditionalFormattings in the worksheet.
  - getActiveCell/setActiveCell : String
```
function getActiveCell() / function setActiveCell(value)
```
    Gets or sets the active cell in the worksheet.
  - getCustomProperties : CustomPropertyCollection
```
function 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.
  - getSmartTagSetting : SmartTagSetting
```
function getSmartTagSetting()
```
    Gets all SmartTagCollection objects of the worksheet.
  - getScenarios : ScenarioCollection
```
function getScenarios()
```
    Gets the collection of Scenario.
  - getCellWatches : CellWatchCollection
```
function getCellWatches()
```
    Gets collection of cells on this worksheet being watched in the 'watch window'.
- Method Detail
  - replace
```
function replace(oldString, newString)
```
    Replaces all cells' text with a new string.
    Parameters:
    oldString: String - Old string value.
    newString: String - New string value.
  - getSelectedRanges
```
function getSelectedRanges()
```
    Gets selected ranges of cells in the designer spreadsheet.
    Returns:
    An java.util.ArrayList which contains selected ranges.
  - getPrintingPageBreaks
```
function getPrintingPageBreaks(options)
```
    Gets automatic page breaks.
    Each cell area represents a paper.
    Parameters:
    options: ImageOrPrintOptions - The print options
    Returns:
    The automatic page breaks areas.
  - toString
```
function toString()
```
    Returns a string represents the current Worksheet object.
    Returns:
  - startAccessCache
```
function startAccessCache(opts)
```
    Starts the session that uses caches to access the data in this worksheet.
    After finishing the access to the data, closeAccessCache(int) should be invoked with same options to clear all caches and recover normal access mode.
    Parameters:
    opts: Number - A AccessCacheOptions value. options of data access
  - closeAccessCache
```
function closeAccessCache(opts)
```
    Closes the session that uses caches to access the data in this worksheet.
    Parameters:
    opts: Number - A AccessCacheOptions value. options of data access
  - convertFormulaReferenceStyle
```
function convertFormulaReferenceStyle(formula, toR1C1, baseCellRow, baseCellColumn)
```
    Converts the formula reference style.
    Parameters:
    formula: String - The formula to be converted.
    toR1C1: boolean - Which reference style to convert the formula to. If the original formula is of A1 reference style, then this value should be true so the formula will be converted from A1 to R1C1 reference style; If the original formula is of R1C1 reference style, then this value should be false so the formula will be converted from R1C1 to A1 reference style;
    baseCellRow: Number - The row index of the base cell.
    baseCellColumn: Number - The column index of the base cell.
    Returns:
    The converted formula.
  - calculateFormula
```
function calculateFormula(formula)
```
    Calculates a formula.
    Parameters:
    formula: String - Formula to be calculated.
    Returns:
    Calculated formula result.
  - calculateFormula
```
function calculateFormula(formula, opts)
```
    Calculates a formula expression directly.
    The formula will be calculated just like it has been set to cell A1. And the formula will be taken as normal formula. If you need the formula be calculated as an array formula and to get an array for the calculated result, please use calculateArrayFormula(java.lang.String, com.aspose.cells.CalculationOptions) instead.
    Parameters:
    formula: String - Formula to be calculated.
    opts: CalculationOptions - Options for calculating formula
    Returns:
    Calculated result of given formula. The returned object may be of possible types of Cell.Value, or ReferredArea.
  - calculateFormula
```
function calculateFormula(formula, pOpts, cOpts, baseCellRow, baseCellColumn, calculationData)
```
    Calculates a formula expression directly.
    The formula will be calculated just like it has been set to the specified base cell. And the formula will be taken as normal formula. If you need the formula be calculated as an array formula and to get an array for the calculated result, please use calculateArrayFormula(java.lang.String, com.aspose.cells.FormulaParseOptions, com.aspose.cells.CalculationOptions, int, int, int, int, com.aspose.cells.CalculationData) instead.
    Parameters:
    formula: String - Formula to be calculated.
    pOpts: FormulaParseOptions - Options for parsing formula.
    cOpts: CalculationOptions - Options for calculating formula.
    baseCellRow: Number - The row index of the base cell.
    baseCellColumn: Number - The column index of the base cell.
    calculationData: CalculationData - The calculation data. It is used for the situation that user needs to calculate some static formulas when implementing custom calculation engine. For such kind of situation, user needs to specify it with the calculation data provided for #Error Cref: M:Aspose.Cells.AbstractCalculationEngine.Calculate(Aspose.Cells.CalculationData).
    Returns:
    Calculated result of given formula. The returned object may be of possible types of Cell.Value, or ReferredArea.
  - calculateArrayFormula
```
function calculateArrayFormula(formula, opts)
```
    Calculates a formula as array formula.
    Parameters:
    formula: String - Formula to be calculated.
    opts: CalculationOptions - Options for calculating formula
  - calculateArrayFormula
```
function calculateArrayFormula(formula, opts, maxRowCount, maxColumnCount)
```
    Calculates a formula as array formula.
    The formula will be taken as dynamic array formula to calculate the dimension and result. User specified maximum dimension is used for cases that the calculated result is large data set (for example, the calculated result may correspond to a whole row or column data) but user does not need so large an array according to business requirement or for performance consideration.
    Parameters:
    formula: String - Formula to be calculated.
    opts: CalculationOptions - Options for calculating formula
    maxRowCount: Number - the maximum row count of resultant data. If it is non-positive or greater than the actual row count, then actual row count will be used.
    maxColumnCount: Number - the maximum column count of resultant data. If it is non-positive or greater than the actual row count, then actual column count will be used.
    Returns:
    Calculated formula result.
  - calculateArrayFormula
```
function calculateArrayFormula(formula, pOpts, cOpts, baseCellRow, baseCellColumn, maxRowCount, maxColumnCount, calculationData)
```
    Calculates a formula as array formula.
    The formula will be taken as dynamic array formula to calculate the dimension and result. User specified maximum dimension is used for cases that the calculated result is large data set (for example, the calculated result may correspond to a whole row or column data) but user does not need so large an array according to business requirement or for performance consideration.
    Parameters:
    formula: String - Formula to be calculated.
    pOpts: FormulaParseOptions - Options for parsing formula
    cOpts: CalculationOptions - Options for calculating formula
    baseCellRow: Number - The row index of the base cell.
    baseCellColumn: Number - The column index of the base cell.
    maxRowCount: Number - The maximum row count of resultant data. If it is non-positive or greater than the actual row count, then actual row count will be used.
    maxColumnCount: Number - The maximum column count of resultant data. If it is non-positive or greater than the actual row count, then actual column count will be used.
    calculationData: CalculationData - The calculation data. It is used for the situation that user needs to calculate some static formulas when implementing custom calculation engine. For such kind of situation, user needs to specify it with the calculation data provided for #Error Cref: M:Aspose.Cells.AbstractCalculationEngine.Calculate(Aspose.Cells.CalculationData).
    Returns:
    Calculated formula result.
  - calculateFormula
```
function calculateFormula(options, recursive)
```
    Calculates all formulas in this worksheet.
    Parameters:
    options: CalculationOptions - Options for calculation
    recursive: boolean - True means if the worksheet' cells depend on the cells of other worksheets, the dependent 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
```
function xmlMapQuery(path, xmlMap)
```
    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: String - xml element path
    xmlMap: 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.
  - refreshPivotTables
```
function refreshPivotTables()
```
    Refreshes all the PivotTables in this Worksheet.
  - refreshPivotTables
```
function refreshPivotTables(option)
```
    Refreshes all the PivotTables in this Worksheet.
    Parameters:
    option: PivotTableRefreshOption - The option for refreshing data source of pivot table.
  - dispose
```
function dispose()
```
    Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
  - getPanes
```
function getPanes()
```
    Gets the window panes.
    If the window is not split or frozen.
  - freezePanes
```
function freezePanes(row, column, freezedRows, 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 froze position and the last two parameters specify the area frozen on the left top pane.
    Parameters:
    row: Number - Row index.
    column: Number - Column index.
    freezedRows: Number - Number of visible rows in top pane, no more than row index.
    freezedColumns: Number - Number of visible columns in left pane, no more than column index.
  - getFreezedPanes
```
function getFreezedPanes()
```
    Gets the freeze panes.
    Returns:
    Return null means the worksheet is not frozen 0:Row index;1:column;2:freezedRows;3:freezedRows
  - split
```
function split()
```
    Splits window.
  - freezePanes
```
function freezePanes(cellName, freezedRows, 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: String - Cell name.
    freezedRows: Number - Number of visible rows in top pane, no more than row index.
    freezedColumns: Number - Number of visible columns in left pane, no more than column index.
  - unFreezePanes
```
function unFreezePanes()
```
    Unfreezes panes in the worksheet.
  - removeSplit
```
function removeSplit()
```
    Removes split window.
  - addPageBreaks
```
function addPageBreaks(cellName)
```
    Adds page break.
    Parameters:
    cellName: String -
  - copy
```
function copy(sourceSheet)
```
    Copies contents and formats from another worksheet.
    Parameters:
    sourceSheet: Worksheet - Source worksheet.
  - copy
```
function copy(sourceSheet, copyOptions)
```
    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: Worksheet - Source worksheet.
    copyOptions: CopyOptions -
  - autoFitColumn
```
function autoFitColumn(columnIndex, firstRow, lastRow)
```
    Autofits the column width.
    This method autofits a row based on content in a range of cells within the row.
    Parameters:
    columnIndex: Number - Column index.
    firstRow: Number - First row index.
    lastRow: Number - Last row index.
  - autoFitColumns
```
function autoFitColumns()
```
    Autofits all columns in this worksheet.
  - autoFitColumns
```
function autoFitColumns(options)
```
    Autofits all columns in this worksheet.
    Parameters:
    options: AutoFitterOptions - The auto fitting options
  - autoFitColumn
```
function autoFitColumn(columnIndex)
```
    Autofits the column width.
    AutoFitColumn is an imprecise function.
    Parameters:
    columnIndex: Number - Column index.
  - autoFitColumns
```
function autoFitColumns(firstColumn, lastColumn)
```
    Autofits the columns width.
    AutoFitColumn is an imprecise function.
    Parameters:
    firstColumn: Number - First column index.
    lastColumn: Number - Last column index.
  - autoFitColumns
```
function autoFitColumns(firstColumn, lastColumn, options)
```
    Autofits the columns width.
    AutoFitColumn is an imprecise function.
    Parameters:
    firstColumn: Number - First column index.
    lastColumn: Number - Last column index.
    options: AutoFitterOptions - The auto fitting options
  - autoFitColumns
```
function autoFitColumns(firstRow, firstColumn, lastRow, lastColumn)
```
    Autofits the columns width.
    AutoFitColumn is an imprecise function.
    Parameters:
    firstRow: Number - First row index.
    firstColumn: Number - First column index.
    lastRow: Number - Last row index.
    lastColumn: Number - Last column index.
  - autoFitColumns
```
function autoFitColumns(firstRow, firstColumn, lastRow, lastColumn, options)
```
    Autofits the columns width.
    AutoFitColumn is an imprecise function.
    Parameters:
    firstRow: Number - First row index.
    firstColumn: Number - First column index.
    lastRow: Number - Last row index.
    lastColumn: Number - Last column index.
    options: AutoFitterOptions - The auto fitting options
  - autoFitRow
```
function autoFitRow(rowIndex, firstColumn, lastColumn)
```
    Autofits the row height.
    This method autofits a row based on content in a range of cells within the row.
    Parameters:
    rowIndex: Number - Row index.
    firstColumn: Number - First column index.
    lastColumn: Number - Last column index.
  - autoFitRow
```
function autoFitRow(rowIndex, firstColumn, lastColumn, options)
```
    Autofits the row height.
    This method autofits a row based on content in a range of cells within the row.
    Parameters:
    rowIndex: Number - Row index.
    firstColumn: Number - First column index.
    lastColumn: Number - Last column index.
    options: AutoFitterOptions - The auto fitter options
  - autoFitRows
```
function autoFitRows()
```
    Autofits all rows in this worksheet.
  - autoFitRows
```
function autoFitRows(onlyAuto)
```
    Autofits all rows in this worksheet.
    Parameters:
    onlyAuto: boolean - True,only autofits the row height when row height is not customed.
  - autoFitRows
```
function autoFitRows(options)
```
    Autofits all rows in this worksheet.
    Parameters:
    options: AutoFitterOptions - The auto fitter options
  - autoFitRows
```
function autoFitRows(startRow, endRow)
```
    Autofits row height in a range.
    Parameters:
    startRow: Number - Start row index.
    endRow: Number - End row index.
  - autoFitRows
```
function autoFitRows(startRow, endRow, options)
```
    Autofits row height in a range.
    Parameters:
    startRow: Number - Start row index.
    endRow: Number - End row index.
    options: AutoFitterOptions - The options of auto fitter.
  - autoFitRow
```
function autoFitRow(startRow, endRow, startColumn, endColumn)
```
    Autofits row height in a rectangle range.
    Parameters:
    startRow: Number - Start row index.
    endRow: Number - End row index.
    startColumn: Number - Start column index.
    endColumn: Number - End column index.
  - autoFitRow
```
function autoFitRow(rowIndex)
```
    Autofits the row height.
    AutoFitRow is an imprecise function.
    Parameters:
    rowIndex: Number - Row index.
  - getAdvancedFilter
```
function getAdvancedFilter()
```
    Gets the settings of advanced filter.
    Returns:
  - advancedFilter
```
function advancedFilter(isFilter, listRange, criteriaRange, copyTo, uniqueRecordOnly)
```
    Filters data using complex criteria.
    Parameters:
    isFilter: boolean - Indicates whether filtering the list in place.
    listRange: String - The list range.
    criteriaRange: String - The criteria range.
    copyTo: String - The range where copying data to.
    uniqueRecordOnly: boolean - Only displaying or copying unique rows.
  - removeAutoFilter
```
function removeAutoFilter()
```
    Removes the auto filter of the worksheet.
  - setVisible
```
function setVisible(isVisible, ignoreError)
```
    Sets the visible options.
    Parameters:
    isVisible: boolean - Whether the worksheet is visible
    ignoreError: boolean - Whether to ignore error if this option is not valid.
  - selectRange
```
function selectRange(startRow, startColumn, totalRows, totalColumns, removeOthers)
```
    Selects a range.
    Parameters:
    startRow: Number - The start row.
    startColumn: Number - The start column
    totalRows: Number - The number of rows.
    totalColumns: Number - The number of columns
    removeOthers: boolean - True means removing other selected range and only select this range.
  - removeAllDrawingObjects
```
function removeAllDrawingObjects()
```
    Removes all drawing objects in this worksheet.
  - clearComments
```
function clearComments()
```
    Clears all comments in designer spreadsheet.
  - protect
```
function protect(type)
```
    Protects worksheet.
    This method protects worksheet without password. It can protect worksheet in all versions of Excel file.
    Parameters:
    type: Number - A ProtectionType value. Protection type.
  - protect
```
function protect(type, password, oldPassword)
```
    Protects worksheet.
    This method can protect worksheet in all versions of Excel file.
    Parameters:
    type: Number - A ProtectionType value. Protection type.
    password: String - Password.
    oldPassword: String - 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 = new cells\Workbook("Book1.xls"); //Accessing the first worksheet in the Excel file $worksheet = $workbook->getWorksheets()->get(0); //Protecting the worksheet with a password $worksheet->protect(cells\ProtectionType::ALL, "aspose", null);
  - unprotect
```
function unprotect()
```
    Unprotects worksheet.
    This method unprotects worksheet which is protected without password.
  - unprotect
```
function unprotect(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: String - Password
  - moveTo
```
function moveTo(index)
```
    Moves the sheet to another location in the spreadsheet.
    Parameters:
    index: Number - Destination sheet index.

aspose.cells

Classes

Constants

Class Worksheet

Property Getters/Setters Detail

getProtection : Protection

getUniqueId/setUniqueId : String

getWorkbook : Workbook

getCells : Cells

getQueryTables : QueryTableCollection

getPivotTables : PivotTableCollection

getType/setType : Number

getName/setName : String

getShowFormulas/setShowFormulas : boolean

isGridlinesVisible/setGridlinesVisible : boolean

isRowColumnHeadersVisible/setRowColumnHeadersVisible : boolean

getPaneState : Number

getDisplayZeros/setDisplayZeros : boolean

getDisplayRightToLeft/setDisplayRightToLeft : boolean

isOutlineShown/setOutlineShown : boolean

isSelected/setSelected : boolean

getListObjects : ListObjectCollection

getTabId/setTabId : Number

getHorizontalPageBreaks : HorizontalPageBreakCollection

getVerticalPageBreaks : VerticalPageBreakCollection

getHyperlinks : HyperlinkCollection

getPageSetup : PageSetup

getAutoFilter : AutoFilter

hasAutofilter : boolean

getTransitionEvaluation/setTransitionEvaluation : boolean

getTransitionEntry/setTransitionEntry : boolean

getVisibilityType/setVisibilityType : Number

isVisible/setVisible : boolean

getSparklineGroups : SparklineGroupCollection

getCharts : ChartCollection

getComments : CommentCollection

getPictures : PictureCollection

getTextBoxes : TextBoxCollection

getCheckBoxes : CheckBoxCollection

getOleObjects : OleObjectCollection

getShapes : ShapeCollection

getSlicers : SlicerCollection

getTimelines : TimelineCollection

getIndex : Number

isProtected : boolean

getValidations : ValidationCollection

getAllowEditRanges : ProtectedRangeCollection

getErrorCheckOptions : ErrorCheckOptionCollection

getOutline : Outline

getFirstVisibleRow/setFirstVisibleRow : Number

getFirstVisibleColumn/setFirstVisibleColumn : Number

getZoom/setZoom : Number

getViewType/setViewType : Number

isPageBreakPreview/setPageBreakPreview : boolean

isRulerVisible/setRulerVisible : boolean

getTabColor/setTabColor : Color

getCodeName/setCodeName : String

getBackgroundImage/setBackgroundImage : byte[]

getConditionalFormattings : ConditionalFormattingCollection

getActiveCell/setActiveCell : String

getCustomProperties : CustomPropertyCollection

getSmartTagSetting : SmartTagSetting

getScenarios : ScenarioCollection

getCellWatches : CellWatchCollection

Method Detail

replace

getSelectedRanges

getPrintingPageBreaks

toString

startAccessCache

closeAccessCache

convertFormulaReferenceStyle

calculateFormula

calculateFormula

calculateFormula

calculateArrayFormula

calculateArrayFormula

calculateArrayFormula

calculateFormula

xmlMapQuery