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 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
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
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
View complete source of ZUML externbook.zul
View complete source of composer ExternBookComposer.java
Version History
Version | Date | Content |
---|---|---|
All source code listed in this book is at Github.