Package io.keikai.api

Interface Range

All Known Implementing Classes:
RangeImpl
public interface Range
Range can represent a cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells, or a 3-D blocks of cells.
You have to use this class's API to do any operation of the Sheet, then the upload will sync to the UI automatically.
To get the instance of a Range, please use the Ranges API.
Note : the range API doesn't check the sheet protection, if you care it, you have to check it by calling isProtected() before you do any operation.
Since:
3.0.0
Author:
dennis
See Also:

  • Method Details

    • getLock

      ReadWriteLock getLock()

    • getBook

      Book getBook()
      Gets the book of this range
      Returns:
      book

    • getSheet

      Sheet getSheet()
      Gets the sheet of this range
      Returns:
      sheet

    • getColumn

      int getColumn()
      Gets the left column of this range
      Returns:
      the left column

    • getRow

      int getRow()
      Gets the top row of this range
      Returns:
      the top row

    • getLastColumn

      int getLastColumn()
      Gets the right/last column of this range
      Returns:

    • getLastRow

      int getLastRow()
      Gets the bottom/last row of this range
      Returns:

    • getRowCount

      int getRowCount()
      Gets the row count of this range
      Returns:
      count of row of this range

    • getColumnCount

      int getColumnCount()
      Gets the column count of this range
      Returns:
      count of column of this range

    • getCellStyleHelper

      Range.CellStyleHelper getCellStyleHelper()
      Gets cell-style-helper, this helper helps you to create new style, font or color
      Returns:

    • sync

      void sync(RangeRunner run)
      Runs runer under synchronization protection
      Parameters:
      run - the runner

    • visit

      void visit(CellVisitor visitor)
      Visits all cells in this range with synchronization protection, make sure you call this in a limited range, don't use it for all row/column selection, it will spend much time to iterate the cell
      Parameters:
      visitor - the cell visitor

    • toShiftedRange

      Range toShiftedRange(int rowOffset, int colOffset)
      Return a new range by shifting the current Range according to the offsets but still keep the original height and width e.g. 
       
 // range is A1:B2
 range.toShiftedRange(1, 0); //returns A2:B3
      Parameters:
      rowOffset - row offset of the new range. Positive number shifts it down, negative number shifts it up.
      colOffset - column offset of the new range. Positive number shifts it right, negative number shifts it left.
      Returns:
      the new shifted range

    • toCellRange

      Range toCellRange(int rowOffset, int colOffset)
      Returns a new range having one cell according to the offset based on the left-top corner. For example: 
       
 //range is A1:B2
 range.toCellRange(0, 0); // returns A1
      Parameters:
      rowOffset - row offset of the cell.
      colOffset - column offset of the cell.
      Returns:
      the new range of the cell

    • toRowRange

      Range toRowRange()
      Return a range that represents all columns and between the first-row and last-row of this range. It is a useful when you want to manipulate entire row (such as deleting rows)

    • toColumnRange

      Range toColumnRange()
      Return a range that represents all rows and between the first-column and last-column of this range It is a useful when you want to manipulate entire column (such as deleting columns)

    • isWholeColumn

      boolean isWholeColumn()
      Check if this range represents a whole column, which means all rows are included,

    • isWholeRow

      boolean isWholeRow()
      Check if this range represents a whole row, which means all column are included,

    • isWholeSheet

      boolean isWholeSheet()
      Check if this range represents a whole sheet, which mean all column and row are included,

    • clearContents

      void clearContents()
      Clears contents

    • clearStyles

      void clearStyles()
      Clears styles

    • clearAll

      void clearAll()
      Clears contents, styles and unmerge this range.

    • paste

      Range paste(Range dest)
      Pastes to destination. Support pasting cells among 2 books.
      Parameters:
      dest - the destination. It can be a range of a different book.
      Returns:
      a Range contains the final pasted range. paste to a protected sheet will always cause paste return null.

    • paste

      Range paste(Range dstRange, boolean cut)
      Cut and paste to destination. Support pasting cells among 2 books.

    • pasteSpecial

      Range pasteSpecial(Range dest, Range.PasteType type, Range.PasteOperation op, boolean skipBlanks, boolean transpose)
      Pastes to destination. Support pasting cells among 2 books.
      Parameters:
      dest - the destination. It can be a range of a different book.
      type - the paste type
      op - the paste operation
      skipBlanks - skip blanks or not
      transpose - transpose the cell or not
      Returns:
      a Range contains the final pasted range. paste to a protected sheet will always cause paste return null.
      Throws:
      IllegalOpArgumentException

    • applyBorders

      void applyBorders(Range.ApplyBorderType applyType, CellStyle.BorderType borderType, String htmlColor)
      apply borders
      Parameters:
      applyType - the apply type
      borderType - the border type
      htmlColor - the color (#rgb-hex-code, e.x #FF00FF)

    • hasMergedCell

      boolean hasMergedCell()
      Returns:
      true if any merged cell inside (fully contains or overlaps) this range

    • isMergedCell

      boolean isMergedCell()
      Returns:
      true if entire range is a merged cell.

    • merge

      void merge(boolean across)
      Merges the range
      Parameters:
      across - true if merge horizontally only

    • unmerge

      void unmerge()
      Unmerge the range

    • insert

      void insert(Range.InsertShift shift, Range.InsertCopyOrigin copyOrigin)
      Insert new cells to the area of this range.
      To insert a row, you have to call toRowRange() first, to insert a column, you have to call toColumnRange() first.
      Parameters:
      shift - the shift direction of original cells
      copyOrigin - copy the format from nearby cells when inserting new cells

    • delete

      void delete(Range.DeleteShift shift)
      Delete cells of the range.
      To delete a row, you have to call toRowRange() first, to delete a column, you have to call toColumnRange() first.
      Parameters:
      shift - the shift direction when deleting.

    • sort

      void sort(boolean desc)
      Sort range
      Parameters:
      desc - true for descent, false for ascent

    • sort

      void sort(boolean desc, boolean header, boolean matchCase, boolean sortByRows, Range.SortDataOption dataOption)
      Sort range
      Parameters:
      desc - true for descent, false for ascent
      header - includes header or not
      matchCase - matches character chase of not
      sortByRows - sorts by row or not
      dataOption - data option for sort

    • sort

      void sort(Range index1, boolean desc1, Range.SortDataOption dataOption1, Range index2, boolean desc2, Range.SortDataOption dataOption2, Range index3, boolean desc3, Range.SortDataOption dataOption3, boolean header, boolean matchCase, boolean sortByRows)
      Sort range
      Parameters:
      index1 - the sort index 1
      desc1 - true for descent, false for ascent of index 1
      header - includes header or not
      matchCase - matches character chase of not
      sortByRows - sorts by row or not
      dataOption1 - data option 1 for sort
      index2 - the sort index 2
      desc2 - true for descent, false for ascent of index 2
      dataOption2 - data option 2 for sort
      index3 - the sort index 3
      desc3 - true for descent, false for ascent of index 3
      dataOption3 - data option31 for sort

    • autoFill

      void autoFill(Range dest, Range.AutoFillType fillType)
      According to current range, fills data to destination range automatically
      Parameters:
      dest - the destination range
      fillType - the fill type

    • fillDown

      void fillDown()
      Fills cells by copying from first/top row data

    • fillLeft

      void fillLeft()
      Fills cells by copying from last/right column data

    • fillUp

      void fillUp()
      Fills cells by copying from bottom row data

    • fillRight

      void fillRight()
      Fills cells by copying from first/left column data

    • shift

      void shift(int rowOffset, int colOffset)
      Shifts/moves cells with a offset row and column

    • setColumnWidth

      void setColumnWidth(int widthPx)
      Sets the width (in pixels) of the columns in this range.

      Visibility effect:

      • If widthPx is 0, it hides the columns implicitly.
      • If widthPx is a positive value, it unhides the columns automatically.
      • It affects entire columns even if the range only contains some cells (implicitly calls toColumnRange()).
      • The column width will be marked as custom and disables "best fit".
      • It may trigger re-refresh of cell contents (e.g., changing displayed number precision).
      Parameters:
      widthPx - width in pixels. A value of 0 hides the columns, while a positive value unhides them and sets their width.
      See Also:

    • setRowHeight

      void setRowHeight(int heightPx)
      Sets the height(in pixel) of row in this range, it effect to whole row.
      Parameters:
      heightPx - height in pixel
      See Also:

    • setRowHeight

      void setRowHeight(int heightPx, boolean isCustom)
      Sets the height(in pixel) of row in this range and specify it's custom size or not.
      Parameters:
      heightPx - height in pixel
      isCustom - true if it's set by users manually, false if it's determined by the system automatically
      Since:
      3.0.1

    • setCellStyle

      void setCellStyle(CellStyle nstyle)
      Sets cell style, applies it to all cells of this range
      Parameters:
      nstyle - new cell style
      See Also:

    • setCellEditText

      void setCellEditText(String editText)
      Works like a user entering a text in a cell via a browser. So, this method supports "Smart Input", and it will parse the text and turn the text into a String, Number, Date, or Boolean according to the supported input format pattern. To enter a formula, the text should stat with =.
      This method applies all cells of this range.
      Parameters:
      editText - the text to enter, it can be a string, integer string, date string or a formula (start with '=')
      Throws:
      IllegalFormulaException

    • setArrayFormula

      void setArrayFormula(String editText)
      Works like a user entering an array formula in a cell or a range via a browser. So, this method supports "Smart Input", and it will parse the text and turn the text into a String, Number, Date, or Boolean according to the supported input format pattern. To enter a formula, the text should stat with =.
      This method applies all cells of this range.
      Parameters:
      editText - the text to enter, it can be a string, integer string, date string or a formula (start with '=')
      Throws:
      IllegalFormulaException
      Since:
      6.0.0

    • setCellValue

      void setCellValue(Object value)
      Sets cell data value and type. Notice that it doesn't parse the specified String object. If you call: setCellValue("100") , it will produce a text cell instead of an integer, one hundred. If you need a number cell, you need to call setCellValue(100) . The same rule applies to Date and Boolean.
      This method applies all cells of this range.
      Parameters:
      value - the cell value, could be null, String, Number, Date or Boolean
      See Also:

    • setCellValues

      void setCellValues(Object... values)
      Sets cell data values, applies them to all cells from left to right and top to bottom consistently. For example:

      Set values in a row

      
 Range rA1C1 = Ranges.range("A1:C1");
 rA1C1.setCellValues(3,2,1);
      The result is [3,2,1] from A1 to C1

      Set values in a column

      
 Range rA1A3 = Ranges.range("A1:A3");
 rA1A3.setCellValues(1,2,3);
      The result is [1,2,3] from A1 to A3

      Set values in an area

      
 Range rA1C3 = Ranges.range("A1:C3");
 rA1C3.setCellValues(9, 8, 7, 6, 5, 4, 3,2,1);
      The result is [9, 8, 7, 6, 5, 4, 3, 2, 1] from A1, B1, C1, A2, ... to C3

      Cell and values count mismatch

      If you pass more values than cell number, then it just sets cells in this range, not over-fill. 
      
 rA1C1.setCellValues(1, 2, 3, 4, 5);
      The result is [1,2,3] from A1 to C1.

      If you pass fewer values than cell number, then it just set cells according the number of values. 
      
 rA1C1.setCellValues(1, 2);
      The result is [1,2] from A1 to B1.

      Transpose 2D Array when necessary

      Since this method sets cells row by row, if you have a 2D array that contains column by column data. Then you can transpose the array first and pass to the method.
      Parameters:
      values - all cell values, could be null, String, Number, Date or Boolean
      Since:
      5.5.0

    • setCellHyperlink

      void setCellHyperlink(Hyperlink.HyperlinkType type, String address, String label)
      Sets cell hyperlink, applies it too all cells
      Parameters:
      type - the hyperlink type
      address - the address, e.x http://www.zkoss.org
      label - the label to display

    • getCellHyperlink

      Hyperlink getCellHyperlink()
      Gets the first cell(top-left) Hyperlink object of this range.
      Returns:

    • getCellStyle

      CellStyle getCellStyle()
      Gets the first cell(top-left) style of this range
      Returns:
      cell-style of this cell, if this cell doesn't has cell-style, it will check row/column's cell-style and then sheet's default cell-style

    • getCellData

      CellData getCellData()
      Gets the first cell(top-left) data of this range
      Returns:

    • getCellEditText

      String getCellEditText()
      Gets the first cell(top-left) edit text of this range
      Returns:
      edit text
      See Also:

    • getCellFormatText

      String getCellFormatText()
      Gets the first cell(top-left) formatted text of this range
      Returns:
      format text
      See Also:

    • getCellDataFormat

      String getCellDataFormat()
      Gets the first cell(top-left) format of this range
      Returns:
      Since:
      3.5.0

    • getCellValue

      Object getCellValue()
      Gets the first cell(top-left) value of this range
      Returns:
      value object or null for a blank cell
      See Also:

    • getCellValues

      List<Object> getCellValues()
      Gets all cell values from left to right and top to bottom of this range in one dimensional list. Blank cell will be null value instead.
      If the range is A1:C3, the method will return you a list of cell values like:
      A1, B1, C1, A2, B2, C2, A3, B3, C3
      Returns:
      values a object list
      Since:
      5.5.0

    • setDisplaySheetGridlines

      void setDisplaySheetGridlines(boolean enable)
      Displays sheet grid-lines or not
      Parameters:
      enable - true to display

    • isDisplaySheetGridlines

      boolean isDisplaySheetGridlines()
      Returns:
      true if display sheet grid-lines is enabled

    • setHidden

      void setHidden(boolean hidden)
      Hide or unhide rows or columns.
      To hide/unhide a row, you have to call toRowRange() first, to hide/un-hide a column, you have to call toColumnRange() or a whole column range.
      Parameters:
      hidden - hide or not

    • setSheetName

      void setSheetName(String name)
      Sets the sheet name
      Parameters:
      name - new sheet name, it must be not same as another sheet name in it's owner book.

    • getSheetName

      String getSheetName()
      Gets the sheet name
      Returns:
      sheet name

    • setSheetOrder

      void setSheetOrder(int pos)
      Sets the sheet order
      Parameters:
      pos - the position

    • getSheetOrder

      int getSheetOrder()
      Gets the sheet order
      Returns:

    • isProtected

      boolean isProtected()
      Returns:
      true if the specified range is protected; i.e. sheet is protected and some cells in the range are locked

    • isAutoFilterEnabled

      boolean isAutoFilterEnabled()
      Returns:
      true if auto filter is enabled.

    • findAutoFilterRange

      Range findAutoFilterRange()
      To find a range of cells for applying auto filter according to this range. Usually, these two ranges are different. This method searches the filtering range through a specific rules.
      Returns:
      a range of cells for applying auto filter or null if can't find one from this Range.

    • enableAutoFilter

      void enableAutoFilter(boolean enable)
      Enable/disable autofilter of the sheet
      Parameters:
      enable - true to enable

    • enableAutoFilter

      void enableAutoFilter(int field, Range.AutoFilterOperation filterOp, Object criteria1, Object criteria2, Boolean showButton)
      Enables auto filter and set extra condition
      Parameters:
      field - the filed index (according to current range, 1 base)
      filterOp - auto filter operation
      criteria1 - criteria for autofilter
      criteria2 - criteria for autofilter
      showButton - true/false for show/hide dropdown button, null will keep the original setting.

    • resetAutoFilter

      void resetAutoFilter()
      Clears condition of filter, show all the data

    • applyAutoFilter

      void applyAutoFilter()
      Re-applies the filter, filter by last condition and data again. Call this if the data was change and you want to re-new the filter result.

    • addPicture

      Picture addPicture(SheetAnchor anchor, byte[] image, Picture.Format format)
      Adds picture to sheet
      Parameters:
      anchor - the anchor for picture
      image - the image binary array
      format - the image format
      Returns:
      the new added picture

    • deletePicture

      void deletePicture(Picture picture)
      Deletes picture that in sheet
      Parameters:
      picture -

    • movePicture

      void movePicture(SheetAnchor anchor, Picture picture)
      Moves picture
      Parameters:
      anchor - the anchor to re-allocate
      picture - the picture to re-allocate

    • updatePicture

      void updatePicture(Picture picture, SheetAnchor.AnchorType anchorType)
      Updates picture with anchor type
      Since:
      5.12.0

    • addChart

      Chart addChart(SheetAnchor anchor, Chart.Type type, Chart.Grouping grouping, Chart.LegendPosition pos)
      Adds chart to sheet
      Parameters:
      anchor - the destination anchor of the chart
      type - the chart type
      grouping - the chart grouping
      pos - the legend position
      Returns:
      the new added chart

    • addChart

      Chart addChart(String name, SheetAnchor anchor, Chart.Type type, Chart.Grouping grouping, Chart.LegendPosition pos)
      Adds chart to sheet
      Parameters:
      name - the name of chart
      anchor - the destination anchor of the chart
      type - the chart type
      grouping - the chart grouping
      pos - the legend position
      Returns:
      the new added chart
      Since:
      6.2.0

    • deleteChart

      void deleteChart(Chart chart)
      Deletes chart
      Parameters:
      chart - the chart to delete

    • moveChart

      void moveChart(SheetAnchor anchor, Chart chart)
      Moves chart to new location
      Parameters:
      anchor - the new location to move
      chart - the chart to move

    • createSheet

      Sheet createSheet(String name)
      Creates a new sheet and appends it as the last sheet. To move the new sheet to the specific position, see setSheetOrder(int).
      Parameters:
      name - the sheet name, it must not be same as another sheet name in book of this range. Otherwise, it takes no effect.
      Returns:
      the new created sheet

    • cloneSheet

      Sheet cloneSheet(String name)
      Clone this sheet; create a sheet and copy the contents of this sheet; then add it as the last sheet.
      Parameters:
      name - the sheet name, it must not be the same as another sheet name in book of this range
      Returns:
      the new created sheet
      Since:
      3.6.0

    • setFreezePanel

      void setFreezePanel(int rowfreeze, int columnfreeze)
      Set the freeze panel
      Parameters:
      rowfreeze - the number of row to freeze, 0 means no freeze
      columnfreeze - the number of column to freeze, 0 means no freeze

    • deleteSheet

      void deleteSheet()
      Deletes sheet. Note: You couldn't delete last sheet of a book.

    • notifyChange

      void notifyChange()
      Notify this range has been changed, then spreadsheet will render the changed cells to a browser. It's usually used with setAutoRefresh(boolean).

    • notifyChange

      void notifyChange(String[] variables)
      Notify the whole book of specified variables change. Then spreadsheet will re-evaluate those cells that reference to these variables.
      Parameters:
      variables - changed variables

    • asString

      String asString()
      get formatted string of this range
      Returns:

    • updateChart

      void updateChart(Chart chart)
      Notify the component that a chart has change, e.g. call it after chart data changes.
      Parameters:
      chart - the chart that contains change
      Since:
      3.5.0

    • updateChart

      void updateChart(Chart chart, SheetAnchor.AnchorType anchorType)
      Notify the component that a chart has change, e.g. call it after chart data changes.
      Parameters:
      chart - the chart that contains change
      anchorType -
      Since:
      5.12.0

    • getInternalRange

      SRange getInternalRange()
      Get internal range implementation
      Since:
      3.5.0

    • createName

      void createName(String nameName)
      Create a name that refers to this range. You can get this named ranage by Ranges.rangeByName(Sheet, String).
      Parameters:
      nameName - name of the range that you can refer in formulas.
      Since:
      3.5.0

    • isSheetProtected

      boolean isSheetProtected()
      Returns:
      true if the sheet is protected
      Since:
      3.5.0

    • protectSheet

      void protectSheet(String password, boolean allowSelectingLockedCells, boolean allowSelectingUnlockedCells, boolean allowFormattingCells, boolean allowFormattingColumns, boolean allowFormattingRows, boolean allowInsertColumns, boolean allowInsertRows, boolean allowInsertingHyperlinks, boolean allowDeletingColumns, boolean allowDeletingRows, boolean allowSorting, boolean allowFiltering, boolean allowUsingPivotTables, boolean drawingObjects, boolean scenarios)
      Protect a Sheet so that it cannot be modified. You can call this method on a protected sheet to change its allowed options if the same password is supplied; otherwise, it is ignored. A protected sheet can be unprotected by calling unprotectSheet(String password). change the protection options; make sure provide
      Parameters:
      password - a case-sensitive password for the sheet; null or empty string means protect the sheet without password.
      allowSelectingLockedCells - true to allow select locked cells; default to true.
      allowSelectingUnlockedCells - true to allow select unlocked cells; default to true.
      allowFormattingCells - true to allow user to format any cell on the protected sheet; default false.
      allowFormattingColumns - true to allow user to format any columns on the protected sheet; default false.
      allowFormattingRows - true to allow user to format any rows on the protected sheet; default false.
      allowInsertColumns - true to allow user to insert columns on the protected sheet; default false.
      allowInsertRows - true to allow user to insert rows on the protected sheet; default false.
      allowInsertingHyperlinks - true to allow user to insert hyperlinks on the protected sheet; default false.
      allowDeletingColumns - true to allow user to delete columns on the protected sheet; default false.
      allowDeletingRows - true to allow user to delete rows on the protected sheet; default false.
      allowSorting - true to allow user to sort on the protected sheet; default false.
      allowFiltering - true to allow user to set filters on the protected sheet; default false.
      allowUsingPivotTables - true to allow user to use pivot table reports on the protected sheet; default false.
      drawingObjects - true to protect objects; default to false.
      scenarios - true to protect scenarios; default to true.
      Since:
      3.5.0
      See Also:

    • protectSheet

      void protectSheet(SheetProtection protection)
      Protect a Sheet so that it cannot be modified.
      Parameters:
      protection -
      Since:
      5.0.0
      See Also:

    • unprotectSheet

      boolean unprotectSheet(String password)
      Removes protection from a sheet. This method has no effect if the sheet isn't protected.
      Parameters:
      password - a case-sensitive password used to unprotect the sheet. If the sheet isn't protected with a password, this argument is ignored. If you omit this argument for a sheet that is protected with a password, you'll be prompted for the password.
      Since:
      3.5.0

    • getSheetProtection

      SheetProtection getSheetProtection()
      Gets SheetProtection which tells what are allowed operations for a protected sheet of this range.
      Returns:
      Since:
      3.5.0

    • validate

      Validation validate(String editText)
      validate the user's input.
      Parameters:
      editText -
      Returns:
      the Validation that constraint the input; null if none.

    • setValidation

      void setValidation(Validation.ValidationType validationType, boolean ignoreBlank, Validation.OperatorType operatorType, boolean inCellDropDown, String formula1, String formula2, boolean showInput, String inputTitle, String inputMessage, boolean showError, Validation.AlertStyle alertStyle, String errorTitle, String errorMessage)
      Add if not exist or modify an existing Validation to this range. There are two ways to input list items for data validation in argument "formula1" and "formula2". One is to specify a range via a formula such as "=A1:A4" where the values in the cells of the range is the constrained value list; another is to input literally the constrained value list delimited by comma such as "1, 2, 3, 4". Note that when you input a formula, must lead the formula with an equal sign('=').
      Parameters:
      validationType - the type of this validation
      ignoreBlank - true if blank values are permitted.
      operatorType - the operator for this validation
      inCellDropDown - true if want to display dropdown list for acceptable values.
      formula1 - the value or expression associated with conditional format or data validation.
      formula2 - the 2nd part of a conditional format or data validation. Useful only when operatorType is BETWEEN or NOT_BETWEEN.
      showInput - true to show the input message.
      inputTitle - title for the data-validation input dialog box.
      inputMessage - message for the data-validation input dialog box.
      showError - true to show the error message.
      alertStyle - validation alert style.
      errorTitle - title of the data validation error dialog.
      errorMessage - data validation error message.
      Since:
      3.5.0

    • getValidations

      List<Validation> getValidations()
      Gets read-only Validation associated with the left-top cell of this range; return null if no associated validation.
      Since:
      3.5.0
      See Also:

    • deleteValidation

      void deleteValidation()
      Delete the Validation associated in the specified range.
      Since:
      3.5.0

    • setCellRichText

      void setCellRichText(String html)
      Set into the left top cell of this Range the specified text in html format.
      Parameters:
      html -
      Since:
      3.6.0

    • getCellRichText

      String getCellRichText()
      Returns text in html format; null if not a rich text.
      Since:
      3.6.0

    • getOrCreateFont

      @Deprecated Font getOrCreateFont(Font.Boldweight boldweight, Color color, int fontHeight, String fontName, boolean italic, boolean strikeout, Font.TypeOffset typeOffset, Font.Underline underline)
      Deprecated.
      As if release 5.3.0, replaced by Range.CellStyleHelper.builder(CellStyle) to create an immutable cell style and font
      Get font if exists or create font if not exists from the book of this range and return it.
      Returns:
      font of the specified font metrics
      Since:
      3.6.0

    • refresh

      void refresh(boolean includeDependants)
      Enforce evaluation(if not cached) and refresh UI of this range and its dependent cells if the includeDependents is true. This method is equal to {@link Range#refresh(boolean includeDependents, boolean false, boolean true)}.
      Since:
      3.6.0

    • setAutoRefresh

      boolean setAutoRefresh(boolean auto)
      Turn on(true)/off(false) of auto refresh of this range and return previous on/off status; default is true. If you turn off auto refresh, you need to call notifyChange() manually at the proper moment.
      Parameters:
      auto - whether refresh automatically
      Returns:
      previous status
      Since:
      3.6.0

    • refresh

      void refresh(boolean includeDependents, boolean clearCache, boolean enforceEval)
      Update data model and refresh UI of this range and its dependent cells if the argument includeDependents is true. Note that when you set parameter clearCache to true, the cached formula result in data model will be cleared first. If you set parameter enforceEval to true, data model associated with this range will be evaluated immediately; otherwise will be evaluated on demand.
      Since:
      3.7.0

    • setSheetVisible

      void setSheetVisible(Range.SheetVisible visible)
      Setup sheet's visibility; can be VISIBLE, HIDDEN, or VERY_HIDDEN.
      • VISIBLE: the sheet is visible
      • HIDDEN: the sheet is hidden but can be unhidden using UI
      • VERY_HIDDEN: the sheet is hidden but can be unhidden only via this API.
      Parameters:
      visible -
      Since:
      3.7.0
      See Also:

    • getCommentRichText

      String getCommentRichText()
      Return the comment rich edit text of the left top cell of this Range.
      Returns:
      the rich edit text of this Range.
      Since:
      3.7.0

    • setCommentRichText

      void setCommentRichText(String html)
      Set comment rich text into the left top cell of this range; null to delete it.
      Since:
      3.7.0

    • setCommentVisible

      void setCommentVisible(boolean visible)
      Set comment visibility into the left top cell of this range; if no comment at the cell, it simply ignored.
      Parameters:
      visible -
      Since:
      3.7.0

    • isCommentVisible

      boolean isCommentVisible()
      Returns whether the comment is always visible at the left top cell of this range; if no comment at the cell, it returns false.
      Returns:
      Since:
      3.7.0

    • notifyChange

      void notifyChange(Range.CellAttribute cellAttr)
      Notify this range has been changed on the specifield attribute.
      Since:
      3.8.0

    • setNameName

      void setNameName(String namename, String newname)
      Set(Rename) the name of the Name(or Table) as specified in this Range(a Book or a Sheet).
      Parameters:
      namename - name of the Name(or Table)
      newname - new name for the Name(or Table)
      Since:
      3.8.0

    • setStringValue

      void setStringValue(String value)
      Save the specified value into this range as a String no matter the value is prefixed with '=' or not.
      Parameters:
      value -
      Since:
      3.8.0

    • getMergedRegion

      CellRegion getMergedRegion()
      Returns the merged region of the left top cell of this Range if any; otherwise, returns null if not a merged region.
      Returns:
      the merged region of the left top cell of this Range if any
      Since:
      3.8.3

    • getDataRegion

      CellRegion getDataRegion()
      Returns the region which contains data(including chart, picture, and fills) in the sheet of this Range; return null if empty sheet.
      Returns:
      the region which contains data(including chart, picture, and fills) in the sheet of this Range; return null if empty sheet.
      Since:
      3.8.3

    • cloneSheetFrom

      Sheet cloneSheetFrom(String name, Sheet sheet)
      Clone from the specified source sheet to the owner book of this range. The book can be different between the owner book of this range and of the specified source sheet.
      Parameters:
      name - the name of the new created sheet; null would try to use the sheet name of the specified source sheet; if the same sheet name already used in the owner book of this range then use default "SheetX" name where X is the next sheet number of the owner book of this range.
      sheet - the source sheet to clone from to the owner book of this range.
      Returns:
      the new created sheet
      Since:
      3.9.0
      See Also:

    • cloneSheetFrom

      Sheet cloneSheetFrom(String name, Sheet sheet, boolean valueOnly)
      Clone from the specified source sheet to the owner book of this range. The book can be different between the owner book of this range and of the specified source sheet.
      Parameters:
      name - the name of the new created sheet; null would try to use the sheet name of the specified source sheet; if the same sheet name already used in the owner book of this range then use default "SheetX" name where X is the next sheet number of the owner book of this range.
      sheet - the source sheet to clone from to the owner book of this range.
      valueOnly - true to copy the cell value only
      Returns:
      the new created sheet
      Since:
      6.0.0

    • zOrderStream

      Stream<Range> zOrderStream()
      Returns a z-order cell range stream which treats a merged cell as one range. For example, 
      
    //  Data:
    //     | A | B | C | D | E |
    //  ---+---+---+---+---+---+
    //   1 | 1 |       | 3 |   |
    //  ---+---+       +---+---+
    //   2 |   |       |   |   |
    //  ---+   +       + 5 +---+
    //   3 | 4 |   2   |   |   |
    //  ---+   +       +---+---+
    //   4 |   |       | 6 |   |
    //  ---+---+       +---+---+
    //   5 | 7 |       | 8 |   |
    //  ---+---+-------+---+---+

    Stream zOrder = Ranges.range("A1:D5").zOrderStream();
          zOrder.map(Range::getCellValue)
                .map(Object::toString)
                .collect(Collectors.joining(", "));
          // Result: 1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0
      Since:
      5.6.0

    • nOrderStream

      Stream<Range> nOrderStream()
      Returns a n-order cell range stream which treats a merged cell as one range. For example, 
      
     //  Data:
     //     | A | B | C | D | E |
     //  ---+---+---+---+---+---+
     //   1 | 1 |       | 3 |   |
     //  ---+---+       +---+---+
     //   2 |   |       |   |   |
     //  ---+   +       + 5 +---+
     //   3 | 4 |   2   |   |   |
     //  ---+   +       +---+---+
     //   4 |   |       | 6 |   |
     //  ---+---+       +---+---+
     //   5 | 7 |       | 8 |   |
     //  ---+---+-------+---+---+

    Stream nOrder = Ranges.range("A1:D5").nOrderStream();
          nOrder.map(Range::getCellValue)
                .map(Object::toString)
                .collect(Collectors.joining(", "));

          // Result: 1.0, 4.0, 7.0, 2.0, 3.0, 5.0, 6.0, 8.0
      Since:
      5.6.0

    • getDirectPrecedents

      Set<Range> getDirectPrecedents()
      Returns Range(s) that are the direct precedents of this cell Range. If this Range covers more than one cell, the left-top one is used.
      Since:
      5.6.0

    • getPrecedents

      Set<Range> getPrecedents()
      Returns all Range(s) that are the direct or indirect precedents of this cell Range. If this Range covers more than one cell, the left-top one is used.
      Since:
      5.6.0

    • getDirectDependents

      Set<Range> getDirectDependents()
      Returns Range(s) that are the direct dependents of this cell Range. If this Range covers more than one cell, the left-top one is used.
      Since:
      5.6.0

    • getDependents

      Set<Range> getDependents()
      Returns all Range(s) that are the direct or indirect dependents of this cell Range. If this Range covers more than one cell, the left-top one is used.
      Since:
      5.6.0

    • tracePrecedents

      void tracePrecedents()
      Trace the precedents of this range.
      Since:
      5.6.0

    • traceDependents

      void traceDependents()
      Trace the dependents of this range.
      Since:
      5.6.0

    • clearTrace

      void clearTrace()
      Clears the current tracing.
      Since:
      5.6.0

    • getRowGroups

      List<Range> getRowGroups()
      Returns all group ranges for rows, which intersect with this range.
      Returns:
      an empty list if not matched.
      Since:
      6.0.0

    • getRowGroups

      List<Range> getRowGroups(int outlineLevel)
      Returns all group ranges matched the given outline level for rows, which intersect with this range.
      Parameters:
      outlineLevel - a valid value in a range is from 1 to 7 (inclusive).
      Returns:
      an empty list if not matched.
      Since:
      6.0.0

    • getRowGroups

      List<Range> getRowGroups(int fromOutlineLevel, int toOutlineLevel)
      Returns all group ranges matched between fromOutlineLevel and toOutlineLevel for rows, which intersect with this range.
      Parameters:
      fromOutlineLevel - 1~7 (inclusive)
      toOutlineLevel - 2~8 (exclusive)
      Returns:
      an empty list if not matched.
      Since:
      6.0.0

    • getColumnGroups

      List<Range> getColumnGroups()
      Returns all group ranges for columns, which intersect with this range.
      Returns:
      an empty list if not matched.
      Since:
      6.0.0

    • getColumnGroups

      List<Range> getColumnGroups(int outlineLevel)
      Returns all group ranges matched the given outline level for columns, which intersect with this range.
      Parameters:
      outlineLevel - a valid value in a range is from 1 to 7 (inclusive).
      Returns:
      an empty list if not matched.
      Since:
      6.0.0

    • getColumnGroups

      List<Range> getColumnGroups(int fromOutlineLevel, int toOutlineLevel)
      Returns all group ranges matched between fromOutlineLevel and toOutlineLevel for columns, which intersect with this range.
      Parameters:
      fromOutlineLevel - 1~7 (inclusive)
      toOutlineLevel - 2~8 (exclusive)
      Returns:
      an empty list if not matched.
      Since:
      6.0.0

    • setSummaryBelow

      void setSummaryBelow(boolean summaryBelow)
      Sets to be true to display summary group as below or to be false to display summary group as above for rows.

      Note: One setting to affect whole sheet, if the value is up-side-down against the previous value, it will clear all outline level for rows and columns.

      Default: true

      Parameters:
      summaryBelow - true to display as below for rows.
      Since:
      6.0.0

    • isSummaryBelow

      boolean isSummaryBelow()
      Returns whether to display group summary as below for rows.

      Default: true

      Since:
      6.0.0

    • setSummaryRight

      void setSummaryRight(boolean summaryRight)
      Sets to be true to display summary group as right or to be false to display summary group as left for columns.

      Note: One setting to affect whole sheet, if the value is up-side-down against the previous value, it will clear all outline level for rows and columns.

      Default: true

      Parameters:
      summaryRight - true to display as right for columns.
      Since:
      6.0.0

    • isSummaryRight

      boolean isSummaryRight()
      Returns whether to display group summary as right for columns.

      Default: true

      Since:
      6.0.0

    • collapse

      void collapse()
      Collapses one group by matching with the range at a time (not affected its descendant).
      Since:
      6.0.0

    • expand

      void expand()
      Expands one group by matching with the range at a time (not affected its descendant).
      Since:
      6.0.0

    • clearOutlineLevel

      void clearOutlineLevel()
      Clears all outline level for all groups that intersect with the range. 2:4 for rows and C:E for columns

    • isCollapsed

      boolean isCollapsed()
      Returns whether the group by matching with the range has collapsed or not. Otherwise, returns false instead.
      Since:
      6.0.0

    • getOutlineLevel

      int getOutlineLevel()
      Returns the outline of the group by matching with the range. Otherwise, returns 0 instead.
      Since:
      6.0.0

    • group

      void group()
      Groups the rows or columns by matching the range, which increases their outline level.

      Note: The maximum level is up to 7.

      Since:
      6.0.0

    • ungroup

      void ungroup()
      Ungroups the rows or columns by matching the range, which decreases their outline level.

      Note: The minimum level is down to 0.

      Since:
      6.0.0