Package io.keikai.axyra.sheets

Class Range

java.lang.Object
io.keikai.axyra.sheets.Range
public final class Range extends Object
A rectangle of cells on one sheet: the bulk API. A range is a (sheet, firstRow, firstColumn, lastRow, lastColumn) view, 0-based and inclusive, and holds no native resource. Get one with Sheet.range(String) or Sheet.range(int, int, int, int).

Each bulk read or write makes one engine call for the whole rectangle, not one per cell. A loop over Cell makes one engine call per cell and per accessor, so for more than a few cells the bulk form is much faster. Prefer values(), setValues(io.keikai.axyra.sheets.CellValue[][]), numbers(), setNumbers(double[]) and setFormulas(java.lang.String[][]) to a per-cell loop. 


 Range data = sheet.range("A1:C2");
 data.setValues(new CellValue[][] {
     {CellValue.text("Item"), CellValue.text("Qty"), CellValue.text("Price")},
     {CellValue.text("Pen"), CellValue.number(3), CellValue.number(1.5)}});
 sheet.range("D2:D2").setFormula("B2*C2");
 CellValue[][] read = sheet.range("A1:D2").values(); // one call, 8 cells
 double[] qty = sheet.range("B2:B2").numbers();      // numeric fast path

Creating a range off the grid throws IndexOutOfBoundsException; a range whose last row or column is before its first throws IllegalArgumentException.

  • Field Summary

    Fields
    Modifier and Type
    Field
    Description
    static final int
    CONTENT_COMMENT
    contentFlags() bit: the cell carries a comment.
    static final int
    CONTENT_HYPERLINK
    contentFlags() bit: the cell carries a hyperlink.
    static final int
    CONTENT_RICH_TEXT
    contentFlags() bit: the cell's text is rich (more than one run).

  • Method Summary

    Modifier and Type
    Method
    Description
    void
    addConditionalFormat(ConditionalFormat format)
    Adds a conditional-formatting rule over this rectangle.
    void
    applyNamedStyle(String name)
    Applies a registered named cell style to every cell in the rectangle, replacing each cell's style.
    String[][]
    arrayFormulaRefs()
    Reads, for each cell, the extent of the legacy array formula (Ctrl+Shift+Enter) whose top-left cell it is: "B2:B5", or "C3" for a one-cell array.
    void
    autoFill(Range destination)
    Detects a pattern in this range and extends it over destination, as Excel's fill handle does: a linear numeric series, or a cyclic copy for text and mixed content.
    int
    autoMerge(boolean horizontal)
    Merges runs of adjacent cells with identical, non-empty displayed text: left to right within each row when horizontal is true, otherwise top to bottom within each column.
    void
    clear()
    Clears the value and formula of every cell in the rectangle.
    int
    clearHyperlinks()
    Removes every hyperlink in the rectangle.
    int
    colCount()
    Number of columns in the rectangle.
    byte[]
    contentFlags()
    Returns per-cell content flags, row-major.
    void
    copyTo(Sheet dstSheet, int dstRow, int dstCol)
    Copies this rectangle to (dstRow, dstCol) on dstSheet, as Excel's Copy and Paste do.
    void
    copyTo(Sheet dstSheet, int dstRow, int dstCol, String pasteType)
    Copies this rectangle with an Excel Paste Special mode.
    void
    createTable(String name, String... columnNames)
    Creates a table (Excel's ListObject, "Format as Table") over this rectangle.
    void
    deleteCells(Shift shift)
    Deletes the cells of this rectangle and moves the remaining cells into the gap: Shift.UP moves the cells below, in these columns, up; Shift.LEFT moves the cells to the right, in these rows, left.
    int
    firstColumn()
    First (left) column, 0-based inclusive.
    int
    firstRow()
    First (top) row, 0-based inclusive.
    String[][]
    formulas()
    Reads every cell's formula text, row-major.
    void
    insertCells(Shift shift)
    Inserts blank cells over this rectangle and moves the existing cells away from it: Shift.DOWN moves the cells of these columns down by rowCount(); Shift.RIGHT moves the cells of these rows right by colCount().
    int
    lastColumn()
    Last (right) column, 0-based inclusive.
    int
    lastRow()
    Last (bottom) row, 0-based inclusive.
    void
    merge()
    Merges the rectangle into one cell, as Excel's Merge Cells does.
    double[]
    numbers()
    Reads the rectangle as a row-major double[] of length rowCount() * colCount(): entry r * colCount() + c is the cell at (firstRow() + r, firstColumn() + c).
    int
    removeDuplicates(int[] keyColumns, boolean hasHeader)
    Removes duplicate rows in the rectangle, comparing the given key columns.
    int
    rowCount()
    Number of rows in the rectangle.
    void
    setArrayFormula(String formula)
    Enters a legacy array formula (Ctrl+Shift+Enter) over the whole rectangle, anchored at the top-left cell.
    void
    setFormula(String formula)
    Fill the rectangle with one formula, as Excel's Ctrl+Enter does: the formula is written for the top-left cell, and every other cell gets it with its relative references shifted by its offset from there (A1*B1 on C1:C3 becomes A2*B2 in C2 and A3*B3 in C3); $-anchored references stay put.
    void
    setFormulas(String[][] formulas)
    Writes one formula per cell from a row-major String[rowCount()][colCount()].
    void
    setNumbers(double[] data)
    Writes the rectangle from a row-major double[] of length rowCount() * colCount().
    void
    setStyle(CellStyle style)
    Replaces the style of every cell in the rectangle with style.
    void
    setValidation(Validation validation)
    Adds a data-validation rule over this rectangle.
    void
    setValues(CellValue[][] values)
    Writes every cell from a row-major CellValue[rowCount()][colCount()].
    int
    sheetIndex()
    The 0-based index of the sheet this range belongs to.
    void
    sort(int column, boolean ascending)
    Sorts the rows of the rectangle by one column, case-insensitively.
    void
    sort(SortKey... keys)
    Sorts the rows of the rectangle by one or more keys, primary key first.
    void
    sortLeftToRight(SortKey... keys)
    Sorts left to right (Excel's Sort Options → "Sort left to right"): reorders whole columns within the rectangle by the values of one or more key rows.
    String
    spillRange()
    Returns the A1 extent of the dynamic-array spill, or of the legacy Ctrl+Shift+Enter array, that contains this range's top-left cell, e.g.
    CellStyle
    style()
    Returns the style of the top-left cell of the rectangle.
    int[]
    styleIds()
    Returns each cell's style id, row-major.
    void
    unmerge()
    Unmerges every merged region that lies wholly inside this rectangle.
    CellValue[][]
    values()
    Reads every cell as a CellValue, row-major: result[r][c] is the cell at (firstRow() + r, firstColumn() + c).

    Methods inherited from class java.lang.Object

    clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

  • Field Details

  • Method Details

    • firstRow

      public int firstRow()
      First (top) row, 0-based inclusive.

    • firstColumn

      public int firstColumn()
      First (left) column, 0-based inclusive.

    • lastRow

      public int lastRow()
      Last (bottom) row, 0-based inclusive.

    • lastColumn

      public int lastColumn()
      Last (right) column, 0-based inclusive.

    • sheetIndex

      public int sheetIndex()
      The 0-based index of the sheet this range belongs to. It is read at each call, so it follows the sheet when other sheets are added, moved or removed.

    • rowCount

      public int rowCount()
      Number of rows in the rectangle.

    • colCount

      public int colCount()
      Number of columns in the rectangle.

    • values

      public CellValue[][] values()
      Reads every cell as a CellValue, row-major: result[r][c] is the cell at (firstRow() + r, firstColumn() + c). A formula cell returns its current result; pending writes are recalculated first. A blank cell returns CellValue.BLANK. One engine call for the whole rectangle; prefer it to a loop over Cell.value().
      Returns:
      a new array; later edits to the sheet do not change it

    • numbers

      public double[] numbers()
      Reads the rectangle as a row-major double[] of length rowCount() * colCount(): entry r * colCount() + c is the cell at (firstRow() + r, firstColumn() + c). Only numeric cells carry a value; every other cell (blank, text, boolean, error) is NaN. A formula cell gives its numeric result. One engine call; the cheapest bulk read for numeric data.
      Returns:
      a new array; later edits to the sheet do not change it

    • setNumbers

      public void setNumbers(double[] data)
      Writes the rectangle from a row-major double[] of length rowCount() * colCount(). Like setValues(io.keikai.axyra.sheets.CellValue[][]), this replaces values only: each cell keeps its style (number format, font, fill), comment and hyperlink, and a formula there is dropped. A NaN entry clears that cell, so the output of numbers() writes back unchanged for numeric and blank cells. One engine call; the cheapest bulk write for numeric data.
      Parameters:
      data - row-major values, one per cell
      Throws:
      IllegalArgumentException - if data.length != rowCount() * colCount()

    • setValues

      public void setValues(CellValue[][] values)
      Writes every cell from a row-major CellValue[rowCount()][colCount()]. Each cell's value is replaced and any formula is dropped; the cell keeps its style, comment and hyperlink. CellValue.BLANK or a null entry clears the value. One engine call for the whole rectangle; prefer it to a loop over Cell.setValue(io.keikai.axyra.sheets.CellValue). For an all-numeric block, setNumbers(double[]) is cheaper.
      Parameters:
      values - row-major values, one per cell
      Throws:
      IllegalArgumentException - if the array shape differs from the range, or an entry is a CellValue.Array

    • formulas

      public String[][] formulas()
      Reads every cell's formula text, row-major. An entry is null for a cell without a formula. Otherwise it is the formula as Excel's formula bar shows it, without the leading =, without braces around an array formula and without _xlfn. prefixes. Use arrayFormulaRefs() to tell whether a formula is a legacy array formula. One engine call.
      Returns:
      a new String[rowCount()][colCount()]; a snapshot

    • arrayFormulaRefs

      public String[][] arrayFormulaRefs()
      Reads, for each cell, the extent of the legacy array formula (Ctrl+Shift+Enter) whose top-left cell it is: "B2:B5", or "C3" for a one-cell array. Every other cell gives null, including cells of a spilled dynamic-array formula (see spillRange()). Re-enter such a formula with setArrayFormula(String) over that extent. One engine call.
      Returns:
      a new String[rowCount()][colCount()], row-major

    • setFormula

      public void setFormula(String formula)
      Fill the rectangle with one formula, as Excel's Ctrl+Enter does: the formula is written for the top-left cell, and every other cell gets it with its relative references shifted by its offset from there (A1*B1 on C1:C3 becomes A2*B2 in C2 and A3*B3 in C3); $-anchored references stay put. Use setFormulas(String[][]) to give each cell its own text. One engine call.

      A leading = is optional. An empty string clears the formula and value of every cell. A relative reference shifted past the edge of the grid becomes #REF!. Each cell keeps its style, comment and hyperlink. A formula that returns an array spills from its cell, as in Excel 365; use setArrayFormula(java.lang.String) for a Ctrl+Shift+Enter array.

      Parameters:
      formula - the formula for the top-left cell; must not be null
      Throws:
      AxyraFormulaException - if the formula does not parse; nothing is written
      NullPointerException - if formula is null

    • setFormulas

      public void setFormulas(String[][] formulas)
      Writes one formula per cell from a row-major String[rowCount()][colCount()]. Each text is used as given; references are not shifted (compare setFormula(java.lang.String)). A null entry leaves the cell unchanged; an empty string clears the cell's formula and value. A leading = is optional. Each written cell keeps its style, comment and hyperlink. Every entry is parsed before any cell is written. One engine call.
      Parameters:
      formulas - row-major formulas, one per cell
      Throws:
      IllegalArgumentException - if the array shape differs from the range
      AxyraFormulaException - if any entry does not parse; no cell is written

    • setArrayFormula

      public void setArrayFormula(String formula)
      Enters a legacy array formula (Ctrl+Shift+Enter) over the whole rectangle, anchored at the top-left cell. The formula is evaluated once and its result fills the rectangle; arrayFormulaRefs() reports the extent. A leading = is optional and is dropped. For an Excel 365 dynamic array that spills by itself, use setFormula(java.lang.String) on one cell instead. One engine call.
      Parameters:
      formula - the array formula
      Throws:
      AxyraFormulaException - if the formula does not parse; nothing is written

    • copyTo

      public void copyTo(Sheet dstSheet, int dstRow, int dstCol)
      Copies this rectangle to (dstRow, dstCol) on dstSheet, as Excel's Copy and Paste do. Values, formulas, styles, comments and hyperlinks are copied. An empty source cell clears the destination cell. Relative references shift by the paste offset; $-anchored ones stay. Merged regions, data validation and conditional formats are not copied. Dependents of the pasted cells recalculate.

      dstSheet must belong to this range's workbook. To copy into another workbook, use Workbook.copySheetFrom(io.keikai.axyra.sheets.Workbook, int) for a whole sheet, or read with values() and write with setValues(io.keikai.axyra.sheets.CellValue[][]). See copyTo(Sheet, int, int, String) for Paste Special.

      Parameters:
      dstSheet - the destination sheet, in the same workbook
      dstRow - 0-based top row of the destination
      dstCol - 0-based left column of the destination
      Throws:
      IllegalArgumentException - if dstSheet belongs to another workbook

    • copyTo

      public void copyTo(Sheet dstSheet, int dstRow, int dstCol, String pasteType)
      Copies this rectangle with an Excel Paste Special mode.
      • "all": as copyTo(Sheet, int, int).
      • "values": formula results as plain values; the destination keeps its style, comment and hyperlink.
      • "formulas": formulas (references shifted) and constants; the destination keeps its style.
      • "formats": styles only.
      • "comments": comments only.
      The mode is case-sensitive; any other string, such as "Values", is treated as "all". Dependents of the pasted cells recalculate.
      Parameters:
      dstSheet - the destination sheet, in the same workbook
      dstRow - 0-based top row of the destination
      dstCol - 0-based left column of the destination
      pasteType - the Paste Special mode, as listed above
      Throws:
      IllegalArgumentException - if dstSheet belongs to another workbook; use Workbook.copySheetFrom(io.keikai.axyra.sheets.Workbook, int), or values() and setValues(io.keikai.axyra.sheets.CellValue[][]), instead

    • spillRange

      public String spillRange()
      Returns the A1 extent of the dynamic-array spill, or of the legacy Ctrl+Shift+Enter array, that contains this range's top-left cell, e.g. "A1:A3", or "A1" for a one-cell result.

      In automatic calculation, pending writes are recalculated first, so a formula just set with setFormula(java.lang.String) reports its spill at once. In manual calculation, call Workbook.recalculate() first; until then a new dynamic formula has no spill.

      Returns:
      the extent, or null when the cell is not part of an array

    • setStyle

      public void setStyle(CellStyle style)
      Replaces the style of every cell in the rectangle with style. Attributes that style leaves unset take their defaults; they are not merged with each cell's old style. To change one attribute, start from style().toBuilder(). Identical styles are shared, so this makes two engine calls whatever the range size. Values, formulas, comments and hyperlinks are not changed. See applyNamedStyle(java.lang.String) for a registered named style.
      Parameters:
      style - the style to apply; must not be null

    • applyNamedStyle

      public void applyNamedStyle(String name)
      Applies a registered named cell style to every cell in the rectangle, replacing each cell's style. Does nothing when name is not registered.
      Parameters:
      name - the named style, as registered

    • style

      public CellStyle style()
      Returns the style of the top-left cell of the rectangle. Other cells are not read; use styleIds() to compare the styles of many cells. Two engine calls.
      Returns:
      a snapshot of the resolved style; the workbook default style for a cell that has none

    • styleIds

      public int[] styleIds()
      Returns each cell's style id, row-major. Equal ids mean equal styles; 0 is the workbook default style. Ids are engine-internal and valid only for this workbook. One engine call. Use style() to read a style itself.
      Returns:
      a new array of length rowCount() * colCount()

    • contentFlags

      public byte[] contentFlags()
      Returns per-cell content flags, row-major. Each entry is a bitwise OR of CONTENT_COMMENT, CONTENT_HYPERLINK and CONTENT_RICH_TEXT; 0 means none. It is the bulk presence check for Cell.comment(), Cell.hyperlink() and Cell.richRunCount(): find the few cells that carry such content in one engine call, then read only those cells.
      Returns:
      a new array of length rowCount() * colCount()

    • sort

      public void sort(SortKey... keys)
      Sorts the rows of the rectangle by one or more keys, primary key first. Sort-key columns are range-relative (0 = the range's first column). A key carrying a custom order sorts its matching values by list position (Excel's "Custom Lists"); unlisted values sort after all listed ones.

      Text compares case-insensitively. Every row of the rectangle is sorted; there is no header detection, so leave a header row out of the range. Whole rows move within the rectangle, with their styles, and moved formulas keep pointing at the cells they pointed at. With no keys the call does nothing. Key columns are not checked against the range.

      Parameters:
      keys - the sort keys, primary first

    • sort

      public void sort(int column, boolean ascending)
      Sorts the rows of the rectangle by one column, case-insensitively. Same as sort(new SortKey(column, ascending)).
      Parameters:
      column - 0-based column index relative to the range (0 = firstColumn())
      ascending - true for A to Z, smallest first

    • sortLeftToRight

      public void sortLeftToRight(SortKey... keys)
      Sorts left to right (Excel's Sort Options → "Sort left to right"): reorders whole columns within the rectangle by the values of one or more key rows. Each key's SortKey.column() is the range-relative key row (0 = the range's first row); custom orders apply as in sort(SortKey...). Text compares case-insensitively. With no keys the call does nothing.
      Parameters:
      keys - the sort keys, primary first; each key's column is a range-relative row index

    • autoFill

      public void autoFill(Range destination)
      Detects a pattern in this range and extends it over destination, as Excel's fill handle does: a linear numeric series, or a cyclic copy for text and mixed content. A source formula repeats, with relative references shifted per cell. destination must be on the same sheet and have the same columns as this range (fill down or up) or the same rows (fill right or left), and should contain this range. A destination that does not contain it is not rejected; it is filled as if the gap were part of the series. Source cells keep their values.
      Parameters:
      destination - the area to fill, normally this range extended along one axis
      Throws:
      IllegalArgumentException - if destination is on another sheet, or shares neither columns nor rows with this range

    • removeDuplicates

      public int removeDuplicates(int[] keyColumns, boolean hasHeader)
      Removes duplicate rows in the rectangle, comparing the given key columns. Key columns are range-relative (0 = the range's first column), as in sort(SortKey...) and Excel's Remove Duplicates.

      Text compares case-insensitively; spaces count; a blank cell differs from an empty string. The first row of each duplicate set is kept. The remaining rows move up inside the rectangle; cells below the rectangle do not move.

      Parameters:
      keyColumns - 0-based key columns relative to the range
      hasHeader - true to keep the first row out of the comparison
      Returns:
      the number of rows removed
      Throws:
      IllegalArgumentException - if keyColumns is empty or a key column lies outside the range

    • setValidation

      public void setValidation(Validation validation)
      Adds a data-validation rule over this rectangle. Rules already on these cells are kept, so a cell can carry two rules; remove the old one first to replace it. Cell values are not checked against the new rule.
      Parameters:
      validation - the rule to add

    • addConditionalFormat

      public void addConditionalFormat(ConditionalFormat format)
      Adds a conditional-formatting rule over this rectangle. Rules already on these cells are kept.
      Parameters:
      format - the rule to add

    • createTable

      public void createTable(String name, String... columnNames)
      Creates a table (Excel's ListObject, "Format as Table") over this rectangle. The first row of the range is the header row.

      With no columnNames, the column names are read from the header row's cells. An empty or duplicate header becomes a generated ColumnN name, and every name is written back into its header cell as text.

      With columnNames, the names are stored in the table definition only; the header cells are not written. Write the same names into the first row (for example with setValues(io.keikai.axyra.sheets.CellValue[][])) so the cells and the table agree. The number of names is not checked against colCount(), and the table name is not checked for uniqueness.

      Parameters:
      name - the table name used in structured references, e.g. Sales[Q1]
      columnNames - the column names, one per column, or none to read them from the header row

    • merge

      public void merge()
      Merges the rectangle into one cell, as Excel's Merge Cells does. The top-left cell keeps its value and style; every other cell in the rectangle is removed, including its style, comment and hyperlink. A one-cell range does nothing. Overlap with an existing merged region is not checked. Use unmerge() to undo the merge; the removed cells stay blank.

    • unmerge

      public void unmerge()
      Unmerges every merged region that lies wholly inside this rectangle. A merged region that only partly overlaps the rectangle stays merged; pass its full extent to unmerge it. Cell values are not changed; the cells that merge() cleared stay blank.

    • autoMerge

      public int autoMerge(boolean horizontal)
      Merges runs of adjacent cells with identical, non-empty displayed text: left to right within each row when horizontal is true, otherwise top to bottom within each column. As with merge(), every cell of a run but the first is cleared.
      Parameters:
      horizontal - true to merge runs within rows, false to merge runs within columns
      Returns:
      the number of merged regions created

    • insertCells

      public void insertCells(Shift shift)
      Inserts blank cells over this rectangle and moves the existing cells away from it: Shift.DOWN moves the cells of these columns down by rowCount(); Shift.RIGHT moves the cells of these rows right by colCount(). Cells outside those columns or rows do not move.
      Parameters:
      shift - Shift.DOWN or Shift.RIGHT
      Throws:
      IllegalArgumentException - if shift is UP or LEFT

    • deleteCells

      public void deleteCells(Shift shift)
      Deletes the cells of this rectangle and moves the remaining cells into the gap: Shift.UP moves the cells below, in these columns, up; Shift.LEFT moves the cells to the right, in these rows, left.
      Parameters:
      shift - Shift.UP or Shift.LEFT
      Throws:
      IllegalArgumentException - if shift is DOWN or RIGHT

    • clearHyperlinks

      public int clearHyperlinks()
      Removes every hyperlink in the rectangle. Cell values and styles are not changed. One engine call.
      Returns:
      the number of hyperlinks removed

    • clear

      public void clear()
      Clears the value and formula of every cell in the rectangle. Styles, comments and hyperlinks stay; see clearHyperlinks(). One engine call.