JSON to doc fill

Mail Merge Excel to PDF and Word documents

Create PDF docs from Excel Data

There are multiple ways to create PDF documents from Excel data. Most of them offer limited functionality and don't allow you to use existing templates as-is. Let's briefly examine them.

  • Acrobat 3rd Party Plug-ins: If you have the full version of Acrobat, there's is an import data option under Tools. The biggest problem with this approach is, you can only create one document at a time for each row. This is as good as manual filling. Hence not suitable if your excel has 100s of records. Hence, many opt for 3rd party plug-ins or build a custom solution using the Acrobat SDK. They are maintenance heavy and comes with a lot of limitations

  • Adobe Indesign: This is the most popular approach. It accepts only CSV or TXT data source files for the population. So you can't populate your Excel as is. Moreover, it's more suitable for small data sets and simple merge.

    To use it, you have to create brand new Indesign document template junking the existing template. This is a tedious and time-consuming job.

Hence, you would see most users convert PDF templates to word, perform a mail merge and convert back to PDFs. We all know the limitations of mail merge as well. With EDocGen, you get a complete solution that addresses every business document creation. Moreover, it ensures template governance and you can be sure that everyone in the organization is using the latest templates and digital assets.

You can use your existing Word/PDF templates for PDF document generation. Business users can create/edit templates in their favorite Word or PDF editors without IT intervention. It's a DIY system for complex and high-volume Excel to PDF document generation.

Mail Merge Excel to PDF

Excel to PDF Example

The following example shows how to go about the population of Excel data into Microsoft Word templates for generation of PDF and Word documents.

The sample template has dynamic text, tables, and hyperlinks. In case you're unable to view the below sample MSWord template and Excel file because of your browser's security settings, you can download them from here.



Excel to PDF Document Generation Excel to Word Document Generation

In the Excel file, notice matching headers with dynamic tag names of the template. For example, "Enter_Name" is present in both Excel and template. Though you can upload any Excel with any header names, having matching headers allows you to skip the mapping screen (2nd step of the population). Thus this saves time if you're planning to populate the same Excel file format again-and-again into your template.

For "linkname" dynamic field, there is no corresponding data in Excel. In such cases, the generated documents display just blank in the place of the dynamic field.


The template has text, table, and hyperlink dynamic fields

Text : {Enter_Name} and {Enter_Email} are dynamic text fields. EDocGen treats text placed in "{}" as dynamic text field.

Table : The Job table has Role, Company and Address columns. Table starts with {#tablename} and ends with {/tablename}. So you notice {#Job} in the first column and {/Job} in the last column. Column values are text fields. EDocGen loops over the JSON to populate a row into the Job table.

Hyperlink : The dynamic data passed to the hyperlink tag would be displayed as a link in blue color. You can use it to display a URL or text that links to another URL. EDocGen treats text placed in "{~}" as hyperlink. In the template {~linkname}{~Enter_Name} are dynamic hyperlinks.

Show text based on Conditions (If-Else): If the Enter_Name value is "Steve", we are showing some text. Similarly, you can also test for boolean for hiding/displaying data.

Excel file population

You can use both PDF and Word templates with EDocGen. Edit them in your favorite editors to add tags and upload into EDocGen. While you can also modify your templates inside the App as well, it is suited more for minor changes.

After uploading, hover-over document template and click “Create Document” action button. This opens a side-screen and in the drop-down select “generate documents from Excel".

Select your Excel file and upload it. After this step, EDocGen prompts you to select the sheet from which you want to populate data and the orientation of Excel file data. EDocGen supports both vertically and horizontally aligned Excel data.

Excel population

The second step of Excel to PDF files creation is data mapping. If your template has a large number of dynamic fields, mapping each one of them to corresponding Excel headers, is time-consuming. If Excel header names match with the template's dynamic fields, EDocGen automatically maps them. This allows you to skip the mapping screen during documents creation, saving you time.

Mapping screen

Clicking on the generate button after selecting PDF as output format creates bulk documents in zip format. They would be mailed to your registered email id and you can also find them in the output folder.

Two special kinds of populations, which you can easily do with EDocGen.

Vertical mail merge

Typically calculations involving summing of various fields are written from top to bottom. This vertically aligned data can be populated into the template using EDocGen. All you've to do is select the orientation of data after uploading excel data.

Much like normal headers, all the fields in the first column would be treated as headers and values in subsequent columns would be treated as data.

Grouping/ Many-to-one merge

The first step is to modify the Excel to group the rows and use that to populate the template. EDocGen is intelligent to understand those groupings to perform both many-to-one mail merge and one-to-one mail merge in the same template.

Please refer to below examples on how to go about the grouping of rows.



Additional Example

Often, you find a need to populate repeat Excel data (loop) into Word/PDF template. Below Excel and Word template files produce a multi-row table and a list.



In the template, you would see the tags {#Job}{/Job}. This tells the system that it's a loop. You can create your own markers {#Marker}{/Marker}.

Trouble Shooting

  • Unable to upload template

    It may be due to the wrong dynamic tags. Just make sure you're closing every tag properly and only uses the tags that are supported by EDocGen