快速入門

本教學假設你的電腦已經安裝好了 xlwings,若沒有,請參考 安裝教學

1. 脚本: 透過 Python 自動化或是 Excel 互動

與工作簿建立聯繫

>>> import xlwings as xw
>>> wb = xw.Book()  # this will create a new workbook
>>> wb = xw.Book('FileName.xlsx')  # connect to an existing file 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']

實例化一個試算表物件:

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

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

>>> sht.range('A1').value = 'Foo 1'
>>> sht.range('A1').value
'Foo 1'

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

>>> sht.range('A1').value = [['Foo 1', 'Foo 2', 'Foo 3'], [10.0, 20.0, 30.0]]
>>> sht.range('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'])
>>> sht.range('A1').value = df
>>> sht.range('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>]
>>> sht.pictures.add(fig, name='MyPlot', update=True)
<Picture 'MyPlot' in <Sheet [Workbook4]Sheet1>>

指定使用中工作表(最上層工作表)的捷徑:xw.Range

若你希望能夠快速的操作目前被開啓的工作表(最上層工作表),你其實不需要實例化一個工作簿與工作表,可以直接寫:

>>> import xlwings as xw
>>> xw.Range('A1').value = 'Foo'
>>> xw.Range('A1').value
'Foo'

備注: xw.Range 只適用於你在與 Excel 互動的場景。若今天你是在寫脚本,還是建議使用 book 與 sheet 物件指定你要操作的工作簿與工作表

2. 巨集:從 Excel 呼叫 Python

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 but you can change that via config. 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].range('A1').value = 'Hello World!'

若想執行以上程式,你需要安裝 xlwings 提供的增益集。要把所有的設定都做好,最簡單的方式是透過 xlwings 的指令列界面:xlwings quickstart myproject

若想了解更多關於增益集的細節,可以參考:Add-in & Settings

3. UDFs: 使用者自定函數(僅限 Windows)

用 Python 定義 Excel 函數十分簡單:

import xlwings as xw

@xw.func
def hello(name):
    return 'Hello {0}'.format(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()

按下在 xlwings 增益集的 import 按鈕便可引用此函數至 Excel,想深入了解,可以參考:User Defined Functions (UDFs)