External References to a Source Workbook"

From Documentation
Line 21: Line 21:
 
===The Destination Workbook===
 
===The Destination Workbook===
 
In the same Excel application(without closing the previous source workbook), construct another destination workbook and create references to the source book.
 
In the same Excel application(without closing the previous source workbook), construct another destination workbook and create references to the source book.
 +
 +
<tt>''dstbook.xlsx''</tt>
  
 
[[image:dstbook.png]]
 
[[image:dstbook.png]]

Revision as of 09:08, 24 February 2011


External References to a Source Workbook



Available in ZK Spreadsheet EE only

ZK Spreadsheet can handle multiple workbooks that include external references. The reference dependency among workbooks are maintained automatically.

Purpose

Associate workbooks that has external references from a destination workbook(s) to a source workbooks(s).

Mind the Book Name

Workbook name is important because the name is used to access the source book. For example, in a cell of a destination workbook, you can specify an external references to a source workbook like following: =SUM([income.xlsx]Year2010!A1:A10). Mainly, simply prefix a normal reference with the source workbook name, income.xlsx, in bracket to specify which source workbook to refer. Basically, you can define any formula with such expressions as you like.

The Source Workbook

Construct an Excel source workbook with proper data in cells and save.

srcbook.xlsx

Srcbook.png

The Destination Workbook

In the same Excel application(without closing the previous source workbook), construct another destination workbook and create references to the source book.

dstbook.xlsx

Dstbook.png

Associate Destination Books with Source Books

Import the two books and then associate them in a BookSeries[1]. And you are done! As simple as you can imaging...

...
	public void doAfterCompose(Component comp) throws Exception {
		super.doAfterCompose(comp);
		
		//prepare excel importer
		final Importer importer = Importers.getImporter("excel");
		
		//prepare source book
		final InputStream is1 = Sessions.getCurrent().getWebApp().getResourceAsStream("/WEB-INF/excel/extern/srcbook.xlsx"); 
		final Book srcbook = importer.imports(is1, "srcbook.xlsx");
		
		//prepare destination book
		final InputStream is2 = Sessions.getCurrent().getWebApp().getResourceAsStream("/WEB-INF/excel/extern/dstbook.xlsx"); 
		final Book dstbook = importer.imports(is2, "dstbook.xlsx");
		
		//add both books into a BookSeries
		final Book[] books = new Book[] {srcbook, dstbook}; 
		bookSeries = new BookSeriesImpl(books);
		
		//associate either book to their UI spreadsheet component
		src.setBook(srcbook);
		dst.setBook(dstbook);
	}
}

ZUML

The zuml page here layouts the two workbook in a page. Try to change value of the cell on the top source workbook, and value of the cell on the bottom destination workbook will change accordingly.

<?xml version="1.0" encoding="UTF-8"?>
<?page id="p1" title="ZK Spreadsheet External Reference to a Source Workbook Example"?>
<window width="100%" vflex="1" apply="org.zkoss.zssessentials.extern.ExternBookComposer">
	<vlayout vflex="1">
	Source Book:
	<spreadsheet id="src" width="100%" vflex="1"></spreadsheet>
	Destination Book:
	<spreadsheet id="dst" width="100%" vflex="1"></spreadsheet>
	</vlayout>
</window>

Result

Externbook-result.png

View complete source of ZUML externbook.zul

View complete source of composer ExternBookComposer.java

Version History

Last Update : 2011/02/24


Version Date Content
     


All source code listed in this book is at Github.


Last Update : 2011/02/24

Copyright © Potix Corporation. This article is licensed under GNU Free Documentation License.