External References to a Source Workbook"
Line 14: | Line 14: | ||
===The Source Workbook=== | ===The Source Workbook=== | ||
Construct an Excel source workbook with proper data in cells and save. | Construct an Excel source workbook with proper data in cells and save. | ||
+ | |||
+ | <tt>''srcbook.xlsx''</tt> | ||
[[image:srcbook.png]] | [[image:srcbook.png]] |
Revision as of 09:08, 24 February 2011
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
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.
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
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.