Client API. The Codename One framework your app is built on: this runs on the device, not in a backend.
public final class Sheet
ImplementsTableModel
One sheet of a Workbook: a grid of cells with no fixed size, each empty or holding a
number, text, a logical value, an error or a formula.
Two ways to address a cell
The methods named ...Cell... address the sheet the way a formula does: row 0, column 0
is A1, whatever the sheet is used for. The TableModel methods
address the data: when setHeaderRow(boolean) is on, the first row of the sheet is
the column names and model row 0 is the row under it.
Values
A cell’s value is a Double, a String, a Boolean, a CellError, a java.util.Date
for a number whose format shows a date, or null for an empty cell. The value of a formula
is what it calculates to; getCellFormula(int, int) answers the formula itself.
A String passed to setValueAt(int, int, Object) is read as if it had been typed into
a spreadsheet: =A1*2 is a formula, 12.5 a number, 45% a number shown as a
percentage, 2024-03-01 a date, TRUE a logical value, and '12 the text 12.
setCellValue(int, int, Object) stores what it is given, so a String stays text.
Methods
public String getName() | The name of the sheet, which formulas on other sheets refer to it by. |
public Workbook getWorkbook() | The workbook the sheet is part of. |
public Object getCellValue(int row, int column) | The value of a cell. |
public Object getCellValue(String address) | The value of a cell named the way a formula names it. |
public void setCellValue(int row, int column, Object value) | Stores a value in a cell, replacing a formula it held, and recalculates what depends on it. |
public void setCellValue(String address, Object value) | Stores a value in a cell named the way a formula names it. |
public String getCellFormula(int row, int column) | The formula of a cell. |
public void setCellFormula(int row, int column, String formula) | Puts a formula into a cell and recalculates what depends on it. |
public void setCellFormula(String address, String formula) | Puts a formula into a cell named the way a formula names it. |
public String getCellText(int row, int column) | The text a cell shows: its value in its number format. |
public String getCellFormat(int row, int column) | The number format of a cell. |
public void setCellFormat(int row, int column, String format) | Sets the number format of a cell: how its value is shown, and for a number whether it is a date. |
public boolean isHeaderRow() | Whether the first row of the sheet holds the names of the columns. |
public void setHeaderRow(boolean headerRow) | Sets whether the first row of the sheet holds the names of the columns. |
public void setEditable(boolean editable) | Sets whether the table model reports its cells as editable. |
public int getRowCount() | Returns the number of rows in the table |
public int getColumnCount() | Returns the number of columns in the table |
public String getColumnName(int i) | The name of a column: the text of the header row’s cell when the sheet has a header row and the cell is not empty, the letters of the column otherwise. |
public boolean isCellEditable(int row, int column) | Returns true if the cell at the given location is an editable cell |
public Object getValueAt(int row, int column) | Returns the value of the cell at the given location |
public void setValueAt(int row, int column, Object o) | Stores a value. |
public String getFormulaAt(int row, int column) | The formula of a cell of the table model. |
public void addDataChangeListener(DataChangedListener d) | Adds a listener that is told when a cell changes: the cell that was edited, and every formula that reads it, directly or through other formulas. |
public void removeDataChangeListener(DataChangedListener d) | Removes a listener to the data changed event |
public void addRow(Object... values) | Adds a row under the last one. |
public void insertRows(int row, int count) | Inserts empty rows. |
public void removeRows(int row, int count) | Removes rows. |
public void insertColumns(int column, int count) | Inserts empty columns. |
public void removeColumns(int column, int count) | Removes columns. |
public GridColumn[] createColumns() | Describes the columns of the sheet for a DataGrid: the kind of each – number, date, logical, text – from the values it holds, a formatter that shows values in the number format most of the column’s cells have, and the width the file gave the column. |
public double getColumnWidth(int column) | The width of a column. |
public void setColumnWidth(int column, double characters) | Sets the width of a column. |
public int getRowOutlineLevel(int row) | The outline level of a row: how deep it is nested in the groups a spreadsheet can collapse. |
public void setRowOutlineLevel(int row, int level) | Sets the outline level of a row. |
public int getFrozenRows() | The number of rows at the top that stay in place while the rest scrolls. |
public int getFrozenColumns() | The number of columns at the left that stay in place while the rest scrolls. |
public void setFrozenPanes(int rows, int columns) | Sets the rows and columns that stay in place while the rest of the sheet scrolls. |
public void addMergedRegion(int firstRow, int firstColumn, int lastRow, int lastColumn) | Merges a rectangle of cells into one, as the file formats record it. |
public int getMergedRegionCount() | The number of merged regions. |
public int[] getMergedRegion(int index) | A merged region. |
Inherited methods
Method details
getName
public String getName()Returns
getWorkbook
public Workbook getWorkbook()Returns
getCellValue
public Object getCellValue(int row, int column)Parameters
rowint- the row of the sheet, 0 for the first
columnint- the column of the sheet, 0 for
A
Returns
getCellValue
public Object getCellValue(String address)Parameters
addressString- an address such as
B7
Returns
getCellValue(int, int)setCellValue
public void setCellValue(int row, int column, Object value)Parameters
rowint- the row of the sheet
columnint- the column of the sheet
valueObject- a
Number,String,Boolean,DateorCellError; null empties the cell. AStringis stored as text even when it looks like a number or a formula.
setCellValue
public void setCellValue(String address, Object value)Parameters
addressString- an address such as
B7 valueObject- the value, see
setCellValue(int, int, Object)
getCellFormula
public String getCellFormula(int row, int column)Parameters
rowint- the row of the sheet
columnint- the column of the sheet
Returns
=, or null when the cell holds no formulasetCellFormula
public void setCellFormula(int row, int column, String formula)Parameters
rowint- the row of the sheet
columnint- the column of the sheet
formulaString- a formula in Excel’s syntax, with or without the leading
=
Throws
IllegalArgumentException- when the formula cannot be read
setCellFormula
public void setCellFormula(String address, String formula)Parameters
addressString- an address such as
B7 formulaString- the formula, see
setCellFormula(int, int, String)
getCellText
public String getCellText(int row, int column)Parameters
rowint- the row of the sheet
columnint- the column of the sheet
Returns
getCellFormat
public String getCellFormat(int row, int column)Parameters
rowint- the row of the sheet
columnint- the column of the sheet
Returns
#,##0.00, General when the cell has nonesetCellFormat
public void setCellFormat(int row, int column, String format)Parameters
rowint- the row of the sheet
columnint- the column of the sheet
formatString- a format code in Excel’s syntax, such as
0.0%ord mmm yyyy; null for none
Throws
IllegalArgumentException- for a format code no file can hold: more than four sections, or a quote, bracket or escape left open
isHeaderRow
public boolean isHeaderRow()Returns
setHeaderRow
public void setHeaderRow(boolean headerRow)Parameters
headerRowboolean- true to take the column names from the first row
setEditable
public void setEditable(boolean editable)Parameters
editableboolean- false for a grid that only shows the sheet
getRowCount
public int getRowCount()Returns
getColumnCount
public int getColumnCount()Returns
getColumnName
public String getColumnName(int i)Parameters
iint- the offset for the column name
Returns
isCellEditable
public boolean isCellEditable(int row, int column)Parameters
rowint- the cell row
columnint- the cell column
Returns
getValueAt
public Object getValueAt(int row, int column)Parameters
rowint- the cell row
columnint- the cell column
Returns
setValueAt
public void setValueAt(int row, int column, Object o)String is read as if it had been typed into a spreadsheet, see
the class description; anything else is stored as it is.Parameters
rowint- the cell row
columnint- the cell column
oObject- the value of the cell at the given location
getFormulaAt
public String getFormulaAt(int row, int column)Parameters
rowint- the row of the model
columnint- the column
Returns
=, or nulladdDataChangeListener
public void addDataChangeListener(DataChangedListener d)Adds a listener that is told when a cell changes: the cell that was edited, and every formula that reads it, directly or through other formulas.
A formula is calculated when its value is asked for, not when what it reads changes, so the event says that the cell is out of date. A cell that is out of date already, because nobody read it since the last event, is not announced a second time.
Parameters
dDataChangedListener- the listener
removeDataChangeListener
public void removeDataChangeListener(DataChangedListener d)Parameters
dDataChangedListener- the listener to remove
addRow
public void addRow(Object... values)Parameters
valuesObject...- the values of the row’s cells from the first column on; each is stored
as
setCellValue(int, int, Object)stores it
insertRows
public void insertRows(int row, int count)Parameters
rowint- the row of the sheet the first new row becomes
countint- how many rows to insert
Throws
IllegalArgumentException- for a row outside the sheet or a negative count
removeRows
public void removeRows(int row, int count)#REF!; a range
that included removed rows shrinks.Parameters
rowint- the first row of the sheet to remove
countint- how many rows to remove
Throws
IllegalArgumentException- for a row outside the sheet or a negative count
insertColumns
public void insertColumns(int column, int count)Parameters
columnint- the column the first new column becomes
countint- how many columns to insert
Throws
IllegalArgumentException- for a column outside the sheet or a negative count
removeColumns
public void removeColumns(int column, int count)#REF!.Parameters
columnint- the first column to remove
countint- how many columns to remove
Throws
IllegalArgumentException- for a column outside the sheet or a negative count
createColumns
public GridColumn[] createColumns()Describes the columns of the sheet for a DataGrid: the kind
of each – number, date, logical, text – from the values it holds, a formatter that
shows values in the number format most of the column’s cells have, and the width the
file gave the column.
The columns describe the sheet as it is now. Call this again after loading other data into it.
Returns
getColumnWidth
public double getColumnWidth(int column)Parameters
columnint- the column
Returns
setColumnWidth
public void setColumnWidth(int column, double characters)Parameters
columnint- the column
charactersdouble- the width in characters of the default font; 0 for the default width
Throws
IllegalArgumentException- for a width that is negative, not a number or infinite, which no file can hold, or a column outside the sheet
getRowOutlineLevel
public int getRowOutlineLevel(int row)Parameters
rowint- the row of the sheet
Returns
setRowOutlineLevel
public void setRowOutlineLevel(int row, int level)Parameters
rowint- the row of the sheet
levelint- the level, 0 to 7
Throws
IllegalArgumentException- for a row outside the sheet, or a level outside 0 to 7, the levels the file formats hold
getFrozenRows
public int getFrozenRows()Returns
getFrozenColumns
public int getFrozenColumns()Returns
setFrozenPanes
public void setFrozenPanes(int rows, int columns)Parameters
rowsint- the count of rows at the top
columnsint- the count of columns at the left
Throws
IllegalArgumentException- for a negative count, or one that leaves no row or column of the sheet to scroll
addMergedRegion
public void addMergedRegion(int firstRow, int firstColumn, int lastRow, int lastColumn)Parameters
firstRowint- the top row
firstColumnint- the left column
lastRowint- the bottom row
lastColumnint- the right column
Throws
IllegalArgumentException- for a rectangle outside the sheet or over a merge the sheet has
getMergedRegionCount
public int getMergedRegionCount()Returns
getMergedRegion
public int[] getMergedRegion(int index)Parameters
indexint- which one, from 0