openpyxl: Tự động hoá tác vụ Excel với Python

openpyxl là một thư viện Python hiệu quả cho phép bạn đọc và ghi tệp Excel ở các định dạng dựa trên XML hiện đại (.xlsx, .xlsm) được giới thiệu từ Excel 2007, với đầy đủ hỗ trợ cho các tính năng nâng cao từ Excel 2010 trở đi. Ban đầu nó được phát triển như một bản chuyển từ PHPExcel sang Python và tiếp tục được duy trì bởi cộng đồng lập trình viên tích cực. Thư viện này đặc biệt mạnh về tự động hoá dữ liệu, lập báo cáo và định dạng, rất hữu ích cho người dùng cần làm việc với tệp Excel nhưng không cài đặt Excel trên hệ thống.

Nhiều tổ chức sử dụng openpyxl để tự động hoá báo cáo tài chính, tạo biểu đồ từ dữ liệu xuất ra từ cơ sở dữ liệu, và áp dụng công thức cùng định dạng cho báo cáo marketing. Thư viện này cung cấp khả năng kiểm soát chi tiết đối với tệp Excel hoàn toàn qua mã Python.

openpyxl là gì?

openpyxl được thiết kế riêng để hỗ trợ bản địa cho các định dạng Excel 2010+. Thư viện làm việc với một số định dạng tệp:

•  .xlsx - Sổ làm việc Excel

•  .xlsm - Sổ làm việc Excel có macro

•  .xltx - Mẫu Excel

•  .xltm - Mẫu Excel có macro

Một lợi thế lớn của openpyxl là bạn không cần cài đặt Excel trên máy. Điều này lý tưởng cho môi trường máy chủ, hệ thống báo cáo tự động và pipeline xử lý dữ liệu. Nhiều thư viện Python khác, bao gồm pandas, dùng openpyxl làm giao diện Excel, cho thấy độ tin cậy và tính năng của nó.

Cài đặt và nhập openpyxl

Cài đặt openpyxl rất đơn giản bằng pip:

pip install openpyxl

Để tăng cường bảo mật khi làm việc với tệp từ nguồn không tin cậy, bạn cũng có thể cài đặt gói tuỳ chọn defusedxml:

pip install defusedxml

Để dùng openpyxl trong script Python của bạn, hãy nhập các lớp cần thiết:

from openpyxl import Workbook, load_workbook

Khái niệm và thuật ngữ chính

Để dùng openpyxl hiệu quả, bạn cần hiểu cấu trúc cơ bản của Excel:

• Workbook: Chính tệp Excel, chứa một hoặc nhiều worksheet
• Worksheet: Các tab/trang tính riêng lẻ bên trong một workbook
• Cell: Ô dữ liệu đơn lẻ trong worksheet, được xác định bởi chữ cột và số hàng (ví dụ "A1")
• Row: Hàng ngang các ô, được đánh số (1, 2, 3...)
• Column: Cột dọc các ô, được đánh chữ (A, B, C...)

Trong openpyxl, bạn có thể tham chiếu ô theo:

• Tham chiếu kiểu Excel: sheet[“A1”]

• Chỉ mục hàng-cột: sheet.cell(row=1, column=1) (Lưu ý: openpyxl dùng chỉ mục bắt đầu từ 1, không phải 0)

Đọc tệp Excel với openpyxl

Dưới đây là ví dụ thực tế về cách đọc dữ liệu từ một tệp Excel:

Tải và khám phá một workbook

from openpyxl import load_workbook

# Load the workbook - use read_only=True for large files
wb = load_workbook('sample.xlsx', read_only=False, data_only=False)
# data_only=True reads values instead of formulas

Các tham số tuỳ chọn kiểm soát cách workbook được tải:

• read_only=True cải thiện hiệu năng với tệp lớn nhưng hạn chế khả năng chỉnh sửa

• data_only=True trả về giá trị đã tính thay vì công thức

Duyệt qua dữ liệu

Để xử lý nhiều ô hiệu quả, hãy dùng các phương thức lặp:

