Spreadsheet Data Model

Book Model Overview

When Spreadsheet loads an Excel file, the file is converted to Spreadsheet's data model (book model) stored in memory. The root of the data model is a book (Book) and a book contains one or more sheets (Sheet) which may contain many cells (CellData), styles (CellStyle, Color), fonts (Font), charts (Chart), and pictures (Picture).

You can directly access model objects like Book or Sheet. However, you should modify data on cells (or rows and columns)via Range interface, then Spreadsheet will handle subsequent synchronization stuff for you, e.g. notify other referenced cells. Range may represent a cell, a row, a column, or a selection of cells containing one or more contiguous blocks of cells, or a 3-D reference. Because of underlying implementation is complicated, you only can obtain a Range object through a facade class named Ranges.

In this section, we will introduce some commonly-used API with examples. For complete information, you can browse Javadoc under org.zkoss.zss.api.* and org.zkoss.zss.api.model.*. To understand example codes, we assume you have known what is composer and how it work with components. If you don't, please read ZK Developer's Reference/MVC/Controller/Composer first.

Load A Book Model

In most cases, we create a book model by loading an Excel file instead of creating it directly. Specifying an Excel file's path in Spreadsheet component's attribute is the simplest way, and Spreadsheet will import the file and construct a book model object. You can also use Importer to construct a Book object by your own and provide it to one or more Spreadsheet components by setBook(). After Spreadsheet loads a book model, we can get it by Spreadsheet.getBook().

By Spreadsheet

ZK Spreadsheet component's Spreadsheet.setSrc(String) can be called to display an Excel file programmatically. Similar to src attribute, this method accepts relative file path.

public class MyComposer extends SelectorComposer<Component> {

	@Wire("spreadsheet")
	Spreadsheet spreadsheet;
	
	@Override
	public void doAfterCompose(Component comp) throws Exception {
		super.doAfterCompose(comp);
		//initialize stuff here
		spreadsheet.setSrc("/WEB-INF/books/startzss.xlsx");

	}
}

• Line 3: The annotation @Wire injects matched component object's reference to annotated variable according to selector syntax. For details, please refer to ZK Developer's Reference/MVC/Controller/Wire Components.

By Importer

In case you want to display user-uploaded Excel book file or display the same Excel book file shared by multiple users, importer interface along with ZK Spreadsheet Spreadsheet.setBook(Book) can be used. Normally one would obtain Book instance by importing an Excel book file. Use Importer.imports(InputStream, String) to import Excel book file. It returns Book instance which can be passed to setBook(Book) to display imported Excel book file.

public class MyComposer extends SelectorComposer<Component> {

	@Wire("spreadsheet")
	Spreadsheet spreadsheet;
	
	@Override
	public void doAfterCompose(Component comp) throws Exception {
		super.doAfterCompose(comp);  //wire variables and event listeners
		//access components after calling super.doAfterCompose()
		loadBook();
	}
	
	public void loadBook() throws IOException{
		Importer importer = Importers.getImporter();
		Book book = importer.imports(getFile(), "sample");
		spreadsheet.setBook(book);
	}
	
	private InputStream getFile(){
		//get a file 
	}
}

This is especially powerful in multi-user collaborative scenario. For example once Excel book file is imported using Importer interface and put into application scope, it can be applied to multiple ZK Spreadsheet components each used by different user. ZK Spreadsheet will propagate any changes made to this Book instance to whichever ZK Spreadsheet component it is applied to and therefore facilitate multiple users to collaborate the same Excel book file.

Access Sheets

The Book object is the root of Spreadsheet's data model, and we can retrieve sheets from it, e.g. by index getSheetAt(), or by name getSheet(). One book object might contains one or more sheets, and we can know how many sheets it have by getNumberOfSheets(). However, Spreadsheet only displays one sheet at one time and the currently-displayed sheet is the selected sheet. We can get selected sheet via Spreadsheet.getSelectedSheet() or set it via Spreadsheet.setSelectedSheet().

The Sheet allows us to get a sheet's status such as protection (isProtected()), auto filter (isAutoFilterEnabled()), hidden and freeze rows or columns (getRowFreeze()), and properties such as name (getSheetName()), row's width, column's height, charts, and pictures which the sheet contains.

Switch Sheets Example

Now, we present basic usage with a custom sheet switching example. Users can use the combobox with sheet name to switch the current selected sheet of the Spreadsheet.

setSheet.zul

