pandas to_excel() và ExcelWriter: ghi Excel nhiều sheet
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
Trong bài này · 12
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.
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:
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ỹ.
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.
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:
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ô 2204Trong 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ự.
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-2Chuỗ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
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_1vàthang_2- Chỉ
thang_2 - Chỉ
thang_1
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
- Đối tượng
Những tên sheet nào làm openpyxl báo ValueError?
Doanh thu 1/2025Kho [nháp]Cà phê & tràTổng quan
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 openpyxlrồi chạy lại - Đổi đuôi file thành
.csvmà giữ nguyên lệnhto_excel()
- Thêm
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.
import pandas as pd
def chia_sheet(bang, cot):
pass # Viết thân hàm thay cho dòng nàyTó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ỗito_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áoValueError.
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ủ đề
Dữ liệu của bài
- Tải xuống
san_pham.csv
CSV · 4.87 KB
Bài này có hữu ích?
Hỏi đáp
Tham vấn chuyên môn
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.