External References to a Source Workbook"
m (correct highlight (via JWB)) |
|||
(5 intermediate revisions by 2 users not shown) | |||
Line 1: | Line 1: | ||
{{ZKSpreadsheetEssentialsPageHeader}} | {{ZKSpreadsheetEssentialsPageHeader}} | ||
+ | |||
+ | |||
+ | {{Deprecated|url=http://books.zkoss.org/wiki/ZK_Spreadsheet_Essentials}} | ||
+ | |||
__TOC__ | __TOC__ | ||
Line 10: | Line 14: | ||
===Mind the Book Name=== | ===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: < | + | 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: <code>=SUM([income.xlsx]Year2010!A1:A10)</code>. Mainly, simply prefix a normal reference with the source workbook name, <code>income.xlsx</code>, in bracket to specify which source workbook to refer. Basically, you can define any formula with such expressions as you like. |
===The Source Workbook=== | ===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. | + | 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. |
− | < | + | <code>''srcbook.xlsx''</code> |
[[image:srcbook.png]] | [[image:srcbook.png]] | ||
===The Destination Workbook=== | ===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 <code>=[srcbook.xlsx]Sheet1!B1+100</code>). | |
− | < | + | <code>''dstbook.xlsx''</code> |
[[image:dstbook.png]] | [[image:dstbook.png]] | ||
− | ===Associate Destination Books | + | ===Associate Destination Books and Source Books Together=== |
[[ZK Spreadsheet Essentials/Working with ZK Spreadsheet/Display the Excel Book File | Import]] the two books and then associate them in a <javadoc type="interface" directory="zss">org.zkoss.zss.model.BookSeries</javadoc><ref>{{ZSS EE}}</ref>. And you are done! As simple as you can imaging... | [[ZK Spreadsheet Essentials/Working with ZK Spreadsheet/Display the Excel Book File | Import]] the two books and then associate them in a <javadoc type="interface" directory="zss">org.zkoss.zss.model.BookSeries</javadoc><ref>{{ZSS EE}}</ref>. And you are done! As simple as you can imaging... | ||
− | <source lang="java" start="39" | + | <source lang="java" start="39" highlight="55,56"> |
... | ... | ||
public void doAfterCompose(Component comp) throws Exception { | public void doAfterCompose(Component comp) throws Exception { | ||
Line 49: | Line 53: | ||
bookSeries = new BookSeriesImpl(books); | bookSeries = new BookSeriesImpl(books); | ||
− | //associate either book to their UI spreadsheet | + | //associate either book to their corresponding UI spreadsheet components |
src.setBook(srcbook); | src.setBook(srcbook); | ||
dst.setBook(dstbook); | dst.setBook(dstbook); | ||
Line 75: | Line 79: | ||
===Result=== | ===Result=== | ||
− | < | + | <code>''externbook.zul''</code> |
[[Image:externbook-result.png]] | [[Image:externbook-result.png]] |
Latest revision as of 12:56, 19 January 2022
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
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.