JSON to doc fill

Convert Excel to PDF - Create PDF docs from Excel

Create PDF docs from Excel Data


There are multiple ways to create PDF documents from Excel data. Let's briefly examine them.

  • Acrobat: 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.
  • 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, creating an Indesign document is a huge pain.
  • PDF APIs: Most of the APIs force you to create the template in their proprietary designer tools. These designers offer only limited features and don't support all the document needs

With EDocGen, you can use your existing Word/PDF templates for PDF document generation. Business users can create/edit templates in their PDF editors without IT intervention. It's a DIY system for Excel to PDF document generation.

Start Excel to PDF Merge






Excel to PDF Example


The following example display how to go about the population of Excel data into 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 word template and Excel file because of your browser's security settings, you can download them from here.

Sample-Template

Sample-Excel



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 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 is 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 wpuld 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


Trouble Shooting

  • Unable to upload template

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