Giới thiệu về các mô-đun xlrd và xlwt
Mô-đun xlrd cung cấp khả năng trích xuất dữ liệu từ tệp Excel (.xls và .xlsx) trên bất kỳ nền tảng nào. Mô-đun xlwt cho phép tạo tệp Excel tương thích với Microsoft Excel 95 đến 2003.
1. Cách cài đặt
# Phương pháp 1: pip3 install xlrd pip3 install xlwt # Phương pháp 2: - Tải gói cài đặt từ trang web chính thức của Python: http://pypi.python.org/pypi/xlrd/(xlwt) - Đặt tệp nén vào thư mục python/Lib/site-packages. - Giải nén tệp: tar zxvf xlrd-1.0.0.tar.gz - Chuyển đến thư mục đã giải nén: cd xlrd-1.0.0 - Chạy lệnh: python setup.py install
2. Hướng dẫn sử dụng xlrd
(1) Chuẩn bị tệp Excel
Chuẩn bị tệp Excel: danh_ba.xls, nội dung như sau:
(2) Lấy tên và đối tượng sheet từ tệp Excel
import xlrd
# Mở tệp Excel, thêm mã hóa utf-8 để tránh lỗi khi có ký tự tiếng Việt
data = xlrd.open_workbook('danh_ba.xls', encoding_override='utf-8')
# Lấy tất cả tên sheet
sheetnames = data.sheet_names()
print(sheetnames)
"""
['Ngân hàng 1', 'Ngân hàng 2']
"""
# Lấy tên sheet theo chỉ số
sheet_name = data.sheet_names()[1]
print(sheet_name)
"""
Ngân hàng 2
"""
# Lấy đối tượng sheet theo tên
table_name = data.sheet_by_name(sheet_name)
print(table_name)
"""
"""
# Lấy đối tượng sheet theo chỉ số
table_index = data.sheet_by_index(0)
print(table_index)
"""
"""
(3) Thao tác với hàng và cột trong sheet
# Lấy số hàng và cột
nrows = table_name.nrows # Tổng số hàng
ncols = table_name.ncols # Tổng số cột
print("Bảng Ngân hàng 2, tổng số hàng: %s, tổng số cột: %s" % (nrows, ncols))
"""
Bảng Ngân hàng 2, tổng số hàng: 5, tổng số cột: 7
"""
# Lấy giá trị của một hàng hoặc cột
sheet_row_val = table_name.row_values(3) # Giá trị của hàng thứ 4
sheet_col_val = table_name.col_values(3) # Giá trị của cột thứ 4
print("Giá trị hàng: %s , \nGiá trị cột: %s" % (sheet_row_val, sheet_col_val))
"""
Giá trị hàng: ['', 'Lý Cửu', 'Phát triển', 'IOS phát triển', 87888.0, 13213123.0, 'wang1@164.com'] ,
Giá trị cột: ['', 'Nhiệm vụ công việc', 'Thiết kế UI', 'IOS phát triển', 'Bảo trì phần cứng']
"""
# Lấy độ dài của hàng hiện tại
print(table_name.row_len(3))
print(table_index.row_len(3))
"""
7
8
"""
(4) Thao tác với ô trong sheet
Ô là đơn vị nhỏ nhất trong bảng, có thể được chia hoặc hợp nhất. Mỗi dữ liệu được nhập và sửa đổi đều trong ô.
# Lấy nội dung của ô cụ thể
print(table_name.cell(1,0).value) # Hàng thứ 2, cột thứ 1
print(table_name.cell_value(2,1)) # Hàng thứ 3, cột thứ 2
print(table_name.row(1)[0].value) # Hàng thứ 2, cột thứ 1
"""
Tên tổ chức
Trương Bát
Tên tổ chức
"""
# Lấy kiểu dữ liệu của ô
# ctype: 0 empty, 1 string, 2 number, 3 date, 4 boolean, 5 error
print(table_index.cell(1,0).ctype) # Bảng Ngân hàng 1, hàng 2, cột 1
print(table_index.cell(3,4).ctype) # Bảng Ngân hàng 1, hàng 4, cột 5
print(table_index.cell(2,6).ctype) # Bảng Ngân hàng 1, hàng 3, cột 7
"""
1
2
3
"""
# Xử lý ô chứa ngày
from datetime import datetime, date
if table_index.cell(2,6).ctype == 3:
print(table_index.cell(2, 6).value)
date_value = xlrd.xldate_as_tuple(table_index.cell(2, 6).value, data.datemode)
print(date_value)
print(date(*date_value[:3]))
print(date(*date_value[:3]).strftime('%Y/%m/%d'))
"""
43592.0
(2019, 5, 7, 0, 0, 0)
2019-05-07
2019/05/07
"""
# Lấy giá trị số trong ô
if table_index.cell(3, 4).ctype == 2:
print(table_index.cell(3, 4).value)
num_value = int(table_index.cell(3, 4).value) # Chuyển sang kiểu int
print(num_value)
"""
888.0
888
"""
(5) Lấy nội dung của ô hợp nhất
Sử dụng thuộc tính merged_cells để lấy nội dung của ô hợp nhất.
# Cần thêm tham số formatting_info khi mở tệp
xls_data = xlrd.open_workbook('danh_ba.xls', formatting_info=True)
sheet_data = xls_data.sheet_by_name('Ngân hàng 2')
print(sheet_data.merged_cells)
"""
[(0, 1, 0, 7), (2, 5, 0, 1)]
"""
Các tham số trả về bởi merged_cells: (row, row_range, col, col_range).
Vì vậy, (0,1,0,7) biểu thị cột 1 đến cột 7 được hợp nhất; (2,5,0,1) biểu thị hàng 3 đến hàng 6 được hợp nhất.
# Lấy nội dung của các ô hợp nhất
merge_value = []
for (row, row_range, col, col_range) in sheet_data.merged_cells:
merge_value.append((row, col))
"""
[(0, 0), (2, 0)]
"""
print(merge_value)
for i in merge_value:
print(sheet_data.cell(i[0], i[1]).value)
"""
Ngân hàng 2
Ngân hàng 2
"""
3. Hướng dẫn sử dụng xlwt
Sử dụng mô-đun xlwt để tạo, thiết lập và lưu tệp Excel.
import xlwt
# Tạo workbook và thiết lập mã hóa
workbook = xlwt.Workbook(encoding='utf-8')
# Tạo worksheet
worksheet = workbook.add_sheet("My Worksheet")
# Thiết lập kiểu (tùy chọn)
style = xlwt.XFStyle() # Khởi tạo kiểu
font = xlwt.Font() # Tạo font
font.name = "Times New Roman"
font.bold = True # In đậm
font.underline = True # Gạch chân
font.italic = True # In nghiêng
style.font = font # Đặt kiểu
# Ghi vào Excel, tham số tương ứng là hàng, cột, giá trị
# Ghi không có kiểu
worksheet.write(1,0, 'Unformatted value')
# Ghi có kiểu
worksheet.write(2,0, 'Formatted value', style)
# Lưu tệp
workbook.save("xlwt_test.xls")
(1) Đặt chiều rộng của ô
import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
worksheet.write(0, 0,'My Cell Contents')
# Đặt chiều rộng của cột
worksheet.col(0).width = 30003
workbook.save('cell_width.xls')
(2) Nhập ngày vào ô
import xlwt
import datetime
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
style = xlwt.XFStyle()
style.num_format_str = 'M/D/YY' # Các tùy chọn khác: D-MMM-YY, D-MMM, MMM-YY, h:mm, h:mm:ss, h:mm, h:mm:ss, M/D/YY h:mm, mm:ss, [h]:mm:ss, mm:ss.0
worksheet.write(0, 0, datetime.datetime.now(), style)
workbook.save('Excel_Workbook.xls')
(3) Thêm công thức vào ô
import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
worksheet.write(0, 0, 5) # Kết quả 5
worksheet.write(0, 1, 2) # Kết quả 2
# Thêm công thức
worksheet.write(1, 0, xlwt.Formula('A1*B1')) # Nên kết quả là "10" (A1[5] * A2[2])
worksheet.write(1, 1, xlwt.Formula('SUM(A1,B1)')) # Nên kết quả là "7" (A1[5] + A2[2])
workbook.save('Excel_formula.xls')
(4) Thêm siêu liên kết vào ô
import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
worksheet.write(0, 0, xlwt.Formula('HYPERLINK("http://www.baidu.com";"Baidu")')) # Hiển thị văn bản "Baidu" liên kết đến http://www.baidu.com
workbook.save('Excel_hyperlink.xls')
(5) Hợp nhất cột và hàng
import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
worksheet.write_merge(0, 0, 0, 3, 'First Merge') # Hợp nhất cột 0 đến cột 3 của hàng 0.
font = xlwt.Font() # Tạo font
font.bold = True # Đặt font thành in đậm
style = xlwt.XFStyle() # Tạo kiểu
style.font = font # Thêm font in đậm vào kiểu
worksheet.write_merge(1, 2, 0, 3, 'Second Merge', style) # Hợp nhất cột 0 đến cột 3 của hàng 1 đến hàng 2.
workbook.save('Excel_merge.xls')
(6) Các thiết lập khác cho ô
"""
Đặt cách căn chỉnh nội dung của ô:
"""
import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
alignment = xlwt.Alignment() # Tạo căn chỉnh
alignment.horz = xlwt.Alignment.HORZ_CENTER # Có thể là: HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED, HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED
alignment.vert = xlwt.Alignment.VERT_CENTER # Có thể là: VERT_TOP, VERT_CENTER, VERT_BOTTOM, VERT_JUSTIFIED, VERT_DISTRIBUTED
style = xlwt.XFStyle() # Tạo kiểu
style.alignment = alignment # Thêm căn chỉnh vào kiểu
worksheet.write(0, 0, 'Cell Contents', style)
workbook.save('Excel_Workbook.xls')
"""
Thêm viền cho ô:
"""
import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
borders = xlwt.Borders() # Tạo viền
borders.left = xlwt.Borders.DASHED
DASHED: Đường đứt quãng
NO_LINE: Không có đường
THIN: Đường mỏng
# Có thể là: NO_LINE, THIN, MEDIUM, DASHED, DOTTED, THICK, DOUBLE, HAIR, MEDIUM_DASHED, THIN_DASH_DOTTED, MEDIUM_DASH_DOT_DOTTED, SLANTED_MEDIUM_DASH_DOTTED, hoặc 0x00 đến 0x0D.
borders.right = xlwt.Borders.DASHED
borders.top = xlwt.Borders.DASHED
borders.bottom = xlwt.Borders.DASHED
borders.left_colour = 0x40
borders.right_colour = 0x40
borders.top_colour = 0x40
borders.bottom_colour = 0x40
style = xlwt.XFStyle() # Tạo kiểu
style.borders = borders # Thêm viền vào kiểu
worksheet.write(0, 0, 'Cell Contents', style)
workbook.save('Excel_Workbook.xls')
"""
Đặt màu nền cho ô:
"""
import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
pattern = xlwt.Pattern() # Tạo mẫu
pattern.pattern = xlwt.Pattern.SOLID_PATTERN # Có thể là: NO_PATTERN, SOLID_PATTERN, hoặc 0x00 đến 0x12
pattern.pattern_fore_colour = 5 # Có thể là: 8 đến 63. 0 = Đen, 1 = Trắng, 2 = Đỏ, 3 = Xanh lá, 4 = Xanh dương, 5 = Vàng, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Xanh lá đậm, 18 = Xanh dương đậm, 19 = Vàng đậm, 20 = Magenta đậm, 21 = Teal, 22 = Xám sáng, 23 = Xám tối, v.v...
style = xlwt.XFStyle() # Tạo kiểu
style.pattern = pattern # Thêm mẫu vào kiểu
worksheet.write(0, 0, 'Cell Contents', style)
workbook.save('Excel_Workbook.xls')
4. Sử dụng xlutils để sửa tệp Excel
Không có phương pháp trực tiếp để sửa tệp .xls. Thông thường, bạn sẽ đọc tệp, sao chép dữ liệu, sửa đổi, và sau đó lưu. Khi sao chép, bạn cần sử dụng phương pháp từ xlutils:
from xlrd import open_workbook
from xlutils.copy import copy
# Mở tệp
rb = open_workbook("example.xls")
# Sao chép
wb = copy(rb)
# Chọn sheet
s = wb.get_sheet(0)
# Ghi dữ liệu
s.write(0, 1, 'new data')
# Lưu
wb.save('example.xls')
5. Xuất Excel trong Django
Trong dự án Django, xuất dữ liệu từ cơ sở dữ liệu ra tệp Excel.
# Đăng ký khách hàng (học sinh) vào stark
class CustomerConfig(ModelStark):
"""Code đã lược bỏ"""
def excel_export(self, request):
"""Xuất tệp Excel"""
list_obj = models.Customer.objects.all().order_by("create_time")
if list_obj:
# Tạo workbook
ws = Workbook(encoding="UTF-8")
w = ws.add_sheet(u'Báo cáo dữ liệu trang 1')
w.write(0, 0, 'id')
w.write(0, 1, u'Họ tên')
w.write(0, 2, u'Giới tính')
w.write(0, 3, u'Dân tộc')
w.write(0, 4, u'Quê quán')
w.write(0, 5, u'Số CMND')
w.write(0, 6, u'Địa chỉ gửi thông báo')
w.write(0, 7, u'Mã bưu điện')
w.write(0, 8, u'Số điện thoại')
w.write(0, 9, u'Số điện thoại 2')
w.write(0, 10, u'Trường học')
w.write(0, 11, u'Chuyên ngành')
w.write(0, 12, u'Ngày tạo')
w.write(0, 13, u'Người giới thiệu')
w.write(0, 14, u'Ghi chú')
# Ghi dữ liệu
excel_row = 1
for obj in list_obj:
data_id = obj.id
data_name = obj.name
# data_gender = obj.gender_choices # Chỉ hiển thị số
data_gender = obj.get_gender_display()
data_nation = obj.nation
data_birth = obj.birth_place
data_identity_num = obj.identity_num
data_address = obj.address
data_postcode = obj.postcode
data_tel = obj.tel
data_tel2 = obj.tel_2
data_school = obj.stu_school.title
data_course = obj.course.first().name
data_time = obj.create_time.strftime('%Y-%m-%d %H:%M:%S')
data_consultant = obj.consultant.name
data_memo = obj.memo
w.write(excel_row, 0, data_id)
w.write(excel_row, 1, data_name)
w.write(excel_row, 2, data_gender)
w.write(excel_row, 3, data_nation)
w.write(excel_row, 4, data_birth)
w.write(excel_row, 5, data_identity_num)
w.write(excel_row, 6, data_address)
w.write(excel_row, 7, data_postcode)
w.write(excel_row, 8, data_tel)
w.write(excel_row, 9, data_tel2)
w.write(excel_row, 10, data_school)
w.write(excel_row, 11, data_course)
w.write(excel_row, 12, data_time)
w.write(excel_row, 13, data_consultant)
w.write(excel_row, 14, data_memo)
excel_row += 1
# Kiểm tra tệp có tồn tại
# Mã trong khung này dùng để lưu tệp cục bộ, nếu không cần hãy xóa
###########################
exist_file = os.path.exists("stu_info.xls")
if exist_file:
os.remove(r"stu_info.xls")
ws.save("customer_info.xls")
############################
sio = BytesIO()
ws.save(sio)
sio.seek(0)
response = HttpResponse(sio.getvalue(), content_type='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment; filename=stu_info.xls'
response.write(sio.getvalue())
return response