News

JETT 0.10.0 (Beta) Released

New in JETT 0.10.0:

  • Ticket #56: Rich text strings using the "automatic" Excel color crashes JETT. Now, JETT takes into account the "automatic" Excel color to avoid the crash.
  • Ticket #57: Two or more levels of nested forEach tags with a JETT Formula that refers to cells at least 2 levels deeper than the formula result in an incorrect Excel formula. There was a problem in the algorithm that JETT relied upon to keep track of cell updates across 2 or more levels of nested looping tags. The formula nesting level is now kept track in the TagContext object, and this bug is fixed.
  • Ticket #58: A "<" character inside a JETT Formula triggers a TagParseException when the TagParser attempts to parse it as a tag. Now JETT skips over JETT Formula text when scanning cell text looking for tags.
  • Ticket #60: Excel 2007+ (.xlsx) templates with Excel comments in them cause JETT to crash when it is cacheing pre-existing fonts when it first reads the spreadsheet. An unexpected color index, 81, was outside the normal range of Excel indexed colors, causing the crash. JETT now takes into account this index to avoid the crash.
  • Ticket #65: Using a lot of span tags, especially in a loop, causes an IllegalStateException from Apache POI because the maximum number of cell styles was exceeded. This was caused because JETT didn't cache the new CellStyle objects, resulting in an excessive number of similar or identical cell styles. The span tag is now caching any new cell styles it creates, so it can find that same existing cell style later.
  • Ticket #67: Create a tag that can supply a dynamic image to the resultant spreadsheet. JETT now has the image tag.
  • JETT is now tested using Apache POI 3.14. JETT now requires Apache POI 3.14 (or greater).

View a history of all changes at the Change Log.

Overview

JETT (Java Excel Template Translator) is a Java 5.0 API that allows speedy creation of Excel spreadsheet reports using Excel spreadsheet templates.

JETT is built on top of the Apache POI library, which gives almost total control to the developer over the reading, creation, and modification of Excel spreadsheets. This includes the very tedious, verbose, and error-prone process of coding cell and text formatting, including font, color, highlighting, borders, alignment, header/footer, etc.

JETT is also built on top of the Apache Commons JEXL library (Java Expression Language), which allows expressions to be evaluated easily.

Additionally, JETT uses the jAgg library (Java Aggregations) to perform aggregate operations, such as Sum and Average, and super-aggregation operations, such as rollups and cubes, and display the calculations in the spreadsheet. It also uses jAgg to perform analytic operations, such as Lag, Lead, and RatioToReport.

JETT allows the developer to follow the MVC pattern easily. JETT is the Controller, the developer creates the Model, and either the developer or another party controls the Excel spreadsheet template, which acts as the View.

To summarize JETT, the developer supplies an Excel spreadsheet template, which provides the structure, layout, and styling of the resultant spreadsheet. Then, the developer creates beans that contain the actual data to be populated in the spreadsheet. Finally, the developer uses an ExcelTransformer, the JETT API entry point, to transform the template spreadsheet into the resultant spreadsheet, complete with the desired data, which can then be published.

Main Features

  • Integration with Apache Commons JEXL 2.1.1 (Java Expression Language). This allows for easy creation of expressions that are substituted with model data in the form of beans at runtime.
  • Integration with Apache POI 3.12. JETT is built on top of the extensive Apache POI library with which it controls sheet transformations.
  • Integration with the SourceForge project jAgg 0.9.0. JETT uses this library to evaluate Aggregate Expressions, a JETT-based extension to JEXL that allows aggregate expressions to be evaluated, such as Sum, Count, Avg, etc.
  • Built-in Tag library that allows XML-like tags to be placed in template spreadsheets, allowing such simple programming-like constructs as “if”, “for”, “forEach”, and more.
  • Looping tags support “copy down” and “copy right” behavior for the display of Collections.
  • Custom Tag Libraries may be coded and supplied to JETT for execution.
  • Like Apache POI, JETT attempts as much as possible to keep existing Excel features intact, including Charts, Macros, etc.
  • The CellListener interface allows dynamic, custom processing of Cells, such as alternate row highlighting, value manipulation, etc.
  • Merged cell regions are shifted and copied as appropriate.
  • Row heights and columns widths are respected as best as possible.
  • Template sheets may be dynamically cloned and separately evaluated in the resultant spreadsheet.
  • Multiple collections may be implicitly processed using a single loop.
  • It is possible to implement and display adjacent, or side-by-side, lists.
  • Supports binary Excel (.xls) and XML Excel (.xlsx) documents.
  • RichTextStrings are supported, even within Expressions.
  • JETT gives access to POI Objects in JEXL Expressions, which in turn allows access to Workbook and Sheet properties, including the Header & Footer, and PageSetup objects.
  • Excel Formula generation support.
  • JDBC Query Execution inside the template.

Requirements

JETT requires the following libraries:

  • Apache POI 3.14
    • poi-3.14-20160307.jar
    • poi-ooxml-3.14-20160307.jar
    • poi-ooxml-schemas-3.14-20160307.jar
    • Apache POI, in turn, depends on the following library: XML Beans 2.6.0.
  • XML Beans 2.6.0
    • xmlbeans-2.6.0.jar
  • Apache Commons JEXL 2.1.1
    • commons-jexl-2.1.1.jar
    • Apache Commons JEXL 2.1.1 in turn depends on Commons Logging 1.1.1.
  • Apache Commons Logging 1.2
    • commons-logging-1.2.jar
  • SourceForge's jAgg 0.9.0
    • jagg-core-0.9.0.jar
  • JUnit 4.8.2 (for testing only)
    • junit-4.8.2.jar
  • HSQLDB 1.8.0.10 (for testing only)
    • hsqldb-1.8.0.10.jar