Combining user-built UI and button actions turning Excel forms into a Web application.

Pag navigation is done by switching sheets and importing different files.

<?xml version="1.0" encoding="UTF-8"?>
<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%" apply="demo.workflow.WorkflowComposer"
	src="/WEB-INF/books/workflow.xlsx" maxVisibleRows="17" maxVisibleColumns="15"
	vflex="1" hiderowhead="true" hidecolumnhead="true"/>
	<script src="/demo/workflow/keikai-823.js"/>
</zk>

package demo.workflow;

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

import java.io.*;
import java.util.*;

/**
 * - an employee can submit a form as a {@link Submission}
 * * a supervisor
 *      - can select one from submission list to review and approve (or reject) a submission
 *      - can't edit an employee's submission
 * - one cell click event listener for each sheet
 */
public class WorkflowComposer extends SelectorComposer<Component> {
    @Wire
    private Spreadsheet spreadsheet;

    //sheet name
    private static final String MAIN = "main";
    private static final String SUBMISSION_LIST = "submission list";
    private static final String FORM_LIST = "form list";

    //display related
    private Book mainBook;
    private Range roleCell;
    private static final int START_ROW = 5;
    private static final String BUTTON1 = "button1";
    private static final String BUTTON2 = "button2";
    private static final SheetProtection ALLOW_SELECT = SheetProtection.Builder.create().withSelectLockedCellsAllowed(true).withAutoFilterAllowed(true).build();
    private static final SheetProtection READ_ONLY = SheetProtection.Builder.create().withSelectLockedCellsAllowed(true).build();

    //workflow related
    private final static String SUPERVISOR = "Supervisor";
    private final static String EMPLOYEE = "Employee";
    private String currentRole;
    private final static String CANCEL = "Cancel";
    private final static String SUBMIT = "Submit";
    private final static String REJECT = "Reject";
    private final static String APPROVE = "Approve";
    private List<Submission> submissionList = new LinkedList<>();
    private List<File> formList = new LinkedList<>();
    private final static String FORM_FOLDER = "/WEB-INF/books/form/";
    private boolean submissionChange = true;
    private WorkflowDao workflowDao = new WorkflowDao();
    private Submission submissionToReview; //the submission under review

    @Override
    public void doAfterCompose(Component comp) throws Exception {
        super.doAfterCompose(comp);
        mainBook = spreadsheet.getBook();
        spreadsheet.setShowSheetbar(false);
        spreadsheet.setShowToolbar(false);
        roleCell = Ranges.rangeByName(mainBook.getSheetAt(0), "Role");
        navigateTo(MAIN);
    }


    @Listen(Events.ON_CELL_CLICK + "= #spreadsheet")
    public void onClick(CellMouseEvent event) {
        String sheetName = event.getSheet().getSheetName();
        switch (sheetName) {
            case MAIN:
                handleClickMain(event);
                break;
            case FORM_LIST:
                handleClickFormList(event);
                break;
            case SUBMISSION_LIST:
                handleClickSubmissionList(event);
                break;
            case "form":
                handleClickForm(event);
                break;
        }
    }

    private void navigateTo(String sheetName) {
        spreadsheet.setSelectedSheet(sheetName);
        if (MAIN.equals(sheetName)) {
            spreadsheet.focusTo(roleCell.getRow(), roleCell.getColumn());
        }
    }

    /**
     * navigateToList a user to a sheet according to the current role
     */
    private void navigateToList() {
        switch (currentRole) {
            case SUPERVISOR:
                navigateTo(SUBMISSION_LIST);
                break;
            case EMPLOYEE:
                navigateTo(FORM_LIST);
                break;
        }
    }

    private void handleClickMain(CellMouseEvent event) {
        if (isEnterClicked(getClickedCell(event))) {
            enter();
        }
    }

