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

You can click cells in resume.xlsx to know how to write an external reference formula.

<?xml version="1.0" encoding="UTF-8"?>
<zk>
	<div height="100%" apply="demo.reference_to_another_workbook.BookReferenceController">
		<groupbox height="250px">
			<caption><label sclass="subtitle">profile.xlsx</label></caption>
			<spreadsheet id="profileSpreadsheet"
				width="100%" height="100%" maxVisibleRows="2" maxVisibleColumns="7"
				showFormulabar="true" />
		</groupbox>
		<groupbox vflex="2">
			<caption><label sclass="subtitle">resume.xlsx</label></caption>
			<spreadsheet id="resumeSpreadsheet"
				width="100%" height="100%" maxVisibleRows="12" maxVisibleColumns="7"
				showFormulabar="true"/>
		</groupbox>
	</div>
</zk>

package demo.reference_to_another_workbook;

import java.io.File;

import org.zkoss.zk.ui.Component;
import org.zkoss.zk.ui.WebApps;
import org.zkoss.zk.ui.select.SelectorComposer;
import org.zkoss.zk.ui.select.annotation.Wire;
import io.keikai.api.BookSeriesBuilder;
import io.keikai.api.Importer;
import io.keikai.api.Importers;
import io.keikai.api.model.Book;
import io.keikai.ui.Spreadsheet;

public class BookReferenceController extends SelectorComposer<Component> {

    @Wire
    private Spreadsheet profileSpreadsheet, resumeSpreadsheet;
    
    public void doAfterCompose(Component comp) throws Exception {
        super.doAfterCompose(comp);
         
        Importer importer = Importers.getImporter();
        Book resumeBook = importer.imports(getFile("/WEB-INF/books/resume.xlsx"),"resume.xlsx");
        Book profileBook = importer.imports(getFile("/WEB-INF/books/profile.xlsx"),"profile.xlsx");
        profileSpreadsheet.setBook(profileBook);
        resumeSpreadsheet.setBook(resumeBook);
         
        BookSeriesBuilder.getInstance().buildBookSeries(new Book[]{ resumeBook, profileBook });
    }
 
    private File getFile(String path){
        return new File(WebApps.getCurrent().getRealPath(path));
    }
}