A data-processing Excel sheet contains input/output fields and formulas, finance models and your domain knowledge for calculating tax, pension, loan, scientific data, etc.

This demo shows how you can easily share your data processing sheet while protecting your models and formulas -- whether having everything on the same sheet or isolating input/output from the analysis.

<?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"
    xmlns:h="native">
    <hlayout height="100%" apply="demo.secured.SecuredComposer">
        <spreadsheet id="spreadsheet" hflex="3" vflex="1" src="/WEB-INF/books/multiple_sales_forecast.xlsx"
                     maxVisibleRows="100" maxVisibleColumns="10" showFormulabar="false"/>
        <div hflex="1">
            <h:h1 class="z-label" style="font-size: 24px; font-weight: bold">Configuration</h:h1>
            <h:span class="z-label subtitle" >Layout</h:span>
                <radiogroup id="useCases" orient="vertical">
                    <radio label="Show input/output only" value="multiple" selected="true"/>
                    <radio label="Show all intermediate calculation" value="single"/>
                </radiogroup>
            <h:span class="z-label subtitle" >Role</h:span>
                <radiogroup id="views" orient="vertical">
                    <radio label="User View - hide formulas" selected="true" value="user"/>
                    <radio label="Admin View - full access" value="admin"/>
                </radiogroup>
        </div>
    </hlayout>
    <style>
    .subtitle{
        color: rgba(0,0,0,0.57);
        display: block;
        font-size: 16px;
        margin: 10px 0;
    }
    </style>
</zk>

package demo.secured;

import io.keikai.api.*;
import io.keikai.ui.Spreadsheet;
import org.zkoss.zk.ui.Component;
import org.zkoss.zk.ui.event.*;
import org.zkoss.zk.ui.select.SelectorComposer;
import org.zkoss.zk.ui.select.annotation.*;
import org.zkoss.zk.ui.util.Clients;
import org.zkoss.zul.Radio;

public class SecuredComposer extends SelectorComposer {

    @Wire
    private Spreadsheet spreadsheet;
    private static final String fileName = "_sales_forecast.xlsx";
    static String defaultBookFolder = "/WEB-INF/books/";
    protected static final SheetProtection ALLOW_SELECT = SheetProtection.Builder.create().withSelectUnlockedCellsAllowed(true).withPassword(fileName).build();
    enum View {user, admin}
    private String view = View.user.name(); //current view



    @Override
    public void doAfterCompose(Component comp) throws Exception {
        super.doAfterCompose(comp);
        activateView();
    }

    @Listen("onCheck = #useCases")
    public void switchCase(CheckEvent e) {
        String caseName = ((Radio) e.getTarget()).getValue();
        spreadsheet.setSrc(defaultBookFolder + caseName + fileName);
        activateView();
    }

    @Listen("onCheck = #views")
    public void switchPermission(CheckEvent e) {
        view = ((Radio) e.getTarget()).getValue();
        activateView();
    }

    /**
     * apply permissions according to the current view.
     */
    private void activateView() {
        if (View.user.name().equals(view)) {
            activateUserView();
        } else { //admin
            activateAdminView();
        }
        focusInputArea(); // hint users to edit cells
    }

    /**
     * focus at the left-top corner cell of the input area.
     */
    private void focusInputArea() {
        Range inputCell = Ranges.rangeByName(spreadsheet.getSelectedSheet(), "input").toCellRange(0, 0);
        spreadsheet.focusTo(inputCell.getRow(), inputCell.getColumn());
    }

    private void activateAdminView() {
        spreadsheet.setShowSheetbar(true);
        spreadsheet.setShowFormulabar(true);
        spreadsheet.setShowToolbar(true);
        Ranges.range(spreadsheet.getSelectedSheet()).unprotectSheet(fileName);
        Clients.resize(spreadsheet);
    }

    /**
     * make the sheet read-only and hide all UI.
     */
    private void activateUserView() {
        spreadsheet.setShowSheetbar(false);
        spreadsheet.setShowToolbar(false);
        spreadsheet.setShowFormulabar(false);
        Ranges.range(spreadsheet.getSelectedSheet()).protectSheet(ALLOW_SELECT);
        spreadsheet.setSelectedSheet(spreadsheet.getBook().getSheetAt(0).getSheetName()); //prevent showing other sheets
    }
}