Agile Java Report Develop Framework based JExcel

Agile Java Report Develop Framework for China Style Report Based on Open Source Component: JExcelAPI

Zhang Yongji<!----><!----><!----> <!---->

 

Ningbo EastSea Linefan Technology Co., Ltd, Ningbo 315103, P.R.China

 

Abstract:   Reports play a very important role in enterprise application. Report developer always spends a lot of time to generate them. Sometimes, many commercial and non-commercial report generating tools can’t do very well with China Style Report. So there are always two type reports, one is generated with report tools and the other has to be generated by writing some code. This is very difficult to maintain this code. In this paper, we present a way to generate report by write custom java code with the well defined API that is provided in our report framework. It has some clear good points , such as it is based on stable domain model, easy to maintain because of identical code base compared to be based on complex SQL and stored procedure, and easy to utilize rich features provided by Office Excel , such as formula definition, multiple format support, flexible cell operation, etc.

Keywords : Agile, Report development, Excel, Java, China style report, Hibernate, spring, JExcelAPI

 

1. Introduction

  Report plays a very important role in enterprise applications. Developers always spend a lot of time to generate them. Reports with China style are pretty complex because four reasons. First, China style reports need to collect data from many data sources, such as database, flat files and office files, etc. Secondly, they always have multiple formats. For example, there are some report cells with oblique line (Figure 1). Most commercial and non-commercial report generating tools can’t do well enough in this condition. Thirdly, China style reports always consist of many fragments that are unrelated logistically, that means the report data may come from many database tables. At last, a type of very special report named Cross report is very popular in China (Figure 2). Its statistical data is obtained from two directions, by row and by column. All these make it very difficult with single report tool to resolving all problems of reports generating.

 

 

<!----><!----><!---->

 

Figure 1. Report with oblique line

 

  Java Excel API is a mature, open source java API enabling developers to read, write, and modify Excel spreadsheets dynamically. Java developers can read Excel spreadsheets, modify them with a convenient and simple API, and write the changes to any output stream (e.g. disk, HTTP, database, or any socket). Any operating system which can run a Java virtual machine (i.e., not just Windows) can both process and deliver Excel spreadsheets. Because it is Java, the API can be invoked from within a servlet, thus giving access to Excel spreadsheets over internet and intranet web applications.

 

<!----><!----><!---->

 

Figure 2. Cross report example

  

   Generally speaking, report generating tools are designed to ease report development. It may as well developers don’t need to write program even a single line and all reports can be generated by drag-and-drop. But in fact, many report generating tools can’t meet this requirement of customers. Developers always have to write some code in some special case like a patch of this tool. Clearly, this is not a good solution. It make the reports difficult be maintained. Developers need a whole solution that can generate all different type reports. Also they hope to maintain code on identical code base.

   Of course, omnipotent report generating tools doesn’t exist. In this paper, we give a light weight framework of report generating. Report developers can generate reports by writing custom java code based on this framework.

   The structure of this paper is as follows: Section 2 analyses the process of report generating. Section 3 describes the motive of this solution. Section 4 describes how to abstract the report data structure. Section 5 describes design consideration of this framework. Section 6 explores the detail of framework design. At last, Section 7 wraps up with conclusions and ideas for future work.

 

2. The process of report generating

   The structure of report consist of two parts generally, report template and report data it contains. Report template contains some information that is static, invariable in the process of report generating. It also always carries some images and has various formats such as different fonts, color, borders, etc. The other part is data that are inserted dynamically when generating reports.

   So, the work of report development also consists of tow parts, getting the report template and filling data into report template (Figure 3). Developers can get the report template using professional report design tools or edit excel files manually or get these template files directly from customers. Generally, customers have some report template files predefined.  

<!----><!----><!----><!---->

 

