How to create a PDF report from Excel using Python

6 min readDec 18, 2021

If you are seeking ways to automate your excel data into PDF report output to dazzle your stakeholders, then you’ve come to the right place!

This blog post will guide you on how to make a PDF report consisting of a logo, an image along-with textual content of various sizes, shapes, and colors by sourcing information from an excel workbook. To perform this task I am going to use the FPDF library of Python so c’mon you all let's just hop right in…!

# Happy Reading

Why PDF reports?

PDF short for Portable Document Format as the name suggests helps you to transfer or move files swiftly with anyone; not needing to worry about the fonts, images, tables, and the formatting it contains. On the contrary, other MS suite file formats such as Word, PowerPoint, or Excel do not provide that level of ease. They often take time to load or consume a lot of space. A few more strong reasons one should favor the use of reports in PDF format over others are — PDFs are

Aesthetically pleasing
Reliable + Secured
Universal compatibility
Version independence
Professionally appealing

You can leverage complete code and learnings from this article to automate and generate PDFs as deemed fit for your requirement.

FPDF Python Library

FPDF is a library for PDF document generation under Python, ported from PHP. F from FPDF stands for Free: which means you may use it for any kind of usage and modify it to suit your needs. The library originally offers the following features -

Choice of measure unit, page format, and margins
Page header and footer management
Automatic page break
Automatic line break and text justification
Image support (JPEG, PNG, and GIF), colors and links support
TrueType, Type1, and encoding support
Page compression
Extensive Tutorial and complete online documentation

Installation

You can install FPDF from the command line using pip.

pip install fpdf

If you are using a jupyter notebook/Spyder, you can write the same line of code just replacing “pip” with “!pip” in the coding area. Another way would be to search this library in the Environments tab of Anaconda Navigator.

Sneak Peek

To fast forward you into the future, this is how our final output would look like. Created out of nothing, literally. You can do wonders with Python code, don’t you agree?

Let’s understand from scratch how I went about it.

Input Data

Starting with what goes in as input, I prepared dummy data points in excel by collating some information from Wikipedia and Britannica about the football club I follow -Arsenal as shown in output PDF, from the famous English Premier League.

The excel file has 8 attributes in the following order: year of foundation, Name of the club’s current stadium, current manager, club motto, list of trophies along with the years/seasons in which they were won, the nickname supporters widely known by, short summary of cabinet glory and a brief history of the club.

Ingredients and the Recipe

Moving to the nitty-gritty, now I will take you through each functionality I leveraged from the FPDF library. At first, you need to import the library and initialize it by assigning it to a variable which in our case named pdf.

from fpdf import FPDF
pdf = FPDF()

The FPDF constructor takes 3 parameters -

Orientation: Portrait(“P”), Landscape (“L”)

Unit: millimeters (“mm”), centimeter(“cm”), inches(“in”)

Format :“A3” ,”A4",”A5",”Letter”,”Legal”. You can also pass on a custom format for example -(WIDTH=100, HEIGHT=150)

If not given explicitly, the object assumes (“P”, “mm”, “A4”) by default.

add_page(orientation = '', format = '', same = False)

It does exactly what its name says, it adds a page to the document. It also takes 3 parameters. By default, it takes whatever is passed on to the constructor at the beginning. You can choose from the same options that are there in FPDF() for orientation and format. To Same, if True is passed it carry forwards settings of the previous page and other arguments are ignored.

set_font(family = "", style = "",size = )

Before exporting the output, we must set the font of the document. Otherwise, it will be invalid. You can specify below arguments for the listed parameters

Family refers to the font name and available case insensitive standard fonts are “Courier”, “Helvetica”, “Arial”, “Times”, “Symbol”, “ZapfDingbats”. You can also explore how to use custom google fonts.

Style: Bold (“B”), Underline (“U”), Italics (“I”), regular(“ ”). You can also specify a combination of two or more font styles like Bold+Underline as “BU”. Keep in mind bold and italic styles do not apply to Symbol and ZapfDingbats.

Size is nothing but font size. The default value is 12 points.

image(name, x = 0, y = 0, w = 0, h = 0, type = '', link = '')

I have used this one quite a lot to make our report interactive. The FPDF library supports JPEG, PNG, and GIF formats.

name is the path of the picture.

x is The distance of a point from the y-axis scaled with the x-axis also known as called abscissa or x coordinate of the point.

y is the distance of a point from the x-axis scaled with the y-axis and is known as ordinate. Both x and y start at the upper left corner of the page. W is width and h is height. All these four dimension parameters if not specified explicitly, automatically get calculated.

typer is the case-insensitive format of the image (i.eJPG, JPEG, PNG, and GIF. If not specified, the type is inferred from the file extension and link is URL or identifier returned by add_link

cell(w,h=0,txt='',border=0,ln=0,align = '',fill = False, link = '')
multi_cell(w,h=0,txt='',border=0,ln=0,align = '',fill = False, link = '')

The only difference between cell and multi_cell is multi_cell comes with automatic line breaks. Cell inserts rectangular text box in simple terms with border and text alignment options.

w: Cell width, h: Cell height, text: strings you want to print, border takes 0 for no border and 1 for a frame or it can take some or all combinations of “L”(left), “R”(right), “T”(top), “B”(bottom).

To align you can pass “L”(left), “R”(right), “C”(center), “J”( justification), and ln indicates where the cursor position should go to. Possible values for which are 0:to the right, 1:to the beginning of the next line, 2:below.

fill indicates if the cell background is to be filled with color(True) specified in fill_colur or make a transparent (False)and the link is URL or identifier returned by add_link

set_fill_color(r: int, g: int , b: int )
set_text_color(r: int, g: int , b: int )

Both of these take RGB components where r: red, g: green, and b: blue. You can specify any value between 0 to 255. text_color defines the color of text and fill_color is for filling up the background of the cell.

set_xy(x: float, y: float)

x takes abscissa and y ordinates. If provided negative values, the cursor would shift to the right bottom corner of the page relatively.

output(name = '', dest = '')

name: The name of the file. Only used when writing to a file and dest: Destination where the document to be sent

Complete Python Code

You can check out my Github repository to find all assets-Logos, Background Image, stadium wallpaper, and the input excel file I used for this exercise.

Stay tuned for the next article! #HappyLearning

References

Why PDFs are better?
FPDF documentation
Learn to add tables in PDF from excel (for you to explore further!)

Previously Published Articles -