Python and Excel Automation Using openpyxl and pandas

Working with Excel files manually can be time-consuming—especially when dealing with repetitive tasks like updating reports, formatting cells, or analyzing data. Python makes Excel automation easy and powerful, especially with libraries like openpyxl and pandas.

This guide shows beginners how to use these tools to read, write, and manipulate Excel files programmatically.

---

Why Automate Excel with Python?

• Save hours on repetitive data entry
  
• Automatically generate or update reports
  
• Extract and analyze Excel data at scale
  
• Eliminate human errors in spreadsheets
  

---

Setting Up Your Tools

Install the necessary libraries:

bash

Copy code

pip install openpyxl pandas

• openpyxl: Ideal for reading and writing .xlsx files and formatting them
  
• pandas: Great for data analysis, filtering, and Excel-to-DataFrame workflows
  

---

Reading Excel Files with pandas

python

Copy code

import pandas as pd

df = pd.read_excel(“sales.xlsx”)

print(df.head())

✅ Use sheet_name=”Sheet1″ if you want to specify the worksheet.

---

Writing to Excel with pandas

python

Copy code

df.to_excel(“updated_sales.xlsx”, index=False)

You can also write multiple DataFrames to one file using ExcelWriter:

python

Copy code

with pd.ExcelWriter(“report.xlsx”) as writer:

    df.to_excel(writer, sheet_name=”Summary”)

    df.describe().to_excel(writer, sheet_name=”Stats”)

---

Editing Excel Files with openpyxl

openpyxl allows more control over formatting and cell-level updates.

Example: Change a cell value

python

Copy code

from openpyxl import load_workbook

wb = load_workbook(“report.xlsx”)

sheet = wb[“Summary”]

sheet[“B2”] = “Updated Value”

wb.save(“report_updated.xlsx”)

Example: Style a cell

python

Copy code

from openpyxl.styles import Font

sheet[“B2″].font = Font(bold=True, color=”FF0000”)

---

Automate Report Generation

Combine pandas and openpyxl:

1. Use pandas to pull and process the data
   
2. Use openpyxl to format the report
   
3. Save and email automatically (with smtplib)
   

---

Practice Tip

Try automating one of your weekly Excel reports by writing a Python script that:

• Reads in raw Excel data
  
• Cleans or summarizes it
  
• Writes it to a new file
  
• Applies simple formatting
  

---

Keep Automating with Python

Excel is just the beginning. With Python, you can automate PDF generation, emails, file management, and more.

🧠 Want hands-on help building automations that save time and impress your team?
👉 https://www.thefullstack.co.in/courses/

You also like this:

What is Backend Development? A Complete Guide for Beginners [2025]

How Can SAP ERP Be Beneficial

What Are the Most Popular Backend Development Languages in 2025?