excel VBA通过xlwings对接python

dndxjj

已于 2022-01-28 08:35:55 修改

阅读量1k

点赞数

分类专栏：工具文章标签： python

于 2022-01-21 08:47:35 首次发布

本文链接：https://blog.csdn.net/dndxjj/article/details/122613914

版权

工具专栏收录该内容

1 篇文章 0 订阅

订阅专栏

1. excel宏设置

注册单元格选择、单元格值改变事件，事件中调用 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