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!
