JSON to doc fill

Mail Merge Excel to PDF and Word documents

Lack of a decent approach


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 and not suitable if your excel has 100s of records. Hence, many opt to build a custom solution using the Acrobat SDK. They are maintenance heavy.

  • Adobe Indesign: This is the most popular approach. To use it, you have to create brand new Indesign document templates junking the existing ones. This is a tedious and time-consuming job.

    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.


Hence, you would see most users convert PDF templates to word, perform a mail merge and convert docx files back to PDFs. This wastes lot of productivity and results are less than satisfactory.

Mail Merge Excel to PDF



EDocGen - Excel to PDF examples


With EDocGen, you get a complete solution that covers every document creation scenario ranging from simple to very complex. You can use generate PDF documents from Excel data using

  1. Word templates
  2. PDF templates
  3. Fillable PDF templates

Non-IT users can use existing Word/PDF templates as-is. They can create/edit templates in their favorite Word or PDF editors to add dynamic tags. Following examples show how to go about Excel to PDF document generation.

Excel to PDF with Word Template


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, you can download them from here.

Sample-Template

Sample-Excel


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.


Description


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


Many-to-one-Template

Many-to-one-Excel

Excel to PDF with non-editable PDF Template


Open your PDF template in Adobe editor or any PDF editor of your choice. There are also numerous free online PDF editors. Add the tags to your template, the way we did in Word template. Upload that PDF template into EDocGen. Rest of the document generation is the same as described above.


Excel to PDF with Fillable Template


Open your PDF template and add the dynamic tags in the fillable portion. Upload that PDF template into EDocGen. Then start the generation of PDF documents in the same way we went about with Word template.


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.

Sample-Template

Sample-Excel



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