Spreadsheet Data Model

Spreadsheet Book Model

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 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. 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 An Excel File

setSrc()

Spreadsheet also provides API to load an Excel file. 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.

setBook()

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;
	
	@Listen("onUpload = button")
	public void showBook(UploadEvent event) throws IOException{
		Media media = event.getMedia();
		if (media.isBinary()) {
			Importer importer = Importers.getImporter();
			InputStream inputStream = WebApps.getCurrent().getResourceAsStream("/WEB-INF/books/startzss.xlsx");
			Book book = importer.imports(inputStream, "startzss");
			spreadsheet.setBook(book);
		}
	}
}

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 Books and Sheets

The Book is the root of Spreadsheet's data model, and we can retrieve sheets from it. The Sheet allows us to get a sheet's status such as protection, auto filter, hidden and freeze rows (columns), and properties such as name, row's width, column's height, charts, and pictures which the sheet contains.

Switch Sheets

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());
		}
		
		BindingListModelList model = new BindingListModelList(sheetNames, true);
		sheetBox.setModel(model);
	}
	
	@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~19: Set name list to the Combobox.
• Line 22: 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 24: Change Spreadsheet's selected sheet when users select a sheet.