脱Excel VBAを目指して、xlwingsを使ってExcelからPythonを呼び出す方法を説明します。
Note: xlwings自体はmacOSにも対応していますが、本記事投稿時点ではUDF(ユーザー定義関数)を使えるのはWindowsのみとなっています。
1. はじめに
1.1. 対象読者
Excel VBAでいろいろとEUCツールを作っているけど、
- もっと効率的にツールを作りたい
- プログラミング技術を高めたい
- VBAのしょぼさにうんざりしている
- C++や.NETでdllを作るのは面倒or難しそう
- Pythonで作ったプログラムをユーザーに配布したい
といった人を対象にしています。VBAしか使ったことがないと、Pythonの説明でよくあるコマンドラインでの実行や、pyファイルの直接実行に慣れないかもしれません。ですが、Pythonのライブラリーxlwingsを使えば、VBAライクにPythonを使い始めることができます。
まずはPythonを使ってみることを目指してやってみましょう。
1.2 Pythonって何?
VBAと同じくプログラミング言語です。VBAとの違いを挙げれば
- 文法がシンプル: 初心者向けプログラミング言語としても使われます。
- 現代的な機能を備えている: 配列ですら使い勝手が全然違います。VBAは、1999年のVBA6から20年以上進化していないので仕方ないのですが。
- ライブラリーが豊富: 高度な科学計算、データ分析、AI開発など、できることが大幅に広がります。
等があります。
1.3 xlwingsって何?
ExcelからPythonを呼び出したり、PythonからExcelを操ったりするパッケージです。Excelを扱うパッケージは他にもopenpyxlなどがありますが、
- 書き方がVBAに似ている
- 既存のマクロを有効活用できる
という点が、VBAに嫌気がさしているけど使い続けなければならないVBAユーザーのステップアップに向いていると思います。
xlwingsを使ってExcelからPythonを呼び出す機能には、Run mainやRunPythonもありますが、UDFはそれらを包含しているので、UDFのみを説明します。
2. 準備
VBAならAlt+F11ですぐに始められますが、Pythonだといろいろと準備が必要になります。
2.1. Pythonのインストール
Pythonディストリビューション(Python本体+外部ライブラリーのセット)の定番Anacondaをインストールします。ここからインストーラーをダウンロードし、管理者権限でインストールします。やり方はググれば山ほど出てきます。
以下の画面では2つともチェックを入れます。1つめのチェックは環境変数PathにAnacondaのインストール先を登録するもので、後で説明するコマンドラインでの作業で必要になります。
チェックを入れ忘れた場合には、以下をPowershellで実行すればユーザー環境変数Pathにパスを設定できます(C:\ProgramData\Anaconda3
はデフォルトのインストール先なので適宜変更してください):
> $newSystemPath = [System.Environment]::GetEnvironmentVariable("Path", "User")
> $newSystemPath += ";C:\ProgramData\Anaconda3"
> $newSystemPath += ";C:\ProgramData\Anaconda3\Scripts"
> $newSystemPath += ";C:\ProgramData\Anaconda3\Library\bin"
> [System.Environment]::SetEnvironmentVariable("Path", $newSystemPath, "User")
なお、ユーザー環境変数ではなく、システム環境変数のPathにパスを設定する場合は、PowerShellを管理者権限で起動し、1行目と最後の行の"User"
を"Machine"
として実行します。
2.2. xlwingsのインストール
Anacondaなら初めから入っていますので作業は不要です。以下のコマンドで確認することもできます:
> pip show xlwings
Name: xlwings
Version: 0.22.2
Summary: Make Excel fly: Interact with Excel from Python and vice versa.
Home-page: https://www.xlwings.org
Author: Zoomer Analytics LLC
Author-email: felix.zumstein@zoomeranalytics.com
License: BSD 3-clause
Location: c:\programdata\anaconda3\lib\site-packages
Requires: pywin32
Required-by:
>
Anaconda以外でPythonをインストールした場合には、pip
でインストールします:
> pip install xlwings
2.3 Excelの設定変更
ファイル > オプション > トラスト センター > トラスト センターの設定 > マクロの設定
を開きます(Exce 2016以前はトラスト センターをセキュリティ センターで読み替える)。マクロの設定
でマクロを有効化し、VBA プロジェクト オブジェクト モデルへのアクセスを信頼する
に✓を入れます。
2.4 アドインのインストールと設定
2.4.1 アドインのインストール
コマンド ラインか手動でインストールします。アドインをインストールすれば、Excelのリボンにxlwingsタブが追加されます。
コマンド ライン
以下をPowerShellで実行します。
xlwings addin install
xlwings config create
2行目はアドインのリボンの設定(後述)を作成し、Interpreter
に現在実行しているxlwingsコマンドの環境(C:\ProgramData\Anaconda3\python.exe、仮想環境の場合は%UserProfile%\.venvs\仮想環境名\Scripts\python.exe等)を設定します。
xlwingsとアドインのバージョンは一致していなければならないため、xlwingsをバージョン アップした際は、xlwings addin update
でアドインもアップデートしてください。
手動
Excel上でファイル > オプション > アドイン
の下段にあるプルダウンでExcel アドイン
を選択し、設定
を押します。参照
ボタンを押して現れるダイアログボックス上で、xlwings.xlamを選択します。xlwings.xlamは、以下のいずれかを使用。
xlwingsのGithubのリリースページから、インストールしているバージョンのxlwings.xlamをダウンロード(Anacondaインストール先)\pkgs\xlwings-0.16.0-py37_0\Lib\site-packages\xlwings\addin
にあるxlwings.xlam
(21/3 削除。この方法でアドインをインストールした場合、他のユーザーとブックを配布した際に不具合が起きるため。)
2.4.2 アドインの設定(グローバル)
ひとまずそのままで大丈夫ですので、読み飛ばしてください。
説明は以下のとおり。そのままで動かない場合には、必要に応じて設定してください。ここでの設定はユーザー単位で適用されます。詳細は公式ドキュメント。
-
Interpreter
: python.exeのパスを設定(例:C:\ProgramData\Anaconda3\python.exe
)。仮想環境を使用している場合は、仮想環境のpython.exeのパスを設定します(例:%UserProfile%\.venvs\仮想環境名\Scripts\python.exe)。Windowsでconda環境を使う場合は空白にします。また、何も入力していなければ、環境変数で設定しているパスから探しに行きます。 -
Conda Base
: Anacondaのインストール先(デフォルトはC:\ProgramData\Anaconda3
)。 -
Conda Env
: xlwingsから呼び出すcondaの仮想環境名(base
、myenv
等)。 -
Import Functions
: Pythonの関数を、Excelファイル内のxlwings_udfs
モジュールに取り込む際に押します。関数名や引数を変更した場合には再度押す必要があります。 -
UDF Modules
: 呼び出し先のPythonファイル名(拡張子.pyを除く)。複数を設定する場合は";"で区切ります。何も入力しなければ、Excel スプレッドシートと同じディレクトリにある同じ名前のファイル(拡張子は .py)をインポートします。この項目は後述のワークブックで設定した方が使い勝手が良いです。 -
Debug UDFs
: 後述のデバッグを行う際にチェックを入れます。 -
Restart UDF Server
: UDFを実行するサーバーをシャットダウンします。サーバーは次のUDF実行時に再起動します。再起動が必要な場合として、Pythonファイルのインポート先の変更を反映したいときがあります。直接呼び出しているPythonファイルの変更(関数名と引数の変更を除く)は自動敵に反映されますが、そのファイルでインポートしているモジュールの変更はそうならないためです。
なお、ワークブックの設定がある場合にはそちらが優先されます。
2.4.3 アドインの設定(ワークブック)
ここもひとまず読み飛ばしてもらってかまいません。
ワークブック内のxlwings.conf
シートで、ワークブック単位でアドインを設定できます。xlwings.conf
シートは、後述のquickstartで作成されるブックに_xlwings.conf
シートがありますので、シートごとコピーして名前を変更します。
A列の項目に対応する値をB列に入力して設定します。不要な設定行は削除可能で、設定行があればワークブックの、なければリボンの設定が反映されます。UDF Modules
のみ設定して、それ以外の行は消すのが実用的な使い方でしょうか。
2.5. テキスト エディター
後述のデバッグで必要となるため、デバッグ可能なテキスト エディターを用意します。Visual Studio Codeをお勧めします。これのインストール方法もググれば山ほどでてきます。Python拡張機能もインストールしておきます。
3. 使ってみよう
以下の3つのケースで説明します。順番に見ていくことで、UDFの使い方に慣れていきましょう。
3.1. UDFとは
UDFはユーザー定義関数(User Defined Function)の略で、Excelの標準モジュール内で定義されたVBA関数のことです。VBA関数なのでVBA内はもちろん、あまり知られていないのかもしれませんが、Exelのセル式でも使えます。こちらでわかりやすく説明されています。
以降、xlwingsを使ってPythonの関数をUDF(=VBAの関数)として使っていく方法を説明します。公式ドキュメントはこちら。
3.2. quickstart コマンド
以下のコマンドを実行すると、カレント ディレクトリーにmyproject
フォルダーが、フォルダー内には、Excelファイルmyproject.xlsm
とPythonファイルmyproject.py
が作成されます(myproject
はお好きな名前に変更してください。)。
> xlwings quickstart myproject
myproject.pyの内容は次のとおりです(説明のため抜粋・コメント追加):
import xlwings as xw #①xlwingsのインポート
@xw.func #②デコレーター
def hello(name): #③関数の宣言
return "hello {0}".format(name)
コードを簡単に説明すると
① xlwingsをPythonで使えるようにインポートします。as xw
で別名xw
で使用できるようにします。
② @xw.func
はPythonの関数をUDFとして使えるようにするためのデコレーター(関数に機能を追加するための仕組み。詳しくはこちら)です。現時点でデコレーターの仕組みを理解する必要はありませんが、おまじない的にExcelで使いたい関数には頭に@xw.func
を付けると覚えておいてください。
③ def
で関数を宣言します。:
以下のインデントされている部分(上記だとreturn ...
)が関数の中身です。
となっています。デコレーターだけちょっと取っ付きにくいですが、Pythonのシンプルさは何となくわかるかと思います。
次に、myproject.xlsmを開き、リボンxlwigsのImport Functions
を押して、myproject.pyのhello関数を取り込みます。
Alt + F11でVBAエディタを開いてください。取り込みが成功していれば、標準モジュールにxlwings_udfs
が追加されています。このモジュール内でPythonのと同じ名前のhello
関数が定義されます。
hello関数を試してみましょう。任意のセルに=hello("Python")と入力すれば、hello Pythonと返されます。
少し改変してみます。myproject.pyを開き、return ...
の部分を次のように変更してみます:
@xw.func
def hello(name):
return "Hello {0}!".format(name)
先ほどのセルを再度計算すると、Hello Python!となります。
今度は関数を追加してみましょう。以下をmyproject.pyに追加します。
@xw.func
def double_sum(x, y):
return 2 * (x + y)
新しい関数を取り込む場合には、再度Import Functions
を押します。任意のセルに=double_sum(2, 3)
と入力して、10が返されれば成功です。簡単ですね。
3.3. 公式のサンプル
先ほどよりも少し複雑な例を公式のサンプルで見ていきましょう。外部ライブラリーとの連携の強力さが少し分かるかと思います。
GitHubからudf.xlsmとudf.pyをダウンロードし、同じフォルダーに保存してudf.xlsmを開きます。
いくつか説明してみます。
add_one
@xw.func
@xw.arg('data', ndim=2)
def add_one(data):
"""Adds 1 to every cell in Range"""
return [[cell + 1 for cell in row] for row in data]
各セルの値に1を足したものを返します。ワークブックでは、セル範囲E11:G12にCtrl + Shift + Enter
で入力されており、配列数式となっています。@xw.arg('data', ndim=2)
は引数を制御するためのデコレーターで、引数data
が単独のセルや行/列であっても、常に2次元のリスト(配列)として読み込むようにします。[cell + 1 for cell in row]
部分はリスト内方表記と呼ばれるもので、簡潔にリスト(配列)を作成することができます。
matrix_mult
@xw.func
@xw.arg('x', np.array, ndim=2)
@xw.arg('y', np.array, ndim=2)
def matrix_mult(x, y):
"""Alternative implementation of Excel's MMULT, requires NumPy"""
return x.dot(y)
行列の積を返します。ExcelのMMULT関数と同じです。@xw.arg('x', np.array, ndim=2)
は、引数x
を2次元のNumpy Arrayとして読み込むようにするものです。Numpy Arrayとは、科学計算ライブラリーNumpyで定義されたリスト(配列)で、Numpy Arrayのdot関数を使って行列の積を計算しています。
CORREL2
@xw.func
@xw.arg('x', pd.DataFrame, index=False, header=False)
@xw.ret(index=False, header=False)
def CORREL2(x):
"""Like CORREL, but as array formula for more than 2 data sets"""
return x.corr()
時系列データ同士の相関行列を返します。@xw.arg('x', pd.DataFrame, index=False, header=False)
は、引数x
をインデックス、ヘッダー無しのPandas DataFrameとして読み込むようにするものです。Pandas DataFrameとは、データ分析用ライブラリーPandasで定義された2次元の配列のようなもので、Pandas DataFrameのcorr関数で相関行列を計算しています。そして、@xw.ret(index=False, header=False)
が、corr関数の計算結果のDataFrameからインデックスとヘッダー除き、値のみにしてExcelに返しています。
同じことをVBAでやろうとすると、forループを書いたりするので長くなりそうですね。また、NumpyやPandasにはこれ以外にも様々な機能があります。これもVBAにはない強みです。
@xw.argと@xw.ret
上記の関数で現れた@xw.argや@xw.retは、コンバーターと呼ばれるもので、ExcelとPython間のデータの型変換などを行います。コンバーターを使ったデータ入出力方法については、こちらをご参照ください。
3.4. 既存のExcelファイルに追加
既存の.xlsmからPythonを呼び出せるようにすることもできます。
既存のExcelファイル「既存.xlsx」から、先ほどのudf.pyを呼び出せるようしてみましょう。
まずはファイルの形式を変更します。F12を押して、形式はマクロ有効ワークブック(*.xlsm)を選択します。
既存.xlsmとudf.pyを同じフォルダーに置きます。既存.xlsmを開き、3.1.で作成したmyproject.シートから、_xlwings.confシートをコピーし、シート名をxlwings.confに変更します。UDF Modulesにudfと入力します。
Alt + F11でVBAのエディターを開き、メニューバーのツール > 参照設定
を開き、xlwingsにチェックを入れます。この操作はよく忘れてしましますので気を付けてください。
リボンでImport Functions
ボタンを押します。取り込みが成功すれば、VBAエディタ上で標準モジュールにxlwings_udfsが追加されます。
double_sum関数を試してみましょう。任意のセルに=double_sum(2,3)と入力すれば、10が返されます。
なお、xlwings.confシートをコピーせずに、Pythonのファイル名を「既存.py」としてもかまいません。ただ、同じPythonファイルを複数のExcelファイルで使ったり、Excelファイルの末尾とかにv2とかを付けてバージョンを分けたりする場合には、xlwings.confシートを使ってモジュールを指定しておくと便利です。
4. デバッグ
UDFは実行だけではなくデバッグもできます。
udf.pyをデバッグしてみましょう。udf.pyの場合は始めから入っていますが、他のコードをデバッグする場合は末尾に以下を追加します。
if __name__ == '__main__':
xw.serve()
次に、Visual Studio Code上でブレークポイント等を設定します。matrix_mult関数のreturn文に設定してみましょう。
エディタでF5を押すと、デバッグの構成を選ぶプルダウンが現れるので、「Python File」を選択すれば、デバッグが実行されます。
なお、左上の歯車マークをクリックして、同じく「Python File」を選択すれば、デバッグ構成のファイルが作成されるので、以降はF5を押すだけでデバッグが実行されます。(launch.jsonのタブはそのまま閉じていい)
Excelの画面に戻って、リボンのDebug UDFs
にチェックを入れます(xlwings.confシートに'Debug UDFs'の行があれば、値Trueを設定)。
シートを再計算(Alt + Shift + F9)すると、先ほどのブレークポイントで実行が止まります。左上の変数で、変数の中身の確認ができます。
小技ですが、デバッグ コンソールから変数を直列化すれば、JupyterNotebookとかで中身を確認したりできて便利です。例えば、Pandas DataFrameの変数df
を、デバッグ コンソールでdf.to_pickle('df.pickle')
で直列化し、JupyterNotebookのセルでdf = pd.read_pickle('df.pickle')
で読み込めば、デバッグ中の変数をJupyteNotebook上でいろいろ見ることができます。
5. おわりに
今回の記事では、xlwingsを使うところまでを説明しました。UDFの注意点ですが、Excel関数のように大量のセルで使用してはいけません。セルごとにUDFサーバーとやりとりする上、マルチスレッド処理が行えないため、遅いです。
では、どう使うのかというと、ボタンに登録します。この時、Python側へのデータの入力は、UDFの引数ではなくPythonの関数の内部で行い、処理結果をExcelのテーブルやCSVファイルに出力します。
この辺の話を含めた、発展的な内容は以下で投稿しています(今後順次投稿するつもりですので、お楽しみに)。
- xlwingsを使ってPythonからExcel VBAマクロを呼び出す
- Excel-Python(Pandas DataFrame)間のデータ入出力
- xlwingsとPybind11でExcelからC++関数を呼び出す
- xlwingsトラブルシューティング
- 【作成中】xlwings UDFを使った実用的な例