# Iterate through rows
for row in sheet.iter_rows(min_row=1, max_row=5, values_only=True):
    print(row)  # Returns a tuple of values

# Iterate through columns
for column in sheet.iter_cols(min_col=1, max_col=3, values_only=True):
    print(column)  # Returns a tuple of values

# Access a range of cells
cell_range = sheet["A1:C5"]
for row in cell_range:
    for cell in row:
        print(cell.value)

Chuyển đổi sang cấu trúc dữ liệu Python

Bạn có thể dễ dàng biến dữ liệu Excel thành các cấu trúc dữ liệu Python:

# Convert worksheet data to a list of dictionaries
def sheet_to_dict(sheet):
    data = []
    headers = [cell.value for cell in sheet[1]]  # First row as headers
    
    for row in sheet.iter_rows(min_row=2, values_only=True):
        row_data = {}
        for key, value in zip(headers, row):
            row_data[key] = value
        data.append(row_data)
    
    return data

# Example usage
data_dict = sheet_to_dict(sheet)
print(data_dict)

Ghi tệp Excel với openpyxl

Việc tạo và chỉnh sửa tệp Excel với openpyxl cũng đơn giản không kém.

Tạo workbook và ghi dữ liệu cơ bản

from openpyxl import Workbook

# Create a new workbook
wb = Workbook()
sheet = wb.active

# Rename the sheet
sheet.title = "Data"

# Write values to cells
sheet["A1"] = "Name"
sheet["B1"] = "Age"
sheet["C1"] = "City"

# Add data
data = [
    ["Alice", 25, "New York"],
    ["Bob", 30, "San Francisco"],
    ["Charlie", 35, "Chicago"]
]

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

# Save the workbook
wb.save("new_workbook.xlsx")

Chỉnh sửa tệp Excel hiện có

from openpyxl import load_workbook

# Load an existing workbook
wb = load_workbook("existing_file.xlsx")
sheet = wb.active

# Modify cell values
sheet["D1"] = "Updated Data"
sheet.cell(row=5, column=2).value = 42

# Save to a new file (to preserve the original)
wb.save("modified_file.xlsx")

Bổ sung dữ liệu động

Phương thức .append() giúp thêm hàng mới rất dễ dàng:

from openpyxl import Workbook

wb = Workbook()
sheet = wb.active

# Add headers
sheet.append(["Date", "Product", "Quantity", "Price"])

# Add multiple rows of data
sales_data = [
    ["2025-04-01", "Laptop", 5, 1200],
    ["2025-04-01", "Mouse", 10, 25],
    ["2025-04-02", "Monitor", 3, 350]
]

for row in sales_data:
    sheet.append(row)

wb.save("sales_report.xlsx")

Định dạng và tạo kiểu trong Excel

openpyxl cung cấp nhiều tuỳ chọn phong phú để định dạng và tạo kiểu cho ô.

Thêm phông chữ, đường viền và căn chỉnh

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

wb = Workbook()
sheet = wb.active

# Create a header style
header_style = NamedStyle(name="header_style")
header_style.font = Font(bold=True, size=12, color="FFFFFF")
header_style.fill = PatternFill(fill_type="solid", start_color="366092")
header_style.alignment = Alignment(horizontal="center", vertical="center")
header_style.border = Border(
    bottom=Side(border_style="medium", color="000000"),
    left=Side(border_style="thin"),
    right=Side(border_style="thin"),
    top=Side(border_style="thin")
)

# Add the style to the workbook
wb.add_named_style(header_style)

# Apply the style to header row
headers = ["ID", "Product", "Category", "Price"]
for col_idx, header in enumerate(headers, start=1):
    cell = sheet.cell(row=1, column=col_idx, value=header)
    cell.style = "header_style"

# Basic styling without named styles
sheet["A2"].font = Font(bold=True, italic=True)
sheet["A3"].alignment = Alignment(horizontal="center")
sheet["A4"].border = Border(bottom=Side(border_style="thin"))

wb.save("styled_workbook.xlsx")

Định dạng có điều kiện

