Bỏ qua đến nội dung chính
Mục lục

pandas to_excel() và ExcelWriter: ghi Excel nhiều sheet

AIDev Team · duyệt: Nguyễn Việt Chung

Bài 5/12 · Chương 3

Đọc và ghi dữ liệu

  • 7 phút
  • Cơ bản
  • Python 3.14
  • Cập nhật

Phương thức to_excel() của pandas ghi một DataFrame ra file Excel .xlsx, còn pd.ExcelWriter gom nhiều bảng vào các sheet của cùng một file. Bạn dùng chúng khi phải gửi báo cáo cho người chỉ mở Excel: mỗi danh mục hàng một sheet, kèm một sheet tổng quan ở đầu.

Cần biết trước: read_excel(), to_csv() và mở và ghi file.

Ghi một bảng ra Excel bằng to_excel()

Cũng như khi đọc, pandas cần gói openpyxl để ghi .xlsx (cài bằng python -m pip install openpyxl). Trình chạy trên trang này không có openpyxl, nên các khối ghi Excel trong bài không có nút Chạy; kết quả trong comment là của một lần chạy trên máy đã cài gói. Bài Blog đọc CSV và Excel bằng pandas so hai định dạng.

python
import pandas as pd

sp = pd.read_csv("san_pham.csv", dtype={"ma_sp": str})
sp.to_excel("san_pham.xlsx", sheet_name="bang_gia", index=False)
print(pd.read_excel("san_pham.xlsx").shape)   # (60, 8)

to_excel() nhận tên file, rồi sheet_name đặt tên sheet (mặc định Sheet1). index=False có cùng vai trò như ở to_csv(): thiếu nó, cột index thành cột A không tên, và đọc lại cho cột Unnamed: 0.

Ghi nhiều sheet vào một file với ExcelWriter

Mỗi lần gọi to_excel("ten.xlsx") tạo lại file từ đầu. Ghi hai bảng vào cùng tên file bằng hai lệnh thì file chỉ còn sheet của lệnh sau:

python
import pandas as pd

sp = pd.read_csv("san_pham.csv", dtype={"ma_sp": str})
sp.head(3).to_excel("hai_sheet.xlsx", sheet_name="dau")
sp.tail(2).to_excel("hai_sheet.xlsx", sheet_name="cuoi")
print(pd.ExcelFile("hai_sheet.xlsx").sheet_names)   # ['cuoi']

pd.ExcelWriter mở file một lần và giữ nó mở cho nhiều lần ghi, như một cuốn sổ bạn mở ra, dán từng bảng vào từng trang rồi mới gập lại. Dùng nó với with để file được đóng và lưu khi khối kết thúc, giống cách with open(...) làm với file văn bản. Mỗi to_excel() bên trong nhận đối tượng writer thay cho tên file. Vòng lặp dưới dùng hai thứ của bài sau: sp["danh_muc"].unique() cho các danh mục khác nhau theo thứ tự gặp, còn sp[sp["danh_muc"] == dm] giữ các dòng thuộc danh mục dm; bài lọc dữ liệu và value_counts() dạy kỹ.

python
import pandas as pd

sp = pd.read_csv("san_pham.csv", dtype={"ma_sp": str})
with pd.ExcelWriter("bao_cao.xlsx") as tep:
    sp.to_excel(tep, sheet_name="tat_ca", index=False)
    for dm in sp["danh_muc"].unique():
        sp[sp["danh_muc"] == dm].to_excel(
            tep, sheet_name=dm, index=False)
print(pd.ExcelFile("bao_cao.xlsx").sheet_names)
# ['tat_ca', 'Cà phê & trà', 'Đặc sản khô', 'Gia dụng',
#  'Văn phòng phẩm', 'Chăm sóc cá nhân']

Sheet nằm trong file theo đúng thứ tự các lệnh ghi. Phần chia bảng không cần Excel, nên chạy được ngay trên trang: ví dụ dưới dựng dict tên sheet và bảng, chính là thứ vòng lặp trên ghi ra.

