Lập Trình Python Cho Excel/Python For Excel #001

NỘI DUNG:

1. Ý nghĩa/Giải thích dòng lệnh

2. Làm quen về hàm/Function

3. Làm quen với VBA

4. Gọi VBA từ python có truyền tham số 

5. Hyperlink Function

THỰC HIỆN:

1. Ý nghĩa/Giải thích dòng lệnh

STT	Yêu Cầu Cấu Hình	Commands	Giải Thích
1	Import thư viện	import xlwings as xw	import tất cả các hàm số trong file xlwings.py với tên đại diện là xw
2	Tạo file mới	xw.Book()	Tạo file excel mới
		xw.App()	Tạo workbook mới bằng App
		xw.App(add_book=False)	Nếu đã có workbook mới rồi thì không tạo workbook mới
		xw.App(visible=False)	Mở nhưng không hiển thị
3	Gọi workbook hiện hành	wb1 = xw.books.active
4	In ra tên của workbook hiện hành	print(wb1.name)
5	Lưu file	wb1.save("Workbook1.xlsx")	Lưu workbook hiện hành thành tên Workbook1.xlsx
6	Đóng	wb1.close()	Đóng file hiện hành
		app.quit()	Đóng app đã mở
7	Gọi workbook có tên Workbook1.xlsx	wb2 = xw.books["Workbook1.xlsx"]	file này phải được mở
8	Mở file	wb3 = xw.Book(r"C:\Users\khanhvc\Workbook1.xlsx")	mở file Workbook1.xlsx tại đường dẫn "C:\Users\khanhvc"
		wb4 = xw.Book(fullname = r"C:\Users\khanhvc\Workbook1.xlsx",password="MatkhauMofile",)	mở file Workbook1.xlsx có mật khẩu mở file là: "MatkhauMofile"
9	Kết nối với sheet	sh_active = wb3.sheets.active	kết nối với sheet hiện hành
		sh_index = wb3.sheets[0] #	là sheet được tạo ra đầu tiên sẽ có index là 0
		sh_name = wb3.sheets["Thu-2"]	sheet có tên "Thu-2"
10	Tạo sheet mới	wb4.sheets.add()	Tạo sheet mới đứng đầu tiên trong danh sách sheet(Phải mở file trước khi dùng lệnh này)
		wb4.sheets.add("Thu-4", after = "Thu-1")	Tạo ra sheet mới có tên Thu-4 và sheet này đứng SAU sheet Thu-1
		wb4.sheets.add("Thu-5", before = "Thu-1")	Tạo ra sheet mới có tên Thu-5 và sheet này đứng TRƯỚC sheet Thu-1
11	Đổi tên sheet	for sh in wb4.sheets:	dùng for để duyệt tất cả các sheet
		if sh.name == "Thu-5":	Nếu tên là "Thu-5" thì
		sh.name = "Thu-05"	đổi tên mới là "Thu-05"
12	Xóa sheet	for sh in wb4.sheets:	dùng for để duyệt tất cả các sheet
		if sh.name == "Thu-05":	Nếu tên là "Thu-05" thì
		sh.delete()	Xóa sheet có tên là: "Thu-05"
13	Xóa sheet (ngoại trừ)	for sh in wb4.sheets:	dùng for để duyệt tất cả các sheet
		if sh.name != "Thu-05":	Nếu KHÔNG PHẢI là "Thu-05" thì
		sh.delete()	xóa tất cả các sheet ngoại trừ sheet có tên Thu-1
14	Tạo nhiều sheet	for i in range(0,12):
		wb4.sheets.add(f"Thang {i + 1}", after=wb4.sheets[i])	Tạo 12 sheet có tên là Thang 1,..., Thang 12 và sắp xếp theo thứ tự tăng dần.
15	Lấy tên sheet trong workbook	lst_sh = []	Tạo list rỗng
		for sh in wb4.sheets:
		lst_sh.append(sh.name)	thêm tên của sheet vào list
		print (lst_sh)	in ra để kiểm tra
16	Copy sheet	wb4.sheets["Thu-1"].copy(name="Thu-01", after=wb4.sheets["Thang 1"])	Cope sheet Thu-1 sang sheet mới có tên Thu-01, và đứng SAU sheet có tên "Thang 1"
17	Di chuyển sheet	wb4.sheets["Thu-1"].copy(name="temp")	Copy sheet "Thu-1" thành sheet mới có tên "temp", sheet "temp" sẽ đứng ở cuối cùng trong tất cả các sheet
		wb4.sheets["Thu-1"].delete()	Xóa sheet "Thu-1"
		wb4.sheets["temp"].name = "Thu-1"	Đổi tên sheet temp thành Thu-1, vì không có thuộc tính rename nên phải làm cách trung gian này
18	Ẩn/Hiện sheet	wb4.sheets["Thu-1"].visible = False	Hiện
		wb4.sheets["Thu-1"].visible = True	Ẩn
19	Hiện tất cả các sheet	for sh in wb4.sheets:	dùng for để duyệt
		sh.visible = True
