JungleDocs | Configuring Excel templates

In this tutorial, you will learn how to:

  • create a library for Excel document templates

  • configure Excel templates by entering field names into spreadsheet cells

  • configure Excel templates by adding comments


Creating a library for Excel document templates 

In order to have a library with an Excel document template, you can add a content type that has an Excel document template assigned to it, or create a library that has an Excel document template.

  1. To create such library, open your site, click settings and Add an app:

  2. Select Document Library:

  3. And click Advanced Options:

  4. In the new window specify library name, other settings and select Microsoft Excel Spreadsheet as the document template:

  5. To start editing the template, navigate to the newly created library, run JungleDocs and you should see one base rule for the "Document" content type.
  6. Click Edit template:
  7. To access   sample   document, click Edit base rule, and it will be under "Document template":

    Sample document contains content controls for all available columns for the current content type. You can use these controls to automatically fill (mail merge) Basic report’s title, dates and other available metadata.


Modifying Excel templates

There are two ways to modify an Excel template:  by  ent ering   column  values as simple text into cells, or by adding a comment.

Method 1: entering field names into cells

Using this first method you simply enter the values into the cells that will later be replaced by data from your SharePoint. This is the quicker and simpler method of the two. However, note that documents created in this way will not be updateable once generated.

Note: If you plan on using "Update Document Content" functionality, we recommend using the second method of using comments.

1. To get started, copy the content controls from sample document to the document template:

Cells with values specified in {} brackets will be replaced by JungleDocs, if it finds corresponding columns in SharePoint.

Note: After figuring out the layout and exact cells in the document, it is very important to format the cells correctly. Microsoft Excel requires that cell type is consistent with the data that is put in it. Default cell type is "General" and if all cells are left this way, some data, like dates and numbers, might not be interpreted correctly. To avoid this, Excel cells must be the same type as SharePoint fields.

2. Right-click on the cell, click "Format cells" and format cell accordingly.

Dates in iour SharePoint are given in the m/d/yyyy format, therefore we must enter the same format in Excel. In this case it can be found under "Custom" cells.

3. Also format cells containing numbers to the correct number of decimals.

Method 2: adding comments

Another method to configure Excel document templates is by adding a comment on a cell that will be filled with data from SharePoint. These comments are only visible in the opened document and will not be printed. The comment must be inserted in the following format: =FieldName. This stops JungleDocs from parsing the actual comments that might be present in the document.

1. To get started add the comments on the cells by right clicking on them and clicking "Insert Comment":

2. Delete the autopopulated name ("Name Surname: ") in the comments field and write down the formula.

3. A red triangle in the top right corner of the cell means a comment is present for that cell. Red triangles will not be visible when the document is printed.

Note: After figuring out the layout and exact cells in the document, it is very importand to format the cells correctly. Microsoft Excel requires that cell type is consistent with the data that is put in it. Default cell type is "General" and if all cells are left this way, some data, like dates and numbers, might not be interpreted corectly. To avoid this, Excel cells must be the same type as SharePoint fields.

What this will look like in the document template:

Generated document:

Still need help? Contact Us Contact Us