ZK Spreadsheet Essentials/Working with Spreadsheet/Advanced/Reference to another Workbook

Spreadsheet supports external reference: a cell in one spreadsheet can reference to a cell of another spreadsheet. This feature is useful when you want to apply a different view for data but don't want to change original book. It also can be used when you want to merge data from multiple books.

Before using this feature, you should build a book series for all book models including source book and target one. Then, use the syntax below to reference cells inside book series:

=[BOOK_NAME]SHEET_NAME!CELL_REFERENCE

For example:

=[sourceBook.xlsx]source!A2

Assume that we have a book with personal profile named "profile.xlsx", and we want to create a resume for it without modifying "profile.xlsx". Therefore, we make cells of book "resume.xlsx" reference to the book "profile.xlsx". The screenshot is below:

We use 2 spreadsheet loaded with "resume.xlsx" and "profile.xlsx" respectively. You can use importer to import Excel file as a book model.

bookSeries.zul

<zk>
	<window hflex="1" vflex="1"
		apply="org.zkoss.zss.essential.BookSeriesComposer">
			<vlayout hflex="1" vflex="1">
				<groupbox hflex="1"  vflex="3" mold="3d">
					<caption id="book1Caption" />
					<spreadsheet id="ss" hflex="1" vflex="1"
						showFormulabar="true" showContextMenu="true" showToolbar="true"
						showSheetbar="true" maxVisibleRows="100" maxVisibleColumns="40"
						src="/WEB-INF/books/resume.xlsx" />
				</groupbox>
				<groupbox hflex="1" vflex="2" mold="3d">
					<caption id="book2Caption" />
					<spreadsheet id="ss2" hflex="1" vflex="1"
						showFormulabar="true" showContextMenu="true" showToolbar="true"
						showSheetbar="true" maxVisibleRows="20" maxVisibleColumns="10" 
						src="/WEB-INF/books/profile.xlsx"/>
				</groupbox>
			</vlayout>
	</window>
</zk>

Use BookSeriesBuilder to build a book series with referencing and referenced books.

public class BookSeriesComposer extends SelectorComposer<Component> {

	
	@Wire
	Spreadsheet ss;
	@Wire
	Spreadsheet ss2;
	
	@Override
	public void doAfterCompose(Component comp) throws Exception {
		super.doAfterCompose(comp);
		
		Book book1 = ss.getBook();
		Book book2 = ss2.getBook();
		
		BookSeriesBuilder.getInstance().buildBookSeries(new Book[]{book1,book2});
		
		//...
	}
	
}

After completing above steps, you can use external cell reference in a book to reference another one.