JSON to doc fill

PDF Generation from Excel Data - Convert Excel to PDF

Limitations of Popular Approaches

If your organization is creating a large number of fillable PDF forms or read-only PDF documents, automating their generation saves time and money. 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 PDF or Word templates as-is.

If your templates have nested loops (Loop inside a loop), or nested tables almost all popular PDF mail merge tools don't support them. Moreover, they are slow. If you're looking to generate a few thousand PDF documents in real-time, none of those solutions work for you. Hence, you see most users convert PDF templates to word, perform a mail merge and convert DOCX files back to PDFs. This wastes a lot of productivity and results are less than satisfactory. Even this approach doesn't support special cases such as many-to-one or directory merge.

Convert Excel to PDF

EDocGen - Mail Merge Excel to PDF

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 XLSX data using

  1. Word templates
  2. Read-only PDF templates
  3. Fillable PDF templates (PDF form)

Non-IT users can use existing Word/PDF templates as-is for conversion. They can create/edit templates in the editor of their choice to add dynamic tags. The system merges XLSX data into these templates for PDF files generation.

The generated documents can be saved into a local machine or mailed to individual recipients. They also can be synced to DMS or cloud storage.

The following example shows how to go about Excel to PDF document generation. The below sample template has dynamic text, tables, and images.

Excel to PDF Document Generation

Excel to Word Document Generation

Excel Description

This Excel file creates two documents for - David and Steve. In Excel, notice headers match with the tag names of the template. For example, "Enter_Name" is present in both Excel and template. Though your Excel can have 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.

If there is no corresponding data in Excel, the generated documents display just blank in the place of the dynamic field. Use conditional statements, if you want to hide that.

Want to draw your attention to the Excel headers related to the template's Job table. They start with the table name Job_. The system then understands that these columns need to be populated into the PDF template's Job table.

Template Description

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 like text fields. The system loops over the Excel data to populate Job table.

Image: The URL of the image location is passed to the Image tag for display. {%photoimg type="image"} is the image tag. The dimensions are optional and the system auto-sizes the image if they are present. You can use the same for the population of QR Codes and Barcodes.

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.

How to convert Excel to PDF

Step1: Excel file population

You can use both PDF and Word templates with EDocGen. Edit them in MSWord/Acrobat editors to add tags and upload them to the system.

Then click the "Generate" button against the document template. From the modal window, select "Upload Data".

Select your Excel file and upload it by dropping the file or selecting it from your system. 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

Step2: Map fields

The second step of Excel to PDF generation is data mapping. If your template has a large number of merge 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

Step3: PDF documents generation

Clicking on the generate button in Step3, after selecting PDF as an output format creates bulk documents in zip format. The zip file would contain individual PDF files.

Step4: Documents Distribution

The PDF file distribution in step4 is optional. The system offers several distribution options for the generated documents.

If your Excel file has an email address column, email messages would be sent to them with individual file attachments. You can customize the message subject and body. The mails would be sent from your business email.

Apart from email, the tool allows you to sync generated files to SharePoint, Microsoft OneDrive, and Google Drive. You can also route it for E-Sign.

Unique Features

The system supports special scenarios for mail merging Excel to PDF.

Vertical PDF mail merge

Typically calculations involving the 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 spreadsheet 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

As you've seen above, the system is intelligent to understand the groupings. It performs both many-to-one and one-to-one mail merge in the same template.

In directory merge, the rows are grouped by first column values. Instead of creating one document for each row, a document is created for a set of rows. Below is the example Excel for the directory merge.

Excel to Word Document Generation

The system creates two documents for this XLSX file, one for David and another for Steve. For David's document, the Job table would have two rows. For Steve, the table would have only one row.

Nested Loops/Tables Example

Often, you find a need to populate Excel data to create lists, bullets, etc. Apart from regular loop creation, EDocGen supports nested lists (lists inside lists) and nested tables creation.

Below is an example template that shows how to create a list inside a table.

Template Package

You can populate a single Excel file into more than one template for creating a template package. Instead of mail merge Excel to PDF templates one by one and combine them, you can populate relevant templates with the source file in one go.

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 use the tags that are supported by the system.

  • Documents are not generated

    Make sure there are no spaces in the first column header. Example: If your column header is "Reference No", change it to "Reference_No". Otherwise, the system throws an error.

  • Documents are generated but dynamic field values are empty

    1. Map the dynamic fields to Excel headers during generation, if tag names are different from Excel headers

    2. If there's no value in the first column, the system ignores that row.

  • Date field formats in documents are different from Excel

    In the Excel cell containing the date value, use Text function. Ex: =TEXT(A1,"mm-dd-yyyy"), =TEXT(A1,"dd-mmm-yyyy")

  • How to create hyperlinks

    For hyperlinks, the system treats text placed in "{~}" as a hyperlink. In the template, you can have {~Enter_Email} to create a dynamic hyperlink.

Popular Posts