Figure 3. The work of report development UML graph

 

   About filling the report data, there are many ways. For example, the professional report tools provide visual interfaces to extract data from databases. In this way , as long as a developer open extract dialog , config the data source that he want, and input some query conditions, the data will be returned . In the ordinary way, reports can be generated by this way. But it doesn’t work in these cases that are described in Section 1. Besides that, developers also can generate report by writing custom java program to fill the data from various data sources. When generating report by this way, the key problem is that data access API used to extract data from data source is very easy-to-use. In other words, developers can access different data source easily, for example reading database by hibernate [11], reading xml data by dom4j [12] and read flat file by JDK. It is because there are so many available components, so data access will not be a painful development work. Otherwise, developers will end up with reading data from all kinds of data sources. This method works very well when being confront of complex situation because we look java as a macro language used to generate report.

  

3. The motive

   By analysis of the former section, we know it is impossible to abstract the process of extract data from data sources because of their variety and complexity.

   Now, we give a light weight solution report building.

We adapt Microsoft excel file as standard output of report based on some reasons as follows:

   First, Office Excel is very popular in most of enterprises. Secondly, it is very easy to use and has strong capacity of data process. Many customers always need to modify the report with such-and-such reasons. Thirdly, Excel has public file format. Thus there are many open source components reading and writing excel files. One of these is Java Excel API [5] that is adapted by this paper.

   We generating report by writing custom java code with some very nice API defined by the framework that is introduced in this paper. Moreover, all the report data only depend on domain model instead of complex SQL or stored procedure that are difficult to be maintained. Generally, domain model is more stable than database schema.

   As a developer, he doesn’t need to learn complicated report generating tool. He just learns the APIs provided by this framework and other APIs provided by Java Excel API [5] component to operate Excel file. At the same time, he can utilize various features of Excel such as formula, statistic graph, even VBA macro to ease report development.

 

4. The abstract of report data

    Figure 4 is an example of China style report. It consists of static data coming from report template files and dynamical data extracted from various data sources. The whole dynamical data region also consists of many snippet data that are unrelated logistically. Different report snippet may come from different data source. In every report snippet, data are close connection logistically and come from same data source such as the same database or the same flat file. 

 

 

<!----><!----><!---->

Figure 4. An example of China style report

 

   So, the result of abstract of report structure data is as follows:

 

   <!----><!----><!----><!---->

        Figure 5. Abstract of report structure

 

5. The design consideration

  In real applications, our framework is integrated with popular open source frameworks (Struts, Spring and Hibernate, ‘SSH’ for short). We also define a well defined data access API based on Spring and Hibernate.

  Before exploring the details of this framework, we see the use case of report generating.

<!---->(1)     <!---->User selects a report to view.

<!---->(2)     <!---->System gets the report template that user select.

<!---->(3)     <!---->System copies the template file as the output file.

<!---->(4)     <!---->According to the template file, system gets the report builder object that is used to fill data. The report builder object is obtained and instantiated from meta data.

<!---->(5)     <!---->The report builder object builds the report.

<!---->(6)     <!---->System returns the output result.

  Next we describe some design consideration of this framework based on the upper user case.

<!---->(1)     <!---->Developers can add new report instead of modify old source code. That means it must meet the Open-Close Principle.

<!---->(2)     <!---->Different report fills the report data by different way. So, filling data is a hot spot of design. Developers only override this method to fill custom report data. It needs to meet the Law of Demeter.

<!---->(3)     <!---->When filling the report, different data always have different format. For example, date format may be ‘YYYY-MM-DD HH:mm:ss’ or ‘MM-DD-YYYY HH:mm:ss’. So our framework should permit developers to extend at this point.

6. Framework design

  There are three layers in context of this framework.

<!---->(1)     <!---->View layer. It is responsible to pass the report template name to business layer.

<!---->(2)     <!---->Business layer. All core logic of report generating is included in this layer. According to the report template name, ReportBuilderFactory class is responsible to create the proper report builder object of the builder class specified in ReportTemplate object.

<!---->(3)     <!---->Data Access layer. All data access logic is wrapped up in this layer. The report builder instance will extract the data from data source to fill the report.

Next we explain the intent of main classes and interfaces in this framework.