20	To PDF	wb4.sheets["Thu-1"].to_pdf()	Chuyển sheet "Thu-1" về PDF
		wb4.sheets["Thu-1"].to_pdf(path=r"c:\tmp\Thu-1")	Chuyển sheet "Thu-1" về PDF vào đường dẫn "c:\tmp\Thu-1"

2. Làm quen về hàm/Function

• Hàm 1 biến số

def kiemtra_chanle(num):
    # hàm kiểm tra chẵn lẽ, 1 đối số
    if num % 2 == 0: # chia lấy phần dư, nếu số dư =0 thì là chẳn
        print (f"So {num} la so chan")
    else:
        print (f"So {num} la so le")

kiemtra_chanle(4) # gọi hàm, kiểm tra 4 là số gì

• Hàm 2 biến số

def kiemtra_chanle(num1, num2 = 0): # nếu truyền 1 đối số, thì đối số  num2 sẽ gán = 0
    # hàm kiểm tra chẵn lẽ, 2 đối số
    for num in [num1, num2]:
        if num % 2 == 0:
            print (f"So {num} la so chan")
        else:
            print (f"So {num} la so le")

kiemtra_chanle(5) # truyền 1 đối số vào thì đối num1 sẽ được gán bằng số truyền vào, num2 sẽ gán = 0
kiemtra_chanle(5,6)
kiemtra_chanle(num2=50,num1=15) # tuy nhiên chúng ta cũng có thể truyền đối số theo cách này

• Hàm nhiều biến

def kiemtra_chanle_splat(*nums): # có nhiều đối số
    for num in nums:
        if num % 2 == 0:
            print (f"So {num} la so chan")
        else:
            print (f"So {num} la so le")      

kiemtra_chanle_splat(10,11,13,6,9,8)

• Hàm truyền key và giá trị

def ten_diem(ten,**so_diem): # truyền key và giá trị
    '''
    in ra ten diem cac mon hoc, va tong diem cac mon
    '''
    print (f"Ten: {ten}")
    for mon, diem in so_diem.items():
        print (f"Mon: {mon},  Diem: {diem}")
    print(f"Tong diem: {sum(so_diem.values())}")
    
ten_diem('khanh', toan = 8, van = 6, TA = 7)

Tìm hiểu thêm về cách viết và gọi hàm tại đây

3. Làm quen với VBA

3.1 Tạo VBA code: Mở Excel tạo file và lưu có phần mở rộng là .xlsm; mở VBA soạn nội dung như bên dưới (vba dưới là protect/unprotect sheet)

Sub ProtectSheetWithPassword()
'Protect worksheet with a password
Sheets("Thu-1").Protect Password:="myPassword"
End Sub

Sub UnProtectSheetWithPassword()
'Unprotect a worksheet with a password
Sheets("Thu-1").Unprotect Password:="myPassword"
End Sub

Tham khảo vba tại exceloffthegrid.com hoặc www.automateexcel.com 

3.2. Gọi VBA từ python

wb5.xw.Boot("Vidu.xlsx")
wb5.save("Vidu.xlsm") # vì macro chỉ hoạt động với đuôi .xlsm nên chúng ta phải lưu theo kiểu này
wb5 = xw.Book("Vidu.xlsm")
protect_sh = wb5.macro("ProtectSheetWithPassword")
protect_sh () # gọi vba để protect sheet hiện hành
protect_sh = wb5.macro("UnProtectSheetWithPassword")
protect_sh () # gọi vba unprotect sheet

4. Gọi VBA từ python có truyền tham số

Tạo VBA code

Sub NamedRanges_Example(vungchon As String, ten_vungchon As String)
' dat ten cho vung/ Define name
  Dim Rng As Range

  Set Rng = Range(vungchon)

  ThisWorkbook.Names.Add Name:=ten_vungchon, RefersTo:=Rng

End Sub

Sub ConditionalFormattingExample(vungchon As String)
 
'Define Range
Dim MyRange As Range
Set MyRange = Range(vungchon)
 
'Delete Existing Conditional Formatting from Range
MyRange.FormatConditions.Delete
 
'Apply Conditional Formatting
        
' neu = 8, mau vang
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=8"
MyRange.FormatConditions(1).Interior.Color = RGB(255, 255, 204)

' neu > 8, mau cam
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=8"
MyRange.FormatConditions(2).Interior.Color = RGB(255, 204, 153)

End Sub

Các bạn có thể record macro hoặc tham khảo VBA tại đây

Gọi VBA từ python

def datten_lab(vungchon, ten_vungchon): # dat ten cho vung lab co diem
	wb1 = xw.Book.caller() # sheet đang active hoặc bạn có thể khởi tạo book mới để test
	datten = wb1.macro(rf'NamedRanges_Example("{vungchon}","{ten_vungchon}")') # gọi macro bên excel
	datten ()

def mau_nen(vungchon): # xét điểm cao đánh màu nền
	wb1 = xw.Book.caller()
	maunen = wb1.macro(rf'ConditionalFormattingExample("{vungchon}")') # gọi macro bên excel
	maunen()

datten_lab("E10:E20") 
mau_nen("E10:E20")

5. Hyperlink Function

=HYPERLINK("#Sheet1!A100","Ten can dat") 

=HYPERLINK("đường dẫn đến file cần mở","Ten can dat")

Xong!