⭐ Python Developer · Chặng 7Bài 3/6Bài học~60 phút

Tự động hóa Excel bằng Python với openpyxl: đọc, ghi, định dạng

Đọc ghi file Excel bằng openpyxl: ô, dòng, sheet, định dạng số, độ rộng cột, công thức, gộp nhiều file thành một báo cáo.

Tự động hóa Excel bằng Python với openpyxl: đọc, ghi, định dạng

Học xong bài này, bạn sẽ

  • Đọc, ghi ô, dòng, sheet trong file .xlsx
  • Định dạng ô, độ rộng cột, công thức
  • Gộp nhiều file Excel thành một báo cáo

Nên học trước: Gọi Web API bằng requests

Nội dung bài viết
  1. 1. Tạo và ghi file
  2. 2. Định dạng cho ra dáng báo cáo
  3. 3. Đọc file
  4. 4. Gộp nhiều file thành một
  5. Tóm tắt
  6. Bài tập

Ở hầu hết công ty Việt Nam, báo cáo cuối cùng vẫn là một file Excel. Tự động hóa Excel bằng Python là một trong những kỹ năng mang lại giá trị nhanh nhất: việc gộp 30 file doanh thu chi nhánh mất cả buổi chiều có thể còn vài giây. Bài này dùng openpyxl, thư viện đọc ghi file .xlsx phổ biến nhất, chạy được mà không cần cài Excel.

1. Tạo và ghi file

Cài: uv add openpyxl.

from openpyxl import Workbook

wb = Workbook()                     # file mới, có sẵn một sheet
ws = wb.active
ws.title = "Doanh thu"

ws["A1"] = "Chi nhánh"              # ghi theo địa chỉ ô
ws["B1"] = "Tháng"
ws["C1"] = "Doanh thu"
du_lieu = [("Hà Nội", 10, 1_250_000_000), ("Đà Nẵng", 10, 640_000_000), ("TP.HCM", 10, 1_830_000_000)]
for dong in du_lieu:
    ws.append(dong)                 # thêm vào dòng trống kế tiếp

ws.cell(row=5, column=1, value="Tổng")
ws["C5"] = "=SUM(C2:C4)"            # công thức Excel: lưu dạng chuỗi bắt đầu bằng =
wb.save("doanh_thu.xlsx")
print(ws.max_row, ws.max_column, ws["A3"].value, ws.cell(row=4, column=3).value)
5 3 Đà Nẵng 1830000000
  • Workbook là file, Worksheet là sheet, Cell là ô. Truy cập ô bằng địa chỉ ws["C2"] hoặc số ws.cell(row=2, column=3) (đánh số từ 1, không phải 0).
  • ws.append(list_hoac_tuple) là cách nhanh nhất để ghi từng dòng dữ liệu.
  • Công thức được lưu nguyên văn; Excel tính khi mở file. openpyxl không tự tính công thức.

2. Định dạng cho ra dáng báo cáo

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

wb = load_workbook("doanh_thu.xlsx")
ws = wb["Doanh thu"]

dam = Font(bold=True, color="FFFFFF")
nen = PatternFill("solid", fgColor="1F4E78")
vien = Border(bottom=Side(style="thin", color="999999"))
for o in ws[1]:                                  # ws[1] là cả dòng 1
    o.font, o.fill = dam, nen
    o.alignment = Alignment(horizontal="center")

for dong in ws.iter_rows(min_row=2, min_col=3, max_col=3):
    for o in dong:
        o.number_format = "#,##0"                # phân cách hàng nghìn
        o.border = vien
ws["A5"].font = ws["C5"].font = Font(bold=True)

ws.column_dimensions["A"].width = 14
ws.column_dimensions["C"].width = 18
ws.freeze_panes = "A2"                           # cố định dòng tiêu đề khi cuộn
ws.auto_filter.ref = "A1:C4"                     # nút lọc trên tiêu đề
wb.save("doanh_thu.xlsx")
print(ws["C2"].number_format, ws.freeze_panes)
#,##0 A2

