Add table to pdf file with style by using data different sources in Python ReportLab

Interactive Google Colab Practice Notebook

Practice building ReportLab PDF tables using multiple data formats (CSV, Excel, JSON, XML, SQL dumps, SQLite, and MySQL databases) directly inside Google Colab.

View on GitHub

All records of Student table in PDF file

Adding tabular data to a PDF file by adding table with option and styles using Python report lab

Adding Table to PDF File 🔝

Importing libraries and setting the path to create the PDF file:

from reportlab.lib.units import inch
from reportlab.lib.pagesizes import letter
from reportlab.platypus import SimpleDocTemplate
from reportlab.platypus.tables import Table, TableStyle, colors
# from my_table_data import my_data # import the data
my_path = 'output/student_table.pdf' # path, file name

Data

The variable name is maintained as my_data so data sources can be easily swapped:

my_data = [
    ['ID', 'Name', 'Class', 'Mark', 'Gender'],
    (1, 'John Deo', 'Four', 75, 'female'),
    (2, 'Max Ruin', 'Three', 85, 'male'),
    (3, 'Arnold', 'Three', 55, 'male')
]

Creating a SimpleDocTemplate object:

my_doc = SimpleDocTemplate(my_path, pagesize=letter)

rowHeights, colWidths, repeatRows

c_width = [0.4 * inch, 1.5 * inch, 1 * inch, 1 * inch, 1 * inch]
t = Table(my_data, rowHeights=20, repeatRows=1, colWidths=c_width)

When unsure about the number of columns, you can fix a common width for all columns:

c_width = [1 * inch]

setStyle()

BACKGROUND and FONTSIZE are set here.
(0,0): (column=0, row=0) Top-left cell
(-1,0): (column=-1, row=0) Top row, rightmost column
(-1,-1): (column=-1, row=-1) Bottom-rightmost cell

marking of rows and columns in table of PDF file

t.setStyle(TableStyle([
    ('BACKGROUND', (0, 0), (-1, 0), colors.lightgreen),
    ('FONTSIZE', (0, 0), (-1, -1), 14)
]))

Full code:

from pathlib import Path
from reportlab.lib.units import inch
from reportlab.lib.pagesizes import letter
from reportlab.platypus import SimpleDocTemplate
from reportlab.platypus.tables import Table, TableStyle, colors

output_dir = Path('output')
output_dir.mkdir(exist_ok=True)
my_path = output_dir / 'student_table.pdf'

my_data = [
    ['ID', 'Name', 'Class', 'Mark', 'Gender'],
    (1, 'John Deo', 'Four', 75, 'female'),
    (2, 'Max Ruin', 'Three', 85, 'male'),
    (3, 'Arnold', 'Three', 55, 'male')
]

my_doc = SimpleDocTemplate(str(my_path), pagesize=letter)
c_width = [0.4 * inch, 1.5 * inch, 1 * inch, 1 * inch, 1 * inch]
t = Table(my_data, rowHeights=20, repeatRows=1, colWidths=c_width)
t.setStyle(TableStyle([
    ('BACKGROUND', (0, 0), (-1, 0), colors.lightgreen),
    ('FONTSIZE', (0, 0), (-1, -1), 10)
]))
elements = [t]
my_doc.build(elements)
print(f"PDF created: {my_path}")

Using Data Files & External Sources

You can import my_data from external files (e.g., my_table_data.py or database connectors) to keep code modular.

Adding tabular data to PDF file from different data sources like SQLite, MySQL student table

Data Sources 🔝

Below is the architectural overview of ingesting data from various external sources into ReportLab PDF tables:

You can download sample student datasets in multiple formats from plus2net pandas student tutorial page:

!wget https://www.plus2net.com/python/download/student.csv
!wget https://www.plus2net.com/python/download/student.xlsx
!wget https://www.plus2net.com/python/download/student.json
!wget https://www.plus2net.com/python/download/student.xml
!wget https://www.plus2net.com/python/download/student.sql

Database Connection and Multi-Source Ingestion 🔝

1. Ingesting Data from CSV File

import csv

my_data = []
with open('student.csv', mode='r', encoding='utf-8') as f:
    reader = csv.reader(f)
    for row in reader:
        my_data.append(row)

2. Ingesting Data from Excel File (openpyxl)

import openpyxl

wb = openpyxl.load_workbook('student.xlsx')
sheet = wb.active
my_data = []
for row in sheet.iter_rows(values_only=True):
    my_data.append(list(row))

3. Ingesting Data from JSON File

import json

with open('student.json', 'r', encoding='utf-8') as f:
    json_data = json.load(f)

headers = list(json_data[0].keys())
my_data = [headers]
for item in json_data:
    my_data.append([item[h] for h in headers])

4. Ingesting Data from XML File

import xml.etree.ElementTree as ET

tree = ET.parse('student.xml')
root = tree.getroot()