<div height="100%" width="100%" apply="org.zkoss.zss.essential.BookSheetComposer">
	<combobox id="sheetBox"/>
	<spreadsheet id="spreadsheet" src="/WEB-INF/books/startzss.xlsx"
		maxrows="200" maxcolumns="40"
		width="100%" height="450px"/>
</div>

Then we listen the Combobox's onSelect event to change current selected sheet.

public class BookSheetComposer extends SelectorComposer<Component>{
	
	@Wire
	Combobox sheetBox;
	@Wire
	Spreadsheet spreadsheet;
	//override
	@Override
	public void doAfterCompose(Component comp) throws Exception {
		super.doAfterCompose(comp);
		
		List<String> sheetNames = new ArrayList<String>();
		int sheetSize = spreadsheet.getBook().getNumberOfSheets();
		for (int i = 0; i < sheetSize; i++){
			sheetNames.add(spreadsheet.getBook().getSheetAt(i).getSheetName());
		}
		
		sheetBox.setModel(new ListModelList<String>(sheetNames));
	}
	
	@Listen("onSelect = #sheetBox")
	public void selectSheet(Event event) {
		spreadsheet.setSelectedSheet(sheetBox.getText());
	}
}

• Line 13~16: Get each sheet's name from Spreadsheet's book model.
• Line 18: Set name list to the Combobox.
• Line 21: The annotation @Listen makes selectSheet() listen onSelect event of the Combobox whose id is sheetBox. That means when a user selects a sheet in the Combobox, the method selectSheet() will be invoked.(For complete syntax, please refer to ZK Developer's Reference/MVC/Controller/Wire Event Listeners)
• Line 23: Change Spreadsheet's selected sheet when users select a sheet.

Access Cells

When you want to change some data in book or sheet, you can directly access their corresponding model objects, Book or Sheet. However, there is no such a cell object for you to access. Because one cell might be referenced by other cells, accessing it may need to notify other cells. This issue can be more complicated if you select multiple cells.

Hence, in order to encapsulate these complicated detail, Spreadsheet provides 2 classes, Ranges and Range (Notice that there is one more "s" in the first one's class name.). The Ranges is a facade class that has 2 kinds of method. One is used to select a range of cells and it will return a Range object that may represents can represent a cell, a row, a column, or a selection of cells containing one or more contiguous blocks of cells, or a 3-D blocks of cells.

Getting Range object example

//select single cell
Range range1 = Ranges.range(spreadsheet.getSelectedSheet(), row, column);

//select an area by cell reference
Range range2 = Ranges.range(spreadsheet.getSelectedSheet(), "A1:D4");
//select an area by 2 pairs of column and row index
Range range3 = Ranges.range(spreadsheet.getSelectedSheet()
							, topRow, leftColumn, bottomRow, rightColumn);

Another is to get cell reference from row and column index.

Getting cell reference example

// Gets single cell reference, e.g. A1
String ref1 = Ranges.getCellReference(row, column);
// Gets single  cell reference with sheet name, e.g. Sheet1!A1
String ref2 = Ranges.getCellReference(spreadsheet.getSelectedSheet(), row, column);

// Gets a range of cell reference, e.g. A1:B2
String ref3 = Ranges.getAreaReference(topRow, leftColumn, bottomRow, rightColumn);
// Gets a range of cell reference with sheet name, e.g. Sheet1!A1:B2
String ref4 = Ranges.getAreaReference(spreadsheet.getSelectedSheet(),
							 topRow, leftColumn, bottomRow, rightColumn);

// Gets column reference, e.g. A or B
String ref5 = Ranges.getColumnReference(column);
// Gets a row reference, e.g. 1 or 12
String ref6 = Ranges.getRowReference(row);

Read Data from Cells

After you get a Range object from Ranges, you can get cell related data from it such as:

//get text from it
range.getCellEditText();
range.getCellFormatText();
range.getCellValue();

//gets the leftmost column index of this range
range.getColumn();
//gets the topmost row index of this range
range.getRow();

//change range to select entire row or column
range.toRowRange();
range.toColumnRange();

There are still many methods that we don't mention here. They will be introduced in later sections.

Set Data to Cells

Because of referencing relationship among cells mentioned at the beginning of this section, if you want change data in cells, we strongly recommend to use CellOperationUtil and SheetOperationUtil. You can use them without knowing more detail about underlying implementation, and they will handle those details for you such as synchronization or checking. Only when you need an operation that these utility classes cannot support, you just consider to implement by your own with Range.