Daily Python Module: openpyxl

Summary in One Sentence: openpyxl allows Python to manipulate Excel as easily as “writing Word documents”, automating reports, batch processing data, and even generating beautiful statistical tables 🚀

🔥 Why Learn openpyxl?

As a Python developer or operations engineer, have you encountered these pain points:

❌ Manually exporting data to Excel is tedious and mechanical ❌ Generating reports on a schedule requires opening Excel and copying and pasting line by line ❌ Batch modifying Excel spreadsheets is prone to errors due to copy and paste

With openpyxl, these pain points disappear!

✅ Read and write Excel files (.xlsx format)
✅ Batch operations on cells
✅ Add formulas / charts / styles
✅ Automate operations reports

🚀 Quick Start: Hello Excel

from openpyxl import Workbook

# Create a new Excel workbook
wb = Workbook()
ws = wb.active  # Get the default worksheet

# Write data
ws['A1'] = "Name"
ws['B1'] = "Score"
ws.append(["Xiao Ming", 90])
ws.append(["Xiao Hong", 85])

# Save the file
wb.save("grades.xlsx")

📂 Result: A file named <span>grades.xlsx</span> is generated in the current directory, which opens to an Excel file with headers and two rows of data.

🔍 Read Excel Files

from openpyxl import load_workbook

# Load an existing Excel file
wb = load_workbook("grades.xlsx")
ws = wb.active

# Iterate and read
for row in ws.iter_rows(values_only=True):
    print(row)

Output:

('Name', 'Score')
('Xiao Ming', 90)
('Xiao Hong', 85)

📊 Practical Case: Automatically Generate Operations Reports

Many operational scenarios require data statistics (such as disk usage, task execution results), and then automatically generate reports to send to management. Let’s create a small case.

Example: Disk Monitoring Report

import psutil
from openpyxl import Workbook
from datetime import datetime

# Get disk partition usage
partitions = psutil.disk_partitions()

wb = Workbook()
ws = wb.active
ws.title = "Disk Monitoring"

# Write headers
ws.append(["Partition", "Total Capacity (GB)", "Used (GB)", "Usage Rate (%)"])

# Fill data
for p in partitions:
    usage = psutil.disk_usage(p.mountpoint)
    ws.append([
        p.mountpoint,
        round(usage.total / (1024**3), 2),
        round(usage.used / (1024**3), 2),
        usage.percent
    ])

# Save the report
filename = f"Disk_Monitoring_{datetime.now().strftime('%Y%m%d')}.xlsx"
wb.save(filename)
print(f"[Report generated successfully] {filename}")

Result:

[Report generated successfully] Disk_Monitoring_20250820.xlsx

📂 Open the Excel file to see the disk partition usage, with the table generated automatically.

🎨 Add Some Style to the Report

from openpyxl.styles import Font, PatternFill

# Set header styles
for cell in ws[1]:
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = PatternFill("solid", fgColor="4F81BD")

Effect: The header turns into white text on a blue background, making it look more like a formal report.

🧰 More Useful Features

Formula Support

ws["C2"] = "=AVERAGE(B2:B10)"  # Automatically calculate average score

Add Charts

from openpyxl.chart import BarChart, Reference

chart = BarChart()
data = Reference(ws, min_col=2, min_row=1, max_row=3)
chart.add_data(data, titles_from_data=True)
ws.add_chart(chart, "E5")

Multi-Worksheet Management

ws2 = wb.create_sheet("Log Statistics")

✅ Summary

With openpyxl, you can automate Excel:

One-click generation of daily operations reports
Batch export of data analysis results
Seamless integration of automation scripts and Excel reports

📌 Small Exercise: Write a script that automatically captures system CPU/memory usage every midnight and writes it to Excel, generating a curve chart after long-term accumulation 📈.