<!---->(1)     <!---->BaseReportAction . It inherit from Struts action class and is responsible to determinate the path of report output file, create and pass the object of ReportTemplate class to business layer. Also it has an abstract method named ‘build’ that is responsible to build the report.

<!---->(2)     <!---->GeneralReportAction . This class override the method ’build’ to create builder object by the ReportBuilderFactory class.

<!---->(3)     <!---->IReportBuilder and AbstractReportBuilder .

    public interface IReportBuilder {

        void build();

        void createTemplate ()

        void fillData ()

        List<ReportSnippet> addReportSnippet (ReportSnippet rs);

     void end();

  }

 

      Figure 6. Design of report framework

 

Here we adapt a Template Method pattern that is describe in [1]. AbstractReportBuilder class implements the IReportBuilder interface and leave ‘fillData’ method overrode by its child classes.

<!---->(4)     <!---->ReportSnippet . It represents a report snippet. It knows where data will be inserted, how many rows will be inserted, which data will be inserted and what format will be applied.

<!---->(5)     <!---->CustomReportSnippet, CustomReportBuilder . This two are not real classes. They represent custom classes extending from this framework. When build a report, developers always need to create custom report builder.

<!---->(6)     <!---->ReportBuilderFactory . It is used to create the report builder instance dynamically.

 

7. Conclusion and future work.

   Now, we review strong points and weak points of this framework and give some advices how to apply it in concrete situation.

   Strong points:

<!---->(1)     <!---->Pure java report framework. It is very easy to integrate with other java open source frameworks.

<!---->(2)     <!---->Base on domain model. Compared to base on database schema with SQL or stored procedure, its source code is easier to be maintained.

<!---->(3)     <!---->Developing report in this way doesn’t need senior programmer. As long as a developer is familiar with these API, he will be soon to develop report with high productivity.

<!---->(4)     <!---->Save a lot of money buying commercial report generating tool.

   Correspondingly, it has some weak points:

<!---->(1)     <!---->Only Excel format output is supported. Some popular formats such as PDF, CSV, and HTML can’t be supported currently.

<!---->(2)     <!---->It has not a visual interface and needs the report developer be able to write java program.

<!---->(3)     <!---->Because it based on the domain model, in some applications, performance is not very well.

   On the whole, when facing some projects that customers prefer excel as their chief output format, this is a pretty well solution.

   This paper has attempted to give a light weight report framework to resolving the problem of generating complex China Style Report. This framework still has many limitations. Future work is concerned with simplifying the API, supporting more data sources and more output formats.  

References

[1] Eric Gamma, etc. Design patterns: Elements of Reusable Object-Oriented Software 1995

[2] Robert C. Martin, Agile Software Development Principles, Patterns, and Practices, 2003

[3] Yan Hong, Java and Patterns 2002

[4] Martin fowler, UML Distilled: A Brief Guide to the Standard Object Modeling Language, Third Edition

[5] http://jexcelapi.sourceforge.net/

[6] http://www.blogjava.net/java-report/MyPosts.html

[7] http://www.eclipse.org/birt/phoenix/

[8] http://jasperforge.org/sf/projects/jasperreports

[9] http://www.quiee.com.cn/sample/sample_list.htm

[10] http://www.lucas-lee.com/#

[11] http://www.hibernate.org

[12] http://www.dom4j.org

 

   

分享到：

软件项目解决方案模板 | 一个基于JExcelAPI的轻量级报表框架

• 2008-12-06 23:41
• 浏览 2631
• 评论(3)
• 查看更多

评论

3 楼 freelancer 2009-02-01  

This is very difficult to maintain this code

好像更习惯的用法是： It's very difficult to maintain this code (很小的毛病，呵呵 )

2 楼 haiquan81 2008-12-22  

strong points  weak points， 这样的英语不太地道吧， disadvantages, advantages 比较好吧

1 楼 zzq230 2008-12-07  

感觉lz写的很专业,不知道lz现在哪里工作?