Excel Smart Documents

Templates in MS Excel that are rendered as Smart Documents.

Installation

Follow the general installation instructions to install the alani and alanioffice modules.

Make sure that BS Office is activated in the license settings.

Smart Preset Configuration

Getting started

Let’s start with a simple template in Microsoft Excel:

Excel Template

Attribute IDs or Technical Attribute Names are placed between two dollar signs and will be dynamically replaced with the content from the PIM or MAM. Other content in the template will remain in the generated document.

We upload this template file to the MAM.

Then we create a new Smart Document Preset. The type of the preset should be Excel. We can also select a sample product to test the template.

Smart Preset Configuration

Next, we select the template file in the Template Options.

Smart Preset Configuration

If we render the document, it might look like this:

Resulting Document

Label, price, and image have been inserted at the top of the document. Below that, a Flex Table with 4 columns was added.

Configuration Options

Since some of the content does not fit into the cells we can adjust the column width to fit with the content:

Smart Document Preset Configuration
Resulting Document

We can also display the price with the currency. Note: This will cause the content to be inserted as text.

Smart Document Preset
Resulting Document

We change the appearance of the images by:

  • Setting the height to 200

  • Selecting a media preset (this one will change the color space of the images to grey)

Furthermore, we change the context selection to add one Worksheet for every child of the selected context.

Note: We have changed the three options at the bottom here
Resulting Document

Image Placeholders

You can use an image as a placeholder in a template and define the image attribute in its alt text. The image will then be dynamically replaced by the referenced image.

Excel Template with Image Placeholder

Adjusting the cell size to the size of the inserted images

In the settings, you can define if the cell size should be adjusted so that it has the same size as the inserted images. If you are using templates with a fixed layout where cell sizes must not change you can switch this option off.

Smart Document Preset

Flex Tables

Next, we want to have a brief look at the appearance of Flex Tables. To illustrate this we simplify the template file:

Excel Template

To visualize what styles we got from the Flex Table we select “Highlight Unknown Styles“ in the settings.

Smart Preset Settings

The generated document reveals that we have been using an empty style template.

Resulting Document, showing missing styles

We add the highlighted style names to the style template. The document with the styles could look like this:

Excel Style Template (used for styling Flex Tables)

We upload the style template to the MAM and select it in the configuration.

If we now generate a fresh document we can see the styles applied to the individual table cells:

Resulting Document

Preset Placeholders

Preset placeholders are placed between two question marks.

Excel Template

They appear in the preset settings. When the document is generated they will be replaced by the content defined in the “Dynamic Preset Fields“ configuration.

[Note: If you want the styles from the template to remain in the document don’t use the Richtext Option]

Smart Preset Editor for Dynamic Preset Fields

Important:
Because we don’t want the preset placeholder to be overwritten by the Flex Table above it, we also check the “Insert Rows for Flex Tables“ option.

Smart Preset Configuration
Resulting document

Function Placeholders

The requirement for this is that a custom PHP Smart Template is used. Functions for placeholders can be programmed in this template, which can then be used in the Excel template.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 <?php CS::includeFile(CSAlaniOffice::getModuleBaseDirectory() . 'api/BSExcelSmartTemplate.php'); /** * The class must be placed inside [...]/plugins/smart/[TemplateName]SmartTemplate.php (naming convention for Smart Templates) */ class CustomExcelSmartTemplate extends BSExcelSmartTemplate { /** * Sample implementation of a function placeholder * * @param CSSmartPreset $oPreset The Smart Document Preset * @param CSItemApiItem $oItem The context item * @param int $iLanguageID Context language ID * * @return string */ protected function executeMyFunction($oPreset, $oItem, $iLanguageID) { return $oPreset->getLabel() . ' --- ' . $oItem->getLabel() . ' --- ' . CSLanguage::getLanguage($iLanguageID)->getFullName(); } protected function executeDate($oPreset, $oItem, $iLanguageID) { return date('d.m.Y'); } }

The template above implements the executeMyFunction and executeData functions. The functions use the Smart Preset, the item and the language ID as parameters as context information. The functions must return a String value.

We can use both placeholder functions without “execute” and between “%%” in the Excel template:

Excel Template

Finally, we have to reconfigure the Smart Preset to both the Custom Smart Template (which we have implemented in PHP in the first step) and the Excel Template:

Smart Preset configuration from Custom PHP Template
Resultung document

Content depending on other Content

You can make a row dependent on whether an attribute in the PIM/DAM has a value.

To do that you can write {{dr: ATTRIBUTE_IDENTIFIER}} anywhere in that row. If that attribute does not have a value the entire row will be removed. Otherwise, {{dr: ATTRIBUTE_IDENTIFIER}} will be removed from the document.

This feature might, for instance, be used for a heading that should only be displayed if the content following it actually has a value.