Lập Trình Python Cho Excel/Python For Excel #002 - Bảng Dữ Liệu - Căn Lề, Kẻ Bảng, Kích Thước, Font Chữ

 NỘI DUNG

1. Định dạng font/kiểu chữ, căn chỉnh lề, kẻ bảng, điều chỉnh kích thước

2. Các ví dụ làm quen với bảng dữ liệu

3. Copy bảng dữ liệu/copy sheet

THỰC HIỆN

1. Định dạng font/kiểu chữ, căn chỉnh lề, kẻ bảng, điều chỉnh kích thước

STT	Yêu Cầu Cấu Hình	Commands	Giải Thích
1	Import thư viện	import xlwings as xw
		from xlwings.constants import HAlign, VAlign
2		xw.Book()
3		sht = xw.sheets.active
4		sht.book.save("BangDuLieu.xlsx")
5	Gán dữ liệu cho ô	sht["C1"].value = "NOI DUNG"	gán ô C1 có giá trị là: NOI DUNG
6	Định dạng font chữ, kiểu chữ cho Cell	sht["C1"].font.name = "Arial"	gán font chữ cho ô C1
		sht["C1"].font.size = 15	Cỡ chữ là 15
		sht["C1"].font.bold = True	định dạng ô C1 là in đậm
		sht["C1"].font.italic = True	định dạng ô C1 là in nghiêng
		sht["C1"].api.Font.Underline = True	định dạng ô C1 gạch chân
7	merge cell	sht["A1:C1"].merge()	merge từ ô A1 -> C1
8		sht["A1:C1"].wrap_text = True	Tự động xuống hàng nếu chữ dài hơn độ rộng của ô
9	Căn giữa	sht["A1"].api.VerticalAlignment = VAlign.xlVAlignCenter
		sht["A1"].api.HorizontalAlignment = HAlign.xlHAlignCenter
10	Gán dữ liệu theo chiều dọc	list_no = list(range(1,10))	Tạo ra list có giá trị 1-9
		sht["A3"].options(transpose=True).value = list_no	transpose = True: là theo chiều dọc, gán giá trị A3 là 1, A4 là 2,.....,
11	Chỉnh kích thước	sht["1:11"].row_height = 20	Chiều cao của các hàng từ 1 đến 11 là 20
		sht["B:C"].column_width = 25	Độ rộng cột B đến C là 25
12	AutoFit	sht["C:C"].autofit()	Dữ liệu cột C tự động điều chỉnh theo thước hiện tại của cột
13	Kẻ bảng	for i in range (7, 13):	bắt buộc 7, 13
		sht["A2:C11"].api.Borders(i).LineStyle = 1	Từ ô A2 đến C11 kẻ đường kẻ đơn, 1 là kẻ đơn

2. Các ví dụ làm quen với bảng dữ liệu

Ví dụ 1: 

Gán dữ liệu vào ô, định dạng như hình và lưu file lại thành file "BangDuLieu.xlsx"

Code:

import xlwings as xw 
from xlwings.constants import HAlign, VAlign

wb = xw.Book()
sht = xw.sheets.active
sht['A1:F100'].clear()

sht['C1'].value = "Thong Tin Nhan Vien"
sht['C1'].api.Font.Name = 'Arial'
sht['C1'].api.Font.Bold = True
sht['A1:E1'].merge()
sht["A1:E1"].api.HorizontalAlignment = HAlign.xlHAlignCenter

list_menu = ["No","Employee Name","National","Age","Gender"] # tiêu đề
sht['A2'].value = list_menu
sht['A2:E2'].api.WrapText = True
sht["A2:E2"].api.HorizontalAlignment = HAlign.xlHAlignCenter
sht["A2:E2"].api.VerticalAlignment = VAlign.xlVAlignCenter
sht['A2:E2'].api.Font.Bold = True
sht.book.save("BangDuLieu.xlsx")

Ví dụ 2:

Mở file "BangDuLieu.xlsx" ở ví dụ 1, điền thông tin, định dạng, điều chỉnh chiều cao cho hàng, độ rộng cho cột và kẽ viền như hình dưới, lưu lại với tên file mới là "BangDuLieu_Vien.xlsx" và đóng file "BangDuLieu.xlsx"

Code:

import xlwings as xw 
from xlwings.constants import HAlign, VAlign

wb = xw.Book(r"C:\tmp\BangDuLieu.xlsx")
sht = wb.sheets["Sheet1"] 
list_No = [1,2,3,4,5]
list_Employee = ['Jonathan Wick', 'Steve Roger', 'Helen Johansson', 'George Butcher', 'Britany Moonwalk']
list_National = ['USA', 'France', 'Italia','USA', 'France']
list_Age = [25,26,27,28,26]
list_gender = ['Male','Male','Female','Male','Female']

sht['A3'].options(transpose=True).value = list_No
sht['B3'].options(transpose=True).value = list_Employee
sht['C3'].options(transpose=True).value = list_National
sht['D3'].options(transpose=True).value = list_Age
sht['E3'].options(transpose=True).value = list_gender

