Enable User Input in My Spreadsheet-based App
Tags: java, spreadsheet, excel
Published: 2020-03-06 09:59:07

# New requirements: add new expenses
A while ago I [turned my colleague Michelle's Excel file into an online budget report web app](https://dev.to/hawkchen/how-i-created-an-online-budget-report-from-excel-files-g3g), to save her from a bunch of manual routines. Recently, she had a new requirement, adding new expenses. She wanted to add new expenses in the sheet and aggregate the new expenses in the current summary report.

If you are new to my blog, this is how the summary report looks like: 
![](https://i.imgur.com/jMzU5AZ.png)


# Turn cells into a button
We both agreed to keep the existing summary report sheet clean and implement the new feature in a new sheet. We decided to put a button "Add my new expense" on the sheet which will open a new form in another sheet.

How can we make a button here? It was very simple. Michelle just merged several cells and set her preferred background-color in the Excel template.

![](https://i.imgur.com/pRuOCrN.png)


# Create a form
To fulfill the new requirement, Michelle needed a form to input new expenses. I asked her to open up the Excel template and add a new sheet with a table and a "Done" button. She also used formulas (e.g. `=C4*D4`) in the subtotal column (column E).

![Imgur](https://i.imgur.com/bDnzqCI.png)

To avoid accidentally deleting the formulas, she protected the whole sheet and set `A4:D7` as "unlocked". Therefore, she can only input new expenses in the unlocked range.


# Change UI without programming
As you may have noticed, everything Michelle added in the sheets so far can be done in Excel, without any programming skills. All I (as a developer) need to do is to take this updated Excel file and import it into Keikai without asking her what she has changed. Then the updated file becomes our new UI.


# Sheet switching
After she finished, it was my turn to add the application logic behind these 2 sheets (summary and new expense form). When Michelle clicked "Add my new expense", Keikai should switch to the new expense form sheet.

Here is the code to add an `onCellClick` listener and select the `NEW` expense form sheet in the controller class:
```java
public class BudgetComposer extends SelectorComposer<Component> {
...
    @Listen(Events.ON_CELL_CLICK + "= #spreadsheet")
    public void onClick(CellMouseEvent event) {
        Range cell = Util.getClickedCell(event);
        if ("Add my new expense".equals(cell.getCellValue())){
            spreadsheet.setSelectedSheet(NEW);
        }...
    }

...
}
```


# Read user input
The final part is to read the new expenses when Michelle clicked the "Done" button.

Let's update the previous event listener for "Done" button clicked:

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

Through `Range` API, I can read data cell by cell to construct an `Expense` object:
```java
    private Expense readExpense(int row, int col) {
        Expense expense = new Expense();
        expense.setCategory(Ranges.range(spreadsheet.getSelectedSheet(), row, 1).getCellData().getStringValue());
        Double value = Ranges.range(spreadsheet.getSelectedSheet(), row, 2).getCellData().getDoubleValue();
        expense.setQuantity(value == null? 0 : value.intValue());
        value = Ranges.range(spreadsheet.getSelectedSheet(), row, 4).getCellData().getDoubleValue();
        expense.setSubtotal(value == null? 0 : value.intValue());
        return expense;
    }
```

To avoid boring you to tears, I won't show all the details here. You can take a look at the [online demo](https://keikai.io/demo/database) to check its source code and understand this application better.