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 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}}

{{#if (not payment_mode)}}
Please Insert the preferred Mode of Payment
{{else}}
{{upper payment_mode}}
{{/if}} 

Note: The if/else statements work only when added inside a single cell. For example:

You can use the conditional statements in the following ways

• if

• if-else

• if-else if

• if-else if-else

• if-else if-else if

• if-else if-else if-else

• and so on...

Below are a few examples :

{{#if show_payment_details}} ACH to {{acNumber}} {{/if}}	prints the text ACH to xxxxxxxxx if show_payment_details is present and not false.
{{#if (payment_status == "paid")}} payment cleared {{/if}}	prints payment cleared if payment_status equals to "paid" (case-sensitive)
{{#if (payment_status != "paid")}} unpaid {{/if}}	prints unpaid if payment_status not equal to "paid" (case-sensitive)
{{#if (payment_status == "paid")}} paid {{else}} unpaid {{/if}}	prints paid if payment_status equals to "paid" otherwise prints unpaid
{{#if (payment_status == "paid")}} paid {{else if (payment_status == "initiated")}} payment initiated {{else}} pending {{/if}}	prints paid if payment_status equals to "paid" prints payment initiated if payment_status equals to initiated otherwise prints pending
{{#if (price > 1000)}} good deal {{/if}}	prints the text good deal if price is greater than 1000
{{#if (price >= 1000)}} decent deal {{/if}}	prints text decent deal if price is greater than or equal to 1000
{{#if (price < 1000)}} small deal {{/if}}	prints text small deal If price less than 1000
{{#if (price <= 1000)}} okay-ish deal {{/if}}	prints text okay-ish deal If price less than or equal to 1000
{{#if (payment_message contains "paid")}} Payment is cleared {{/if}}	prints Payment is cleared if payment_message contains the text paid (for example, this invoice has been paid contains the text paid in it)
{{#if (payment_message not_contains "paid")}} Payment is due {{/if}}	prints Payment is due if payment_message does not contain the text paid (for example, this invoice is not cleared does not contain the text paid in it)
{{#if (status == "open" or status == "on-hold")}} Payment is pending {{/if}}	prints Payment is pending if status is open OR on-hold
{{#if (status == "open" or status == "on-hold" or status == "under review")}} Payment is pending {{/if}}	prints Payment is pending if status is open OR on-hold OR under review
{{#if (status != "open" and status != "on-hold")}} Payment is not pending {{/if}}	prints Payment is not pending if status is not open AND not on-hold
{{#if (status == "open" and priority == "high")}} High priority open task {{/if}}	prints High priority open task if status is open AND if priority is high
{{#if (payment_status != "paid")}}Please Clear the Dues within 30 days {{/if}}	prints Please Clear the Dues within 30 days if the payment_status is not equal to paid
{{#if (status in "open,pending")}}In Progress... Please stay tuned for further updates. {{/if}}	prints In Progress... Please stay tuned for further updates. if status is sub-string of "open,pending"

Hiding Empty Sections & Lines

In the following example, when the address field has a value, it gives desired outcome. If the address is empty, it leaves an empty line in the output.

{{customer_name}},
{{#if address}}{{address}},{{/if}}
{{city}}, {{country}}

Data	Output
customer_name = John Doe city = NY country = US	John Doe, NY, US
customer_name = John Doe street = WinWood St. city = NY country = US	John Doe, WinWood St., NY, US

To hide empty lines, we should open the if condition in previous line or close it in next line. Here are examples on how the output will look like when street address is passed and when it is not:

Syntax	Data	Output
{{customer_name}},{{#if street}} {{street}},{{/if}} {{city}}, {{country}}	customer_name = John Doe city = NY country = US	John Doe, NY, US
{{customer_name}},{{#if street}} {{street}},{{/if}} {{city}}, {{country}}	customer_name = John Doe street = WinWood St. city = NY country = US	John Doe, WinWood St., NY, US
{{customer_name}}, {{#if street}}{{street}}, {{/if}}{{city}}, {{country}}	customer_name = John Doe city = NY country = US	John Doe, NY, US
{{customer_name}}, {{#if street}}{{street}}, {{/if}}{{city}}, {{country}}	customer_name = John Doe street = WinWood St. city = NY country = US	John Doe, WinWood St., NY, US

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 available 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.