Practical phpxlsx

Working with templates

Introduction

Making a spreadsheet from scratch is a complex task when, for example, you search for a very elaborate visual detail of the file or to customize its content.

To simplify the spreadsheet creation process, phpxlsx allows to use templates. In phpxlsx a template is an XLSX file, which contains one or multiple variables to be replaced.

To work with templates you have to use the class CreateXlsxFromTemplate, which provides all necessary methods to modify spreadsheets.

Template methods replace placeholders in the sheet target (sharedStrings XML content). To replace placeholders in other scopes such as headers, footers or comments, the target option is available.

Setting up the template

By default, phpxlsx uses the symbol '$' to wrap the variables to be replaced. Therefore, to define a variable with the name SIGN, add it to the spreadsheet by writing $SIGN$.

If you prefer to work with another symbol, call the method setTemplateSymbol. For example, to use '#' as symbol for templates, apply the code:

Instead of using just one symbol, '${ }' can also be used to wrap placeholders:

Loading the template

Create a new object using the class CreateXlsxFromTemplate:

Now, let's replace the contents of this $xlsx object.

Texts

The easiest element for replacing a placeholder is a string. replaceVariableText is the method to be used:

The available parameters are:

  • $variables: placeholders to be replaced
  • $options: it's an array which can have these keys:
    • 'target': sheets (default), headers, footers, comments

For example, replace VAR_1 and VAR_2 placeholders with new texts:

Images

Images can be set to be replaced adding a placeholder in the alternate text option. In order to do this, just right click on it, open the image properties and search for the field alternate text (this name may vary in each version of MS Excel). Type in this field the name of the placeholder, for example: $IMAGE$.

To replace an image, use the replaceVariableImage method:

The available parameters are:

  • $variables: placeholders to be replaced
  • $options: it's an array which can have these keys:
    • mime: forces a mime type (image/jpg, image/jpeg, image/png, image/gif)
    • streamMode: if true, uses src path as stream. PHP 5.4 or greater needed to autodetect the mime type; otherwise set it using mime option. Default is false
    • target: sheets (default), headers, footers, comments

For example, replace the image with placeholder $IMAGE_1$:

Adding new contents

The CreateXlsxFromTemplate class inherits CreateXlsx, so all methods available in this class can be used to add new contents, change settings and other tasks when using XLSX templates.

Code sample:

Removing placeholders

Sometimes there's no need to replace every placeholder, and some of them remain in the template. To erase them, call removeVariableText:

The available options are:

  • $variables: array of placeholders to remove
  • $options: it's an array which can have these keys:
    • target: sheets (default), headers, footers, comments

For example, to delete the placeholders HEADER_TITLE and HEADER_CUSTOM in the headers target:

Image placeholders can be removed using removeVariableImage.

Tips and Tricks

How to retrieve variables with getTemplateVariables: this method informs at any time which placeholders haven't been replaced. getTemplateVariables sends back an array with the variables by target.

Working with prefixes allows to carry an extensive control of every placeholder, divided by groups of work, and even permits to classify them by substitution priority. This makes easier its further replacement and deletion.

Template methods replace placeholders in the sheet target (sharedStrings XML content). To replace placeholders in other scopes such as headers, footers or comments, the target option is available.

Next - Images