Excel File(XLSX)

This section helps you to generate dynamic Excel Spread Sheets.

Getting Started

You can use Excel Spread Sheets ( .xlsx is the supported format ) as your document template, you can build your document in Ms Excel, Google Sheets or other supporting platforms and then upload it to Docupilot as a .xlsx file. Replace the info in excel sheet like client name, client company name to {{client.name}} and {{client.company}}.

Excel Spread Sheet (.xlsx) template overview.

Uploading XLSX

Updating XLSX

If you want to make changes to your Excel Spread Sheet make changes in MS Excel, Google Sheets etc , and upload it to Docupilot as a .xlsx file.

Types of Merge Fields

Docupilot supports a variety of merge fields for you to use.

• Tokens (Merge Field)

• Condition (if-else & unless-else)

• Iterating inside a cell

• Tables

Tokens (Merge Field)

These are simple fields embraced in {{}}. When you provide your custom data, Docupilot will replace these fields with the data. For example {{email}}, {{clientname}}, {{company_name}}.

If you want to group your fields you can use dot(.) while defining the tokens. For example your client has first name & last name, you can write the tokens as {{client.first_name}}, {{client.last_name}}.

Condition (if-else & unless-else)

You can use conditional statements to show or hide contents based on your data. For example, If you are creating an invoice, you want to display CASH if the payment_mode is cash, else if the payment_mode is the card you want to show CARD.

{{#if payment_mode "==" "cash"}}
CASH
{{else if payment_mode "==" "card"}}
CARD
{{else}}
Please Insert preferred Mode of Payment{{/if}}

{{#unless payment_mode "in" "cash, card"}}
Please Insert the preferred Mode of Payment
{{else unless payment_mode "==" "card"}}
CASH
{{else}}
CARD
{{/unless}} 

Note: The if/else & unless/else statements work only when incorporated in a single cell (i.e. the whole statement shall end in a single cell in an excel sheet).

You can use the conditional statements in the following ways

• if Statement.

• if-else Statement.

• if-else if-else Statement.

• unless Statement.

• unless-else Statement.

• unless-else unless-else Statement.

Below are a few examples :

Example	Description
{{#if payment_status}} {{/if}}	if payment_status is not empty or blank.
{{#if payment_status "==" "paid"}} {{/if}}	if payment_status equal to paid (case-sensitive).
{{#if payment_status "!=" "paid"}} {{/if}}	if payment_status not equal to paid (case-sensitive).
{{#if price ">" 1000}} {{/if}}	If price greater than 1000
{{#if price ">=" 1000}} {{/if}}	If price greater than and equal to 1000
{{#if price "<" 1000}} {{/if}}	If price lesser than 1000
{{#if price "<=" 1000}} {{/if}}	If price lesser than 1000
{{#if payment_status "contains" "paid"}} {{/if}}	If payment status contains paid
{{#if payment_status "not_contains" "paid"}} {{/if}}	If payment status does not contains paid
{{#if status "in" "open, on-hold"}} {{/if}}	This is similar to logical operator OR. If status is open or on-hold.
{{#if status "not_in" "open, on-hold"}} {{/if}}	This is similar to inverse logical operator OR. If status is not open or on-hold.
{{#if status "==" "open"}}{{#if priority "==" "high"}} {{/if}}{{/if}}	This is similar to the logical operator AND. If the status is open AND priority is high.
{{#unless payment_status}} {{/unless}}	Unless payment_status is not empty or blank.
{{#unless payment_status "==" "paid"}} {{/unless}}	Unless payment_status is equal to paid (case-sensitive).
{{#unless payment_status "!=" "paid"}} {{/unless}}	Unless payment_status is not equal to paid (case-sensitive).
{{#unless price ">" 1000}} {{/unless}}	Unless price is greater than 1000
{{#unless payment_status "contains" "paid"}} {{/unless}}	Unless payment status contains paid
{{#unless status "in" "open, on-hold"}} {{/unless}}	This is similar to the logical operator OR. Unless the status is open or on-hold.
{{#unless status "==" "open"}}{{#unless priority "==" "high"}} {{/unless}}{{/unless}}	This is similar to the logical operator AND. Unless status is open AND priority is high

Hiding Empty Sections & Lines

You can use If/Unless to hide sections/lines that do not have a value. Hide section example

1. Name: {{name}}
   {{#if address}}Address: {{address}}{{/if}}
2. Name:{{name}}   
   {{#unless address "==" ""}}Address: {{address}}{{/unless}}   

In the above examples when the Address is empty, the Address section will be removed. Hide empty lines examples

1. {{name}}{{#if address}}
   {{address}}{{/if}}
   {{city}}{{country}}
2. {{name}}{{#unless address "==" ""}}
   {{address}}{{/unless}}
   {{city}}{{country}}

In the above example when the Address is empty, the Address empty line will be removed to achieve better document formatting.

Iterating inside a cell

Multiple entries inside a cell can be incorporated by using the following syntax.

Iterating inside a cell in an Excel Sheet

Output for above input.

Tables

Format

The Format for inserting tables into a XLSX file or an Excel Spreadsheet is as follows.

{{table:ITEMS.PROPERTY}}

Example :

• {{table:line_items.name}}

• {{table:line_items.qty}}

• {{table:line_items.amount}}

Here for the field namedline_items we have name,amount and qty as the properties.

Example : In the below example we can see that there are fields Name,Price and Quantity in the table line_items.

Inserting a table in Excel Sheet.

Example output of a table in Excel Sheet.

Inserting a dynamic image

An image can be dynamically added into XLSX template from a URL. The URL should be a publicly avaialble URL. To add image dynamically, add the following syntax in one of the desired cells:

Syntax : {{insert_image image width="width" height="height"}}

Example: {{insert_image image width=300 height=300}}

If you want to automatically adjust the height to keep proportions, you can use: {{insert_image image width=300}}

Similarly to automatically adjust the width to keep proportions, you can use {{insert_image image height=300}}

The image inserted will be aligned to the cell top left corner.

The image URL needs to be publicly accessible so we can download and insert it in the document.

To insert images from google drive, reach out to our support.