Excel exports via JXLS templates
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.
| Name | Definition | Description |
|---|---|---|
| asDate | Date 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. |
| asNumber | Number 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. |
| attributeShortDescription | String attributeShortDescription(String attributeId, String languageId) | Returns shortDescription of an Attribute for appropriate language. If ShortDescription is not available in language then returns FallbackDescription. |
| attributeUoMDescription | String 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. |
| attributeUoMSymbol | String 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. |
| attributeValue | String attributeValue(Product product, String attribute) | Returns evaluated value for attribute with no language. |
| attributeValueDescription | String 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. |
| attributeValueDescriptionJoining | String 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. |
| attributeValueJoining | String attributeValueJoining(Product product, String attribute, String separator) | Returns evaluated values joined by separator or empty string if attributeValue not found. |
| attributeValues | List attributeValues(Product product, String attribute) | Returns list of raw attributeValue domain models matching attribute. |
| attributeValueLanguage | String 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. |
| attributeValueLanguageJoining | String 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. |
| price | Price 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. |
| prices | List 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. |
| productRelationType | ProductRelationType 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. |
| relatedProductsJoined | String 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. |
| relatedProductIdsJoined | String 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 'Yes' in case attribute value is true and 'No' in n case it's false. In case values are not maintained at all, an empty string is exported:
${attributeValue(product, "isNew")=='true' ? 'Yes': attributeValue(product, "isNew")=='false'?'No':''}
Simple export of products
Template:
This template exports basic information like product-id and descriptions into a single sheet.
Complex multisheet export of products
Template:
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.