快速入门#

This guide assumes you have xlwings already installed. If that’s not the case, head over to 安装.

1. Interacting with Excel from a Jupyter notebook#

If you’re just interested in getting a pandas DataFrame in and out of your Jupyter notebook, you can use the view and load functions, see Jupyter Notebooks: Interact with Excel.

2. Scripting: Automate/interact with Excel from Python#

建立到工作簿(workbook)的连接:

>>> import xlwings as xw
>>> wb = xw.Book()  # this will open a new workbook
>>> wb = xw.Book('FileName.xlsx')  # connect to a file that is open or in the current working directory
>>> wb = xw.Book(r'C:\path\to\file.xlsx')  # on Windows: use raw strings to escape backslashes

如果在两个Excel实例中打开了同一个Excel文件,需要通过使用app实例来完全限定具体的文件。可以通过 xw.apps.keys() 来查找app实例的值(PID):

>>> xw.apps[10559].books['FileName.xlsx']

初始化工作表(sheet)对象:

>>> sheet = wb.sheets['Sheet1']

从区域(range)读数据/往区域写数据就像下面这么简单:

>>> sheet['A1'].value = 'Foo 1'
>>> sheet['A1'].value
'Foo 1'

还有很多 方便的特性 ,比如区域扩展:

>>> sheet['A1'].value = [['Foo 1', 'Foo 2', 'Foo 3'], [10.0, 20.0, 30.0]]
>>> sheet['A1'].expand().value
[['Foo 1', 'Foo 2', 'Foo 3'], [10.0, 20.0, 30.0]]

强大的转换器 能够处理大多数数据类型的双向转换,包括Numpy arrays和Pandas DataFrames:

>>> import pandas as pd
>>> df = pd.DataFrame([[1,2], [3,4]], columns=['a', 'b'])
>>> sheet['A1'].value = df
>>> sheet['A1'].options(pd.DataFrame, expand='table').value
       a    b
0.0  1.0  2.0
1.0  3.0  4.0

Matplotlib 图表可以作为图片放在Excel中展示:

>>> import matplotlib.pyplot as plt
>>> fig = plt.figure()
>>> plt.plot([1, 2, 3, 4, 5])
[<matplotlib.lines.Line2D at 0x1071706a0>]
>>> sheet.pictures.add(fig, name='MyPlot', update=True)
<Picture 'MyPlot' in <Sheet [Workbook4]Sheet1>>

3. Macros: Call Python from Excel#

可以通过点击插件中的 Run 按钮(v0.16的新增功能)来调用Python,也可以通过在VBA中使用 RunPython 函数:

Run 按钮会在与工作簿同名的Python模块中寻找 main 函数。最妙的是这里并不需要在工作簿上启用宏,我们还是可以把它存为 xlsx 文件。

不管Python函数是在哪个模块、叫什么名字都可以用 RunPython 来调用:

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

备注

Per default, RunPython expects hello.py in the same directory as the Excel file with the same name, but you can change both of these things: if your Python file is an a different folder, add that folder to the PYTHONPATH in the config. If the file has a different name, change the RunPython command accordingly.

Refer to the calling Excel book by using xw.Book.caller():

# hello.py
import numpy as np
import xlwings as xw

def world():
    wb = xw.Book.caller()
    wb.sheets[0]['A1'].value = 'Hello World!'

To make this run, you’ll need to have the xlwings add-in installed or have the workbooks setup in the standalone mode. The easiest way to get everything set up is to use the xlwings command line client from either a command prompt on Windows or a terminal on Mac: xlwings quickstart myproject.

具体细节参考 Add-in & Settings

4. UDFs: User Defined Functions (Windows only)#

用Python写一个UDF就这么简单:

import xlwings as xw

@xw.func
def hello(name):
    return f'Hello {name}'

UDFs中也可以使用转换器。还是用一个Pandas DataFrame的例子:

import xlwings as xw
import pandas as pd

@xw.func
@xw.arg('x', pd.DataFrame)
def correl2(x):
    # x arrives as DataFrame
    return x.corr()

Import this function into Excel by clicking the import button of the xlwings add-in: for a step-by-step tutorial, see User Defined Functions (UDFs).