Định dạng có điều kiện cho phép bạn áp dụng kiểu dựa trên giá trị ô:

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill
from openpyxl.styles.differential import DifferentialStyle
from openpyxl.formatting.rule import Rule, ColorScaleRule

wb = Workbook()
sheet = wb.active

# Add sample data
for row in range(1, 11):
    for col in range(1, 5):
        sheet.cell(row=row, column=col, value=(row-1)*4 + col)

# Add color scale (green to red gradient)
color_scale = ColorScaleRule(
    start_type="min", start_color="63BE7B",  # Green
    mid_type="percentile", mid_value=50, mid_color="FFEB84",  # Yellow
    end_type="max", end_color="F8696B"  # Red
)
sheet.conditional_formatting.add("A1:D10", color_scale)

# Add a rule for values less than 5
red_text = Font(color="FF0000")
red_fill = PatternFill(start_color="FFCCCC", end_color="FFCCCC", fill_type="solid")
dxf = DifferentialStyle(font=red_text, fill=red_fill)
rule = Rule(type="cellIs", operator="lessThan", formula=["5"], dxf=dxf)
sheet.conditional_formatting.add("A1:D10", rule)

wb.save("conditional_format.xlsx")

Tính năng nâng cao

openpyxl hỗ trợ nhiều tính năng Excel nâng cao giúp xử lý bảng tính phức tạp.

Thêm công thức

from openpyxl import Workbook

wb = Workbook()
sheet = wb.active

# Add data
for row in range(1, 6):
    sheet.cell(row=row, column=1, value=row)

# Add formulas
sheet["B1"] = "=A1*2"
sheet["B2"] = "=A2*2"
sheet["C1"] = "=SUM(A1:A5)"
sheet["C2"] = "=AVERAGE(A1:A5)"
sheet["C3"] = "=COUNTIF(A1:A5,\">2\")"

wb.save("formulas.xlsx")

Lưu ý: Khi dùng load_workbook(), openpyxl không tự động tính toán công thức. Để xem giá trị đã tính, hãy dùng load_workbook(“file.xlsx”, data_only=True).  

Chèn biểu đồ

openpyxl có thể tạo nhiều loại biểu đồ:

from openpyxl import Workbook
from openpyxl.chart import BarChart, LineChart, Reference

wb = Workbook()
sheet = wb.active

# Add data
months = ["Jan", "Feb", "Mar", "Apr", "May"]
values = [30, 45, 37, 50, 62]

for i, (month, value) in enumerate(zip(months, values), start=1):
    sheet.cell(row=i, column=1, value=month)
    sheet.cell(row=i, column=2, value=value)

# Create a bar chart
bar_chart = BarChart()
bar_chart.title = "Monthly Sales"
bar_chart.x_axis.title = "Month"
bar_chart.y_axis.title = "Sales"

# Define the data range
data = Reference(sheet, min_col=2, min_row=1, max_row=5)
categories = Reference(sheet, min_col=1, min_row=1, max_row=5)

# Add the data to the chart
bar_chart.add_data(data)
bar_chart.set_categories(categories)

# Add the chart to the worksheet
sheet.add_chart(bar_chart, "D1")

# Create a line chart on the same data
line_chart = LineChart()
line_chart.title = "Monthly Sales Trend"
line_chart.add_data(data)
line_chart.set_categories(categories)
sheet.add_chart(line_chart, "D15")

wb.save("charts.xlsx")

Chèn hình ảnh

from openpyxl import Workbook
from openpyxl.drawing.image import Image

wb = Workbook()
sheet = wb.active

# Add an image
img = Image("logo.png")

# Resize the image (optional)
img.width = 150
img.height = 75

# Add the image to cell A1
sheet.add_image(img, "A1")

wb.save("with_image.xlsx")

Lưu ý: Bạn cần cài đặt thư viện Pillow để làm việc với hình ảnh: pip install pillow

Quản lý sheet, hàng và cột

openpyxl cung cấp các hàm để quản lý cấu trúc tệp Excel của bạn:

Tạo và xoá sheet

from openpyxl import Workbook

wb = Workbook()

# Create new sheets
wb.create_sheet("Data")
wb.create_sheet("Summary", 0)  # Add at the beginning

