Python与Excel最好的交互方式应该是这样

Python代替VBA之说

早就听说微软想用Python代替VBA开发Excel。
而实际上VBA有庞大的用户基础，舍弃VBA重新开发一个Python版本的Excel对象模型对用户来说会增加学习成本。

我们不妨畅想一下,微软真的把Python集成到了Excel里面生成了一个新的语言叫PyA。那么它会面临哪些挑战。

1. VB解释器换成了Py解释器
2. 常用的数据分析的类库一并加入Office中。数据分析类库是用Python开发Excel的唯一优势，除了这一项Python没有哪里比VB好。但是添加了一些类库那么整个Office都要变得更大，而这些类库对Office的其它产品没有意义。
3. 开发的时候Python可能会添加外部引用，这样生成的启用宏的工作簿要把外部引用一并打包进去。
4. 编辑器要换一套
5. 开发一套Python的Excel对象模型

面对种种原因，我不觉得微软会把Python集成到Office。

Python与VBA合作

既然VBA是开发Excel最好的语言，那么我们用Python与Excel交互可以让Python与VBA相互合作从而实现优势互补。怎么合作，就是本篇文章的重点所在。

我们需要安装一个Excel插件SqlCel或者SqlCelFuncs引用其中的SqlCelFuncs函数实现交互。
SqlCelFuncs下载地址
https://sqlcel.com/sqlcel/sqlcel/sqlcelfuncs.zip
SqlCelFuncs具体使用方法请参考：
https://sqlcel.com/sqlcelfuncs/

接下来介绍在Python中引用这些函数的具体步骤。
1)在python项目中添加引用pythonnet（File->Settings->Project Interpreter->添加pythonnet引用）；
2)将Bridge.dll（可在SqlCel或SqlCelNear安装目录找到）复制到Python项目根目录；
3)通过以下语句引用当前模块的函数

import clr
clr.FindAssembly('Bridge.dll')  #加载Bridge.dll
from Bridge import AddInFuncs   #导入类AddInFuncs
s = AddInFuncs()

if __name__ == '__main__':   #主程序
    s.AddMoudle()    #引用Python函数

给一个具体的栗子：
先在Python项目中新建一个文件取名为VBA(右键项目 -> New -> File -> Text)并录入以下VBA代码：

Function GetRngValue(add As String)
    GetRngValue = Range(add).Value
End Function

Function GetRng(add As String) As Range
    Set GetRng = Range(add)
End Function

'将Python的二维数组写入Excel
Sub TransPyArrToVba(pyarr As Variant, rn As Range)
    Dim arr() As Variant
    bound1 = UBound(pyarr)
    bound2 = UBound(pyarr(0))
    ReDim Preserve arr(bound1, bound2)
    Dim i As Long, j As Long
    For i = 0 To bound1
        For j = 0 To bound2
            arr(i, j) = pyarr(i)(j)
        Next j
    Next i
    Range(rn, Cells(rn.Row + UBound(arr, 1), rn.Column + UBound(arr, 2))).Value = arr
End Sub

新建一个Python文件并录入以下代码：

import clr
import sys
clr.FindAssembly('Bridge.dll')     #加载Bridge.dll
from Bridge import AddInFuncs      #导入AddInFuncs类
s = AddInFuncs()         #为便于引用将AddInFuncs类赋予变量s
if __name__ == '__main__':         #主程序
    s.ClearMoudle()                #先清空模块
    s.AppendFile(sys.path[0] + "\\VBA")    #将VBA代码写入Excel
    val = s.Run("GetRngValue", "A1:D3")     #调用自定义的VBA函数GetRngValue并传入参数"A1:D3"
    val = [[val[i, j] * *2 for j in range(1, val.GetUpperBound(1) + 1)]
           for i in range(1, val.GetUpperBound(0) + 1)]    #求取每个数字的平方
    s.Run("TransPyArrToVba", val, s.Run("GetRng", "F1"))    #结果从F1单元格向下写入

一般情况下我们不会直接在Python环境中写VBA代码，而是在Excel的VBE中写好之后再复制到Python环境中。

点我了解SqlCel