Configuring Excel templates

In this JungleDocs 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. Click Advanced Options:

  4. In the new window specify the 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  the 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 entering   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  the 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  our  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  auto-populated  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 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.

What this will look like in the document template:

Generated document:

Did this answer your question? Thanks for the feedback There was a problem submitting your feedback. Please try again later.

Still need help? Contact Us Contact Us