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.
Getting started
Let’s start with a simple template in Microsoft Excel:
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.
Next, we select the template file in the Template Options.
If we render the document, it might look like this:
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:
We can also display the price with the currency. Note: This will cause the content to be inserted as text.
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.
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.
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.
Flex Tables
Next, we want to have a brief look at the appearance of Flex Tables. To illustrate this we simplify the template file:
To visualize what styles we got from the Flex Table we select “Highlight Unknown Styles“ in the settings.
The generated document reveals that we have been using an empty style template.
We add the highlighted style names to the style template. The document with the styles could look like this:
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:
Preset Placeholders
Preset placeholders are placed between two question marks.
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]
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.
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:
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:
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.
