Python的xlwings模块在Excel中的使用总结_如何用xlwings构建[["四"], ["五"], ["六"]], columns=['你好']函

1. xlwings自定义Excel的数组公式

数组公式运用前是要以数组的形式嵌入公式，要选定数组区间，再按F2输入公式再按下ctrl+shift+enter，这样才能执行数组计算，得出的结果才是正确的。

输入数组公式首先必须选择用来存放结果的单元格区域（可以是一个单元格），在编辑栏输入公式，然后按Ctrl+Shift+Enter组合键锁定数组公式，Excel将在公式两边自动加上花括号“{}”。注意：不要自己键入花括号，否则，Excel认为输入的是一个正文标签。

数组包含数个单元格，这些单元格形成一个整体，所以，数组里的某一单元格不能单独编辑。在编辑数组前，必须先选取整个数组。 选取数组的步骤为：

(1) 选取数组中的任一单元格。

(2) 按下Ctrl+Shift+Enter

示例中用到的xlwings自定义公式如下：

@xw.func
def add_one(data):
return [[cell+1 for cell in row] for row in data]

上面的公式存在一个问题：它需要一个类似嵌套列表 [[1, 2], [3, 4]] 的“2维”的输入。所以如果你把这个公式用于一个单元格，会得到下面的错误信息： 类型错误: 'float' 对象不可迭代 。

要强制Excel不管输入参数是单元格、单行/单列还是2维区域都把它转化为2维数组，可以把上面的公式做如下扩展：

@xw.func
@xw.arg('data', ndim=2)
def add_one(data):
    return [[cell + 1 for cell in row] for row in data]

 通常会在自定义公式（UDF）中用到NumPy array或者Pandas DataFrame。要用numpy array来定义一个矩阵运算公式，可以定义下面的公式:

import xlwings as xw
import numpy as np
@xw.func
@xw.arg('x', np.array, ndim=2)
@xw.arg('y', np.array, ndim=2)
def matrix_mult(x, y):
    return x @ y

把Pandas用于实际工作的一个例子是创建基于数组的 CORREL 公式。Excel版的 CORREL 必须用在2个数据集上。Pandas使得创建数组相关的 CORREL2 公式就比较简单：

import xlwings as xw
import pandas as pd
@xw.func
@xw.arg('x', pd.DataFrame, index=False, header=False)
@xw.ret(index=False, header=False)
def CORREL2(x):
    return x.corr()

其中header和index的含义如下图所示： 

>>> import pandas as pd
>>> df = pd.DataFrame([[1,2,3],[4,5,6],[7,8,9]],columns=['a','b','c'])
>>> sheet['A1'].value = df
>>> sheet['A1'].options(pd.DataFrame, expand='table').value
       a    b    c
0.0  1.0  2.0  3.0
1.0  4.0  5.0  6.0
2.0  7.0  8.0  9.0

前面使用Excel的数组公式的时候，要预先通过选定结果区域来确定范围，然后输入公式，最后敲组合键 Ctrl-Shift-Enter 。这在实际操作中常常弄得很复杂，特别是在处理与时间序列相关的动态数组时更是如此。xlwings提供了动态UDF扩展，下面这个简单的例子展示了UDF扩展的语法和效果：

import numpy as np
 
@xw.func
@xw.ret(expand='table')
def dynamic_array(r, c):
    return np.random.randn(int(r), int(c))

2. 装饰器@xw.arg和@xw.ret

a装饰器之于UDF就如options方法之于Range对象：他们允许对函数的参数(@xw.arg) 和返回值(@xw.ret)使用转换器和各种选项。比如，要把参数x转换成pandas DataFrame并且在返回时抑制索引，可以这样做：rg

@xw.func
@xw.arg('x', pd.DataFrame)
@xw.ret(index=False)
def myfunction(x):
   return x

默认情况，@xw.func传递的参数只是单元格或区域的数值，要传递Range，需要设置@xw.arg：

@xw.func
@xw.arg("data",xw.Range)
def MySolver(data):    
    if data.formula:
        return data.formula
    else:
        return "Not Found"

（1）装饰器@xw.ret 的transpose参数实现读写转置，比如，我们可以把一个列表在Excel中写为一列：

>>>sheet['A1'].options(transpose=True).value = [1, 2, 3]
 
