Client API. The Codename One framework your app is built on: this runs on the device, not in a backend.
public final class Workbook
- Object
- 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.Format | A 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 IOException | Reads 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 IOException | Writes 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()Method details
read
public static Workbook read(InputStream in, Workbook.Format format)
throws IOExceptionParameters
inInputStream- the file; read to its end and not closed
formatWorkbook.Format- the format of the file, see
Format.detect(String, byte[])
Returns
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
setCellLimit
public static void setCellLimit(long limit)read fills, for an application that reads larger files and
runs where there is memory for them.Parameters
limitlong- the most cells
write
public void write(OutputStream out, Workbook.Format format)
throws IOExceptionWrites 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
outOutputStream- where to write; flushed and not closed
formatWorkbook.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
gridDataGrid- the grid to copy
Returns
fromModel
public static Workbook fromModel(TableModel model)Parameters
modelTableModel- the model to copy
Returns
getSupportedFunctions
public static String[] getSupportedFunctions()Returns
addSheet
public Sheet addSheet(String name)Parameters
nameString- 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
IOExceptionunlesssetExcelLimitsEnforced(boolean)turns that off
Returns
Throws
IllegalArgumentException- when the workbook has a sheet of that name
removeSheet
public void removeSheet(int index)#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
indexint- the position of the sheet
getSheetCount
public int getSheetCount()Returns
getSheet
public Sheet getSheet(int index)Parameters
indexint- the position, from 0
Returns
getSheet
public Sheet getSheet(String name)Parameters
nameString- the name
Returns
isExcelLimitsEnforced
public boolean isExcelLimitsEnforced()Returns
setExcelLimitsEnforced
public void setExcelLimitsEnforced(boolean enforced)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
enforcedboolean- false to write past the limits
isDate1904
public boolean isDate1904()Returns
setDate1904
public void setDate1904(boolean date1904)Parameters
date1904boolean- true to count days from 1904
setDefinedName
public void setDefinedName(String name, String formula)=SUM(Sales) or =Price*TaxRate.Parameters
nameString- the name; replaces a definition of the same name, ignoring case
formulaString- what the name stands for, such as
Sheet1!$A$1:$A$10or0.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, soSheet1!$A2in a formula in row 5 meansSheet1!$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)Parameters
nameString- the name, ignoring case
Returns
=, or null when the name is not definedgetDefinedNames
public String[] getDefinedNames()Returns
recalculate
public void recalculate()