How to set the header or footer of a spreadsheet

About this article

This article will explain what the header and footer setting is and provide examples of how it can be utilized with the binding syntax in smart templates.  

• What is the header and footer setting? 
• How to set the header and footer setting in a smart template
• Header and footer examples

 

Prerequisites

Library and at least one Dynamics module enabled Templafy Desktop and Office VSTO add-in installed on the PC Tenant admin/owner access or Space owner access

 

What is the header and footer setting? 

The header and footer allows to input specific text, numbers, or dates in the header or footer of an Excel spreadsheet. The header and footer setting can be set statically or dynamically by utilizing the binding syntax. 

 

The header and footer setting is only supported in Excel for files meant to be stored in the Spreadsheets Library.

 

Images cannot be inserted with the header and footer setting.

 

How to set the header and footer setting in a spreadsheet 

When building the template settings and properties, the Header Footer setting can be utilized to set the header and footer for the smart spreadsheet. Setting the Header Footer setting can be achieved by following these steps: 

1. In the Template Designer, navigate to the Advanced tab.
2. Click + Add property.
3. Select Header Footer in the Type dropdown.
4. Select the Header Footer Type:
   • allHeader: apply to all headers of the spreadsheet.
   • firstHeader: apply only to the first header of the spreadsheet.
   • evenHeader: apply to all even headers of the spreadsheet.
   • oddHeader: apply to all odd headers of the spreadsheet.
   • allFooter: apply to all footers of the spreadsheet.
   • firstFooter: apply only to the first footer of the spreadsheet.
   • evenFooter: apply to all even footers of the spreadsheet.
   • oddFooter: apply to all odd footers of the spreadsheet.
5. Enter the required binding in the desired field:
   • Left Section value: apply the value to the left section of the header/footer.
   • Center Section value: apply the value to the center section of the header/footer.
   • Right Section value: apply the value to the right section of the header/footer.
6. Optionally, set an additional option to the fields:
   
   • Disable updates: Disallow the Left Section value / Center Section value / Right Section value value from being updated after its generation when edited by the user through the document content updater. This option will not be visible if the document content updater is not enabled on the smart template. 
7. Click Add to document:
   
   • The Header Footer setting will be added to the smart template.

 

Once the setting has been created, it can be edited or deleted. Several Header Footer settings can be created. The formatting (alignment, bold, of the Header Footer can be achieved by adding the relevant Header and Footer Codes with the binding in Left/Center/Right Section value fields.

 

Header and footer examples

 

Example 1

Using the Header Footer setting to set the left section of the header based on the Name field originating from the user profile.

Binding	{{UserProfile.Name}}
Input	UserProfile.Name = "Jane Doe"	UserProfile.Name = "John Doe"
Output	"Jane Doe"	"John Doe"

 

Example 2

Using the Header Footer setting input the disclaimer in the right section of the footer based on the "Classification" dropdown question originating from the response form 

Binding	{{Form.Classification.Name}}
Input	Form.Classification.Name = "Internal"	Form.Classification.Name = "External"
Output	"This is an internal disclaimer"	"This is an external disclaimer"

 

Example 3

You can use Header and Footer Codes to apply formatting to the text in the header or footer.
For example:

• &B: Turns bold text on or off.
• &E: Turns double-underline text on or off.
• &I: Turns italic text on or off.
• &S: Turns strikethrough text on or off.
• &U: Turns underline text on or off.
• &X: Turns superscript text on or off.
• &Y: Turns subscript text on or off.
  
  
• &"fontname": Shows the text that follows in the specified font. Include the double quotation marks.
• &K<color>: Shows the text that follows in the specified color. Use a hexadecimal color value.
• &<nn>: Shows the text that follows in the specified font size. Use a two-digit number to specify the size in points.

 

Using the Header Footer setting to set the left section of the header based on the Name and Title fields originating from the user profile and formatting the Name to show in bold text using &B:

Binding	&B{{UserProfile.Name}}&B - {{UserProfile.Title}}
Input	UserProfile.Name = "Jane Doe" UserProfile.Title = "Sales Manager"	UserProfile.Name = "John Doe" UserProfile.Title = "Product Manager"
Output	"Jane Doe - Sales Manager"	"John Doe - Product Manager"

 

The code &B shows the text that follows in bold. Use &B again to return back to normal text. If you want to show a single ampersand ('&') then use the code &&.

 

Example 4

In example 3 only &B is used, but you can also use multiple codes at the same time (see examples of codes listed in example 3).

In Template Designer on the Form tab, click + Add question and create two Text form fields named 'Title' and 'Project ID'. Then click Add to form.

In the Header Footer setting in the Right Section Value field use this binding:

&24&U&"Segoe UI"&K8B5DDB{{Form.Title}}&U&"Arial"&K000000
&08&B&"Arial Narrow"Project ID:&B&"Arial" {{Form.ProjectID}}
&8&B&"Arial Narrow"Author:&B&"Arial" {{UserProfile.DisplayName}}

 

Explanation:

Line 1: &24 sets the font size to '24', &U makes the text underlined, &"Segoe UI" sets the 'Segoe UI' font to be used and &K8B5DDB sets the font color to purple. Then the value of {{Form.Title}} is shown. The second &U turns underline off, &"Arial" changes the font to 'Arial' and &K000000 sets the font color to black.

Line 2: &08 sets the font size to '8', &B makes the text bold, &"Arial Narrow" sets the font to 'Arial Narrow'. Then the plain text 'Project ID:' is shown. The second &B turns bold off, &"Arial" changes the font to 'Arial' and then the value of {{Form.ProjectID}} is shown.

Line 3: &08 sets the font size to '8', &B makes the text bold, &"Arial Narrow" sets the font to 'Arial Narrow'. Then the plain text 'Author:' is shown. The second &B turns bold off, &"Arial" changes the font to 'Arial' and then the value of {{UserProfile.DisplayName}} is shown.

 

The result looks like this:

 

Note that between each code no space is used. A space can be inserted but it will be shown in the output. For example if a space is inserted after the first &U the output will show a '_' before the title because the space is underlined.

 

When a user creates a spreadsheet (with the title 'hello world') then the result looks like this:

 

 

Related articles

What is a response form? What are functions, conditions, and operators? How to create a smart template with Template Designer Configuration of form fields in the User Profile