@xw.arg('x', transpose=True)
@xw.ret(transpose=True, expand=’table’)
def myfunction(x):
    return x

 （2）expand参数的功能和区域的 table , vertical 及 horizontal 属性一样，只是在区域取值的时候才去求值： 

>>> import xlwings as xw
>>> sheet = xw.Book().sheets[0]
>>> sheet['A1'].value = [[1,2], [3,4]]
>>> range1 = sheet['A1'].expand()
>>> range2 = sheet['A1'].options(expand='table')
>>> range1.value
[[1.0, 2.0], [3.0, 4.0]]
>>> range2.value
[[1.0, 2.0], [3.0, 4.0]]
>>> sheet['A3'].value = [5, 6]
>>> range1.value
[[1.0, 2.0], [3.0, 4.0]]
>>> range2.value
[[1.0, 2.0], [3.0, 4.0], [5.0, 6.0]]

（3）numbers参数：单元格里的数字，缺省作为 float 类型，不过也可以转换为 int 类型 

>>> sheet['A1'].value = 1
>>> sheet['A1'].value
1.0
>>> sheet['A1'].options(numbers=int).value
1

或者，可以指定为其他函数或类型，只要它们关键字参数和float类型的相同即可。用在UDF中时是这样的:

@xw.func
@xw.arg('x', numbers=int)
def myfunction(x):
    # all numbers in x arrive as int
    return x

 3. 自定义公式UDF中的字符串

下面的例子演示了怎样在函数中加上函数及参数x、y的文档字符串，这些文档字符串能在Excel的函数向导里面显示出来：

import xlwings as xw
@xw.func
@xw.arg('x', doc='This is x.')
@xw.arg('y', doc='This is y.')
def double_sum(x, y):
    return 2 * (x + y)

4. UDF的caller参数

通常需要知道哪个单元格调用UDF。为此，xlwings 提供了保留参数，该参数将调用单元格作为 xlwings 范围对象返回：caller。

@xw.func
def get_caller_address(caller):
    return caller.address

 5. UDF中的VBA关键字

通过使用关键字，可以以 pywin32 对象的形式访问任何 Excel VBA对象。例如，如果要以sheet对象的形式传递CodeName，可以按如下方式执行：

arg('sheet', vba='Sheet4') #第4个sheet传递给参数sheet
def get_name(sheet):
    return sheet.Name

 6. @xw.sub装饰器新建宏

Windows系统中除了通过RunPython调用创建宏，还可以使用@xw.sub装饰器创建宏。

import xlwings as xw
@xw.sub
def my_macro():
    """Writes the name of the Workbook into Range("A1") of Sheet 1"""
    wb = xw.Book.caller()
    wb.sheets[0].range('A1').value = wb.name

 

当点击“Import Functions”后，就可以使用这个宏了，通过Alt+F8执行或者将其绑定在一个按钮上。要执行后者，请确保开启“开发工具”选项卡。然后，在选项卡下，可以插入一个按钮，绘制按钮后，系统将提示为其分配宏，可以选择这个宏。

需要主要的是：@xw.func定义的UDF作为用户自定义方程，只能在调用单元格（及@xw.ret定义的expand区域）输入函数返回值，并不能在其它单元格写入数值。要对所有单元格自由读写，较好的办法是@xw.sub定义的宏来实现或在py文件中直接读写，但是都无法传递参数。

7. xlwings.Book().sheets[].expand()的用法

xlwings库中的expand方法用于扩展Excel单元格范围。该方法可以接受一个参数，用于指定扩展的方向。参数可以是字符串或整数。当参数为字符串时，可以取以下几种值:

table：读取整个表格中的数据，包括表头和数据区域。

down:从指定单元格向下读取数据，直到遇到第一个空白行为止。

right:从指定单元格向右读取数据，直到遇到第一个空白列为止。

>>> sheet['a1'].value = [['yisl','wll','yhw'],[42,45,13],['A','B','C']]
>>> sheet['a1'].expand('down').value
['yisl', 42.0, 'A']
>>> sheet['a1'].expand('right').value
['yisl', 'wll', 'yhw']

 8. 在Excel中插入Matplotlib图表

