Vấn đề mình gặp phải: 500 file Excel mỗi tháng
Hồi đầu năm mình nhận task xử lý báo cáo doanh thu từ 12 chi nhánh — mỗi chi nhánh gửi 1 file Excel/tuần, tổng cộng khoảng 500 file/tháng. Merge tay, tô màu tay, kẻ border tay. Mình mất gần 2 ngày mỗi tháng chỉ để làm việc đó.
Sau 3 tuần viết script với openpyxl, toàn bộ pipeline đó chạy xong trong 4 phút. Bài này mình ghi lại những gì thực sự hữu dụng — không phải tutorial Hello World, mà là những thứ mình dùng trên production.
Khái niệm cốt lõi cần nắm trước
openpyxl làm việc với 3 tầng chính: Workbook (file Excel), Worksheet (sheet), và Cell (ô). Hiểu rõ 3 tầng này là hiểu được 80% thư viện.
Có 2 chế độ đọc file quan trọng mà nhiều người bỏ qua:
- Normal mode: Load toàn bộ file vào RAM — tiện để đọc/ghi linh hoạt, nhưng file 50MB trở lên thì RAM ngốn thấy rõ.
- Read-only mode (
read_only=True): Dùng generator, đọc từng dòng mà không load hết vào bộ nhớ — file 200MB+ không lo hết RAM.
Chọn openpyxl cụ thể vì nó xử lý .xlsx thuần: đọc, ghi, giữ nguyên định dạng, hỗ trợ styles và charts. Pandas giỏi phân tích số liệu hơn, nhưng export ra Excel không giữ được border hay màu nền. Với task cần output gửi thẳng khách hàng thì openpyxl là lựa chọn rõ hơn.
Thực hành: Từ đọc dữ liệu đến báo cáo hoàn chỉnh
1. Đọc file lớn không lo hết RAM
Với file nhỏ dưới 10MB, dùng normal mode cho tiện:
from openpyxl import load_workbook
wb = load_workbook('bao_cao_thang.xlsx')
ws = wb.active
for row in ws.iter_rows(min_row=2, values_only=True):
chi_nhanh, doanh_thu, so_don = row[0], row[1], row[2]
print(f"{chi_nhanh}: {doanh_thu:,} VNĐ ({so_don} đơn)")
File trên 50MB thì chuyển sang read-only mode ngay:
wb = load_workbook('data_lon.xlsx', read_only=True)
ws = wb.active
total = 0
for row in ws.iter_rows(min_row=2, values_only=True):
if row[1]: # Bỏ qua dòng trống
total += row[1]
wb.close() # Quan trọng: phải close khi dùng read_only
print(f"Tổng: {total:,}")
2. Ghi dữ liệu và tạo file báo cáo mới
Tạo file báo cáo tổng hợp từ nhiều file nguồn — đây là use case mình dùng nhiều nhất:
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
import glob
wb_out = Workbook()
ws_out = wb_out.active
ws_out.title = "Tổng hợp"
# Header
headers = ["Chi nhánh", "Doanh thu", "Số đơn", "Trung bình/đơn"]
ws_out.append(headers)
# Style header
for col_idx, cell in enumerate(ws_out[1], 1):
cell.font = Font(bold=True, color="FFFFFF")
cell.fill = PatternFill(fill_type="solid", fgColor="2E5090")
cell.alignment = Alignment(horizontal="center")
# Đọc từng file chi nhánh
for filepath in glob.glob('chi_nhanh_*.xlsx'):
wb_src = load_workbook(filepath, read_only=True)
ws_src = wb_src.active
for row in ws_src.iter_rows(min_row=2, values_only=True):
if row[0]: # Có dữ liệu
tb = row[1] / row[2] if row[2] else 0
ws_out.append([row[0], row[1], row[2], round(tb, 0)])
wb_src.close()
wb_out.save('tong_hop.xlsx')
3. Định dạng điều kiện (Conditional Formatting)
Đây là phần mình tốn thời gian nhất để tìm hiểu. openpyxl hỗ trợ conditional formatting khá đầy đủ nhưng API hơi khác so với Excel UI.
Tô màu ô dựa trên giá trị — ví dụ highlight doanh thu thấp:
from openpyxl.formatting.rule import ColorScaleRule, CellIsRule
from openpyxl.styles import PatternFill
# Color scale: đỏ → vàng → xanh theo giá trị
color_scale = ColorScaleRule(
start_type='min', start_color='FF0000',
mid_type='percentile', mid_value=50, mid_color='FFFF00',
end_type='max', end_color='00FF00'
)
ws_out.conditional_formatting.add('B2:B50', color_scale)
# Tô đỏ những ô có giá trị < 50,000,000
red_fill = PatternFill(start_color='FFB3B3', end_color='FFB3B3', fill_type='solid')
low_rule = CellIsRule(operator='lessThan', formula=['50000000'], fill=red_fill)
ws_out.conditional_formatting.add('B2:B50', low_rule)
Mình hay dùng song song cả hai: color scale để thấy phân bổ tổng thể ngay từ đầu, CellIsRule để đánh dấu đúng những ô cần kiểm tra ngay.
4. Tạo biểu đồ báo cáo
openpyxl hỗ trợ nhiều loại chart hơn mình nghĩ lúc đầu — bar, line, pie, scatter. Cho báo cáo doanh thu thì BarChart là đủ dùng:
from openpyxl.chart import BarChart, Reference
from openpyxl.chart.series import SeriesLabel
# Giả sử dữ liệu ở cột A (tên) và B (doanh thu), từ dòng 1 đến 13
chart = BarChart()
chart.type = "col" # Cột dọc
chart.title = "Doanh thu theo chi nhánh"
chart.y_axis.title = "Doanh thu (VNĐ)"
chart.x_axis.title = "Chi nhánh"
chart.style = 10 # Preset style của Excel
chart.width = 20
chart.height = 12
# Data series (cột B)
data = Reference(ws_out, min_col=2, min_row=1, max_row=13)
chart.add_data(data, titles_from_data=True)
# Labels (cột A)
categories = Reference(ws_out, min_col=1, min_row=2, max_row=13)
chart.set_categories(categories)
# Chèn chart vào sheet, bắt đầu từ ô D2
ws_out.add_chart(chart, "D2")
wb_out.save('bao_cao_co_bieu_do.xlsx')
5. Điều chỉnh độ rộng cột tự động
Chi tiết nhỏ nhưng khách hàng hay hỏi tại sao cột bị cắt. Hàm này mình copy vào mọi script Excel:
def auto_fit_columns(ws):
for col in ws.columns:
max_len = 0
col_letter = col[0].column_letter
for cell in col:
try:
val_len = len(str(cell.value)) if cell.value else 0
if val_len > max_len:
max_len = val_len
except:
pass
# +2 để có chút padding
ws.column_dimensions[col_letter].width = min(max_len + 2, 50)
auto_fit_columns(ws_out)
6. Đặt kẻ border cho bảng
Border mỏng quanh toàn bộ dữ liệu trông professional hơn hẳn:
from openpyxl.styles import Border, Side
thin = Side(border_style='thin', color='AAAAAA')
border = Border(left=thin, right=thin, top=thin, bottom=thin)
for row in ws_out.iter_rows(min_row=1, max_row=ws_out.max_row,
min_col=1, max_col=ws_out.max_column):
for cell in row:
cell.border = border
Kết luận: Những gì đáng để tự động hóa
Sau 6 tháng production, openpyxl làm tốt nhất 3 việc: merge file thành báo cáo tổng hợp, áp định dạng nhất quán theo template, và sinh chart từ data. Nếu task của bạn thuộc 1 trong 3 nhóm đó — script 50 dòng code sẽ thay được 2 tiếng làm tay mỗi lần.
Những thứ mình không dùng openpyxl: phân tích thống kê phức tạp (dùng pandas tốt hơn), xử lý file .xls cũ (dùng xlrd), hay cần pivot table thực sự (vẫn phải làm tay hoặc dùng win32com trên Windows).
Xử lý Excel thủ công hơn 1 tiếng mỗi tuần — đó là tín hiệu để nghĩ đến script. Mình bỏ ra một buổi chiều viết code lần đầu. Đổi lại là 2 ngày mỗi tháng từ đó đến giờ.
