pandas read_excel(): đọc file Excel, chọn sheet
Bài 4/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
Hàm pd.read_excel() của pandas đọc một sheet trong file Excel .xlsx thành DataFrame, giống read_csv() đọc file CSV. Bạn dùng nó khi kế toán hay phòng kho gửi bảng tính có nhiều sheet và bạn muốn lấy đúng sheet cần, hoặc gom cả file vào code thay cho chép tay từng ô.
Cần biết trước: read_csv(), read_csv nâng cao và dictionary.
Cài openpyxl để pandas đọc được Excel
pandas không tự đọc định dạng .xlsx; nó nhờ gói openpyxl, một thư viện riêng chuyên đọc ghi file Excel. Cài cả hai bằng một lệnh trong terminal (trên macOS, Linux gõ python3 thay cho python):
python -m pip install pandas openpyxl
Trình chạy trên trang này không có openpyxl, nên các khối gọi read_excel() trong bài không có nút Chạy: chép chúng về máy đã cài openpyxl để chạy. Thiếu gói này, lệnh đọc Excel dừng ở cùng một lỗi:
Đọc một sheet theo tên với sheet_name
Giả sử file san_pham.xlsx có hai sheet: bang_gia (mã, tên, danh mục, giá bán của 60 sản phẩm) và ton_kho (mã và số tồn). Không có sheet_name, read_excel() đọc sheet đầu tiên. sheet_name nhận tên sheet, hoặc vị trí của sheet đếm từ 0. Kết quả ghi trong comment là của một lần chạy trên máy có openpyxl:
import pandas as pd
gia = pd.read_excel("san_pham.xlsx", sheet_name="bang_gia",
dtype={"ma_sp": str})
print(gia.shape) # (60, 4)
print(gia["ma_sp"].head(3).to_list()) # ['0001', '0002', '0003']
ton = pd.read_excel("san_pham.xlsx", sheet_name=1)
print(ton.columns.to_list()) # ['ma_sp', 'ton_kho']
Tham số quen thuộc của read_csv() dùng y như cũ: dtype giữ số 0 đầu của mã, vì read_excel() cũng tự đoán cột mã là số. Ô Excel đã lưu số ở dạng số, nên không cần sep, thousands hay decimal. Tên sheet phân biệt chữ hoa, dấu và dấu gạch dưới: sheet_name="Bảng giá" dừng với ValueError báo không có sheet đó.
Máy chưa cài được openpyxl thì mở file bằng Excel, lưu sheet cần thành "CSV UTF-8", rồi đọc bằng read_csv() với cùng tham số. Ví dụ dưới chạy trên trang, đọc bản CSV của sheet bang_gia:
import pandas as pd
sp = pd.read_csv("san_pham.csv", dtype={"ma_sp": str},
usecols=["ma_sp", "ten_sp", "gia_ban"])
print(sp.shape)
print(sp.head(3))KẾT QUẢ
(60, 3)
ma_sp ten_sp gia_ban
0 0001 Cà phê rang xay Đắk Lắk 500g 165000
1 0002 Cà phê hạt Robusta Gia Lai 1kg 290000
2 0003 Cà phê phin giấy hộp 10 gói 85000Đọc mọi sheet thành dict với sheet_name=None
Báo cáo bán hàng của chuỗi quán thường để mỗi chi nhánh một sheet. sheet_name=None đọc hết các sheet một lần và trả về một dict: khóa là tên sheet theo thứ tự trong file, giá trị là DataFrame của sheet đó. Dict này giống một tủ hồ sơ: mỗi ngăn dán nhãn tên chi nhánh, bên trong là một bảng.
import pandas as pd
cac_sheet = pd.read_excel("ban_hang.xlsx", sheet_name=None)
print(type(cac_sheet), list(cac_sheet))
Trên trang, ví dụ dưới dựng sẵn đúng dict mà lệnh trên trả về cho một file ba sheet, mỗi bảng đọc từ một chuỗi CSV:
import io
import pandas as pd
HUE = "mon,so_ly\nCà phê sữa,42\nBạc xỉu,18"
DA_LAT = "mon,so_ly\nTrà đào,25"
HA_NOI = "mon,so_ly\nCà phê sữa,30\nTrà đào,12"
cac_sheet = {"Huế": pd.read_csv(io.StringIO(HUE)),
"Đà Lạt": pd.read_csv(io.StringIO(DA_LAT)),
"Hà Nội": pd.read_csv(io.StringIO(HA_NOI))}
print(type(cac_sheet), list(cac_sheet))
for ten, bang in cac_sheet.items():
print(ten, len(bang), bang["so_ly"].sum())KẾT QUẢ
<class 'dict'> ['Huế', 'Đà Lạt', 'Hà Nội']
Huế 2 60
Đà Lạt 1 25
Hà Nội 2 42list(cac_sheet) cho tên các sheet, và .items() đi qua từng cặp tên, bảng như với dict bất kỳ. Đổi Trà đào,25 thành Trà đào,31 rồi chạy lại: dòng của Đà Lạt in Đà Lạt 1 31.
Bỏ dòng tiêu đề thừa trong sheet
Sheet do người soạn tay thường có tên báo cáo ở vài dòng đầu, rồi mới tới dòng tên cột. read_excel() nhận skiprows, header, names, usecols, nrows, na_values với cùng nghĩa như ở read_csv():
import pandas as pd
bc = pd.read_excel("bao_cao_thang.xlsx", sheet_name="Huế",
skiprows=2, usecols=["mon", "so_ly"])
Dòng 1 và 2 của sheet bị bỏ, dòng 3 thành tên cột, và chỉ hai cột được giữ. Một khác biệt với CSV: ô gộp (merge cells) ở phần tiêu đề chỉ giữ giá trị ở ô đầu, các ô còn lại đọc thành NaN, nên bỏ hẳn phần đó bằng skiprows gọn hơn là đọc rồi sửa.
Kiểm tra nhanh
File Excel có ba sheet bang_gia, ton_kho, ghi_chu. Lệnh nào đọc sheet ton_kho?
pd.read_excel("san_pham.xlsx")pd.read_excel("san_pham.xlsx", sheet_name=1)pd.read_excel("san_pham.xlsx", sheet_name=2)
Với sheet_name=None, read_excel() trả về gì?
- Một DataFrame ghép mọi sheet
- Một dict, khóa là tên sheet, giá trị là DataFrame
- Một list các DataFrame
Máy báo ImportError:
Import openpyxlfailed khi gọi read_excel(). Những cách nào sửa được?- Chạy
python -m pip install openpyxltrong đúng môi trường của code - Lưu sheet thành CSV UTF-8 rồi đọc bằng
read_csv() - Thêm
sheet_name=None - Thêm
encoding="utf-8"
- Chạy
Trong khung bẫy của bài, lệnh print(cac_sheet.head()) báo lỗi gì?
- Báo lỗi
AttributeError - Báo lỗi
KeyError - Báo lỗi
ImportError
- Báo lỗi
Bài tập
Tìm các sheet có một cột cho trước
Phòng kho gửi file Excel nhiều sheet, và bạn đã đọc nó bằng read_excel(..., sheet_name=None) thành dict cac_sheet. Viết thân hàm sheet_co_cot(cac_sheet, ten_cot): tìm các sheet mà bảng có cột tên đúng là ten_cot, để biết nên ghép những sheet nào theo cột đó.
Tên sheet giữ thứ tự của dict; không sheet nào có cột đó thì kết quả rỗng. Hàm không sửa dict cac_sheet của nơi gọi.
Trả về: tên các sheet có cột ten_cot, một list.
import pandas as pd
def sheet_co_cot(cac_sheet, ten_cot):
pass # Viết thân hàm thay cho dòng nàyTóm tắt
pd.read_excel("file.xlsx")đọc sheet đầu tiên thành DataFrame; pandas cần gói openpyxl để đọc.xlsx.Thiếu openpyxl thì lệnh đọc báo
ImportError; cài bằngpython -m pip install openpyxl.sheet_name="ten"đọc sheet theo tên,sheet_name=1theo vị trí đếm từ0.sheet_name=Nonetrả về dict tên sheet và DataFrame; lấy một bảng ra bằng tên trước khi gọihead().dtype,skiprows,usecols,na_valuesdùng như ởread_csv().
Câu hỏi thường gặp
Lệnh pip install pandas openpyxl gõ ở đâu, cài một lần là xong chưa?
Gõ trong terminal, không gõ trong file .py: python -m pip install pandas openpyxl. Mỗi môi trường ảo cần cài riêng; code chạy trong môi trường nào thì cài vào đó, theo bài môi trường ảo venv. Cài xong, import pandas là đủ, không cần import openpyxl. Muốn tự động hóa cả việc định dạng ô hay ghi công thức, bài Blog Python cho người dùng Excel đi qua openpyxl.
Đọc file Excel bằng pandas có đọc được file .xls cũ không?
File .xls của Excel 97–2003 cần gói xlrd thay cho openpyxl. Cách gọn hơn là mở file bằng Excel và lưu lại thành .xlsx, rồi đọc như bài này.
read excel trong Python có giữ công thức trong ô không?
Không, read_excel() đọc giá trị mà công thức đã tính và Excel đã lưu. File tạo bằng code mà chưa mở bằng Excel lần nào có thể chưa có giá trị đã tính, khi đó ô đọc ra NaN.
Làm sao ghi ngược DataFrame ra file Excel nhiều sheet?
Dùng to_excel() với pd.ExcelWriter, cũng cần openpyxl. Bài to_excel() dạy cách đó; bài Blog đọc CSV và Excel bằng pandas so hai đị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.