News

JETT 0.11.0 (Beta) Released

New in JETT 0.11.0:

  • Ticket #68: If a "<" character is inside the body of a tag, JETT had mistakenly parsed it as the beginning of a tag and thrown a TagParseException. This is now parsed correctly.
  • Ticket #71: Create a tag to set the value of a variable. JETT now has the new set tag to assign a value to a variable in the beans map.
  • Ticket #74: The "findCellStyleWithFont" method didn't cache a font object if the font was not found and a new one is created. Now it does cache it.
  • Ticket #76: The "rows" and "cols" attributes on the image tag had no effect and weren't working. This is now fixed in JETT.
  • Ticket #77: Performance issues, calling "copyColumnWidthsRight" when a rightward looping tag is embedded in a downward looping tag. Now JETT will only set column widths on the first iteration of the inner, rightward looping tag.
  • Ticket #78: Extraneous blank cells created upon processing of nested jt:forEach tags. The logic has been updated and now this has been resolved.
  • Ticket #79: Implicit Sheet cloning fails if the collection contains exactly one element. There was an off-by-one error in the logic. This has been resolved.
  • Ticket #81: Add a "page break" tag. JETT now has the new pageBreak tag
  • Ticket #82: In JETT Formulas, a cell reference doesn't get updated unless there is a space in front of punctuation/operators. The JETT formula parser has been fixed to recognize the case of a new cell reference following a comma, a right parenthesis, or a double-quote character.
  • Ticket #85: Use a logging library instead of printing to System.err. JETT is now using Log4J 2, version 2.9.0. Because Log4J 2 requires JDK 1.7, now JETT requires at least JDK 1.7 as well.
  • Ticket #86: Existing hyperlinks in a template spreadsheet are not shifted, copied, or moved. JETT had ignored existing hyperlinks in a template spreadsheet; now they are shifted, copied, and moved when appropriate.
  • Ticket #87: Add an attribute evaluation method for non-negative doubles. Now AttributeUtil has a method for validating that an attribute contains a non-negative double value.

View a history of all changes at the Change Log.

Overview

JETT (Java Excel Template Translator) is a Java 7 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.14. 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.2.
  • 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
  • Log4J 2 (2.9)
    • log4j-api-2.9.0.jar
    • log4j-core-2.9.0.jar