Excel automation is a very important skill for Python developers, especially for those working in data processing, office automation, and report generation.
The openpyxl library allows us to work with Excel files easily using Python.
In this guide, you will learn how to work with Workbook and Worksheet using openpyxl with real-time practical examples.
What is a Workbook in openpyxl?
A Workbook represents an entire Excel file.
When you open or create an Excel file in Python, you are working with a Workbook.
Example:
from openpyxl import load_workbook
wb = load_workbook("students.xlsx")
What is a Worksheet?
A Worksheet represents a single sheet inside an Excel file.
Each tab inside Excel is a Worksheet.
Example:
ws = wb.active
in students.xlsx
👉 First sheet = Data
👉 Second sheet = Report
ws = wb["Students"]
ws2 = wb["Report"]
Creating a New Workbook
You can create a brand new Excel file using Python.
from openpyxl import Workbook
wb = Workbook()
wb.save("new_file.xlsx")
Opening an Existing Workbook
To work with an already created Excel file:
from openpyxl import load_workbook
wb = load_workbook("students.xlsx")
Creating a New Worksheet
You can add new sheets for reports or summaries.
from openpyxl import load_workbook
wb = load_workbook("students.xlsx")
ws2 = wb.create_sheet(title="Report")
wb.save("students.xlsx")
Renaming a Worksheet
You can change the name of any sheet.
from openpyxl import load_workbook
wb = load_workbook("students.xlsx")
ws = wb["Sheet1"]
ws.title = "Student_Report"
wb.save("students.xlsx")
Deleting a Worksheet
To remove unwanted sheets:
from openpyxl import load_workbook
wb = load_workbook("students.xlsx")
del wb["Sheet1"]
wb.save("students.xlsx")
Writing Data into Worksheet
You can insert data into Excel cells using Python.
from openpyxl import load_workbook
wb = load_workbook("students.xlsx") # Excel file open
ws = wb["Sheet1"] # Sheet1 select
ws["A1"] = "Name" # A1 cell : Name
ws["B1"] = "Marks" # B1 cell : Marks
ws["A2"] = "Arun" # A2 cell : Arun
ws["B2"] = 85 # B2 cell-: 85
wb.save("students.xlsx") # ⚠️ Save changes
Reading Data from Worksheet
To read values from Excel:
print(ws["A2"].value)
Loop Through Rows Using iter_rows()
To read all rows efficiently:
for row in ws.iter_rows(values_only=True):
print(row)
To skip header row:
for row in ws.iter_rows(min_row=2, values_only=True):
print(row)
Merging Multiple Sheets into One Sheet
Useful for consolidating monthly or departmental data.
combined = wb.create_sheet("Combined")
for sheet in wb.sheetnames:
ws = wb[sheet]
if sheet != "Combined":
for row in ws.iter_rows(values_only=True):
combined.append(row)
Merging Multiple Workbooks into One File
This is commonly used in office reporting systems.
files = ["jan.xlsx","feb.xlsx","mar.xlsx"]
master = load_workbook(files[0])
mws = master.active
for f in files[1:]:
wb = load_workbook(f)
ws = wb.active
for row in ws.iter_rows(min_row=2, values_only=True):
mws.append(row)
master.save("master.xlsx")
Finding Particular Data in Worksheet
To search for a specific value:
for row in ws.iter_rows(values_only=True):
if "Arun" in row:
print("Found:", row)
Filtering Records Using Conditions
Example: Display students who scored below 40.
for row in ws.iter_rows(min_row=2, values_only=True):
if row[1] < 40:
print(row)
Highlighting Failed Records Automatically
This is useful for result analysis.
from openpyxl.styles import PatternFill
for row in ws.iter_rows(min_row=2):
if row[1].value < 35:
for cell in row:
cell.fill = PatternFill(start_color="FF9999", fill_type="solid")
Creating a Summary Sheet
You can generate a summary report automatically.
summary = wb.create_sheet("Summary")
summary["A1"] = "Total Students"
summary["B1"] = ws.max_row - 1
Saving the Workbook
After making all changes:
wb.save("final_report.xlsx")
Why Workbook & Worksheet Are Important
Workbook and Worksheet handling is the foundation of Excel automation using Python.
These concepts are used in:
- Office report automation
- Government MIS systems
- Student result processing
- Attendance systems
- Data cleaning and validation
- Power BI data preparation
Conclusion
Mastering Workbook and Worksheet operations in openpyxl helps you automate Excel tasks efficiently using Python.
Whether you are working in education, government projects, data analytics, or office automation, these skills are extremely valuable.
If you are learning Python for real-world applications, Excel automation using openpyxl is a must-learn topic.