python
import pandas as pd

sp = pd.read_csv("san_pham.csv", dtype={"ma_sp": str},
                 usecols=["ma_sp", "ten_sp", "danh_muc", "gia_ban"])
cac_sheet = {}
for dm in sp["danh_muc"].unique():
    cac_sheet[dm] = sp[sp["danh_muc"] == dm]
for ten, bang in cac_sheet.items():
    print(ten, bang.shape)

KẾT QUẢ

Cà phê & trà (12, 4)
Đặc sản khô (12, 4)
Gia dụng (12, 4)
Văn phòng phẩm (12, 4)
Chăm sóc cá nhân (12, 4)

unique() giữ thứ tự xuất hiện trong bảng, nên sheet đầu là danh mục của dòng đầu. Đổi "danh_muc" thành "don_vi" ở cả hai chỗ trong vòng for dm rồi chạy lại: mỗi dòng in là một đơn vị tính, như Gói (11, 4).

Thêm sheet tổng quan đứng đầu báo cáo

Người đọc báo cáo bận thường chỉ xem sheet đầu, nên sheet đó nên là bảng tóm tắt. Tính nó trước: groupby("danh_muc")["ton_kho"].sum() cộng tồn kho của từng danh mục, bài groupby() dạy kỹ; rồi ghi nó trước các sheet chi tiết:

python
import pandas as pd

sp = pd.read_csv("san_pham.csv", dtype={"ma_sp": str})
tong_quan = sp.groupby("danh_muc")["ton_kho"].sum().reset_index()
print(tong_quan)

KẾT QUẢ

           danh_muc  ton_kho
0  Chăm sóc cá nhân     2416
1      Cà phê & trà     1579
2          Gia dụng     1236
3    Văn phòng phẩm     1765
4       Đặc sản khô     2204

Trong khối with pd.ExcelWriter(...), dòng tong_quan.to_excel(tep, sheet_name="tong_quan", index=False) đặt trước vòng lặp là đủ để sheet này đứng đầu file.

Đặt tên sheet mà Excel chấp nhận

Tên sheet trong Excel dài tối đa 31 ký tự và không được chứa bảy ký tự [ ] : * ? / \. Gặp ký tự cấm, openpyxl dừng ngay.

Tên dài hơn 31 ký tự thì openpyxl chỉ cảnh báo, nhưng Excel có thể không mở được file. Ví dụ dưới làm sạch một tên sheet: thay từng ký tự cấm rồi cắt còn 31 ký tự.

python
ten = "Tồn kho từng chi nhánh: quý 3/2025 [nháp]"
print(len(ten))
for ky_tu in "[]:*?/\\":
    ten = ten.replace(ky_tu, "-")
print(ten[:31])

KẾT QUẢ

41
Tồn kho từng chi nhánh- quý 3-2

Chuỗi "[]:*?/\\" có bảy ký tự; \\ là cách viết một dấu gạch chéo ngược trong chuỗi Python.

Kiểm tra nhanh

  1. Bạn gọi to_excel("bc.xlsx", sheet_name="thang_1") rồi to_excel("bc.xlsx", sheet_name="thang_2"). File bc.xlsx có những sheet nào?

    • thang_1 và thang_2
    • Chỉ thang_2
    • Chỉ thang_1
  2. Trong khối with pd.ExcelWriter("bc.xlsx") as tep:, đối số đầu của mỗi to_excel() là gì?

    • Đối tượng tep
    • Chuỗi "bc.xlsx"
    • Tên sheet
  3. Những tên sheet nào làm openpyxl báo ValueError?

    • Doanh thu 1/2025
    • Kho [nháp]
    • Cà phê & trà
    • Tổng quan
  4. Máy báo ModuleNotFoundError: No module named 'openpyxl' khi gọi to_excel(). Bạn làm gì?

    • Thêm index=False
    • Cài gói bằng python -m pip install openpyxl rồi chạy lại
    • Đổi đuôi file thành .csv mà giữ nguyên lệnh to_excel()
