Python Daily Practice: Creating an Automated Daily Report Generator

“The first thing to do every morning at work is to organize yesterday’s daily report? Why not let Python help you automatically generate an Excel daily report, complete with charts!” 🚀

🧠 Background

In the daily work of operations, operations management, and data analysis, daily reports are essential:

Server CPU / Memory / Disk usage
Website traffic / User activity data
Sales / Order status

👉 Manually writing daily reports = tedious + prone to errors 👉 Automatically generating daily reports = efficient + stable + reusable

Today, we will use openpyxl + schedule to create an automated daily report generator!

🛠️ Tech Stack

openpyxl 👉 Manipulate Excel (write data, set styles, insert charts)
schedule 👉 Task scheduling, executed at a fixed time every day
psutil 👉 Get system resource usage (simulated data source)

🚀 Practical Code

import psutil
import schedule
import time
from datetime import datetime
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill
from openpyxl.chart import LineChart, Reference

def generate_report():
    """Generate daily report Excel file"""

    # Filename with date to avoid overwriting
    filename = f"系统日报_{datetime.now().strftime('%Y%m%d')}.xlsx"

    # Create workbook and worksheet
    wb = Workbook()
    ws = wb.active
    ws.title = "系统资源监控"

    # Write header
    ws.append(["时间", "CPU使用率(%)", "内存使用率(%)", "磁盘使用率(%)"])

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

    # Simulate sampling 5 times (can be changed to all-day sampling)
    for i in range(5):
        cpu = psutil.cpu_percent(interval=1)
        memory = psutil.virtual_memory().percent
        disk = psutil.disk_usage("/").percent
        ws.append([
            datetime.now().strftime("%H:%M:%S"),
            cpu, memory, disk
        ])

    # Insert line chart
    chart = LineChart()
    chart.title = "系统资源趋势"
    chart.y_axis.title = "使用率(%)"
    chart.x_axis.title = "时间"

    # Data range (rows 2-6, columns 2-4)
    data = Reference(ws, min_col=2, min_row=1, max_col=4, max_row=6)
    categories = Reference(ws, min_col=1, min_row=2, max_row=6)
    chart.add_data(data, titles_from_data=True)
    chart.set_categories(categories)

    ws.add_chart(chart, "F5")

    # Save file
    wb.save(filename)
    print(f"[日报已生成] {filename}")

# Scheduled task: execute every day at 9 AM
schedule.every().day.at("09:00").do(generate_report)

print("✅ 自动化日报生成器已启动，每天 09:00 自动运行...")

# Loop to keep the task running
while True:
    schedule.run_pending()
    time.sleep(1)

📂 Running Results

Automatically generated Excel file:
```
系统日报_20250905.xlsx
```
Table content (example):

Time	CPU Usage (%)	Memory Usage (%)	Disk Usage (%)
09:00:01	25	43	71
09:00:02	30	42	71
…	…	…	…

Line chart automatically drawn:

Three trend lines (CPU, Memory, Disk) clearly show changes
Report can be sent to leaders with one click, looking nice and professional 😎

🧰 Expandable Features

🔄 Change data source: Pull business data from a database / API instead of system monitoring
📧 Automatically send daily reports: Combine smtplib email module to automatically send the report to your email after generation
🕒 Refined scheduling: Use APScheduler instead of schedule for more complex task management

✅ Summary

In this article, we implemented an automated daily report generator:

Used psutil to get data
Used openpyxl to generate Excel reports + charts
Used schedule for timed scheduling, truly achieving unattended daily reports

From now on, you will receive an Excel daily report every morning on time, without having to organize it manually. Additionally, you can combine it with SMTP to send emails to your inbox 🚀