Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Create a Tailored Web-Based Excel Report Generator

DZone 's Guide to

Create a Tailored Web-Based Excel Report Generator

A developer shows how he created a web application that generates reports based on Excel files.

· Web Dev Zone ·
Free Resource

The Traditional Way to Build a Custom Excel report

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

She goes through my Business Trip Application form, identifies if a visa is required for the country I am going to, 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, searching manually and copying forms around? Isn't there a more efficient way to do this? What if a system can load these forms and combine the sheets we select?

Design Main UI

I quickly draw up a plan in my head: list 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 two hours. Here is the main UI:

Get Form Templates Ready

Following my plan, I first ask my colleague to create an Excel sheet that contains all the form names and checkboxes and prepare a copy of all the templates and forms. What I need to do is to import the main Excel file (above) and all template Excel files into Keikai for future use.

    private Map<String, Workbook> templateMap = new HashMap();
    ...
    private void importTemplates() throws FileNotFoundException, AbortedException, DuplicateNameException {
        for (String name : templateNames) {
            Workbook templateBook = spreadsheet.imports(name, new File(REPORT_FOLDER, name + ".xlsx"));
            templateMap.put(name, templateBook);
        }
    }
  • Line 1: I store those imported templates (Workbook) into a Map.

Then, I select the main book, setActiveWorkbook(), as the system main UI.

spreadsheet.setActiveWorkbook(mainBook.getName());

Add an Event Listener

After the user selects the required templates with checkboxes, they will click the button to generate a customized report. Hence, I have to register an event listener on that button and implement the application logic in the event listener including checking checkboxes and copying sheets:

private void addEventListener() {
    spreadsheet.getWorksheet().getButton("build-book").addAction(buttonShapeMouseEvent -> {
        // implement the application logic
        ...
        collectSelectedTemplates(); 
        ...
    });
}
  • Line 2: build-book is the button's name I specify in Excel.  addAction() can add an onClick event listener on the button.

Copy Sheets Into the Final Workbook

Finally, I need to copy those sheets of the selected templates to my personalized report:

...
    //assume each checkbox' name matches template file name
    private String[] templateNames = {"Travel Reservation Form", "Travel Insurance Form", "Travel Health Management", "Currency Exchange Request"};
    // template name : imported Workbook
    private Map<String, Workbook> templateMap = new HashMap();

...
    private void collectSelectedTemplates() {
        atLeast1TemplateSelected = false;
        for (String name : templateNames) {
            if (mainSheet.getCheckbox(name).isChecked()) {
                templateMap.get(name).getWorksheet().copyToEnd(reportBook);
                atLeast1TemplateSelected = true;
            }
        }
    }
  • Line 11: Get the status of each checkbox in the main sheet.
  • Line 12: Copy a sheet among books with copyToEnd().

That's all! Now I can produce a custom Excel report by selecting the 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:

spreadsheet.getRangeByName("category").getValue();

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:

spreadsheet.getRangeByName("user-name").setValue(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:

spreadsheet.getWorksheet().rename(prefix + sheetName);

Source Code

I hope you enjoyed reading my article. The complete source code of this report generator is available on GitHub.

Just run the project and visit http://localhost:8080/dev-ref/case/report

Related Articles

Topics:
keikai ,web dev ,web application development ,java web development

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}