Excel exports via JXLS templates

Last modified by Joel Schmalöer on 14.09.2022, 13:07

General information

jXLS is a small and easy-to-use Java library for writing Excel templates to generate customer specific exports.
With jXLS you can generate an Excel in a user-friendly way with built-in & customized helper functions.

It is possible to change the formatting of each cell/column/row as needed. WYSIWYG.
The library allows you to immediately generate small and simple exports, as well as more complex ones.

Large files with more than 1 million records per sheet are also supported with jXLS.

jXLS standard documentation http://jxls.sourceforge.net/index.html

Control structures like loops and conditions are implemented via comments, while raw data is written in the cells.

Features

  • Multiple sheets output
  • Native Excel formulas
  • Excel comments mark-up for command definition
  • Custom Command definition

Functions to access product data

These helper functions provide easier access to products.

NameDefinitionDescription
asDateDate asDate(String value)Returns Date what makes it possible to display the result formatted based on the cell type (i.e. Short Date, Long Date). The passed string has to have the "yyyy-MM-dd" format. If the passed value is not a date, an empty string will be returned.
asNumberNumber asNumber(String value)Returns Number what makes it possible to display the result formatted based on the cell type (i.e. Number). If the passed string contains a fractional number, a decimal point has to be used as a separator. If the passed value is not a number, an empty string will be returned.
attributeShortDescriptionString attributeShortDescription(String attributeId, String languageId)Returns shortDescription of an Attribute for appropriate language. If ShortDescription is not available in language then returns FallbackDescription.
attributeUoMDescriptionString attributeUoMDescription(Product product, String attributeId, String languageId)Returns shortDescription of the UoM referenced in the Attribute Type Extension of the given Attribute for appropriate language or empty string if attributeUoMDescription not found.
attributeUoMSymbolString attributeUoMSymbol(Product product, String attributeId)Returns the symbol of the UoM referenced in the Attribute Type Extension of the given Attribute or empty string if attributeUoMSymbol not found.
attributeValueString attributeValue(Product product, String attribute)Returns evaluated value for attribute with no language.
attributeValueDescriptionString attributeValueDescription(Product product, String attribute, String language)Returns value list item description that corresponds to attribute value for appropriate language. If there is no description for the language, a fallback description will be returned (description for empty language). If the fallback description is missing, an empty string will be returned. If the attribute assigned to the product is multi-valued and has several values, all values will be returned separated by commas. If the attribute assigned to the product doesn't have a value list, evaluated value will be returned.
attributeValueDescriptionJoiningString attributeValueDescriptionJoining(Product product, String attribute, String language, String separator)Returns value list item description that corresponds to attribute value for appropriate language. If there is no description for the language, a fallback description will be returned (description for empty language). If the fallback description is missing, an empty string will be returned. If the attribute assigned to the product is multi-valued, but has only one value or the attribute is not multi-valued, a single value without a separator will be returned. If the attribute assigned to the product doesn't have a value list, evaluated value will be returned.
attributeValueJoiningString attributeValueJoining(Product product, String attribute, String separator)Returns evaluated values joined by separator or empty string if attributeValue not found.
attributeValuesList attributeValues(Product product, String attribute)Returns list of raw attributeValue domain models matching attribute.
attributeValueLanguageString attributeValueLanguage(Product product, String attribute, String language)Returns evaluated attribute value for appropriate language. If there is no value for the language, a fallback value will be returned (value for empty language). If the fallback value is missing, an empty string will be returned. If the attribute assigned to the product is multi-valued and has several values, all values will be returned separated by commas.
attributeValueLanguageJoiningString attributeValueLanguageJoining(Product product, String attribute, String language, String separator)Returns evaluated attribute values joined by separator or empty string if AttributeValue not found. If the attribute assigned to the product is multi-valued, but has only one value or the attribute is not multi-valued, a single value without a separator will be returned.
pricePrice price(Product product, String contractId, String typeId, String currencyId, Integer validFromQuantity)Returns Price domain model matching search criteria fields: contractId, typeId, currencyId, validFromQuantity. If null is passed instead of value, then search by that field is not performed. Returns null if there is no Price matching criteria.
pricesList prices(Product product, String contractId, String typeId, String currencyId, Integer validFromQuantity)Returns list of Price domain models matching search criteria fields: contractId, typeId, currencyId, validFromQuantity. If null is passed instead of value, then search by that field is not performed.
productRelationTypeProductRelationType productRelationType(String typeId)Returns ProductRelationType domain model by typeId. If null is passed instead of value, then search by that field is not performed. Returns null if there is no Product Relation Type matching criteria.
relatedProductsJoinedString relatedProductsJoined(Product product, String relationTypeId, String separator, boolean reverseRelations)Returns "(" + RelatedCatalogID + "," + RelatedProductID + "," + RelatedVariantID + ")" + Separator + .... If null or '*' are passed instead of relationTypeId, then all Related Product will be included in the result string.
relatedProductIdsJoinedString relatedProductIdsJoined(Product product, String relationTypeId, String separator, boolean reverseRelations)Returns string of Related Product ids. If null or '*' are passed instead of relationTypeId, then all ids of Related Product will be returned. In the case 'reverseRelations' parameter is true, Reverse Relation will be used.

Beside these functions, which are available for the JXLS templates, you can find all available fields in the export feed documentation, e.g. here, or in the template editor in PXM.

Examples

  • 'Translating' boolean attribute values 
    Following example will export 'Ja' in case attribute value is true and 'Nein' in n case it's false. In case values is not maintained at all, an empty string is exported:

     ${attributeValue(product, "isNew")=='true'  ? 'Ja': attributeValue(product, "isNew")=='false'?'Nein':''} 

Simple export of products

Template: ProductExportTemplateJXLS.xlsx

This template exports basic information like product-id and descriptions into a single sheet.

Complex multisheet export of products

Template: ProductsRaw.xlsx

This template exports almost all product information into separate sheets. It is based on the "Products (Raw) Excel export."

The template covers iterations over all the product data, sorting of products by catalog and product-Id and almost all available fields.

Currently not covered by the export are compatibility information and product relation descriptions. Also the The sheet "ClassificationGroupAssoc" must be renamed to import it using the "Excel (Raw)" format.