Assessment for Excel Solutions in Java
There are following options available for writing the Excel spreadsheets through Java based APIs for web and non-web applications: –
- Jakartas POI
- Office writer
- eSeExcelFromJDBC from Actuate
- Invoking Excel COM objects by using native Java API
- By using Excel objects in Java
- Changing the content-type of HTML into Excel
Each one of these alternatives is discussed in detail below.
The alternatives have been accessed on the following standpoints:
- Performance at runtime
- Resource utilization in terms of memory and CPU time.
- Suitability to the In-home application in terms of data size and response time
- Jakartas POI:
POI can be used for applying various formats in Excel, retaining the data type per column data. This option will allow performing numeric options in created excel. The existing application works perfectly fine up to a specific data volume using POI. Beyond a specific data limit it fails giving java.lang.OutOfMemory Exception. The reason for this is, since POI uses File handling, resource utilization in terms of memory and CPU time is high.
This API has similar features to Jakarta POI. This API also uses File handling and hence has high resource utilization in terms of memory and CPU time. The Excel created using this API will render the data in the default font, and will display the numbers to 3 decimal places. In order to supply formatting information to Excel, we must make use of the overloaded constructor, which takes an additional object containing the cell’s formatting information, which results in extensive memory consumption.
- Office writer:
Office writer uses ExcelWriter for writing Excel files. ExcelWriter generates a file from a template spreadsheet and a data source. ExcelWriter enters data source values in template fields and preserves all content and features included in the template, but ExcelWriter cannot change the template’s structure, formatting, or static content. ExcelWriter inserts data source values in the spreadsheet vertically. ExcelWriter cannot insert data horizontally. ExcelWriter cannot insert data into a non-cell object such as a sheet name, a print title, a cell comment, or a hyperlink. ExcelWriter does not calculate formula values. The main problem with this alternative is there need to be as many number of template files as are the types of content to be rendered. This becomes a bottleneck especially when the content is decided at runtime (the templates need to be chosen at runtime).
- eSeExcelFromJDBC from Actuate:
The excel template and the content to be rendered as excel is abstracted. The template file is then applied on the content at runtime by encapsulating it in an excel book object. Thereafter the object is written to a physical file as a byte-stream. The main problem with this alternative is also that it needs to have as many number of template files as are the types of content to be rendered. This becomes a bottleneck especially when the content is decided at runtime (the templates need to be chosen at runtime).
- Invoking Excel COM objects by using native Java API:
The COM objects of MS excel is invoked through the Java application by using native Java calls.
This has the following limitations:
1. The COM DLL has to be registered on the server. So, it always requires a Windows OS and cant work on non-windows platforms.
2. Supports only excel 5.
- By using MS Excel objects in Java:
These are specific to the MS SDK for Java. The APIs cannot be used on the Sun JDK. Is a MS specific solution and cannot be used where the deployment is on Linux.
- Changing the content-type of HTML into Excel:
Using this alternative data can be written into an excel sheet like a CSV. In this option, formatting like bold and alignment can be applied but it would be same for all columns. Formatting depending on the data type cannot be applied for eg: Numeric data type cannot be retained with format number.
Even though some basic formatting can be applied with this option, this option has an overhead of the data volume that the JSP will hold while loading. If the data volume is very large it will take more time for the JSP to load. With current data volume in In-Home, this option will not prove to be beneficial from the performance standpoint.