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

public final class Workbook

  1. Object
  2. Workbook

A spreadsheet: sheets of cells that hold numbers, text, dates, logical values and formulas, read from and written to Excel (.xlsx), OpenDocument (.ods), CSV and TSV files.

Every Sheet is a TableModel, so a sheet, read from a file with read or built in code as here, is shown by handing it to a grid:

Workbook book = new Workbook();
// com.codename1.ui has a Sheet too, hence the full name
com.codename1.spreadsheet.Sheet sheet = book.addSheet("Prices");
sheet.setCellValue("A1", "Item");
sheet.setCellValue("B1", "Price");
sheet.setCellValue("A2", "Tea");
sheet.setCellValue("B2", Double.valueOf(3.5));
sheet.setCellFormula("B3", "=SUM(B2:B2)");
sheet.setHeaderRow(true);
DataGrid grid = new DataGrid(sheet);
grid.setColumns(sheet.createColumns());
Form form = new Form("Prices", new BorderLayout());
form.add(BorderLayout.CENTER, grid);
form.show();

and fromGrid turns what a grid shows back into a workbook, which write saves as a file:

DataGrid grid = new DataGrid(new DefaultTableModel(new String[] {"Item", "Price"},
        new Object[][] {{"Tea", Double.valueOf(3.5)}, {"Cake", Double.valueOf(4)}}, false));
Workbook book = Workbook.fromGrid(grid);
Log.p(book.getSheet(0).getCellText(1, 1));

Formulas

A cell whose content starts with = is a formula in Excel’s syntax. Formulas are calculated here, on the device: changing a cell recalculates every cell that depends on it, and the sheet tells its listeners which cells that was. A formula read from a file keeps the value the file stored for it until something it depends on changes.

A function this implementation does not have evaluates to #NAME?; the formula itself is kept and written back as it was read. getSupportedFunctions() lists the functions there are.

Threads

A workbook is not thread safe. Use it from one thread at a time; one that a grid shows belongs to the event dispatch thread.

Nested types

enum Workbook.FormatA file format a workbook is read from or written to.

Constructors

public Workbook()Creates a workbook with no sheets.

Methods

public static Workbook read(InputStream in, Workbook.Format format) throws IOExceptionReads a workbook.
public static long getCellLimit()The most cells read fills before it gives up on a file with an IOException, one million unless it is changed.
public static void setCellLimit(long limit)Changes the most cells read fills, for an application that reads larger files and runs where there is memory for them.
public void write(OutputStream out, Workbook.Format format) throws IOExceptionWrites the workbook.
public static Workbook fromGrid(DataGrid grid)Creates a workbook with one sheet that holds what a grid shows: its rows in the order they are sorted and filtered to, its visible columns in the order they are arranged in, and the column titles as a header row.
public static Workbook fromModel(TableModel model)Creates a workbook with one sheet that holds the rows and columns of a model, with the column names as a header row.
public static String[] getSupportedFunctions()The names of the functions formulas can use, in alphabetical order.
public Sheet addSheet(String name)Adds an empty sheet after the others.
public void removeSheet(int index)Removes a sheet.
public int getSheetCount()The number of sheets.
public Sheet getSheet(int index)The sheet at a position.
public Sheet getSheet(String name)The sheet with a name, ignoring case.
public boolean isExcelLimitsEnforced()Whether writing XLSX refuses what Excel cannot open as it is.
public void setExcelLimitsEnforced(boolean enforced)Sets whether writing XLSX refuses what Excel cannot open as it is: a sheet name over 31 characters, a formula or what a defined name refers to over 8,192 (counted as the file holds it, with the prefixes newer functions take) or a text over 32,767.
public boolean isDate1904()Whether day numbers count from 1904, as files from old Macintosh versions of Excel do, and not from 1900.
public void setDate1904(boolean date1904)Sets the date system.
public void setDefinedName(String name, String formula)Defines a name formulas can use in place of a reference or a constant, as in =SUM(Sales) or =Price*TaxRate.
public String getDefinedName(String name)What a defined name stands for.
public String[] getDefinedNames()The defined names, in the order they were defined.
public void recalculate()Calculates every formula again, whether or not anything it reads has changed.

Inherited methods

Constructor details

Workbook

public Workbook()
Creates a workbook with no sheets.

Method details

read

public static Workbook read(InputStream in, Workbook.Format format) throws IOException
Reads a workbook.

Parameters

in InputStream
the file; read to its end and not closed
format Workbook.Format
the format of the file, see Format.detect(String, byte[])

Returns

the workbook

Throws

IOException
when the stream fails or the file is not of that format

getCellLimit

public static long getCellLimit()

The most cells read fills before it gives up on a file with an IOException, one million unless it is changed. A million numbers take about 75 MB.

A file is not trusted with memory: a few hundred bytes of a compressed file can name a cell in the last column of every row, or say that one row repeats a million times. What is counted is the room the rows take, so a single cell far to the right counts as every cell to its left, and the rows above the last one count too. A formula counts as eight cells and more for a long one, and the shared strings, the column widths and the spaces a file asks for are counted with the cells.

Returns

the limit

setCellLimit