>>> import matplotlib.pyplot as plt
>>> fig = plt.figure()
>>> plt.plot([1,2,3,4,5])
[<matplotlib.lines.Line2D object at 0x000001C9925F5F50>]
>>> sheet.pictures.add(fig, name="Matplotlib Fig", update=True)
<Picture 'Matplotlib Fig' in <Sheet [pendulum_correction.xlsm]test>>

通过使用 pictures.add()，能很容易地把Matplotlib图表当作图片贴进Excel中。

import matplotlib.pyplot as plt
import xlwings as xw
 
fig = plt.figure()
plt.plot([1, 2, 3])
 
sheet = xw.Book().sheets[0]
sheet.pictures.add(fig, name='MyPlot', update=True)

如果设置 update=True,就可以做到Excel里移动和缩放图表后，后续调用 pictures.add() 时如果图表名称相同('MyPlot')，会更新图表，不改变它的位置和大小

用 RunPython 调用上面的代码并把它绑定到一个按钮上是件水到渠成的事情，并且能够跨平台工作。不过，在Windows系统上可以通过下面的代码来定义一个 UDF 使得集成度更高：

@xw.func
def myplot(n, caller): #caller参数，调用单元格
    fig = plt.figure()
    plt.plot(range(int(n)))
    caller.sheet.pictures.add(fig, name='MyPlot', update=True)
    return 'Plotted with n={}'.format(n)

导入这个UDF函数并在B2上调用它，图表会随着B1的值而变化：

 大小、位置和其他属性可以通过 pictures.add() 的参数设定，也可以通过对返回的图片对象进行操作。

import matplotlib.pyplot as plt
import xlwings as xw
@xw.func
def myplot(n, caller):
    fig = plt.figure()
    plt.plot(range(int(n)))
    sht = caller.sheet
plot = sht.pictures.add(fig, name='MyPlot', update=True, 
left=sht.range("A3").left, top=sht.range("A3").top)
    plot.height /= 2 #高度缩小50%
    plot.width /= 2 #宽度缩小50%
    return 'Plotted with n={}'.format(n)

9. 在VBA编辑器中用RunPython调用Python创建宏

打开Excel的开发工具菜单的VBA编辑器（Alt+F11），可以在VBA模块中写入如下代码：

Sub HelloWorld()
    RunPython "import hello; hello.world()"
End Sub

 上面的代码会调用hello.py中的内容：

# hello.py
import xlwings as xw
def world():
wb = xw.Book.caller()
sheet = wb.sheets['test']
sheet["A1"].value = "Writing OK!"

10. 在VBA中调用UDF

导入的函数同样可以在VBA中使用。例如，一个返回2维数组的函数:

Sub MySub()
Dim arr() As Variant
Dim i As Long, j As Long
    arr = my_imported_function(...)
    For j = LBound(arr, 2) To UBound(arr, 2)
        For i = LBound(arr, 1) To UBound(arr, 1)
            Debug.Print "(" & i & "," & j & ")", arr(i, j)
        Next i
    Next j
End Sub

11. 使用Jupyter实现和Excel的交互：xlwings.view()，xlwings.load() 

使用Jupyter笔记将Excel用作交互式数据查看器或暂存器，从中可以加载数据帧。

 xlwings.view()函数：视图函数几乎接受任何感兴趣的对象，无论是数字、字符串、嵌套列表、NumPy 数组还是 pandas DataFrame。

xlwings.load()函数：要在 Excel 工作表中的某个范围内加载为 pandas DataFrame，请使用该函数，如果您只选择一个单元格，它将自动扩展以覆盖整个范围。但是，如果选择大于一个单元格的特定范围，则该范围将仅加载到所选单元格中。如果 Excel 中的数据没有索引或标题，请将它们设置为如下所示： 

12.  在VBA中使用RunPython向Python脚本传递参数

在Excel的VBA中，定义sub函数和main函数：

Sub HelloWorld(ArrayAddress As String)
    RunPython "import hello; hello.world('" & ArrayAddress & "')"
End Sub
 
Sub HelloMain()
    HelloWorld "A1:B3"
End Sub

 通过ArrayAddress传递操作单元格地址，在hello.py文件中定义world函数：

import xlwings as xw
import numpy as np
 
def world(ArrayAddress):
    wb = xw.Book.caller()
    sheet = wb.sheets.active
    data = sheet[ArrayAddress].options(np.array,ndim=2)
    for row in data:
        for cell in row:
            cell.value = "Cell {} is OK!".format(cell.address)