The traditional way to build a custom Excel report

Ever since I turned an Excel file into the Buy Currency App for Michelle in the administration department, we became closer. This time, I ask her to help me get prepared for my business trip.

Michelle goes through my Business Trip Application form, identifies if a visa is required for the country I am going, and if I need any insurance or accommodation arrangements, etc. etc. Then, she logs on to our file server where lots of templates and forms are stored and copies relevant Excel forms. She emails those to me, asking me to fill them out and send them back to her so that she can take care of everything else for me.

I appreciate her help -- but again, search manually and copy forms around? isn't there a more efficient way to do this? What if a system can load these forms and combine the required sheets for us?

I quickly draw a plan in my head: list down all the form names in an Excel sheet, give them each a checkbox, and at the end add a "build book" button. Import it to Keikai Spreadsheet, wire the logic on the button -- upon receiving the clicking event, check the value of the checkboxes, and pull out all the relevant (checked) sheets from various files, insert them to the same book. The resulting book can either be a Web sheet or can be exported as an Excel book.

Yes, should be easily possible in 2 hours.

Design Main UI

First, Michelle gives me the sheet she designs to select template forms. Import it to Keikai in a zul, then our main user interface is done:

<spreadsheet apply="io.keikai.devref.usecase.ReportGeneratorController" 
    height="100%" width="100%"
    src="/WEB-INF/books/report/Travel Management.xlsx"
    maxVisibleRows="12" maxVisibleColumns="6"
    hidecolumnhead="true" hiderowhead="true"/>

From the screenshot, you can see I hide toolbar, sheetbar, formula bar, and row/column heading to make Keikai more like a normal web page.

Get Form Templates Ready

Following my plan, I ask Michelle to give me those 4 xlsx template files. Each xlsx file name match its form name e.g. Travel Insurance Form.xlsx. What I need to do is to import all template Excel files into Keikai for future use.

 1 public class ReportGeneratorController extends SelectorComposer {
 2     private static Map<String, Book> templateMap = new HashMap();
 3     ...
 4     @Override
 5     public void doAfterCompose(Component comp) throws Exception {
 6         super.doAfterCompose(comp);
 7         ...
 8         importTemplates();
 9     }    
10     ...
11     private void importTemplates() throws IOException {
12         if (templateMap.size() > 0){
13             return; // already imported
14         }
15         int row = table.getRow();
16         int lastRow = table.getLastRow();
17         int templateColumn = table.getLastColumn();
18         for ( ; row <= lastRow ; row++) {
19             String name = Ranges.range(spreadsheet.getSelectedSheet(), row, templateColumn).getCellValue().toString() ;
20             Book templateBook = Importers.getImporter().imports(new File(DEFAULT_TEMPLATE_FOLDER, name+ ".xlsx"), name);
21             templateMap.put(name, templateBook);
22         }
23     }

• Line 2: I can store those imported templates (Book) into a Map without assigning them to a Spreadsheet. Because I don't need to show templates at the beginning.

Toggle Check Mark

Since there's no checkbox control in the current Keikai version I use, I put a checkbox symbol in each cell instead. Each time a user clicks the checkbox, my code will switch the cell between the checked checkbox and the unchecked one.

Listen to ON_CELL_CLICK

Register an ON_CELL_CLICK event listener by @Listen, then each time a user click a cell. Keikai will invoke this method in the controller.

    @Listen(Events.ON_CELL_CLICK + "= spreadsheet")
    public void onCellClick(CellMouseEvent e){
        if (inCheckMarks(e)) {
            toggleCheckMark(getRange(e));
        }...
    }

Switch Check Mark Symbol

I can change a cell value by Range.setCellValue().

    private static String NOT_CHECKED = "\uD83D\uDDF8";
    private static String CHECKED = "✓";
...
    private void toggleCheckMark(Range checkMarkCell) {
        if (checkMarkCell.getCellValue().equals(NOT_CHECKED)){
            checkMarkCell.setCellValue(CHECKED);
        }else{
            checkMarkCell.setCellValue(NOT_CHECKED);
        }
    }    

Copy Sheets into the Final Book

Finally, I need to copy those sheets from selected templates to my personalized report when clicking "Build" button. Listen ON_CELL_CLICK and check if someone clicks the "Build" button at cell E11.

...
    @Listen(Events.ON_CELL_CLICK + "= spreadsheet")
    public void onCellClick(CellMouseEvent e){
        if (inCheckMarks(e)) {
            ...
        }else if (inBuildButton(e)){
            build();
        }
    }

Then copy the selected template into a new book.

 1 private void build() {
 2     Book newReport = Books.createBook("newReport");
 3     int row = table.getRow();
 4     int lastRow = table.getLastRow();
 5     for ( ; row <= lastRow ; row++) {
 6         if (isChecked(Ranges.range(spreadsheet.getSelectedSheet(), row, table.getColumn()))){
 7             String fileName = Ranges.range(spreadsheet.getSelectedSheet(), row, table.getLastColumn()).getCellValue().toString();
 8             Book template = templateMap.get(fileName);
 9             Ranges.range(newReport).cloneSheetFrom(template.getSheetAt(0).getSheetName(), template.getSheetAt(0));
10         }
11     }
12     if (newReport.getNumberOfSheets() > 0 ){
13         spreadsheet.setBook(newReport);
14         enableEditMode();
15     }
16 }

• Line 2: Create a new Book without any sheet.
• Line 9: Clone a sheet from another book by Range.cloneSheetFrom().
• Line 13: Assing the new report book to Spreadsheet, so that Keikai will show new book in a browser.

After creating a custom report based on templates, I should make sheet bar visible and enlarge the visible rows and columns.

    private void enableEditMode() {
        spreadsheet.setShowSheetbar(true);
        spreadsheet.setMaxVisibleRows(40);
        spreadsheet.setMaxVisibleColumns(15);
    }

That's all! Now I can produce a custom Excel report by selecting desired sheets from checkboxes. This simple app works like this:

To be Continued

The small "custom reporting" program is now done, and I am ready to depart for my trip. I have a few ideas to make the app more complete after I am back:

Common Fields

There are some common fields that reside in every template, like "Category" or "Form Number". Instead of having to read the value of the field at different cells (e.g. A3 or D4) on each template, I shall define a named range for such cell in each template so that I can easily read all the common fields using the same line of code:

Ranges.rangeByName("category").getCellValue();

Pre-filled Content

I will integrate our program with our existing SSO service and database, by doing so I can pre-fill some personal information e.g. Name when inserting a template sheet so that the end user does not have to fill out personal information again and again in multiple sheets:

Ranges.rangeByName("user-name").setCellValue(userName);

Post-Processing

After a user submits a report, I will append the sheet name with a consistent prefix and make it, for example, JohnDoe_InsuranceApplication for ease of reading:

Ranges.range(spreadsheet.getBook().getSheetAt(0)).setSheetName(prefix + sheetName);

Source Code

I hope you enjoy reading my article. The complete source code of this report generator is available at Github.

Just run the project and visit http://localhost:8080/dev-ref/usecase/reportGenerator.zul