# Remove a sheet
wb.remove(wb["Sheet"])  # Remove the default sheet

print(wb.sheetnames)  # ['Summary', 'Data']

wb.save("multiple_sheets.xlsx")

Đổi tên sheet

wb = Workbook()
sheet = wb.active
sheet.title = "Sales Report"
wb.save("renamed_sheet.xlsx")

Sao chép worksheet

from openpyxl import Workbook

wb = Workbook()
source = wb.active
source.title = "Original"

# Add some data to copy
source["A1"] = "Test Data"

# Create a copy
wb.copy_worksheet(source)

# The copied sheet will have "Copy of Original" name
wb.save("copied_sheet.xlsx")

Chèn và xoá hàng, cột

from openpyxl import Workbook

wb = Workbook()
sheet = wb.active

# Add some data
for i in range(1, 6):
    for j in range(1, 4):
        sheet.cell(row=i, column=j, value=f"R{i}C{j}")

# Insert a row at position 2
sheet.insert_rows(2)

# Insert multiple rows
sheet.insert_rows(5, 3)  # Insert 3 rows at position 5

# Insert a column at position 2
sheet.insert_cols(2)

# Delete rows
sheet.delete_rows(7, 2)  # Delete 2 rows starting at row 7

# Delete columns
sheet.delete_cols(3)  # Delete column C

wb.save("modified_structure.xlsx")

Cố định ngăn và thêm bộ lọc

from openpyxl import Workbook

wb = Workbook()
sheet = wb.active

# Add headers
headers = ["ID", "Name", "Department", "Salary"]
for col_idx, header in enumerate(headers, start=1):
    sheet.cell(row=1, column=col_idx, value=header)

# Add data
data = [
    [1, "John Smith", "HR", 55000],
    [2, "Jane Doe", "IT", 65000],
    [3, "Bob Johnson", "Finance", 60000]
]
for row_idx, row_data in enumerate(data, start=2):
    for col_idx, value in enumerate(row_data, start=1):
        sheet.cell(row=row_idx, column=col_idx, value=value)

# Freeze the header row
sheet.freeze_panes = "A2"

# Add autofilter
sheet.auto_filter.ref = "A1:D4"

wb.save("freeze_and_filter.xlsx")

Hạn chế và phản hồi từ cộng đồng

Mặc dù openpyxl giàu tính năng, người dùng cũng chỉ ra một số hạn chế:

• Hỗ trợ hạn chế cho named range
• Ví dụ trong tài liệu còn ít cho các tính năng nâng cao
• Hiệu năng có thể chậm với tệp rất lớn (dù chế độ chỉ đọc giúp cải thiện)

Dù vậy, openpyxl có cộng đồng hỗ trợ mạnh mẽ. Bạn có thể báo lỗi trên GitLab tracker, và đôi khi có dịch vụ hỗ trợ chuyên nghiệp. Để biết thêm thông tin và hỗ trợ, bạn có thể tham khảo:

• Tài liệu openpyxl
• Mã nguồn
• Trình theo dõi vấn đề
• Hỗ trợ chuyên nghiệp

Kết luận

openpyxl là thư viện vững chắc cho tự động hoá Excel bằng Python, cung cấp đầy đủ hỗ trợ đọc, ghi và tạo kiểu tệp Excel. Nó cho phép bạn kiểm soát chi tiết đầu ra bảng tính trong quy trình tự động, từ thao tác từng ô đến tạo biểu đồ và áp dụng định dạng phức tạp.

Với những ai cần tạo hoặc phân tích tệp Excel bằng lập trình, đặc biệt khi xây dựng báo cáo hoặc xử lý dữ liệu dạng bảng, openpyxl cung cấp một bộ công cụ phong phú. Khả năng hoạt động mà không cần cài đặt Excel khiến nó đặc biệt hữu ích cho môi trường máy chủ và pipeline dữ liệu tự động.

Dù bạn đang tạo mô hình tài chính, phân tích dữ liệu hay lập báo cáo, openpyxl mang đến các công cụ để xử lý tệp Excel hiệu quả bằng mã Python.