External References to a Source Workbook

From Documentation


External References to a Source Workbook




Stop.png This article is out of date, please refer to http://books.zkoss.org/wiki/ZK_Spreadsheet_Essentials for more up to date information.


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 includes 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

Following, we show you a very simple example that the value of the destination workbook is the value of the source workbook plus one hundred. Let us construct an Excel source workbook with proper data in cells and save.

srcbook.xlsx

Srcbook.png

The Destination Workbook

Then in the same Excel application(without closing the previous source workbook), construct another destination workbook and create references to the source book (e.g. in cell B1, key in =[srcbook.xlsx]Sheet1!B1+100).

dstbook.xlsx

Dstbook.png

Associate Destination Books and Source Books Together

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 corresponding UI spreadsheet components
		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.zul

Externbook-result.png

View complete source of ZUML externbook.zul

View complete source of composer ExternBookComposer.java

Version History

Last Update : 2022/01/19


Version Date Content
     


All source code listed in this book is at Github.

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