PDF Filler

Tool to support bulk PDF Mail Merge / PDF Filler using data in Excel spreadsheet and sending of generated PDF files to email recipients defined in the same spreadsheet.

Use Cases

• PDF Mail Merge<p> Create PDF files by merging records retrieved from an Excel spreadsheet into PDF form fields in one or multiple PDF form files.
• Bulk Email<p> Email generated PDF files to one or multiple email recipients. Subject line and email body are customizable using spreadsheet records.
• Secure PDF<p> PDF documents may be encrypted and can be decrypted using a master key or record-specific key.

Installation

Download and install the latest Java SE Runtime Environment 11.

Download the latest released version pdffiller-0.1.3.zip of the PDF Filler application and unzip it in the location of your choice.

The folders of the distribution has the following structure:

  pdffiller-<version>
       |-- bin
       |-- conf
       |-- lib
       |-- licenses

The bin folder contains the Linux and Windows pdffiller shells that are used to invoke the application from the command line. The lib folder contains the PDF Filler jar file and all jars of the utilized third-party open source libraries. The conf folder contains optional configuration definitions for logging, the version file, and the license file. The folder licenses contains the licenses of the third party libraries this application depends on at run-time.

The application can be used after defining a project and calling the pdffiller command with the proper parameters as described here.

User Guide

Project Structure

A Mail Merge project has the following folder structure:

  project folder
   |
   |-- config
   |    |---- configuration file (*.properties)
   |
   |-- sources
   |    |---- PDF Template files (*.pdf)
   |    |---- Mail body text Mustache templates (*.mustache)
   |    |---- Spreadsheet (*.xlsx)
   |
   |-- generated

The project folder name can be chosen but the name of the child folders sources and generated is defined by default. The configuration child folder should have the name config. The config folder contains the project configuration file. The sources folder contains PDF templates, text templates, and spreadsheets with the records to be merged. Generated PDF files are placed in the generated folder.

Project Configuration

A configuration file defines all non-confidential settings of the application. Confidential information such as the email account password or the master PDF encryption key are not included and must be passed as parameters of the application command line interface.

Basic PDF Mail Merge Configurations

This section contains the configurations required to create PDF files from PDF form templates.

Configuration Key | Mandatory | Description :-------------------| :-------- | :-------------------------------------- excel.file_name | Y | Name of the Excel xslx spreadsheet in the sources folder that contains the records to be merged. For each record one or multiple PDF files are created according to the configuration. excel.sheet_name | Y | Name of the sheet in the excel spreadsheet with the records. excel.secret_column | Y | Column in selected input sheet that contains the PDF encryption key. excel.group_columns | N | Columns that have have the same values for each group of records. The first column is used as the group criterion. file.name_template | Y | Text template using Mustache syntax to create the name of the generated file. The system variable {{_BaseName_}} contains the template file name without the postfix (e.g. '.pdf'). file.group_name_template | N | Text template using Mustache syntax to create the name of the generated file if it contains grouped records. Only use the system variable {{_BaseName_}} or a reference to an Excel column referenced in the excel.group_columns property. template.<alias> | Y | Defines a name of a PDF form template located in the sources folder. The name is associated with the alias <alias>. Multiple PDF files may be defined, each with a different alias name <alias>. mappings.<alias> | N | Defines for PDF template with alias name <alias> a list of column mappings <form field> : <sheet column> that is used to map excel sheet columns to PDF form fields.

The option to map PDF form columns to spreadsheet columns is useful if you need to map data to PDF forms for which you cannot define the column names.

:heavy_exclamation_mark: Please note that PDF files without a form or a file with a non-PDF type will not be used to generate a derived PDF document. Instead, the original document will be included into emails. This feature is helpful for emailing unmodified documents in addition to generated ones.

Example Configuration

excel.file_name     = GSSB Raw Results.xlsx
excel.sheet_name    = Testergebnisse
excel.secret_column = Key
file.name_template  = {{_BaseName_}} - {{Name}}.pdf