    private void handleClickFormList(CellMouseEvent event) {
        if (isLeaveClicked(getClickedCell(event))) {
            navigateTo(MAIN);
        } else if (event.getColumn() >= 2 && event.getColumn() <=4) {
            int index = getClickedCell(event).getRow() - START_ROW;
            if (index >= 0 && index < formList.size()) {
                File selectedForm = formList.get(index);
                try {
                    Book book = Importers.getImporter().imports(selectedForm, selectedForm.getName());
                    spreadsheet.setBook(book);
                    addButtons(book.getSheetAt(0));
                    Ranges.range(spreadsheet.getSelectedSheet()).protectSheet(ALLOW_SELECT);
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    private void handleClickSubmissionList(CellMouseEvent event) {
        if (isLeaveClicked(getClickedCell(event))) {
            navigateTo(MAIN);
        } else if (event.getColumn() >= 2 && event.getColumn() <= 6) {
            int index = getClickedCell(event).getRow() - START_ROW;
            if (index >= 0 && index < submissionList.size()) {
                submissionToReview = submissionList.get(index);
                if (submissionToReview.getState() == Submission.State.WAITING){
                    showSubmission(submissionToReview);
                    Ranges.range(spreadsheet.getSelectedSheet()).protectSheet(READ_ONLY);
                }
            }
        }

    }

    private void showSubmission(Submission submission) {
        try {
            Book form = Importers.getImporter().imports(new ByteArrayInputStream(submission.getForm().toByteArray()), submission.getFormName());
            spreadsheet.setBook(form);
            addButtons(form.getSheetAt(0));
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    private void handleClickForm(CellMouseEvent event) {
        Range button1 = Ranges.rangeByName(event.getSheet(), BUTTON1);
        Range button2 = Ranges.rangeByName(event.getSheet(), BUTTON2);
        Range clickedCell = getClickedCell(event);
        if (clickedCell.asString().equals(button1.asString())
                || clickedCell.asString().equals(button2.asString())) {
            String label = clickedCell.getCellValue().toString();
            switch (label) {
                case APPROVE:
                    approve();
                    break;
                case REJECT:
                    reject();
                    break;
                case SUBMIT:
                    submitForm();
                    break;
            }
            exitForm();
        }
    }

    private void approve() {
        submissionToReview.setState(Submission.State.APPROVED);
        submissionToReview.setLastUpdate(Calendar.getInstance().getTime());
        workflowDao.update(submissionToReview);
        submissionChange = true;
    }

    private void reject() {
        submissionToReview.setState(Submission.State.REJECTED);
        submissionToReview.setLastUpdate(Calendar.getInstance().getTime());
        workflowDao.update(submissionToReview);
        submissionChange = true;
    }

    private void submitForm() {
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        try {
            lockCells();
            Exporters.getExporter().export(spreadsheet.getBook(), outputStream);
            Submission submission = new Submission();
            submission.setForm(outputStream);
            submission.setFormName(spreadsheet.getBook().getBookName());
            submission.setOwner(this.currentRole);
            workflowDao.insert(submission);
            submissionChange = true;
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * set cells locked to avoid being modify by a supervisor
     */
    private void lockCells() {
        final String[] unlockedCells1 = {"E4", "E5", "D6"};
        final String[] unlockedCells2 = {"D6", "D8", "J6", "L6", "D11", "J12", "D14"};

        String bookName = spreadsheet.getBook().getBookName();
        switch (bookName){
            case "Leave Application.xlsx":
                lockCells(unlockedCells1);
                break;
            case "Business Trip Request.xlsx":
                lockCells(unlockedCells2);
                break;
        }
    }

    private void lockCells(String... cells){
        for (String cell : cells){
            Range range = Ranges.range(spreadsheet.getSelectedSheet(), cell);
            CellStyle oldStyle = range.getCellStyle();
            EditableCellStyle newStyle = range.getCellStyleHelper().createCellStyle(oldStyle);
            newStyle.setLocked(true);
            range.setCellStyle(newStyle);
        }
    }

    private void exitForm() {
        spreadsheet.setBook(mainBook);
        navigateToList();
        populateList();
    }

    /**
     * add available buttons into a sheet according to the current role.
     */
    private void addButtons(Sheet sheet) {
        Range button1 = Ranges.rangeByName(sheet, BUTTON1);
        Range button2 = Ranges.rangeByName(sheet, BUTTON2);
        switch (currentRole) {
            case SUPERVISOR:
                button1.setCellValue(REJECT);
                button2.setCellValue(APPROVE);
                break;
            case EMPLOYEE:
                button1.setCellValue(CANCEL);
                button2.setCellValue(SUBMIT);
                break;
        }
    }

    private boolean isLeaveClicked(Range range) {
        return range.getCellValue() != null
                && range.getCellValue().toString().equals("Leave");
    }

    private boolean isEnterClicked(Range range) {
        return range.getCellValue() != null
                && range.getCellValue().toString().equals("Enter");
    }

    private Range getClickedCell(CellMouseEvent event) {
        return Ranges.range(spreadsheet.getSelectedSheet(), event.getRow(), event.getColumn());
    }

    private void enter() {
        currentRole = roleCell.getCellValue().toString();
        navigateToList();
        populateList();
        Ranges.range(spreadsheet.getSelectedSheet()).protectSheet(ALLOW_SELECT);
    }

    private void populateList() {
        switch (currentRole) {
            case SUPERVISOR:
                showSubmissionList();
                break;
            case EMPLOYEE:
                if (formList.size() == 0) {
                    loadFormList();
                    showFormList();
                }
                break;
        }
    }

    private void showSubmissionList() {
        if (submissionChange) {
            clearTable();
            submissionList = workflowDao.queryAll();
            Sheet sheet = mainBook.getSheet(SUBMISSION_LIST);
            for (int index = 0; index < submissionList.size() - 1; index++) {
                Ranges.range(sheet, START_ROW, 2).toRowRange().insert(Range.InsertShift.DOWN, Range.InsertCopyOrigin.FORMAT_LEFT_ABOVE);
            }
            for (int index = 0; index < submissionList.size(); index++) {
                Submission s = submissionList.get(index);
                Util.setCellValuesInRow(Ranges.range(sheet, START_ROW + index, 2)
                        , s::getId, s::getFormName, s::getOwner, s.getState()::toString, s::getLastUpdate);
            }
            submissionChange = false;
        }
    }

    private void clearTable() {
        Sheet sheet = mainBook.getSheet(SUBMISSION_LIST);
        if (submissionList.size() > 1) {
            Ranges.range(sheet, START_ROW, 2, START_ROW + submissionList.size() - 2, 6).delete(Range.DeleteShift.UP);
        }
    }

    private void showFormList() {
        Sheet sheet = mainBook.getSheet(FORM_LIST);

        for (int index = 0; index < formList.size() - 1; index++) {
            Ranges.range(sheet, START_ROW, 2).toRowRange().insert(Range.InsertShift.DOWN, Range.InsertCopyOrigin.FORMAT_LEFT_ABOVE);
        }
        for (int index = 0; index < formList.size(); index++) {
            Ranges.range(sheet, START_ROW + index, 2).setCellValue(formList.get(index).getName());
        }
    }

    private void loadFormList() {
        File[] fileList = new File(WebApps.getCurrent().getRealPath(FORM_FOLDER)).listFiles((File pathname) -> pathname.isFile() && pathname.getPath().endsWith(".xlsx"));
        formList = Arrays.asList(fileList);
    }
}

package demo.workflow;

import java.util.*;

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

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

    public void insert(Submission submission){
        submission.setId(id);
        id ++;
        submissionTable.add(submission);
    }

    public void update(Submission submission){
        int index = find(submission);
        if (index > -1){
            submissionTable.set(index, submission);
        }
    }

    /**
     *
     * @param submission
     * @return -1 not found
     */
    public int find(Submission submission){
        int index = -1;
        for (int i = 0 ; i < submissionTable.size() ; i++){
            if (submission.getId() == submissionTable.get(i).getId()){
                index = i;
                break;
            }
        }
        return index;
    }
}

package demo.workflow;

import java.io.*;
import java.time.LocalDateTime;
import java.util.*;

/**
 * represent a form submission
 */
public class Submission {
    public enum State{
        WAITING, APPROVED, REJECTED
    }

    private int id;
    private State state = State.WAITING;
    private Date lastUpdate = Calendar.getInstance().getTime();
    private ByteArrayOutputStream form;
    private String formName;
    private String owner;

    public int getId() {
        return id;
    }

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

    public State getState() {
        return state;
    }

    public void setState(State state) {
        this.state = state;
    }

    public Date getLastUpdate() {
        return lastUpdate;
    }

    public void setLastUpdate(Date lastUpdate) {
        this.lastUpdate = lastUpdate;
    }

    public ByteArrayOutputStream getForm() {
        return form;
    }

    public void setForm(ByteArrayOutputStream form) {
        this.form = form;
    }

    public String getFormName() {
        return formName;
    }

    public void setFormName(String formName) {
        this.formName = formName;
    }

    public String getOwner() {
        return owner;
    }

    public void setOwner(String owner) {
        this.owner = owner;
    }
}