Excel File(XLSX)
This section helps you to generate dynamic Excel Spread Sheets.
Last updated
Was this helpful?
This section helps you to generate dynamic Excel Spread Sheets.
Last updated
Was this helpful?
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}}.
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.
Docupilot supports a variety of merge fields for you to use.
Tokens (Merge Field)
Condition (if-else
)
Iterating inside a cell
Tables
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}}.
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.
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"
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.
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:
Multiple entries inside a cell can be incorporated by using the following syntax.
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
.
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.