Tự Động Hóa Báo Cáo Excel Bằng Python: Openpyxl Và XlsxWriter

pip install openpyxl xlsxwriter pandas

import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.chart import BarChart, Reference
import xlsxwriter
import pandas as pd

# Đọc file có sẵn
wb = openpyxl.load_workbook("report_template.xlsx")
ws = wb.active

# Ghi dữ liệu
ws["A1"] = "Báo Cáo Doanh Thu"
ws["A1"].font = Font(name="Arial", size=16, bold=True, color="003366")
ws["A1"].fill = PatternFill(start_color="E6F3FF", fill_type="solid")

# Ghi hàng loạt từ Pandas DataFrame
df = pd.read_sql("SELECT * FROM sales", conn)
for r_idx, row in enumerate(df.itertuples(index=False), start=3):
    for c_idx, value in enumerate(row, start=1):
        ws.cell(row=r_idx, column=c_idx, value=value)

wb.save("report_output.xlsx")

# Tạo Bar Chart tự động
chart = BarChart()
chart.title = "Doanh Thu Theo Tháng"
chart.y_axis.title = "VND"
chart.x_axis.title = "Tháng"

data = Reference(ws, min_col=2, min_row=2, max_row=13, max_col=2)
cats = Reference(ws, min_col=1, min_row=3, max_row=13)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
chart.shape = 4  # Bar style

ws.add_chart(chart, "D2")  # Đặt chart tại D2
wb.save("report_with_chart.xlsx")

workbook = xlsxwriter.Workbook("sales_report.xlsx")
ws = workbook.add_worksheet("Report")

# Định nghĩa format
header_fmt = workbook.add_format({
    "bold": True, "font_color": "white",
    "bg_color": "#2B579A", "border": 1,
    "align": "center", "font_size": 12
})
money_fmt = workbook.add_format({"num_format": "#,##0 ₫", "border": 1})

# Ghi headers
headers = ["Tháng", "Doanh Thu", "Chi Phí", "Lợi Nhuận"]
for col, h in enumerate(headers):
    ws.write(0, col, h, header_fmt)

# Conditional formatting: highlight số âm
ws.conditional_format("D2:D13", {
    "type": "cell", "criteria": "<", "value": 0,
    "format": workbook.add_format({"font_color": "red", "bold": True})
})

workbook.close()

import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.base import MIMEBase
from email import encoders

def send_report(file_path, to_email):
    msg = MIMEMultipart()
    msg["Subject"] = f"Báo Cáo Tuần {datetime.now().strftime("%d/%m/%Y")}"
    
    with open(file_path, "rb") as f:
        part = MIMEBase("application", "octet-stream")
        part.set_payload(f.read())
        encoders.encode_base64(part)
        part.add_header("Content-Disposition", f"attachment; filename={file_path}")
        msg.attach(part)
    
    with smtplib.SMTP("smtp.gmail.com", 587) as server:
        server.starttls()
        server.login(email, password)
        server.send_message(msg)