Client API. The Codename One framework your app is built on: this runs on the device, not in a backend.

public final class Sheet

  1. Object
  2. AbstractTableModel
  3. 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()
The name of the sheet, which formulas on other sheets refer to it by.

Returns

the name

getWorkbook

public Workbook getWorkbook()
The workbook the sheet is part of.

Returns

the workbook

getCellValue

public Object getCellValue(int row, int column)
The value of a cell.

Parameters

row int
the row of the sheet, 0 for the first
column int
the column of the sheet, 0 for A

Returns

a Double, String, Boolean, CellError or Date, or null for an empty cell

getCellValue

public Object getCellValue(String address)
The value of a cell named the way a formula names it.

Parameters

address String
an address such as B7

Returns

the value, see getCellValue(int, int)

setCellValue

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.

Parameters

row int
the row of the sheet
column int
the column of the sheet
value Object
a Number, String, Boolean, Date or CellError; null empties the cell. A String is stored as text even when it looks like a number or a formula.

setCellValue

public void setCellValue(String address, Object value)
Stores a value in a cell named the way a formula names it.

Parameters

address String
an address such as B7
value Object
the value, see setCellValue(int, int, Object)

getCellFormula

public String getCellFormula(int row, int column)
The formula of a cell.

Parameters

row int
the row of the sheet
column int
the column of the sheet

Returns

the formula with its leading =, or null when the cell holds no formula

setCellFormula

public void setCellFormula(int row, int column, String formula)
Puts a formula into a cell and recalculates what depends on it.

Parameters

row int
the row of the sheet
column int
the column of the sheet
formula String
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)
Puts a formula into a cell named the way a formula names it.

Parameters

address String
an address such as B7
formula String
the formula, see setCellFormula(int, int, String)

getCellText

public String getCellText(int row, int column)
The text a cell shows: its value in its number format.

Parameters

row int
the row of the sheet
column int
the column of the sheet

Returns

the text, empty for an empty cell

getCellFormat

public String getCellFormat(int row, int column)
The number format of a cell.

Parameters

row int
the row of the sheet
column int
the column of the sheet

Returns

a format code such as #,##0.00, General when the cell has none

setCellFormat

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.

Parameters

row int
the row of the sheet
column int
the column of the sheet
format String
a format code in Excel’s syntax, such as 0.0% or d 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()
Whether the first row of the sheet holds the names of the columns. When it does, the table model’s rows start under it.

Returns

true when the first row is a header

setHeaderRow

public void setHeaderRow(boolean headerRow)
Sets whether the first row of the sheet holds the names of the columns.

Parameters

headerRow boolean
true to take the column names from the first row

setEditable

public void setEditable(boolean editable)
Sets whether the table model reports its cells as editable.

Parameters

editable boolean
false for a grid that only shows the sheet

getRowCount

public int getRowCount()
Returns the number of rows in the table

Returns

the number of rows in the table

getColumnCount

public int getColumnCount()
Returns the number of columns in the table

Returns

the number of columns in the table

getColumnName

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.

Parameters

i int
the offset for the column name

Returns

name to display at the top of the table

isCellEditable

public boolean isCellEditable(int row, int column)
Returns true if the cell at the given location is an editable cell

Parameters

row int
the cell row
column int
the cell column

Returns

true if the cell at the given location is an editable cell

getValueAt

public Object getValueAt(int row, int column)
Returns the value of the cell at the given location

Parameters

row int
the cell row
column int
the cell column

Returns

the value of the cell at the given location

setValueAt

public void setValueAt(int row, int column, Object o)
Stores a value. A String is read as if it had been typed into a spreadsheet, see the class description; anything else is stored as it is.

Parameters

row int
the cell row
column int
the cell column
o Object
the value of the cell at the given location

getFormulaAt

public String getFormulaAt(int row, int column)
The formula of a cell of the table model.

Parameters

row int
the row of the model
column int
the column

Returns

the formula with its leading =, or null

addDataChangeListener

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

d DataChangedListener
the listener

removeDataChangeListener

public void removeDataChangeListener(DataChangedListener d)
Removes a listener to the data changed event

Parameters

d DataChangedListener
the listener to remove

addRow

public void addRow(Object... values)
Adds a row under the last one.

Parameters

values Object...
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)
Inserts empty rows. Formulas that refer to cells under them are rewritten to follow the cells, as a spreadsheet does.

Parameters

row int
the row of the sheet the first new row becomes
count int
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)
Removes rows. A formula that referred to a removed cell evaluates to #REF!; a range that included removed rows shrinks.

Parameters

row int
the first row of the sheet to remove
count int
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)
Inserts empty columns. Formulas follow the cells that move.

Parameters

column int
the column the first new column becomes
count int
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)
Removes columns. A formula that referred to a removed cell evaluates to #REF!.

Parameters

column int
the first column to remove
count int
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

one column per column of the sheet

getColumnWidth

public double getColumnWidth(int column)
The width of a column.

Parameters

column int
the column

Returns

the width in characters of the default font, the unit spreadsheet files use; 0 when the column has the default width

setColumnWidth

public void setColumnWidth(int column, double characters)
Sets the width of a column.

Parameters

column int
the column
characters double
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)
The outline level of a row: how deep it is nested in the groups a spreadsheet can collapse.

Parameters

row int
the row of the sheet

Returns

the level, 0 for a row in no group

setRowOutlineLevel

public void setRowOutlineLevel(int row, int level)
Sets the outline level of a row.

Parameters

row int
the row of the sheet
level int
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()
The number of rows at the top that stay in place while the rest scrolls.

Returns

the count of frozen rows

getFrozenColumns

public int getFrozenColumns()
The number of columns at the left that stay in place while the rest scrolls.

Returns

the count of frozen columns

setFrozenPanes

public void setFrozenPanes(int rows, int columns)
Sets the rows and columns that stay in place while the rest of the sheet scrolls.

Parameters

rows int
the count of rows at the top
columns int
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)
Merges a rectangle of cells into one, as the file formats record it. The value shown is the one of the top left cell.

Parameters

firstRow int
the top row
firstColumn int
the left column
lastRow int
the bottom row
lastColumn int
the right column

Throws

IllegalArgumentException
for a rectangle outside the sheet or over a merge the sheet has

getMergedRegionCount

public int getMergedRegionCount()
The number of merged regions.

Returns

the count

getMergedRegion

public int[] getMergedRegion(int index)
A merged region.

Parameters

index int
which one, from 0

Returns

a new array of the top row, left column, bottom row and right column