Đúng hết lần đầu +15 XP

Bài tập

Chia bảng thành các sheet hợp lệ

Bạn sắp ghi báo cáo tồn kho, mỗi giá trị của một cột là một sheet. Viết thân hàm chia_sheet(bang, cot): với mỗi giá trị khác nhau trong cột cot, theo thứ tự xuất hiện đầu tiên trong bảng, lấy các dòng có giá trị đó làm bảng của một sheet.

Tên sheet là giá trị đó dạng chuỗi, với từng ký tự [ ] : * ? / \ thay bằng -, rồi cắt còn tối đa 31 ký tự. Bảng của mỗi sheet có index đánh lại từ 0. Hàm không sửa bang.

Trả về: dict tên sheet và bảng của sheet đó, một dict.

python
import pandas as pd

def chia_sheet(bang, cot):
    pass  # Viết thân hàm thay cho dòng này

Tóm tắt

  • df.to_excel("ten.xlsx", sheet_name="...", index=False) ghi một bảng ra Excel; cần gói openpyxl.

  • Mỗi to_excel() nhận tên file tạo lại file từ đầu, nên lệnh ghi sau xóa sheet của lệnh trước.

  • with pd.ExcelWriter("ten.xlsx") as tep: mở file một lần; mỗi to_excel(tep, sheet_name=...) thêm một sheet theo thứ tự ghi.

  • Ghi sheet tổng quan trước để nó đứng đầu file.

  • Tên sheet tối đa 31 ký tự, không chứa [ ] : * ? / \; ký tự cấm làm openpyxl báo ValueError.

Câu hỏi thường gặp

Xlsxwriter là gì, có cần cài thay cho openpyxl không?

XlsxWriter là một gói ghi .xlsx khác, mạnh về định dạng ô, biểu đồ và độ rộng cột, nhưng không đọc được Excel. Cài nó rồi viết pd.ExcelWriter("bc.xlsx", engine="xlsxwriter"); không có engine, pandas dùng XlsxWriter nếu máy có, không thì openpyxl. Chỉ cần đọc và ghi bảng thường thì openpyxl là đủ.

Pandas ExcelWriter có thêm sheet vào file Excel đã có sẵn được không?

Được, với pd.ExcelWriter("bc.xlsx", mode="a", if_sheet_exists="replace") và engine openpyxl: các sheet khác của file giữ nguyên, sheet cùng tên được ghi đè. Không có mode="a", ExcelWriter tạo lại file từ đầu như to_excel().

Xuất file Excel bằng Python có giữ số 0 đầu của mã sản phẩm không?

Có, nếu cột mã là chuỗi khi ghi: ô Excel giữ chữ 0001. Nếu cột đã thành số từ lúc đọc, số 0 mất trước khi ghi; đọc với dtype={"ma_sp": str} như bài read_csv().

Khi nào nên gửi CSV thay cho Excel?

Khi người nhận đưa file vào một chương trình khác, hay bảng quá lớn: CSV ghi nhanh, không cần gói thêm, và đọc được bằng hầu hết công cụ dữ liệu. Excel hợp khi người đọc cần nhiều sheet trong một file. Bài Blog Python cho người dùng Excel bàn thêm về chọn định dạng.

Chủ đề

pandas to excel

Dữ liệu của bài

Bài này có hữu ích?

Hỏi đáp

Đang tải câu hỏi…

Tham vấn chuyên môn

Nguyễn Việt Chung

Nhà sáng lập AIDev và VietPlatform · Thạc sĩ AI MSE

  • Tốt nghiệp thủ khoa xuất sắc Thạc sĩ AI MSE.
  • Đồng sáng lập, CTO E-University, SVOnline: số hóa 10+ đại học, 40.000+ sinh viên.
  • Xây hệ thống AI cho doanh nghiệp tại VietPlatform.
Biên soạn: AIDev Team · Rà soát · Quy trình biên tập