快速入門

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

與工作簿建立聯繫

>>> 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 的實例開啓同一個檔案，你需要該實例的 PID，而取得 PID 的方法是透過 xw.apps.keys()：

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

實例化一個試算表物件：

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

從儲存格範圍讀取出來或將資料寫入儲存格範圍十分簡單：

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

本套件有提供許多 方便的功能, 像是 Range expanding（自動偵測範圍大小）:

>>> 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 Array 或是 Pandas DataFrame：

>>> 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 圖表可以以圖片的形式呈現在試算表内

>>> 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

You can call Python functions either by clicking the Run button (new in v0.16) in the add-in or from VBA using the RunPython function:

The Run button expects a function called main in a Python module with the same name as your workbook. The great thing about that approach is that you don’t need your workbooks to be macro-enabled, you can save it as xlsx.

If you want to call any Python function no matter in what module it lives or what name it has, use 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 定義 Excel 函數十分簡單：

import xlwings as xw

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

資料轉換器可以被用於使用者自定函數，以 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).