Automate Excel Sheet Processing with Java
Tags: web, spreadsheet, java, excel
Published: 2020-05-19 04:34:57


# A Sheet Processing Use Case
My colleague Penny in the HR department has to do a routine task each month: making payroll sheets for everyone in the company. There is a sheet containing a table of all employees with salary information:

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

And she has to create payroll sheets for each person on the list based on the template sheet:

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

To avoid copying cell by cell manually, I create a web application with [Keikai](http://keikai.io/) to read salary data row by row and produce payroll sheets. The whole process is:
![Imgur](https://i.imgur.com/cLmMFGm.png)


# Import a Pre-designed Excel File
I create a Keikai spreadsheet by writing tags and attributes in a zul of [ZK framework](https://www.zkoss.org) which is a UI framework based on Java EE. By specifying at `src` attribute with a file path, Keikai can import my `Payroll.xlsx`.

```xml
    <spreadsheet height="100%" width="100%" src="/WEB-INF/books/Payroll.xlsx"
				 maxVisibleColumns="15" maxVisibleRows="20"
				 hidecolumnhead="false"  hiderowhead="false"
				 showToolbar="true" showSheetbar="true" showFormulabar="true"
				 apply="io.keikai.devref.usecase.PayrollComposer"/>
```
When I visit the zul with a browser, Keikai renders the file:

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

The ZK framework will parse the zul page above and instantiate a Keikai Java object([`Spreadsheet`](https://keikai.io/javadoc/latest/io/keikai/ui/Spreadsheet.html)) for us to control. 

# Controller
Then I also create a Java controller, `PayrollComposer` to access the Keikai Java object and apply the controller on the page at `apply` attribute: `apply="io.keikai.devref.usecase.PayrollComposer"`

# Named Range
I make a cell like a button on the sheet. When I click the cell, Keikai will start to produce payroll sheets. I give a name, `Generate`, to the cell.

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

After that, I can create a [`Range`](https://keikai.io/javadoc/latest/io/keikai/api/Range.html) object (`generateButton`) with the name:

`generateButton = Ranges.rangeByName(sheet, "Generate");`

Here is the related code snippet:
```java
public class PayrollComposer extends SelectorComposer<Component>{

    @Wire("spreadsheet")
    private Spreadsheet spreadsheet;
    final private static String EMPLOYEE_SHEET = "Payroll";
	private Range generateButton;
	private Sheet sheet;

	@Override
	public void doAfterCompose(Component comp) throws Exception {
		super.doAfterCompose(comp);
		sheet = spreadsheet.getBook().getSheet(EMPLOYEE_SHEET);
		generateButton = Ranges.rangeByName(sheet, "Generate");
	}
...
```

# Listen to Button Clicking
Then I register an event listener for the cell (button) clicking by [`@Listen`](https://www.zkoss.org/wiki/ZK%20Developer's%20Reference/Event%20Handling/Event%20Listening) and check whether the button `generateButton` is clicked or not:

```java
	@Listen(Events.ON_CELL_CLICK + "=spreadsheet")
    public void onCellClick(CellMouseEvent e) {
        String sheetName = e.getSheet().getSheetName();
        switch (sheetName) {
            case EMPLOYEE_SHEET:
            	if (RangeHelper.isRangeClicked(e, generateButton))
               	    fillPayrollSlips();
                break;
        }
    }
```
When the button is clicked, it just starts to fill the payroll slips.

# Fill payroll data into sheets
Before filling a payroll sheet, I have to read all employees' salary data row by row from the table. The table also has a corresponding named range, `PayrollTable`, that I don't need to hard code its cell address.

```java
private void fillPayrollSlips() {
    String tableName = "PayrollTable";
    Range payrollRange = Ranges.rangeByName(sheet, tableName);
    List<Map<String, Object>> employeeSalaries = getEmployeeSalaries(payrollRange);
    generateAllPayrollSlips(employeeSalaries);
}
```

To avoid filling data into a fixed cell address of the payroll sheet, I create a named range for each field that maps the corresponding column name. 

**Column Name** <==> **Named Range**

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


Hence, I can fill data in a simple loop. Just clone ([cloneSheet()](https://keikai.io/javadoc/latest/io/keikai/api/Range.html#cloneSheet-java.lang.String-)) the template sheet and fill each field by the named range.

```java
	private void generateAllPayrollSlips(List<Map<String, Object>> employeeSalaries) {
		for (Map<String, Object> employee : employeeSalaries) {
			Sheet payrollSheet = Ranges.range(spreadsheet.getBook().getSheet("Form"))
					.cloneSheet((String) employee.get("Name"));
			for (String field : employee.keySet()) {
				Ranges.rangeByName(payrollSheet, field).setCellValue(employee.get(field));
			}
		}
	}
```

With the named range, I don't need to hard code a cell address in the code, which makes the code more robust against sheet layout change.

# Source Code
I was able to turn this manual Excel copy-pasting task into an automated task in less than 100 lines. I hope you find this example interesting. You can find out the complete code at [Github](https://github.com/keikai/dev-ref/blob/master/src/main/java/io/keikai/devref/usecase/PayrollComposer.java)