Tự động hóa báo cáo Excel bằng Python: openpyxl & xlsxwriter

pip install openpyxl xlsxwriter pandas

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side

wb = Workbook()
ws = wb.active
ws.title = "Báo cáo doanh thu"

# Header style
header_font = Font(bold=True, color="FFFFFF", size=12)
header_fill = PatternFill(start_color="16A34A", fill_type="solid")

# Viết header
headers = ["STT", "Sản phẩm", "Số lượng", "Đơn giá", "Thành tiền"]
for col, header in enumerate(headers, 1):
    cell = ws.cell(row=1, column=col, value=header)
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = Alignment(horizontal="center")

# Viết dữ liệu
data = [
    [1, "Laptop Dell", 10, 25000000, 250000000],
    [2, "Mouse Logitech", 50, 500000, 25000000],
    [3, "Keyboard Corsair", 30, 1500000, 45000000],
]

for row_idx, row_data in enumerate(data, 2):
    for col_idx, value in enumerate(row_data, 1):
        ws.cell(row=row_idx, column=col_idx, value=value)

# Auto-fit columns
for col in ws.columns:
    max_length = max(len(str(cell.value or "")) for cell in col)
    ws.column_dimensions[col[0].column_letter].width = max_length + 4

wb.save("bao_cao.xlsx")
print("Đã tạo báo cáo thành công!")

from openpyxl.chart import BarChart, Reference

chart = BarChart()
chart.title = "Doanh thu theo sản phẩm"
chart.y_axis.title = "VNĐ"

data_ref = Reference(ws, min_col=5, min_row=1, max_row=4)
cats_ref = Reference(ws, min_col=2, min_row=2, max_row=4)

chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(cats_ref)
ws.add_chart(chart, "G2")

import pandas as pd

df = pd.read_csv("raw_data.csv")
summary = df.groupby("category")["revenue"].sum().reset_index()

with pd.ExcelWriter("report.xlsx", engine="xlsxwriter") as writer:
    summary.to_excel(writer, sheet_name="Summary", index=False)

    workbook = writer.book
    worksheet = writer.sheets["Summary"]

    # Format tiền VNĐ
    money_fmt = workbook.add_format({"num_format": "#,##0"})
    worksheet.set_column("B:B", 15, money_fmt)

    # Thêm chart
    chart = workbook.add_chart({"type": "column"})
    chart.add_series({
        "categories": ["Summary", 1, 0, len(summary), 0],
        "values": ["Summary", 1, 1, len(summary), 1],
    })
    worksheet.insert_chart("D2", chart)