The configuration determines that the source of the mail merge operations is a sheet called 'Testergebnisse' in the Excel spreadsheet 'GSSB Raw Results.xlsx'. The encryption key is extracted column Key of the current excel sheet row. The named of the generated files are defined to begin with the base name of the PDF form template, which is expressed by {{_BaseName_}} followed by ' - ' and the record's value of the Name column, which is expressed by the Mustache expression {{Name}}.

template.pdf1 = AATG Raw Score.pdf
template.pdf2 = AATG Gold.pdf
template.pdf3 = AATG Silver.pdf
template.pdf4 = AATG Bronze.pdf
template.pdf5 = AATG Achievement.pdf
template.pdf6 = AATG Participation.pdf

mappings.pdf2 = Text1 : Name, Text3 : Level, Text4 : LehrerIn, Text5 : Schule
mappings.pdf3 = Text1 : Name, Text3 : Level, Text4 : LehrerIn, Text5 : Schule
mappings.pdf4 = Text1 : Name, Text3 : Level, Text4 : LehrerIn, Text5 : Schule
mappings.pdf5 = Text1 : Name, Text2 : Level, Text3 : LehrerIn, Text4 : Schule
mappings.pdf6 = Text5 : Name, Text6 : Level, Text7 : LehrerIn, Text8 : Schule

The configuration defines six PDF form documents with alias names pdf1 through pdf6. PDF forms pdf2 through pdf6 define mappings between PDF form fields and Excel columns. For example, Excel column Name is mapped to PDF Form field Text1 for pdf2 through pdf5 and to PDF form field Text5 for pdf6.

Advanced PDF Mail Merge Configuration for PDF Form Choices

The PDF Filler application supports data-driven decision for the selection of a PDF form using the choice configuration. For example, students may receive a certificate based on a PDF form that reflects the level of accomplishment.

PDF Filler selects a PDF form using the value of a spreadsheet column and the name supplied to the '_BaseName_` is either the base name of the selected PDF form file or the name that is supplied in the configuration.

Multiple PDF files may be generated based on separate sets of choice configurations. Each group of choice definitions must use the same choice.<name> key prefix.

Configuration Key | Mandatory | Description :------------------------- | :-------- | :-------------------------------------- choice.<name>.select | Y | A list of selection criteria <value> : <alias>. <value> defines the expected value and <alias> is the PDF file alias defined in the template.<alias> configuration. choice.<name>.selectcolumn | N | Column in the spreadsheet that defines the value used for selecting a PDF Form template. The default value is Template. choice.<name>.basename | N | Is used as the value of the system variable _BaseName_. The default value is the template file name without its type postfix.

:heavy_exclamation_mark: PDF form templates are always processed if they are not referenced in any choice definition.

Example Configuration

choice.certificate.selectcolumn = Award
choice.certificate.basename     = AATG Certificate
choice.certificate.select       = Goldurkunde:pdf2, Silberurkunde:pdf3, Bronzeurkunde:pdf4, Achievement:pdf5, Participation:pdf6

The configuration defines a choice configuration named 'certificate' that selects one of the five PDF forms pdf2 through pdf6. The value that the decision is based on is located in column Award of the sheet. For example, pdf2 (mapped to PDF form 'AATG Gold.pdf') is selected if the sheet column for the current record contains the value 'Goldurkunde'.

The definition of the basename with value 'ATTG Certificate' in conjunction with the previously defined PDF file name expression {{_BaseName_}} - {{Name}}.pdf implies that all generated PDF files have a name that begins with 'ATTG Certificate - ' followed by the value in the records name column.

PDF form pdf1 is not used in the choice configuration and therefore is always used to create a new PDF document per record.

Grouping Records and Merging Into one Document

By default, PDF Filler processes the source spreadsheet row-by-row to create PDF documents and to send them to a set of email recipients. It is sometimes desirable to create documents using multiple related records and send documents created from related records in one email to the recipients.

For example, it may be desirable to send documents related to multiple students of the same family in one email message to their parents. Some documents may be created once for each student and other documents may be created once per family.

One supported use case is the student enrollment notification for a family, which makes it necessary to deliver registration information once per family instead of once per stud