DEV Community

Cover image for Protect Formulas in a Web Spreadsheet Application
Hawk Chen
Hawk Chen

Posted on

Protect Formulas in a Web Spreadsheet Application

The User Story

One of our customers is a financial professional who manages financial risks for his clients. He develops a tool based on Excel to analyze the risk of investments and forecast the market trend. He creates many valuable formulas in his Excel file that help him do the job.

Now he plans to turn this tool into a web application that allows users to input financial data and produces a risk analysis result. But he wants to hide his valuable formulas since that's his business secret.

Imgur

Overall Process

We can implement his plan with Keikai. The overall idea of this system is like:

Imgur

Therefore, I create an xlsx file with 3 sheets:

  • source sheet: accept user input
  • transform sheet: calculate results with formulas by referencing cells in the source sheet. To protect the valuable formulas, this sheet is hidden from users.
  • display sheet: display the calculation result to users.

Import xlsx File

By specifying the path of an xlsx file, I can import it into Keikai.

dataTransform.zul

    <spreadsheet height="100%" width="100%" src="/WEB-INF/books/transform.xlsx"
                 maxVisibleColumns="15" maxVisibleRows="20"
                 apply="io.keikai.devref.usecase.DataTransformComposer"/>
Enter fullscreen mode Exit fullscreen mode

Because Keikai supports MVC pattern, I can also apply a Controller DataTransformComposer to manipulate Keikai and listen to events in Java API.

This is the imported result in a browser:
Imgur

As you can see in the screenshot, there is no sheet tab. Keikai allows you to hide the sheet tab. Hence, users can't switch to the sheet that contains the formulas.

Formulas Supported

Keikai supports most of the Excel functions (over 250) and syntax. Keikai can import the formulas you write in Excel without any modification and they will work as they were.

Protect Sheets

I don't want users to arbitrarily change my sheet, so I want to enable sheet protection. Before that, I need to create a SheetProtection with proper permissions. There are 14 permissions to choose. Keikai provides a Builder pattern API, so I can just setup those permissions I care:

private static final SheetProtection VIEW_ONLY = SheetProtection.Builder.create()
.withSelectLockedCellsAllowed(true)
.withSelectUnlockedCellsAllowed(true)
.withAutoFilterAllowed(true)
.build();
Enter fullscreen mode Exit fullscreen mode

Then, I enable sheet protect in a loop with SheetProtection:

    private void protectAllSheets() {
        for (int i = 0; i < spreadsheet.getBook().getNumberOfSheets(); i++) {
            Ranges.range(spreadsheet.getBook().getSheetAt(i)).protectSheet(VIEW_ONLY);
        }
    }
Enter fullscreen mode Exit fullscreen mode

Unlocked Cells

Under the sheet protection, every cell is read-only. But I still need to accept user input in the source sheet, so I need to set several cells as unlocked in Excel to make them editable. Just open cell format / Protection, uncheck "Locked":

Imgur

Listen to Cell Click

There are 2 cells working as buttons. When users click them, Keikai will calculate and show the result. I have to implement this logic in my controller class by the code below:

    @Listen(Events.ON_CELL_CLICK + "=spreadsheet")
    public void onCellClick(CellMouseEvent e) {
        String sheetName = e.getSheet().getSheetName();
        switch (sheetName) {
            case SOURCE_SHEET :
                if(e.getRow() == 14 && e.getColumn() == 2)
                    simpleWorkflow();
                if(e.getRow() == 16 && e.getColumn() == 2)
                    complexWorkflow();
                break;
        }
    }
Enter fullscreen mode Exit fullscreen mode
  • @Listen(Events.ON_CELL_CLICK + "=spreadsheet") is to register an ON_CELL_CLICK event listener, method onCellClick(), for component spreadsheet. Therefore, when a user clicks a cell, it will invoke onCellClick().
  • In this listner method, I need to check clicked cell's sheet, row, and column to determine which workflow to perform.

Show Display Sheet

Finally, I call setSelectedSheet(SIMPLE_DISPLAY_SHEET) to show the display sheet to users.

    private void simpleWorkflow() {
        spreadsheet.setSelectedSheet(SIMPLE_DISPLAY_SHEET);
    }
Enter fullscreen mode Exit fullscreen mode

Benefits

After turning the xlsx file into a web application the formulas are fully protected. In addition, the financial professional can still modify his formulas from time to time without changing my Java code since the analysis is implemented in his formulas instead of Java.

Complete Source Code

You can check the full source code at Github.

Top comments (0)