number_format dùng đúng mã định dạng như trong hộp thoại Format Cells của Excel: "#,##0", "0.0%", "dd/mm/yyyy". Excel hiển thị dấu phân cách theo cài đặt vùng của máy người mở (máy cài tiếng Việt sẽ thấy 1.250.000.000), còn giá trị trong ô vẫn là số để cộng trừ, lọc, vẽ biểu đồ. Đây là lý do ghi số chứ không ghi chuỗi đã định dạng vào ô.

3. Đọc file

from openpyxl import load_workbook

wb = load_workbook("doanh_thu.xlsx", data_only=True)   # data_only: lấy giá trị đã tính của công thức
ws = wb.active
print(wb.sheetnames)
for chi_nhanh, thang, doanh_thu in ws.iter_rows(min_row=2, max_row=4, values_only=True):
    print(f"{chi_nhanh:<8} T{thang}: {doanh_thu / 1e9:.2f} tỷ")
print(ws["C5"].value)
['Doanh thu']
Hà Nội   T10: 1.25 tỷ
Đà Nẵng  T10: 0.64 tỷ
TP.HCM   T10: 1.83 tỷ
None

iter_rows(values_only=True) trả từng dòng thành tuple giá trị, tiện mở gói thẳng vào biến. Chú ý dòng cuối: data_only=True đọc giá trị công thức mà Excel đã tính và lưu lần cuối. File do openpyxl tạo chưa từng được mở bằng Excel nên chưa có giá trị đó, kết quả là None. Nếu cần con số tổng trong Python, hãy tự tính bằng Python thay vì đọc lại công thức.

Bẫy hay gặp khi đọc file Excel thậtFile người dùng gửi thường có dòng tiêu đề không ở dòng 1, ô gộp, dòng trống giữa bảng, số lưu dạng chữ ("1.250.000"), ngày lưu dạng chuỗi. Luôn kiểm tra kiểu từng giá trị và viết hàm làm sạch riêng. openpyxl chỉ đọc .xlsx; file .xls cũ cần chuyển đổi hoặc dùng thư viện khác.

4. Gộp nhiều file thành một

Bài toán kinh điển: mỗi chi nhánh gửi một file doanh_thu_<chi_nhanh>.xlsx cùng cấu trúc, cần gộp vào một sheet.

from pathlib import Path

from openpyxl import Workbook, load_workbook

# Tạo dữ liệu mẫu: 3 file chi nhánh
thu_muc = Path("bao_cao_chi_nhanh")
thu_muc.mkdir(exist_ok=True)
for cn, so in {"ha_noi": [120, 95], "da_nang": [60], "hcm": [210, 180, 75]}.items():
    wb = Workbook()
    wb.active.append(["Mã đơn", "Số tiền"])
    for i, tien in enumerate(so, 1):
        wb.active.append([f"{cn[:2].upper()}{i:03d}", tien * 1000])
    wb.save(thu_muc / f"doanh_thu_{cn}.xlsx")


def gop(thu_muc: Path, dich: Path) -> int:
    ket_qua = Workbook()
    ws_ra = ket_qua.active
    ws_ra.title = "Tổng hợp"
    ws_ra.append(["Chi nhánh", "Mã đơn", "Số tiền"])
    so_dong = 0
    for p in sorted(thu_muc.glob("doanh_thu_*.xlsx")):
        chi_nhanh = p.stem.removeprefix("doanh_thu_")
        ws_vao = load_workbook(p, read_only=True).active
        for ma, tien in ws_vao.iter_rows(min_row=2, values_only=True):
            if ma is None:                     # bỏ dòng trống
                continue
            ws_ra.append([chi_nhanh, ma, tien])
            so_dong += 1
    ws_ra.append(["Tổng", None, f"=SUM(C2:C{so_dong + 1})"])
    ket_qua.save(dich)
    return so_dong


print(gop(thu_muc, Path("tong_hop.xlsx")), "dòng")
for dong in load_workbook("tong_hop.xlsx").active.iter_rows(values_only=True):
    print(dong)
6 dòng
('Chi nhánh', 'Mã đơn', 'Số tiền')
('da_nang', 'DA001', 60000)
('ha_noi', 'HA001', 120000)
('ha_noi', 'HA002', 95000)
('hcm', 'HC001', 210000)
('hcm', 'HC002', 180000)
('hcm', 'HC003', 75000)
('Tổng', None, '=SUM(C2:C7)')

