Spreadsheet Data Model"

From Documentation
Line 1: Line 1:
{{ZKSpreadsheetEssentials3.xPageHeader}}
+
{{ZKSpreadsheetEssentials3PageHeader}}
  
  

Revision as of 09:47, 8 July 2013


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 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 A Book Model

In most cases, we create a book model by loading an Excel file instead of creating it directly. Giving a an Excel file's path to Spreadsheet component 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");

	}
}


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;
	
	@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. Before sharing a book model, remember you should set its share scope with setShareScope(). For concurrent access, you might also need to call getSync() to get the object to synchronize a book model.

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.

Zss-essentials-book-sheet.png


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.