Introduction

Excel files are still everywhere in daily work. Finance teams export reports as spreadsheets, operations teams track inventory in .xlsx files, and analysts often receive raw Excel data from clients or coworkers. Instead of copying values by hand, Python can automate these repetitive tasks in minutes.

One of the most popular libraries for handling Excel files in Python is openpyxl. It is written in pure Python, supports modern .xlsx files, and works well for both small scripts and larger automation projects.

In this tutorial, you will learn how to:

• Install openpyxl
• Read Excel files
• Write data into spreadsheets
• Handle common real-world automation tasks

All code in this tutorial is tested on Python 3.12 and openpyxl 3.1.5.

---

Installing openpyxl

Install openpyxl with pip:

pip install openpyxl

You can verify the installation by importing the package in Python:

import openpyxl

print(openpyxl.__version__)

Expected output:

3.1.5

If you see a version number, openpyxl is installed correctly.

---

Reading an Excel File

Before reading data, create a simple Excel file named sample.xlsx.

Create a Sample Excel File

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

ws.title = "Sales"

ws.append(["Quarter", "Revenue"])
ws.append(["Q1", 12000])
ws.append(["Q2", 15500])
ws.append(["Q3", 18200])
ws.append(["Q4", 21000])

wb.save("sample.xlsx")

print("sample.xlsx created successfully")

Expected output:

sample.xlsx created successfully

This code creates a workbook with quarterly sales data and saves it as sample.xlsx.

---

Open an Existing Workbook

from openpyxl import load_workbook

wb = load_workbook("sample.xlsx")

ws = wb.active

print(ws.title)

Expected output:

Sales

This code opens the Excel file and returns the currently active worksheet.

---

Read a Single Cell

from openpyxl import load_workbook

wb = load_workbook("sample.xlsx")
ws = wb.active

print(ws["A1"].value)
print(ws["B2"].value)

Expected output:

Quarter
12000

ws["A1"] accesses a specific cell directly. The .value attribute returns the actual content stored in the cell.

---

Iterate Through Rows

from openpyxl import load_workbook

wb = load_workbook("sample.xlsx")
ws = wb.active

for row in ws.iter_rows(values_only=True):
    print(row)

Expected output:

('Quarter', 'Revenue')
('Q1', 12000)
('Q2', 15500)
('Q3', 18200)
('Q4', 21000)

This approach is useful when processing tables or exporting spreadsheet data into another system.

---

Read a Specific Range

from openpyxl import load_workbook

wb = load_workbook("sample.xlsx")
ws = wb.active

for row in ws["A2:B4"]:
    values = [cell.value for cell in row]
    print(values)

Expected output:

['Q1', 12000]
['Q2', 15500]
['Q3', 18200]

This code reads only a selected range instead of the entire worksheet. That becomes useful when working with large spreadsheets.

---

Iterate Through Columns

from openpyxl import load_workbook

wb = load_workbook("sample.xlsx")
ws = wb.active

for column in ws.iter_cols(values_only=True):
    print(column)

Expected output:

('Quarter', 'Q1', 'Q2', 'Q3', 'Q4')
('Revenue', 12000, 15500, 18200, 21000)

Column iteration is helpful when you need to analyze a specific field across many rows.

---

Writing to an Excel File

Reading files is only half the job. In many office workflows, Python scripts also need to generate reports automatically.

Create a New Workbook

from openpyxl import Workbook

wb = Workbook()

ws = wb.active
ws.title = "Monthly Report"

wb.save("monthly_report.xlsx")

print("Workbook created")

Expected output:

Workbook created

This creates a brand-new Excel workbook with a worksheet named Monthly Report.

---

Write to a Single Cell

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

ws["A1"] = "Employee"
ws["B1"] = "Sales"

ws["A2"] = "Maria"
ws["B2"] = 8700

wb.save("employee_sales.xlsx")

print("Data written successfully")

Expected output:

Data written successfully

This example writes labels and values directly into specific cells.

---

Append a Single Row

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

ws.append(["Product", "Units Sold"])

ws.append(["Laptop", 25])
ws.append(["Keyboard", 80])

wb.save("products.xlsx")

print("Rows added")

Expected output:

Rows added

