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.
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
Ở 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.
"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=Truelấ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
- 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.
- 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.