my_data = [['id', 'name', 'class', 'mark', 'gender']]
for student in root.findall('student'):
    my_data.append([
        student.find('id').text,
        student.find('name').text,
        student.find('class').text,
        student.find('mark').text,
        student.find('gender').text
    ])

5. Ingesting Data from SQL Dump File into SQLite

import sqlite3

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

with open('student.sql', 'r', encoding='utf-8') as f:
    sql_script = f.read()

cursor.executescript(sql_script)
cursor.execute("SELECT * FROM student")

headers = [description[0] for description in cursor.description]
my_data = [headers] + cursor.fetchall()

6. Database Connection with SQLAlchemy (SQLite / MySQL)

We use try-except error handling to manage database exceptions:

from sqlalchemy import create_engine
from sqlalchemy.exc import SQLAlchemyError

db_file = 'my_db.db'  # change your path
my_data = [] 
try:
    file1 = 'sqlite:///' + db_file 
    my_conn = create_engine(file1)
    # For MySQL use:
    # my_conn = create_engine("mysql+mysqldb://id:pw@localhost/my_tutorial")
    r_set = my_conn.execute("SELECT * FROM student")
    my_data.append(list(r_set.keys())) # add column names
    for row in r_set.fetchall():
        my_data.append(row) # add each row
except SQLAlchemyError as e:
    error = str(e.__dict__['orig'])
    print(error)

Adding Title and Footer along with Data Table 🔝

from reportlab.platypus import SimpleDocTemplate, Paragraph, Spacer
from reportlab.lib.styles import getSampleStyleSheet
from reportlab.rl_config import defaultPageSize
from reportlab.lib.units import inch
from reportlab.platypus.tables import Table, TableStyle, colors
from pathlib import Path

PAGE_HEIGHT = defaultPageSize[1]
PAGE_WIDTH = defaultPageSize[0]
styles = getSampleStyleSheet()
Title = "This is my Main Title"
pageinfo = "At footer sample by www.plus2net.com"

output_dir = Path('output')
output_dir.mkdir(exist_ok=True)
my_path = output_dir / 'title_footer_table.pdf'

my_data = [
    ["ID", "Name", "Class", "Mark", "Gender"],
    (1, "John Deo", "Four", 75, "female"),
    (2, "Max Ruin", "Three", 85, "male"),
    (3, "Arnold", "Three", 55, "male"),
]

def myFirstPage(canvas, doc):
    canvas.saveState()
    canvas.setFont("Times-Bold", 16)
    canvas.drawCentredString(PAGE_WIDTH / 2.0, PAGE_HEIGHT - 50, Title)
    canvas.setFont("Times-Roman", 9)
    canvas.drawString(inch, 0.75 * inch, "First Page / %s" % pageinfo)
    canvas.restoreState()

def myLaterPages(canvas, doc):
    canvas.saveState()
    canvas.setFont("Times-Roman", 9)
    canvas.drawString(inch, 0.75 * inch, "Page %d %s" % (doc.page, pageinfo))
    canvas.restoreState()

def go():
    doc = SimpleDocTemplate(str(my_path))
    style = styles["Normal"]
    some_text = "This is a string . " * 10
    p = Paragraph(some_text, style)

    c_width = [0.4 * inch, 1.5 * inch, 1 * inch, 1 * inch, 1 * inch]
    t = Table(my_data, rowHeights=20, repeatRows=1, colWidths=c_width)
    t.setStyle(
        TableStyle(
            [
                ("BACKGROUND", (0, 0), (-1, 0), colors.lightgreen),
                ("FONTSIZE", (0, 0), (-1, -1), 10),
            ]
        )
    )
    elements = [t, p]
    doc.build(elements, onFirstPage=myFirstPage, onLaterPages=myLaterPages)
    print(f"PDF created: {my_path}")

go()

Practical Uses of Tables in ReportLab

Tables are vital for organizing structured information in professional reports and documents:

Student Report Cards & Grade Sheets: List course subjects, marks, and grades cleanly in tabular format.
Financial Invoices & Billing Statements: Present itemized product descriptions, quantities, unit prices, and totals.
Multi-Format Data Exports: Dynamically convert CSV, Excel, JSON, XML, or SQL queries into formatted multi-page PDF tables.

Common Errors & Troubleshooting

Table Column Width Overflow: If table columns exceed the page width, text can get clipped or overlap. Ensure the sum of colWidths fits within printable canvas bounds.
Missing Header Repetition: When tables span multiple pages, set repeatRows=1 in the Table constructor to repeat headers on each page.
Data Format Incompatibilities: When parsing XML or JSON, ensure missing or optional keys are handled safely to maintain consistent list lengths across table rows.

Frequently Asked Questions (FAQ)

Q: How do I apply alternating background colors to table rows?
A: You can iterate through row indices or use conditional styles in TableStyle, e.g., applying row background colors based on `row % 2 == 0`.

Q: Can I put paragraphs inside table cells?
A: Yes! Wrapping cell text in ReportLab Paragraph objects allows automatic text wrapping and rich text formatting within table cells.

PDF File from Database & Multi-Source Tables