public static void setCellLimit(long limit)
Changes the most cells read fills, for an application that reads larger files and runs where there is memory for them.

Parameters

limit long
the most cells

write

public void write(OutputStream out, Workbook.Format format) throws IOException

Writes the workbook. Formulas are written with the values they have now, so that a program that does not calculate still shows them.

CSV and TSV hold one sheet, the first, as the text each cell shows; formulas are written as their values.

Parameters

out OutputStream
where to write; flushed and not closed
format Workbook.Format
the format to write

Throws

IOException
when the stream fails, or when writing XLSX with the limits of setExcelLimitsEnforced(boolean) on and the workbook is past one of them

fromGrid

public static Workbook fromGrid(DataGrid grid)

Creates a workbook with one sheet that holds what a grid shows: its rows in the order they are sorted and filtered to, its visible columns in the order they are arranged in, and the column titles as a header row.

Values keep their types – a number is a number in the file, a date a date – and are never the text the grid formats them to. The depth of a row of a tree grid becomes its outline level, up to 7, the deepest level the file formats hold, and the grid’s frozen columns stay frozen.

A grouped grid is copied as it is shown: each group row becomes a row holding the group’s key (in the grouped column, when that column is shown) and its aggregates as plain values, and the rows of a collapsed group are not in the file. A total over a column of the result counts those aggregates as well; copy the model with fromModel for the data alone.

Parameters

grid DataGrid
the grid to copy

Returns

a new workbook that shares nothing with the grid

fromModel

public static Workbook fromModel(TableModel model)
Creates a workbook with one sheet that holds the rows and columns of a model, with the column names as a header row.

Parameters

model TableModel
the model to copy

Returns

a new workbook that shares nothing with the model

getSupportedFunctions

public static String[] getSupportedFunctions()
The names of the functions formulas can use, in alphabetical order.

Returns

a new array

addSheet

public Sheet addSheet(String name)
Adds an empty sheet after the others.

Parameters

name String
the name of the sheet, which formulas on other sheets use to refer to it. Excel takes names of up to 31 characters, and writing a workbook with a longer one as XLSX throws IOException unless setExcelLimitsEnforced(boolean) turns that off

Returns

the new sheet

Throws

IllegalArgumentException
when the workbook has a sheet of that name

removeSheet

public void removeSheet(int index)
Removes a sheet. Formulas that refer to it evaluate to #REF! from then on, except a reference across a span of sheets that it ends, as Jan:Mar!B2: the span shrinks to the sheets that remain, as in Excel.

Parameters

index int
the position of the sheet

getSheetCount

public int getSheetCount()
The number of sheets.

Returns

the count

getSheet

public Sheet getSheet(int index)
The sheet at a position.

Parameters

index int
the position, from 0

Returns

the sheet

getSheet

public Sheet getSheet(String name)
The sheet with a name, ignoring case.

Parameters

name String
the name

Returns

the sheet, or null when there is none of that name

isExcelLimitsEnforced

public boolean isExcelLimitsEnforced()
Whether writing XLSX refuses what Excel cannot open as it is.

Returns

true, the default, when a write past Excel’s limits throws

setExcelLimitsEnforced

public void setExcelLimitsEnforced(boolean enforced)
Sets whether writing XLSX refuses what Excel cannot open as it is: a sheet name over 31 characters, a formula or what a defined name refers to over 8,192 (counted as the file holds it, with the prefixes newer functions take) or a text over 32,767. By default such a write throws IOException naming the sheet or the cell, because Excel opens such a file only by repairing it, and drops or cuts what is over the limit. With false the file is written as it is, which suits a file meant for a spreadsheet that has no such limits.

Parameters

enforced boolean
false to write past the limits

isDate1904

public boolean isDate1904()
Whether day numbers count from 1904, as files from old Macintosh versions of Excel do, and not from 1900.

Returns

true for the 1904 date system

setDate1904

public void setDate1904(boolean date1904)
Sets the date system. Changing it moves every date in the workbook by four years.

Parameters

date1904 boolean
true to count days from 1904

setDefinedName

public void setDefinedName(String name, String formula)
Defines a name formulas can use in place of a reference or a constant, as in =SUM(Sales) or =Price*TaxRate.

Parameters

name String
the name; replaces a definition of the same name, ignoring case
formula String
what the name stands for, such as Sheet1!$A$1:$A$10 or 0.17; null removes the name. Every reference names its sheet. A reference without $ is relative, as in Excel: written as if the name were used in A1, and moved to the cell that uses it, so Sheet1!$A2 in a formula in row 5 means Sheet1!$A6, and the last row stands for the row above.

Throws

IllegalArgumentException
when the formula cannot be read, or has a reference that does not name its sheet

getDefinedName

public String getDefinedName(String name)
What a defined name stands for.

Parameters

name String
the name, ignoring case

Returns

the formula without a leading =, or null when the name is not defined

getDefinedNames

public String[] getDefinedNames()
The defined names, in the order they were defined.

Returns

a new array

recalculate

public void recalculate()
Calculates every formula again, whether or not anything it reads has changed. A formula that could not be read keeps the value its file stored.