Transform Your Workflow with Python: Automating Excel, PDFs, and Daily Reports
Automate Excel, PDFs, and reporting with Python using openpyxl, PyPDF2, and reportlab to save time, reduce errors, and build powerful end-to-end workflows.
Automation really starts with a simple question: what boring, repetitive task is chewing up your time that a computer could easily handle faster and without getting tired?
In the last article, we looked at how to automate emails with Python. You learned how to send messages, how to schedule them, and how to write small scripts that handle routine communication while you focus on actual work.
Now we are going to take that same idea and open it up to a bigger area: automating documents.
Almost every business, school, and team runs on spreadsheets, PDFs, and reports. Some even depend on them so much that someone spends half the week copying numbers from one sheet to another, exporting PDFs one at a time.
Every week you’ll be introduced to a new topic in Python, think of this as a mini starter course to get you going and allow you to have a structured roadmap that actually builds to create you a solid foundation in Python. Join us today!
This article is about what that looks like in real life, not just in theory. You will see how to work with Excel, PDFs, and automated reports using libraries like openpyxl, reportlab, and PyPDF2.
You can think of this as the next layer on top of what you already learned. In the last article, you automated communication. In this one, you automate the actual information inside your files.
Once you put those together, you are no longer writing one-off scripts. You are starting to build real systems. Let’s walk through it step by step.
Thank you guys for allowing me to do work that I find meaningful. This is my full-time job so I hope you will support my work by joining as a premium reader today.
If you’re already a premium reader, thank you from the bottom of my heart! You can leave feedback and recommend topics and projects at the bottom of all my articles.
You can get started with Python today with the goal to land a job in the next few months - Join the Masterclass Here.
👉 I genuinely hope you get value from these articles, if you do, please help me out, leave it a ❤️, and share it with others who would enjoy this. Thank you so much!
Automating Excel with openpyxl
Excel sits at the center of most offices. It holds budgets, schedules, sales numbers, project plans, inventory, and just about anything people can fit into a spreadsheet. The challenge is that these files rarely stay the same. Numbers change, reports get updated, sheets get added or removed, and someone has to keep everything current.
openpyxl is a Python library that can read, write, and update Excel files without needing Excel installed. It lets you open a workbook, update cells, write formulas, create charts, and format the sheet through code.
Picture this: You are on a team that tracks weekly sales. Every Monday, someone opens the Excel file, enters the new sales numbers, updates the totals, and sends it to management. It takes around thirty minutes each week. Over a year, that adds up to more than a full day of work spent on something a short script can finish in less than a second.
Here is what the core of that script might look like.
from openpyxl import load_workbook
workbook = load_workbook(”sales.xlsx”)
sheet = workbook[”January”]
sheet[”B10”] = 15700 # new weekly sales
sheet[”C10”] = “=SUM(B2:B10)” # update total formula
workbook.save(”sales.xlsx”)This code opens the file, inserts the new data, updates the total using a formula, and saves the workbook. Once you set this up, the script can run daily or weekly, or even pull numbers from an API or a CSV before updating the sheet.
You can also create new Excel files automatically. If you want a fresh monthly template every month, Python can build it for you without any copying or manual setup.
from openpyxl import Workbook
workbook = Workbook()
sheet = workbook.active
sheet.title = “Template”
sheet[”A1”] = “Date”
sheet[”B1”] = “Sales”
sheet[”C1”] = “Running Total”
workbook.save(”monthly_template.xlsx”)
Without automation, people rely on old templates or copy and paste from last month’s file. That is usually where errors slip in. A wrong formula, a missing cell reference, or a number typed in the wrong place can throw everything off. Automation cuts down on mistakes because Python repeats the same steps exactly the same way every time.
The real value shows up when you connect Excel automation with the other tools in this article. You might grab numbers from Excel, turn those numbers into a PDF report, and send that report by email automatically.
Once your script handles the whole process, you are no longer stuck doing the same updates every morning. You get that time back, and the computer handles the routine work without slipping. As they say, time is money.
Learn Python. Build Projects. Get Confident!
Most people get stuck before they even start… But that doesn’t have to be you!
The Python Masterclass is designed to take you from “I don’t know where to start” to “I can build real-world Python projects” — in less than 90 days.
👉 I’m giving you my exact system that’s been proven and tested by over 1,500 students over the last 4+ years!
My masterclass is designed so you see your first win in less than 7 days — you’ll build your first working Python scripts in week one and finish projects in your first month.
The sooner you start, the sooner you’ll have projects you can actually show to employers or clients.
Imagine where you’ll be 90 days from now if you start today.
👉 Ready to get started?
P.S. — Get 20% off your First Month with the code: save20now. Use it at checkout!
Working with PDFs using PyPDF2 and reportlab
PDFs have a different feel from Excel files. Excel is made to be edited and updated. PDFs are usually treated as finished documents. They lock in the layout, the formatting, and the content so anyone who opens them sees the same thing.
They are great for sharing, but that does not mean they cannot be automated. In many cases, automating PDFs is one of the smoothest ways to handle documents.
There are two main ways to work with PDFs. You can edit or break apart existing files, or you can create new ones from scratch.
PyPDF2 is built for working with existing PDFs. It can merge them, split them, rotate pages, pull out text, and rearrange the order of pages. A short Python script can take over that job completely.
from PyPDF2 import PdfMerger
files = [”invoice1.pdf”, “invoice2.pdf”, “invoice3.pdf”]
merger = PdfMerger()
for f in files:
merger.append(f)
merger.write(”combined_invoices.pdf”)
merger.close()
Another useful feature is text extraction. If you receive a large number of PDF receipts and need to pull out specific details, Python can scan through them in moments.
Many receipts follow predictable text patterns, which makes it easy to search for what you need.
from PyPDF2 import PdfReader
reader = PdfReader(”receipt.pdf”)
text = “”
for page in reader.pages:
text += page.extract_text()
print(text)
Once you have the text, you can search it, match patterns, or feed it into a larger automated system.
Editing and extracting from PDFs is only one part of the picture. Sometimes you need to create your own PDF files. That is where reportlab comes in. Instead of filling in a template, reportlab lets you build a PDF directly from code by placing text, headings, tables, images, and charts exactly where you want them.
from reportlab.pdfgen import canvas
pdf = canvas.Canvas(”weekly_report.pdf”)
pdf.drawString(50, 800, “Weekly Performance Report”)
pdf.drawString(50, 760, “Sales: $15,700”)
pdf.drawString(50, 740, “New Customers: 42”)
pdf.save()
This creates a basic PDF. You can add charts, images, tables, or multiple pages as your report grows. The real benefits show up when the PDF is built from live data pulled from Excel, a database, or an API.
One of the biggest advantages of automating PDFs is consistency. When reports are created by hand, small mistakes creep in over time. You get the exact same format every time, and if you ever want to improve the layout, you only need to update the script once.
Automated Reporting Systems
At this point you have all the parts you need. You know how to work with Excel. You know how to edit and create PDFs. From the previous article, you also know how to send emails without doing anything by hand. The real strength of automation shows up when you connect these pieces instead of treating them as separate tasks.
An automated reporting system is a smooth chain of steps. This kind of workflow sits at the center of analytics, reporting, and internal communication.
A script might follow a pattern like this:
Pull the latest data from a database or CSV
Load the numbers into Python and update an Excel log
Calculate totals and trends
Create a PDF that highlights the key numbers
Email the PDF automatically
The whole thing finishes in moments. Just as important, it avoids mistakes. The script does not forget steps, misplace numbers, or skip details.
Here is a simplified example that shows the general flow of things:
from openpyxl import load_workbook
from reportlab.pdfgen import canvas
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.application import MIMEApplication
# Step 1: Load the data (simplified)
new_checkins = 24
discharges = 18
open_beds = 7
# Step 2: Update Excel log
workbook = load_workbook(”clinic_log.xlsx”)
sheet = workbook.active
sheet.append([”Today”, new_checkins, discharges, open_beds])
workbook.save(”clinic_log.xlsx”)
# Step 3: Generate PDF
pdf = canvas.Canvas(”daily_report.pdf”)
pdf.drawString(50, 800, “Clinic Daily Report”)
pdf.drawString(50, 760, f”New Check-ins: {new_checkins}”)
pdf.drawString(50, 740, f”Discharges: {discharges}”)
pdf.drawString(50, 720, f”Open Beds: {open_beds}”)
pdf.save()
# Step 4: Email the PDF
message = MIMEMultipart()
message[”Subject”] = “Daily Clinic Report”
message[”From”] = “automation@example.com”
message[”To”] = “director@example.com”
with open(”daily_report.pdf”, “rb”) as f:
pdf_attachment = MIMEApplication(f.read(), _subtype=”pdf”)
pdf_attachment.add_header(”Content-Disposition”, “attachment”, filename=”daily_report.pdf”)
message.attach(pdf_attachment)
with smtplib.SMTP(”smtp.gmail.com”, 587) as smtp:
smtp.starttls()
smtp.login(”automation@example.com”, “password”)
smtp.send_message(message)
This is only a rough outline, but it shows how naturally the steps connect once you have the right tools in place.
The real value of automated reporting is how easily it grows. Once you build one working report, adding the next one requires far less effort. Each new report takes a fraction of the time because the structure is already in place.
Instead of spending time preparing information, they spend time understanding it. You move from gathering data to making decisions, which is where the work actually matters.
👉 My Python Learning Resources
Here are the best resources I have to offer to get you started with Python no matter your background! Check these out as they’re bound to maximize your growth in the field.
Zero to Knowing: Over 1,500+ students have already used this exact system to learn faster, stay motivated, and actually finish what they start.
P.S - Save 20% off your first month. Use code: save20now at checkout!
Code with Josh: This is my YouTube channel where I post videos every week designed to help break things down and help you grow.
My Books: Maybe you’re looking to get a bit more advanced in Python. I’ve written 3 books to help with that, from Data Analytics, to SQL all the way to Machine Learning.
My Favorite Books on Amazon:
Python Crash Course - Here
Automate the Boring Stuff - Here
Data Structures and Algorithms in Python - Here
Python Pocket Reference - Here
Final Thoughts
Document automation is one of the clearest examples of how useful Python can be in everyday work. It reaches into Excel, PDFs, email, scheduling, reporting, and communication.
It saves time right away, cuts down on mistakes, and gives you reliable results every time. More importantly, it shifts your attention toward work that actually requires thought instead of routine steps.
This is more than a technical skill. It is a way of looking at your work. Once you realize how much time automation gives back, you start building systems because you want your future self to have an easier day. That is the moment everything changes.
For now, you have everything you need to start automating the documents you deal with every day. Try these tools, experiment with them, and shape them to fit your workflow.
Once you see the difference they make, you will wonder why you waited this long to start. If these tips saved you even 10 minutes, hit the like button!
Hope you all have an amazing week nerds ~ Josh (Chief Nerd Officer 🤓)
👉 If you’ve been enjoying these lessons, consider subscribing to the premium version. You’ll get full access to all my past and future articles, all the code examples, extra Python projects, and more.