#Set witdh to columns and height to rows
sht['1:7'].row_height = 20
sht['A:A'].column_width = 5
sht['B:B'].column_width = 15
sht['C:E'].column_width = 10
sht['A3:E7'].api.HorizontalAlignment = HAlign.xlHAlignCenter

#Set border to sample excel
for i in range(7,13):
    sht['A2:E7'].api.Borders(i).LineStyle = 1 

wb = xw.books.active
wb.save("BangDuLieu_Vien.xlsx")
wb.close("BangDuLieu.xlsx")

Ví dụ 3:

- Tạo tiêu đề cho bảng dữ liệu với 3 cột có nội dung là: No., Full Nam, Age. Với các giá trị từng cột là

+ No. là số từ 1-9
+ Full Name: là các tên được sinh ra ngẫu nhiên
+ Age: là số ngẫu nhiên trong khoảng từ 20-50

- Căn chỉnh chữ, kích thước vừa các hàng cột

- Kẻ khung nét kẻ đơn cho bảng dữ liệu

- Lưu file với tên "BangDuLieu_ngaunhien.xlsx"

Code:

import xlwings as xw 
from xlwings.constants import HAlign, VAlign
import random

wb = xw.Book()
sht = xw.sheets.active

sht['A1'].value = "TẠO BẢNG DỮ LIỆU NGẪU NHIÊN"
sht['A1'].api.Font.Name = 'Arial'
sht['A1'].api.Font.Bold = True
sht['A1:C1'].merge()
sht["A1:C1"].api.HorizontalAlignment = HAlign.xlHAlignCenter
sht["A1:C1"].api.VerticalAlignment = VAlign.xlVAlignCenter

sht["A2"].value = ["No.","Full Name","Age"] # ô A2 có nội dung là No., A3 là Full Name,....
sht["2:2"].font.bold = True
sht["2:2"].api.HorizontalAlignment = HAlign.xlHAlignCenter
sht["2:2"].api.VerticalAlignment = VAlign.xlVAlignCenter

list_no = list(range(1,10)) # Tạo ra list có giá trị 1-9 
sht["A3"].options(transpose=True).value = list_no # transpose = True: là theo chiều dọc, gán giá trị A3 là 1, A4 là 2,.....,

def get_full_name(first_name, mid_name, last_name): # hàm nối chuỗi, fullname
    full_name = f"{first_name} {mid_name} {last_name}"
    return full_name 

list_first_name = ["Van","Nguyen","Tran","Le","Pham"]
list_mid_name = ["Cong","Hoang","Quynh"]
list_last_name = ["Khanh","Chin","Huy","Dung","Phi","Son","Hung","Cuong"]

list_full_name =[] # list trống, để chứa cá tên
list_tuoi = []

for i in range(1,10):
    first_name = random.choice(list_first_name) # chọn ngẫu nhiên mội tên/giá trị trong list_first_name
    mid_name = random.choice(list_mid_name)
    last_name =random.choice(list_last_name)
    list_full_name.append(get_full_name(first_name,mid_name,last_name)) #gọi hàm get_full_name và thêm vào list
    list_tuoi.append(random.randrange(20,50,1)) # chọn ngẫu nhiên số trong khoảng 20 -> 50, bước nhảy là 1

sht["B3"].options(transpose=True).value = list_full_name # gáng giá trị của list_full_name cho B3, B4, B5....
sht["C3"].options(transpose=True).value = list_tuoi # tương tự, gán giá trị cho C3, C4,...

# ===chỉnh kích thước, autofit
sht["C:C"].autofit() # vì A1 đến C1 merge cell nên chúng ta phải để đầu tiên
sht["1:1"].row_height = 30 # chiều cao của hàng đầu tiên
sht["2:11"].row_height = 20 # chiều cao của hàng
sht["A:A"].column_width = 5 # độ rộng cột A là 5
sht["B:B"].column_width = 25 # độ rộng cột B - C là 25
sht["C:C"].api.HorizontalAlignment = HAlign.xlHAlignCenter

# ===Kẻ bảng/border
for i in range (7, 13): # bắt buộc 7, 13
    sht["A2:C11"].api.Borders(i).LineStyle = 1 # 1 là kẻ đơn

wb = xw.books.active
wb.save("BangDuLieu_ngaunhien.xlsx")

Kết quả:

3. Copy bảng dữ liệu/copy sheet

Code:

import xlwings as xw

wb = xw.Book(r"C:\tmp\BangDuLieu_ngaunhien.xlsx")
xw.sheets.add(name="backup", after="Sheet1") # tạo sheet mới có tên backup, sheet này đứng sau sheet1
sht = xw.sheets["Sheet1"]
sht_bk = xw.sheets["backup"]
sht["A2:C11"].copy(sht_bk["A2:C11"]) # copy dữ liệu từ A2:C11 của sheet1 đến sheet có tên backup
wb.save("BangDuLieu_copy.xlsx")

Kết quả:

Xong!