Excel Templates

Our platform allows you to export Data in the layouts you want by using Microsoft Excel templates.  

If you already have Excel documents that you have been printing out to use as data capture Forms, this means that with a few minor changes you can reuse these as data templates.

This functionality works similarly to a Microsoft Word “mail merge” in that the system produces output based upon pre-formatted templates.  

The template contains the static (non-changing) text, images and formatting that you want to appear in the final output.  
It also includes placeholders for injecting where you want Form entry answers to go. 

For example, imagine you have an Inspection Form and you want to export the Inspection Data captured on the app as a standard letter.

You could create a template that sets out the standard text of the letter along with placeholders for where various Inspection answers should go. 

Want to jump straight into the deep end?  Take a look at our "kitchen sink" template file attached at the bottom of this article.

Adding a Template to a Form

1. First identify the Form that you wish to create a template for.
   If you don’t already have a Form, you can quickly create the Form using the Form Screen designer.
   
   
2. Next you’ll need a new or existing Word or Excel file.  
   If you already have Word/Excel files that you have been printing to use as data capture Forms, these can easily be reused as a template.
   
   
3. Go through your template and insert placeholders wherever you want the system to inject answers from data entries of your chosen Form.
   Placeholders are entered using the unique data name of the Form field, wrapped in curly braces – e.g. {{dataname}}
   
   So for example if your Form has a field with data name of “LastName”, then you can instruct the system to inject the answer for this field wherever the placeholder {{LastName}} appears.
   
   Any formatting (font, color, highlighting, size etc) that you apply to the placeholder will be honoured by the system and applied to the answer value inserted.
   
   Important Note:
   You may only have ONE placeholder or formula per cell.  Any other text in the cell will be discarded!
   
   
4. Once you have inserted all the placeholders needed, upload the template file to your Form screen.
   There are two places where you can add a template to a Form:
   1. Form Level
      

      If you add a template at this level, you are instructing the system to use this template as the default whenever a PDF is generated.

      Upload your template using the "Excel Template" field found in your Form's Settings page.

      
      

   2. Connector Level
   3. This is a template that is specific to one or more Connectors, but should not be used as a default.
      Upload your template using the "add data template" link found on appropriate Connectors in your Form's Connectors page.

NOTE: Uploading a template also makes new export options available on the Data area.

Multiple Sheets

The template engine supports multiples sheets in one Excel document. When outputting to a PDF, each sheet will start on a new page.

Testing Your Templates

Assuming you have at least one existing Form entry for your Form, you can test your templates fairly easily by using the Data area.

In the Data page, search entries against your Form and the version onto which you uploaded your template.

Once you have some Data results, hover over a row and click the Export option to get an instant look at your template output :)

Tips and Tricks For Great PDF Output

• We strongly suggest that you do not use textboxes or other floating field types.  These are not well supported by our template generation software.
  If you must use these, then ensure "Object Positioning" is set to be "Move but don’t size with cells" (this option is found in the Properties tab when you right click and go Format Picture on your floating image).
  
  
• Similarly we strongly recommend you avoid using Excel conditional display formatting in your templates.
  This is not officially supported by our template generation engine, and its quite possible that your templates may break in the future even if they work today.
  If your templates do break or stop applying conditional formatting, this will not be supported or considered a defect by us.
  You have been warned!
  
  
• You must put each individual page underneath each other.
  Do not place pages horizontally side by side.
  This is because our Excel to PDF generator always exports using the "Fit All Columns on One Page" option found in the Print settings of Excel.
  
  

• To get a fair idea of how your Excel template will export to PDF, turn on the following settings in Excel when creating a template: 

  • Set your Print setting to "Fit All Columns on One Page"
  • Under the View option in the ribbon, make sure Page Break Preview is turned on.
    
    

• This way you will see exactly what space you have to work with, where expected page breaks are, and also how the pages will be scaled (if necessary). 

• Manually inserting Page Breaks is a good way to control the page output of your template.
  
  
• Getting blank (or almost blank) pages?
  Despite adding your own Page Breaks, sometimes you may find individual pages are overflowing into another page (giving almost blank pages) when output as PDF.
  This is because our PDF generation does not scale exactly the same as Excel does.
  
  

  The slight difference in scaling often means that pages are rendered slightly taller by our PDF component when compared to Excel.

  As a result, the offending pages will run slightly over the space available on an A4 page in the PDF, resulting in the overflow being written to a new page.

  
  One way to fix this is to count the rows in the pages that are not overflowing, and then adjusting your offending pages to have the same or less rows.
  This assumes your rows are roughly equal in height.
  
  Another option is adjust the offending pages, making them shorter in height and moving up your manual Page Breaks.

• Automatic Page Breaks are also supported - you can get an idea of the PDF output in Excel by applying the following settings:
  In the Page Layout tab of your worksheet:
  • Ensure the Height setting is"Automatic" in the Scale to Fit area.
  • To be sure you have no manual breaks, you can go to Breaks -> "Reset all page breaks".

"Kitchen Sink" Example Template

If you're the kind of person that likes to jump straight into the deep end, then take a look at the attached xlsx file below :)

This is a "kitchen sink" example of most of our functionality for Excel data templates.

We strongly suggest you read the other topics in this section to understand how the functionality in the example template works.