There are 2 ways to save data from Keikai Spreadsheet into a database:

1. Export to an Excel file: It's the most common way. All information of a book model can be exported to an Excel file. You can store the file in a BLOB field of a table in a database.
2. Store cell data: Extract concerned cell data or formulas with Range getter method and insert them into corresponding columns of the database table. When displaying data from the database, you can publish data with Range setter methods into corresponding cells. A typical use case is to load a pre-defined Excel template and populate data from the db into the template for further editing or reporting.

This demo shows the 2nd method: using the Range API to save the cell data back to the database and publish a table's data to cells. The architecture is as follows: :

<zk xmlns="http://www.zkoss.org/2005/zul"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.zkoss.org/2005/zul http://keikai.io/2019/zul/zul.xsd">
    <spreadsheet id="spreadsheet" width="100%" height="100%" apply="demo.database.BudgetComposer"
                 maxVisibleRows="35" maxVisibleColumns="11" hiderowhead="true" hidecolumnhead="true"
                 src="/WEB-INF/books/budget.xlsx"/>
</zk>

package demo.database;

import demo.Util;
import io.keikai.api.*;
import io.keikai.ui.Spreadsheet;
import io.keikai.ui.event.*;
import org.zkoss.zk.ui.Component;
import org.zkoss.zk.ui.select.SelectorComposer;
import org.zkoss.zk.ui.select.annotation.*;

import java.util.List;

public class BudgetComposer extends SelectorComposer<Component> {
    @Wire
    protected Spreadsheet spreadsheet;
    protected ExpenseDao expenseDao = new ExpenseDao();
    //sheet name
    protected static final String SUMMARY = "summary";
    protected static final String NEW = "new";
    //named range
    protected  static final String QUANTITY = "QUANTITY";
    protected  static final String CATEGORY = "CATEGORY";

    protected static final SheetProtection ALLOW_SELECT = SheetProtection.Builder.create().withSelectLockedCellsAllowed(true).withAutoFilterAllowed(true).build();

    @Override
    public void doAfterCompose(Component comp) throws Exception {
        super.doAfterCompose(comp);
        loadExpenseToSheet();
        Ranges.range(spreadsheet.getSelectedSheet()).protectSheet(ALLOW_SELECT);
    }

    protected void loadExpenseToSheet() {
        List<Expense> expenseList = expenseDao.queryAll();
        fillExpenses(expenseList);
    }

    protected void fillExpenses(List<Expense> list) {
        Range quantity1stCell = Ranges.rangeByName(spreadsheet.getBook().getSheet(SUMMARY), QUANTITY);
        for (int i = 0; i < list.size(); i++) {
            Expense expense = list.get(i);
            Ranges.range(spreadsheet.getSelectedSheet(), quantity1stCell.getRow() + i, quantity1stCell.getColumn()).setCellValue(expense.getQuantity());
            Ranges.range(spreadsheet.getSelectedSheet(), quantity1stCell.getRow() + i, quantity1stCell.getColumn() + 1).setCellValue(expense.getSubtotal());
        }
    }

    @Listen(Events.ON_CELL_CLICK + "= #spreadsheet")
    public void onClick(CellMouseEvent event) {
        Range cell = Util.getClickedCell(event);
        if ("Add new".equals(cell.getCellValue())){
            spreadsheet.setSelectedSheet(NEW);
        }else if ("Done".equals(cell.getCellValue())){
            readExpense();
            spreadsheet.setSelectedSheet(SUMMARY);
            loadExpenseToSheet();
        }
    }

    /**
     * read input expense list in a specific range into the database
     */
    protected void readExpense() {
        Range category1stCell = Ranges.rangeByName(spreadsheet.getSelectedSheet(), CATEGORY);
        int startingRow = category1stCell.getRow();
        int categoryColumn = category1stCell.getColumn();
        for (int rowIndex = startingRow; rowIndex < startingRow + 4; rowIndex++) {
            Expense expense = readExpense(rowIndex, categoryColumn);
            if (!validate(expense)) {
                break; //don't read the next row
            }
            expenseDao.update(expense);
        }
        Ranges.range(spreadsheet.getSelectedSheet(), startingRow, 0, startingRow+3, 3).clearContents();
    }


    protected boolean validate(Expense expense) {
        return  expense.getCategory() != null
                && expense.getQuantity() > 0
                && expense.getSubtotal() > 0;
    }

    protected Expense readExpense(int row, int col) {
        Expense expense = new Expense();
        expense.setCategory(Ranges.range(spreadsheet.getSelectedSheet(), row, col).getCellData().getStringValue());
        Double value = Ranges.range(spreadsheet.getSelectedSheet(), row, col+1).getCellData().getDoubleValue();
        expense.setQuantity(value == null? 0 : value.intValue());
        value = Ranges.range(spreadsheet.getSelectedSheet(), row, col+3).getCellData().getDoubleValue();
        expense.setSubtotal(value == null? 0 : value.intValue());
        return expense;
    }

}

package demo.database;

import java.util.*;

/**
 * Data Access Object (DAO), simulates a persistence layer to access a simulated database
 */
public class ExpenseDao {
    private List<Expense> expenseTable = new LinkedList<>(); //simulated database table
    private int id = 0;

    //sample expenses
    public ExpenseDao() {
        Expense expense1 = new Expense();
        expense1.setCategory("Transportation");
        expense1.setQuantity(10);
        expense1.setSubtotal(2400);
        expenseTable.add(expense1);
        Expense expense2 = new Expense();
        expense2.setCategory("Food");
        expense2.setQuantity(16);
        expense2.setSubtotal(15);
        expenseTable.add(expense2);
        Expense expense3 = new Expense();
        expense3.setCategory("Lodging");
        expense3.setQuantity(12);
        expense3.setSubtotal(160);
        expenseTable.add(expense3);
        Expense expense4 = new Expense();
        expense4.setCategory("Entertainment");
        expense4.setQuantity(11);
        expense4.setSubtotal(120);
        expenseTable.add(expense4);
    }

    public List<Expense> queryAll() {
        List result = new LinkedList();
        result.addAll(expenseTable);
        return Collections.unmodifiableList(result);
    }


    public void update(Expense newExpense) {
        Expense expense = find(newExpense);
        if (expense != null) {
            expense.setQuantity(expense.getQuantity() + newExpense.getQuantity());
            expense.setSubtotal(expense.getSubtotal() + newExpense.getSubtotal());
        }
    }

    /**
     * @param newExpense
     * @return a dummy object or found expense
     */
    public Expense find(Expense newExpense) {
        Expense target = null; //dummy object
        for (int i = 0; i < expenseTable.size(); i++) {
            target = expenseTable.get(i);
            if (newExpense.getCategory().equals(target.getCategory())) {
                break;
            }
        }
        return target;
    }
}

package demo.database;

public class Expense {
    private int id;
    private String category;
    private int quantity;
    private int subtotal;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getCategory() {
        return category;
    }

    public void setCategory(String category) {
        this.category = category;
    }

    public int getQuantity() {
        return quantity;
    }

    public void setQuantity(int quantity) {
        this.quantity = quantity;
    }

    public int getSubtotal() {
        return subtotal;
    }

    public void setSubtotal(int subtotal) {
        this.subtotal = subtotal;
    }

    @Override
    public String toString() {
        return id+category+quantity+subtotal;
    }
}