The problem

These days I needed to create lots of .PDF (kinda certificates like the picture below) to be sign from my team. I had to generate PDFs from an excel file.

So I had basic 3 problems:

  1. Read .XLSX file
    • There are some information I have to put in the doc. So I create a XLSX which columns are these information and rows are items that will become a single PDF file.
  2. Create a .PDF template in python
    • The template has basically three things: image header; information footer and a default text in the middle. This text has the information read from XLSX.
  3. Write the PDF with the information from the XLSX
    • Finally, I iterate thru the the XLSX lines reading all the information, filling the text and saving the PDF.

1. Read .XLSX file

To read a file we must have a file. So I've created one with two generic items:

I used XLRD to read these 7 fields from .XLSX. I've just opened the file, selected the first sheet and started reading from the second line (because the first one is the title in my example above). Then I've just iterated the lines.

2. Create a .PDF template in python

For this template I used this pyFPDF library which is an old simples lib ported from PHP. All I had to do was define a header class and a footer class. In the header I defined the position of the image. In the footer I wrote some text. The code says by itself :P

3. Write the PDF with the information from the XLSX

Finally I wrote the PDF with a text filled from the information read from the .XLSX. I had to configure some sizes, types (bold or not) and alignment for each part of the text. I tried to put everything in relative position from the page sizes. It worked and that's the result from that .XLSX example:

Show me the code!!

# -*- coding: utf-8 -*-
Created on Sun Sep 22 19:29:43 2019

@author: Thomaz Maia

import xlrd 
from fpdf import FPDF

class PDF(FPDF):
    # Image header
    def header(self):
        imgSize = 35
        imgPosition = (self.w - imgSize)/2
        self.image(name='header.png', x=imgPosition, y=10, w=imgSize)

    # Information footer
    def footer(self):
        # Line
        self.set_draw_color(r=100, g=100, b=100)
        self.line(x1=30, y1=self.h-22, x2=self.w-30, y2=self.h-22)
        # Text
        self.set_text_color(100, 100, 100)
        self.set_font('Arial', 'I', 10)
        self.cell(w=0, h=5, txt='Instituto Federal do Ceará - Campus Maranguape', border=0, ln=1, align='C')
        self.cell(w=0, h=5, txt='CE-065 - Novo Parque Iracema, Maranguape - CE', border=0, ln=1, align='C')
        self.set_text_color(0, 0, 255)
        self.set_font('Arial', 'u', 10)
        self.cell(w=0, h=5, txt='www.ifce.edu.br/maranguape', border=0, ln=0, align='C', fill=False, link='http://www.ifce.edu.br/maranguape')

# Open .XLSX file and select 1st sheet
wb = xlrd.open_workbook("infos.xlsx") 
sheet = wb.sheet_by_index(0) 

for i in range(1,sheet.nrows,1): 
    pdf = PDF()
    # Get the real page width
    pgWidth = pdf.w - 2*pdf.l_margin
    # Create a new page (must have)
    # Configure title style
    pdf.set_font(family='Arial', style='B', size=14)
    # Set vertical position of the title
    pdf.cell(w=0, h=60, txt="DECLARAÇÃO DE MATRÍCULA", border=0, ln=1, align='C')
    # Configure text style
    pdf.set_font(family='Arial', style='', size=14)
    # Here is the text with those fields from .XLSX
    pdf.multi_cell(pgWidth, 6, 'Declaramos para os devidos fins que o aluno ' + sheet.cell_value(i, 0).upper() + ', CPF n° ' + sheet.cell_value(i, 1)  + ', matrícula ' + sheet.cell_value(i, 2) + ', encontra-se matriculado no campus Maranguape, no turno da ' + sheet.cell_value(i, 3) + ', com carga horária equivalente ao ' + sheet.cell_value(i, 4) + '° Período do curso ' + sheet.cell_value(i, 6).upper() + ' deste instituto.')
    # Here is the date
    pdf.cell(w=0, h=60, txt='Maranguape, ' + sheet.cell_value(i, 5), border=0, ln=1, align='R')
    # Set vertical position for the signature
    pdf.line(x1=55, y1=pdf.h-57, x2=pdf.w-55, y2=pdf.h-57)
    pdf.cell(w=0, h=5, txt='SECRETARIA DE ALUNOS', border=0, ln=1, align='C')
    # PDF output
    pdf.output(sheet.cell_value(i, 2) + '_' + sheet.cell_value(i, 0).upper() + '.pdf', 'F')