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)

  • 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)

You can use condition to show or hide contents based on your data. Example If you are creating an invoice you want display CASH if the payment_mode is cash, else if the payment_mode is 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}}

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

You can use the condition in three ways

  • if statement.

  • if - else statement.

  • if - else if - else statement.

Below are 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 logical operator AND.

If status is open AND priority is high.

Hiding Empty Sections & Lines

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

Name: {{name}}
{{#if address}}Address: {{address}}{{/if}}

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

{{name}}{{#if address}}
{{address}}{{/if}}
{{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.