How I Created an Online Budget Report from Excel Files 
Tags: java, spreadsheet, web, excel
Published: 2020-02-19 06:36:39

# Issues when publishing an Excel file
My colleague Michelle in the finance team has a routine job; she has to publish a budget report on a weekly basis. Previously, she had to query budget data from a database. Then, fill the budget data manually into a template sheet like this:

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

Since this is a routine task, I feel I can help her by automating the data-filing process so that she does not have to do it manually & repeatedly.

Another issue is that she has been publishing the budget report as an attachment. Readers have to download the file to see the content on an Excel-ready device. Also, it is hard to control where the attachment ends up to, she has to remove all sensitive and raw data before sending it out.

To solve the above issues, I created a web page with a web spreadsheet component, [Keikai](https://keikai.io). Keikai allows me to reuse the existing Excel budget template so that I don’t need to create everything from scratch. It is also important that I can control it with Java.

# Load the template file first
To start simple, I loaded the Excel template sheet into Keikai to show it in a browser:
![Imgur](https://i.imgur.com/rzwSmPL.png)

Such page was created by [ZUL](http://books.zkoss.org/wiki/ZUML_Reference), an XML format language from [ZK framework](http://www.zkoss.org/):

```xml
<spreadsheet id="ss" height="100%" width="100%" src="/WEB-INF/books/budget.xlsx"
    maxVisibleRows="150" maxVisibleColumns="40"
    showContextMenu="true" showToolbar="true" showSheetbar="true" 
    showSheetTabContextMenu="true" showFormulabar="true"/>
```
But you can also use Keikai with [JSP](https://github.com/keikai/dev-ref/blob/master/src/main/webapp/jsp/index.jsp).

The tag, `<spreadsheet>`, represents the Keikai spreadsheet, and I can configure Keikai via tag attributes. 

When a user visits http://localhost:8080/database.zul, keikai will respond with a set of javascript and CSS and render the content file in the browser.


# Data Access Object
In order to load data from the database, I created `Expense.java` to store each record and `ExpenseDao.java` to query and update the database.


# Create a controller to populate data
Next, I implemented a Java controller class to populate data from the database. The controller has to extend `SelectorComposer` so that it can inject `Spreadsheet` objects for me.

```java
public class BudgetComposer extends SelectorComposer<Component> {
    @Wire
    private Spreadsheet spreadsheet;
    ...
}
```
`@Wire` can inject a `Spreadsheet` object on the page, that's why I didn't call any constructor like `new Spreadsheet()`.


## Range API
Then, I needed to set data into cells by `Range`. One `Range` object can represent one or more cells/rows/columns, or even one sheet. I can get a `Range` object with a factory method `Ranges.range(Sheet targetSheet, int rowIndex, int columnIndex)`. For example, `Ranges.range(currentSheet, 0, 0)` represents `A1`. 

To show data in a cell, just call `range.setCellValue()`. So the code is quite straightforward. After querying a list of `Expense`, I can populate data into cells in a loop like:

```java
private void fillExpenses(List<Expense> list) {
    for (int i = 0; i < list.size(); i++) {
        Expense expense = list.get(i);
        Ranges.range(spreadsheet.getSelectedSheet(), START_ROW + i, 1)
            .setCellValue(expense.getQuantity());
        Ranges.range(spreadsheet.getSelectedSheet(), START_ROW + i, 2)
            .setCellValue(expense.getSubtotal());
    }
}
```


# Result
I was able to quickly turn my colleague’s manual Excel routine into an automated Web app. Now whenever we access the budget report page, Keikai queries the data and displays 
it within the report template like:
![](https://i.imgur.com/jMzU5AZ.png)


# Try it yourself
If you find this article interesting, there are more examples and [demos](https://keikai.io/demo/database) on [Keikai’s website](https://keikai.io). Or you can run the example project on [github](https://github.com/keikai/dev-ref).