ws.append() is cleaner than manually assigning each cell one by one.

---

Append Multiple Rows

from openpyxl import Workbook

sales_data = [
    ["Month", "Revenue"],
    ["January", 32000],
    ["February", 28700],
    ["March", 35100],
]

wb = Workbook()
ws = wb.active

for row in sales_data:
    ws.append(row)

wb.save("quarter1_sales.xlsx")

print("Multiple rows written")

Expected output:

Multiple rows written

This pattern is common when exporting database results or API responses into Excel.

---

Common Real-World Tasks

Convert CSV to Excel

Many systems export data as CSV files. With openpyxl, you can convert them into Excel workbooks in just a few lines of code.

Example CSV file (sales.csv):

Employee,Department,Sales
Alice,Electronics,12000
Bob,Furniture,9800
Carol,Clothing,14300

Conversion script:

import csv
from openpyxl import Workbook

wb = Workbook()
ws = wb.active

with open("sales.csv", "r", newline="", encoding="utf-8") as file:
    reader = csv.reader(file)

    for row in reader:
        ws.append(row)

wb.save("sales.xlsx")

print("CSV converted to Excel")

Expected output:

CSV converted to Excel

This script reads every row from a CSV file and writes it into an Excel workbook automatically.

---

Merge Multiple Excel Files

Suppose different departments send separate Excel reports every week. Instead of copying data manually, Python can merge them.

Example files: branch_a.xlsx and branch_b.xlsx, both containing the same column structure:

Product	Sales
Monitor	15
Mouse	40

Merge script:

from openpyxl import load_workbook, Workbook

source_files = ["branch_a.xlsx", "branch_b.xlsx"]

merged_wb = Workbook()
merged_ws = merged_wb.active

header_added = False

for file in source_files:
    wb = load_workbook(file)
    ws = wb.active

    for index, row in enumerate(ws.iter_rows(values_only=True)):
        if index == 0 and header_added:
            continue

        merged_ws.append(row)

    header_added = True

merged_wb.save("merged_report.xlsx")

print("Excel files merged")

Expected output:

Excel files merged

This code combines multiple workbooks into one consolidated report while avoiding duplicate headers.

---

Filter Rows and Save Results

Filtering data is another common business task.

Example: keep only high-sales records.

from openpyxl import load_workbook, Workbook

source_wb = load_workbook("sales_data.xlsx")
source_ws = source_wb.active

new_wb = Workbook()
new_ws = new_wb.active

for index, row in enumerate(source_ws.iter_rows(values_only=True)):
    if index == 0:
        new_ws.append(row)
        continue

    employee, department, sales = row

    if sales >= 12000:
        new_ws.append(row)

new_wb.save("high_sales.xlsx")

print("Filtered file created")

Expected output:

Filtered file created

This script copies only rows where sales are greater than or equal to 12000 into a new Excel file.

---

Generate a Simple Automated Report

Another practical use case is generating reports automatically every week or month.

from openpyxl import Workbook
from datetime import date

weekly_sales = [
    ["Store", "Revenue"],
    ["Downtown", 45200],
    ["Airport", 38900],
    ["Mall", 51700],
]

wb = Workbook()
ws = wb.active

ws.title = "Weekly Report"

for row in weekly_sales:
    ws.append(row)

ws["D1"] = "Generated On"
ws["D2"] = str(date.today())

wb.save("weekly_report.xlsx")

print("Weekly report generated")

Expected output:

Weekly report generated

This type of script is useful for scheduled reporting systems and internal dashboards.

---

Beyond the Basics

openpyxl can do much more than basic reading and writing. It also supports:

• Cell formatting
• Fonts and colors
• Formulas
• Charts
• Multiple worksheets
• Conditional formatting

For larger automation projects, the official documentation is worth reading. You can also expand these examples into full reporting pipelines or scheduled automation scripts. If your data starts as a CSV, the pandas cleaning guide shows how to process it before writing to Excel.

---

Wrap-Up

openpyxl makes Excel automation straightforward in Python. With just a few functions, you can read spreadsheets, generate reports, merge files, and process business data automatically.

The best way to learn is to modify these examples and try them on your own files. If you run into issues or have ideas for future tutorials, get in touch via the Contact page.