JETT is similar to jXLS, because JETT performs the same function -- translating Excel template spreadsheets into spreadsheets with the desired data populated. Here, JETT 0.9.0 is compared with the latest version of jXLS, 1.0.6:
| Feature | JXLS | JETT |
|---|---|---|
| Excel File Formats Supported | 97-2003 (.xls), 2007+ (.xlsx) | 97-2003 (.xls), 2007+ (.xlsx) |
| Excel Spreadsheet API Used | Apache POI | Apache POI |
| Expressions Support | Apache Commons JEXL | Apache Commons JEXL |
| Tagless Export of Collections -- Implicit Collections Processing |
Supported; can be disabled, but only overall, not on a per-collection name basis. It can be controlled in the following ways, with metadata, e.g. ${collName.property}//:2, Label://collName. Include a "//collName" suffix to include cells in the repeating block that should be included, even though they don't explicitly reference the collection name in an Expression. Include a "//:n" suffix to indicate how many rows should be included in each repeating block. |
Supported; can be disabled on a per-collection name basis. It can be controlled in the following ways: Include a "?@" metadata suffix, plus key/value pairs, e.g. ${collName.property}?@extraRows=1;left=1;right=2.
|
| Tagless Export of Collections - Implicit Sheet Cloning | Not supported |
Supported; can be disabled on a per-collection name basis. It can be controlled in the following ways: Include a "$@" metadata suffix, plus key/value pairs, e.g. ${coll.prop}$@i=n;l=10.
|
| Formulas |
Supported, format $[formula(cellref)]. Optionally, provide a default value in case the cell references get deleted: $[formula(cellref)@defValue]. |
Supported, format $[formula(cellref)]. Optionally, provide a default value in case the cell references get deleted: $[formula(cellref||defValue)]. Additionally, create dynamic formulas with the formula tag. |
| Aggregate Expressions | Limited support, e.g. ${Func(field):collection}, where "Func" is one of "sum", "min", "max", "avg", and "count". | Supported, with jAgg integration, using Aggregate Expressions, e.g. ${jagg:eval(list, 'Func(field)')} -- and the agg tag. "Func" can be any one of 20 Aggregators built-in to the jAgg library, including "Sum", "Min", "Max", "Avg", "Count", and many more, or a custom Aggregator. |
| Merged Regions | Takes into account merged regions when shifting and copying cells. | Takes into account merged regions when shifting and copying cells. |
| Preserve Excel Features | Preserves Excel macros, charts, drawings, images. | Preserves Excel macros, charts, drawings, images, rich text strings. |
| Extracting Data From an Excel Spreadsheet | jXLS contains the jxls-reader module | Not supported |
| Transformer | ||
| Transformer Class (API Entry Point) | XLSTransformer transformer = new XLSTransformer(); | ExcelTransformer transformer = new ExcelTransformer(); |
| Transform (Basic) |
|
|
| Transform One Template Sheet Into Many Result Sheets |
|
|
| Fixed Collection Names | transformer. markAsFixedSizeCollection ("employees"); |
transformer. addFixedSizeCollectionName ("employees"); |
| Turn Off Implicit Collections Processing | transformer. setJexlInnerCollectionsAccess (true); |
transformer. turnOffImplicitCollectionProcessing ("employees"); |
| Evaluate all formulas after transformation | Not supported | transformer.setEvaluateFormulas(true); |
| Force formula recalculation upon opening | Not supported | transformer.setForceRecalculationOnOpening(boolean); |
| Tags | ||
| Tag Bodies |
All tags that have their end tag below their start tag must have both tags exist alone on their respective rows. The entire rows are removed prior to processing, and any content below the tags is shifted up. All tags that have their end tag to the right of their start tag must have both tags exist on the same row. The cells containing the tags are removed prior to processing, and any content to the right is shifted left. This can interfere with an attempt to preserve things like column widths. To display multiple rows of Collection output, repeating to the right, one must use one tag per row. |
All tags can coexist in the same cell as other content, eliminating the need to shift other content because of the removal of tag content. Many tags can explicitly set directionality, so that it is not dependent on where the end tag is, relative to the start tag. To display multiple rows of Collection output, repeating to the right, one can use one tag and explicitly state that the additional blocks should be copied right. |
| Looping Tag to Display a Collection |
jx:forEach tag
|
jt:forEach tag
|
| Looping Tag to Display Multiple Collections At Once | No such tag exists, but it is possible with implicit collections processing. | jt:multiForEach tag or implicit collections processing. |
| Loop Over Arbitrary Numbers | Not supported directly. To workaround, supply a Collection of numbers to the beans map and use a jx:forEach tag. | jt:for tag |
| Display Content Conditionally |
jx:if tag - Must have a body.
|
jt:if tag
|
| Grouping Excel Rows |
jx:outline tag, grouping rows only
|
jt:group tag, grouping rows or columns
It is also possible to create an Excel Group (Outline) around a dynamically determined range of rows or columns with looping tags such as "forEach". |
| Excel Comment Creation | Not supported |
jt:comment tag
Because JETT is built on Apache POI, creating Comments on a Sheet will most likely corrupt any pre-existing Charts or other Drawings, but only for .xls spreadsheets (97-2003 versions). |
| Hyperlink Creation | Not supported |
jt:hyperlink tag
|
| Dynamic Formula Creation | Not supported |
jt:formula tag |
| Dynamic Named Range Manipulation | Not supported |
jt:name tag |
| Dynamic Style | Not supported |
jt:style tag, where the "style" attribute controls:
... by specifying style properties directly, or by specifying a class name that is defined in a CSS-like file with the "class" attribute. |
| Expression Output | jx:out tag or ${expression} | ${expression} only |
| Hide Columns, Rows, and Sheets |
|
jt:hideCols, jt:hideRows, jt:hideSheet tags |
| Perform SQL-like "group by" Operations on Data | Not supported | jt:agg tag, which also supports super-aggregate operations such as rollups and cube |
| Tag Support for Aggregate Expressions | Not supported | jt:total tag |
| Perform Analytic Operations on Data | Not supported | jt:ana tag |
| Don't Transform an Expression | Not supported | jt:null tag, and also \${expression} |
| Other | ||
| SQL Execution in the Template | Supported, with the ReportManager bean. | Supported, with the JDBCExecutor bean. Also supported are "?" placeholders in the query, plus additional arguments passed as bind variable values. |
| Access to POI Objects in the Template | workbook, sheet, hssfRow | workbook, sheet, cell |
| Execution Hooks for Custom Processing | CellProcessor, PropertyPreprocessor, RowProcessor | CellListener, TagListener, TagLoopListener, SheetListener |
| Fixed Size Collections | Supported on a collection name basis | Supported on a collection name basis, plus the "fixed" attribute for looping tags, plus implicit collections processing. |
| Custom Tag Libraries | Not documented, but it is possible by supplying a subclass of Configuration to the XLSTransformer. This subclass registers a custom TagLib. | Supported, through the direct registration of a custom TagLibrary in the ExcelTransformer. |
| Expose JEXL Engine Properties | Not supported. | Silent, Lenient, and Debug flags are exposed. Controlling the size of the JEXL Engine parse cache is exposed. Adding JEXL custom namespace functions is exposed. |
While there are some features of JETT that do not exist in jXLS, and some features of jXLS that do not exist in JETT, there are lots of features that are common, but sometimes the syntax varies. Here is a guide comparing those commonly used features:
Here is an example of jXLS's "forEach" tag. Notice how separate rows are needed for the "jx:forEach" tag, and separate cells are needed for the "jx:if" tag. Each cell is less cluttered, but jXLS needs to perform extra shifting when removing the tags.
| Employee | Salary | Manager | ||
| <jx:forEach items="${employees}" var="employee" select="${employee.salary >= 900}" varStatus="status"> | ||||
| ${status.index + 1}. ${employee.lastName}, ${employee.firstName} | ${employee.salary} | <jx:if test="${employee.getManager() != null}"> | ${employee.manager.lastName}, ${employee.manager.firstName} | </jx:if> |
| </jx:forEach> |
Here is the equivalent JETT "forEach" tag. The cells with tags are more cluttered, but no shifting to remove tags is needed.
| Employee | Salary | Manager |
| <jt:forEach items="${employees}" var="employee" where="${employee.salary >= 900}" indexVar="index">${index + 1}. ${employee.lastName}, ${employee.firstName} | ${employee.salary} | <jt:if test="${employee.getManager() != null}" then="${employee.manager.lastName}, ${employee.manager.firstName}"/></jt:forEach> |
Both templates would, in their respective frameworks, yield the following transformation. Notice how Elmer Fudd, salary $800, is not present.
| Employee | Salary | Manager |
| 1. Stack, Robert | $1000.00 | |
| 2. Queue, Suzie | $900.00 | Stack, Robert |
| 3. Bunny, Bugs | $1500.00 |
Here is an example of jXLS's "forEach" tag. Notice how one "forEach" tag is needed per row.
| Employee | <jx:forEach items="${employees}" var="employee"> | ${employee.lastName}, ${employee.firstName} | </jx:forEach> | ||
| Salary | <jx:forEach items="${employees}" var="employee"> | ${employee.salary} | </jx:forEach> | ||
| Manager | <jx:forEach items="${employees}" var="employee"> | <jx:if test="${employee.getManager() != null}"> | ${employee.manager.lastName}, ${employee.manager.firstName} | </jx:if> | </jx:forEach> |
Here is the equivalent template in JETT, using only one "forEach" tag. Notice the use of the "copyRight" attribute to determine directionality.
| Employee | <jt:forEach items="${employees}" var="employee" copyRight="true">${employee.lastName}, ${employee.firstName} | ||||
| Salary | ${employee.salary} | ||||
| Manager | <jt:if test="${employee.getManager() != null}">${employee.manager.lastName}, ${employee.manager.firstName}</jt:if></jt:forEach> |
Both templates would, in their respective frameworks, yield the following transformation.
| Employee | Stack, Robert | Queue, Suzie | Fudd, Elmer | Bunny, Bugs | |
| Salary | $1,000.00 | $900.00 | $800.00 | $1,500.00 | |
| Manager | Stack, Robert | Bunny, Bugs |
Here is an example of jXLS's implicit collections processing. Notice the use of "//collName" and "//:2" on different cells to control the processing.
| Company: ${companyName} | Name://employees | ${employees.lastName}, ${employees.firstName}//:2 | Department Name://departments | ${departments.name}//:2 | ID: ${departments.id} |
| Salary: ${employees.salary} | Bonus: ${employees.bonus} | Number of Employees://departments | ${departments.numEmployees} |
Here is an example of JETT's implicit collections processing. Notice the use of metadata keys "extraRows", "left", and "right", all on one cell, to control the processing.
| Company: ${companyName} | Name: | ${employees.lastName}, ${employees.firstName}?@extraRows=1;left=1;right=3 | Department Name: | ${departments.name} | ID: ${departments.id} |
| Salary: ${employees.salary} | Bonus: ${employees.bonus} | Number of Employees: | ${departments.numEmployees} |
Both templates would, in their respective frameworks, yield the following transformation. Notice how the company name is not copied with the rest of the collections.
| Company: Whatsit | Name: | Stack, Robert | Department Name: | Application Development | ID: 101 |
| Salary: 1000 | Bonus: 200 | Number of Employees: | 3 | ||
| Name: | Queue, Suzie | Department Name: | Human Resources | ID: 102 | |
| Salary: 900 | Bonus: 300 | Number of Employees: | 2 | ||
| Name: | Fudd, Elmer | Department Name: | Quality Assurance | ID: 103 | |
| Salary: 800 | Bonus: 400 | Number of Employees: | 2 | ||
| Name: | Bunny, Bugs | ||||
| Salary: 1500 | Bonus: 500 |
Here is an example of the usage of the ReportManager in jXLS:
Connection conn = getConnection();
Map<String, Object> beans = new HashMap<String, Object>();
ReportManager rm = new ReportManagerImpl(conn, beans);
beans.put("rm", rm);
Workbook workbook = transformer.transformXLS(inputStream, beans);
| <jx:forEach items="${rm.exec('SELECT first_name, last_name, salary, title FROM employee')}" var="employee"> |
JETT's JDBCExecutor is very similar:
Connection conn = getConnection();
Map<String, Object> beans = new HashMap<String, Object>();
JDBCExecutor jdbc = new JDBCExecutor(conn);
beans.put("jdbc", jdbc);
Workbook workbook = transformer.transform(inputStream, beans);
| <jt:forEach items="${jdbc.execQuery('SELECT first_name, last_name, salary, title FROM employee')}" var="employee"> |
However, a JDBCExecutor also has the ability to run JDBC select statements that contain ? placeholders. There must be one additional argument passed to the execQuery method for each ?.
| <jt:forEach items="${jdbc.execQuery('SELECT first_name, last_name, salary, title FROM employee WHERE title = ?', myTitle)}" var="employee"> |