Create Excel document from template

If you want to automate the generation of purchase orders in your company, this article will help you achieve that. After going through it, you will know how to create an Excel document from an XLSX template with the help of Processes, a Plumsail Documents tool for document generation.

Processes provides a user-friendly intuitive interface for creating documents from templates, converting them, and delivering to different systems for further management.

With its help, we’ll create a purchase order from a template. This is how the result document will look at the end:

create xlsx from template

Let’s go through each step from the very beginning.

Configure Process

First, register or log in to your Plumsail account.

Create new process

Add a new process by simply clicking on the Add Process button. It will show two options - Create from blank and Create from template. Let’s create from blank.

Give a name to the Process to recognize it later. As we’re going to create documents from an Excel template, select XLSX for the template type.

create-xlsx-process

Configure template

Once you’re done with the first step Create Process, press the Next button, and you’ll proceed to the next step – Configure Template.

It includes two substeps:

  • Editor;

  • Settings.

In Editor, you can compose the template from scratch or upload a local file from your computer. It’s also possible to modify the uploaded template online.

Feel free to download an XLSX purchase order template that we have already prepared.

purchase-order-template

Then upload it to the process.

upload template file

Templating syntax

When creating your own templates, mind the templating language.

Plumsail Excel XLSX templates use a different approach than most other templating solutions. It uses a minimal amount of syntax to make your work done.

Read this article to get familiar with the templating engine.

In short, the templating engine thinks that everything between these {{ }} brackets is variables to which it will apply your specified data. In our case the most basic example would be {{orderDate}} and {{orderNumber}} tags. They let the engine know that we want to render the purchase order’s number and date.

But of course, we can implement a more complex scenario. In our template, we refer to properties inside a collection of products. For that, we use nested tags with a dot operator:

  • The {{product.name}}, {{product.quantity}}, {{product.price}}, {{product.cost}} tags get the name, quantity, price, and total cost properties in each product object.

The templating engine is smart enough to understand that we refer to properties inside a collection. That is how it knows what content to duplicate. It will iterate through all objects in the array to render them and add the rows automatically.

You can learn more about table rendering here.

Please also note that we are using these formulas to calculate the total cost for each item individually and for all of the items:

  • =[Quantity]*[Unit Price] - for each item

  • =SUM(D14) - for all of the items

Test template

Click on the Test template button to see how the resulting document will look. You will see the dialog where you can fill in the auto-generated testing form. Form fields are created based on tokens from your document template. You can adjust the look of the testing form by changing token types.

test-template-xlsx

To proceed to the Settings substep, press Save&Next.

There you’ll set the following parameters. Descriptions are under the picture.

configure XLSX template

Template mode

It is Testing by default. It means you won’t be charged for this process runs, but result documents will have a Plumsail watermark. Change it to Active to remove the watermark.

Output filename

Use tokens to make it personalized. They work the same way as in the template. For instance, we use the following tokens to define the output file name - {{orderNumber}}. As a result, we’ll receive a purchase order marked with its number - Purchase order 432.

Output type

By default, it is the same as your template’s format. In this particular case, it’s XLSX. And we kept it to create the Excel XLSX document from a template.

Test template

You can test the template from here as well - to check how the customized settings will appear in the resulting document. The procedure is the same as we’ve already described above.

Delivery

The next step is delivery. For demonstrating purpose, we’ll store the result file in OneDrive. But there are other options.

You need to connect to your OneDrive from the Plumsail account. After that, set the folder’s name where to save the ready document. Here you can use tokens as well.

create XLSX from template

You can configure as many deliveries as you need.

Start Process

Now everything is ready, and you can start generating Excel XLSX documents. The step Start process will show available options with a description for each.

start process to create Word from template

You can start the process :

Hint

Use Power Automate Flow and Zapier to connect the process with other apps. It enables you to gather data from one app and pass on to the process to populate an XLSX template. Thus, you can populate the XLSX template from various web forms, CRM systems, SharePoint lists, and thousands of other web applications.