excel VBA通过xlwings对接python

1. excel宏设置


excel VBA通过xlwings对接python

 注册单元格选择、单元格值改变事件,事件中调用 xlwings UDF(用户自定义)pyhon函数。


Private Sub Worksheet_Change(ByVal Target As range)
    Dim ret As Variant
    ret = PyWorkSheetChange(Target.Address(0, 0))
    If ret(0, 0) <> 0 Then
        MsgBox "[PyWorkSheetChange][Error]: ret=" & ret(0, 0)
    End If
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As range)
    ret = PyWorkSheetSelectionChange(Target.Address(0, 0))
    If ret <> 0 Then
        MsgBox "[PyWorkSheetSelectionChange][Error]: ret=" & ret
    End If
End Sub

2. python回调函数

os.chdir(os.path.split(os.path.realpath(__file__))[0])
print(os.getcwd())
sys.path.append(os.getcwd())
from data_utility import *
from data_utility import *


@xw.func
def get_caller_address(caller):
    # caller will not be exposed in Excel, so use it like so:
    # =get_caller_address()
    return caller.address


@xw.func
def PyWorkSheetChange(range):
    wbName = xw.Book.caller().name
    shtName = xw.Book.caller().sheets.active.name
    print("[Info]:[SheetChange][%S][%s]: range=%s" % (wbName, shtName, range))
    return 0


@xw.func
def PyWorkSheetSelectionChange(range):
    wbName = xw.Book.caller().name
    shtName = xw.Book.caller().sheets.active.name
    print('[Info]:[SelectionChange][%s][%s]: range=%s' % (wbName, shtName, range))
    get_reg_value(range)
    return 0

3. 工程示范

excel_VBA_xlwings工程-Python文档类资源-CSDN文库

上一篇:VBA实战(9) - 工作簿(WorkBook)


下一篇:如何在Excel中调用Python脚本,实现数据自动化处理