Creating Excel Reports just got a whole lot simpler

An Excel Dashboard

You can read more about the tool in the attached PDF titled Creating Excel Reports Made Easy and read the FAQ to get answer on some of your possible questions.

The source code is located on the talendForge site. (http://talendforge.org/exchange/tos/?cid=6). If you search by Author Hugo in the java category you can download the addin.

Love it or loath it the spreadsheet is a ubiquitous business application which empowers the end user. Whilst data can be conveyed in many formats, most business users are happiest with data in Excel.

I think every IT professional and business user alike will agree with the above statement. While spreadsheets can be a complete nightmare and are frequently abused, for many purposes including reporting and analysis the business find them to be the ideal medium.

Having spent the majority of my IT career working in data related fields I have produced more than my fair share of ETL routines and reports. However until recently something was missing; creating Excel reports was a pain.

Whilst you could create a report in a tool such as Business Objects, Crystal Reports, JasperReports, JFreeReports or any other dedicated reporting tool then export the report to Excel, I have always found these tools to be inflexible when exporting to Excel. They lack the ability to have features such as split panes, drop down filters (eg autofilters), multiple worksheets and lack control over the layout of the data.

Therefore I have created an open source extension for Talend in java which I hope can ease this pain. Traditionally you could create Excel reports in java by using a library such as POI or JXL and writing plenty of code. Alternatively you could write the program in VB/A or .NET but all of these approaches require large amounts of code and can be inflexible, with many lines dedicated to trivial issues such as formatting and data placement. If you wanted to change the report layout or format you needed to change the code and then undergo regression testing to ensure that you hadn't inadvertently broken something else.

By using this extension and a template based approach the whole process is greatly simplified, but certainly doesn't lack in power or flexibility. Indeed if you discover there are features which you can't accomplish with the tool and templates you can include a piece of VBA in the template which will run when the workbook is open to complete the tasks!

I believe that the tool is straightforward and it should be possible for a business user or someone without extensive IT training to quickly get up to speed and create great looking reports without the need for a single line of bespoke code. More adventurous users will appreciate the power offered by the Tags and ability to loop on collections and access underlying features such as renaming worksheets based on object field values. You can read more about the tool in the attached PDF titled Creating Excel Reports Made Easy, infact you should use that as your guide to get up and running with the tool, since it contains numerous useful examples.

Whilst I have endeavored to test the extension and make it as flexible as possible I am of course limited to my own imagination and tests. I would therefore be greatful if others would download the tool provide feedback that will help to improve the tool and benefit us all.

In a nutshell the report is creating by using Talend and mapping the incoming data to objects. The Image below shows a template where all data has been mapped to a single employee object - hence the tags such as ${employee.name}
Creating an Excel Report using Tags and Java

By using the expressions $[b2 + c2] and $[sum(e2)] in the above report we actually retain one of the many advantages of excel, notably the ability to change fields and see the effects, since at run time these values will become formulas such as =sum(e2:e10). Therefore the business might choose to flex the created spreadsheet to see what the effect would be of changing some variables. In a more traditional report generated by a tool like Crystal these fields would be pre-calculated and we loose one of the advantages which excel offers.

It is possible to nest these objects as well eg you might have an incoming row which has joined two relational tables together eg Emp and Dept. In this situation you would map some fields to the Employee Object and some fields to the Department Object, in addition you would tell the tool on which fields it can join the Employee and Department objects.

By allowing for the nesting of objects it is possible to create nested / banded reports e.g. see below: - Note if the pictures looks funny please trust me this is Excel, however this report was created on a mac - so it should be noted that these spreadsheets have been produced on a unix machine, although being java based the reports can also be created on windows and linux

An excel report containing grouping
The actual Excel Template looks like the one below - note this is one of the more complex spreadsheets you could hope to create since it has a variety of features. The Tags use JEXL syntax and should therefore be familiar to anyone with a knowledge of JSP - and are simple to pick up by business users. If the Tags scare you then believe me, you would need to write a lot more code in VBA or a similar language to import the data from the source system and create a report such as this.
Creating an Excel Report with groups



The main piece of the work is done by the Talend Extension - however fear not this is very simple to use. It comprises of very few fields.

Creating the Excel Report with the Talend ETL extension

Please don't let the images showing tags scare you, they really are quite simple when you get the hand of them.

In addition it is possible to create charts, filter the data, add outlines to the data, group the data and use a whole host of formulas.

The source code is located on the talendForge site. (http://talendforge.org/exchange/tos/?cid=6). If you search by Author Hugo in the java category you can download the addin.