read_only=True giúp đọc file lớn nhanh và tốn ít bộ nhớ. Với dữ liệu phân tích phức tạp (lọc, nhóm, pivot), pandas ở chặng 8 gọn hơn nhiều: pd.read_excel rồi pd.concat. openpyxl mạnh ở chỗ kiểm soát chi tiết từng ô và định dạng.

Tóm tắt

  • Workbook() tạo mới, load_workbook(p) mở; ws["A1"], ws.cell(row, column), ws.append().
  • Định dạng: Font, PatternFill, Border, number_format, column_dimensions, freeze_panes.
  • Đọc: iter_rows(min_row=2, values_only=True); data_only=True lấy giá trị công thức đã lưu.
  • Ghi số vào ô và định dạng bằng number_format, không ghi chuỗi đã định dạng.

Bài tập

Bài 1. Báo cáo định dạng sẵn

Tạo bao_cao.xlsx từ list sản phẩm (tên, số lượng, đơn giá): cột thành tiền dùng công thức =B*C, tiêu đề in đậm nền xanh, cột tiền định dạng #,##0, dòng tổng dùng SUM.

Xem đáp án
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill

hang = [("Bàn phím", 3, 850_000), ("Chuột", 5, 250_000), ("Màn hình", 2, 3_900_000)]
wb = Workbook()
ws = wb.active
ws.append(["Sản phẩm", "Số lượng", "Đơn giá", "Thành tiền"])
for o in ws[1]:
    o.font = Font(bold=True, color="FFFFFF")
    o.fill = PatternFill("solid", fgColor="2E75B6")
for i, (ten, sl, gia) in enumerate(hang, start=2):
    ws.append([ten, sl, gia, f"=B{i}*C{i}"])
cuoi = len(hang) + 1
ws.append(["Tổng", f"=SUM(B2:B{cuoi})", None, f"=SUM(D2:D{cuoi})"])
for cot in "CD":
    for o in ws[cot][1:]:
        o.number_format = "#,##0"
    ws.column_dimensions[cot].width = 15
ws.column_dimensions["A"].width = 14
wb.save("bao_cao.xlsx")
print([o.value for o in ws[cuoi + 1]])
['Tổng', '=SUM(B2:B4)', None, '=SUM(D2:D4)']

Mở file bằng Excel: cột Thành tiền tự tính, sửa số lượng thì tổng cập nhật ngay. Đó là ưu điểm của ghi công thức thay vì ghi kết quả tính sẵn.

Bài 2. Gộp file kèm tên file

Sửa hàm gop ở mục 4: bỏ qua file đang mở bởi Excel (tên bắt đầu bằng ~$), định dạng cột tiền #,##0, và in số dòng lấy từ mỗi file.

Xem gợi ý

Excel tạo file khóa ẩn ~$doanh_thu_ha_noi.xlsx khi đang mở file; mẫu doanh_thu_*.xlsx không khớp tên này nên đã an toàn, nhưng nếu đổi mẫu thành *.xlsx thì phải lọc if p.name.startswith("~$"): continue. Đếm số dòng mỗi file bằng một biến riêng trong vòng lặp ngoài, ghi log mức INFO thay vì print như chặng 3.

Nguồn tham khảo: openpyxl: Tutorial; openpyxl: Working with styles; openpyxl: Optimised modes (read-only).

Bài tập thực hành

  1. Tạo file bao_cao.xlsx có tiêu đề in đậm, cột tiền định dạng #,##0 và dòng tổng dùng công thức SUM.
  2. Gộp các file doanh_thu_*.xlsx (cùng cấu trúc) trong một thư mục vào một sheet, thêm cột tên file.

Làm xong bài tập mới chuyển sang bài tiếp theo: tự viết code là cách duy nhất để nhớ lâu.

Bài tiếp theo
Ban biên tập HocFree

Chúng tôi viết những bài hướng dẫn dễ hiểu, thực tế để ai cũng có thể tự học lập trình và AI, hoàn toàn miễn phí.