Einführung in Python für VBA-Benutzer - Aufrufen von Python aus Excel mit xlwings-

Ich werde erklären, wie man Python mit xlwings aus Excel aufruft, um Excel VBA zu eliminieren.

** Hinweis **: xlwings selbst ist mit macOS kompatibel, aber zum Zeitpunkt der Veröffentlichung dieses Artikels kann UDF (User Defined Function) nur unter Windows verwendet werden.

1. Zuallererst

1.1. Zielgruppe

Ich mache verschiedene EUC-Tools mit Excel VBA,

――Ich möchte Werkzeuge effizienter gestalten ――Ich möchte meine Programmierkenntnisse verbessern

• Ich habe VBA satt
• Das Erstellen einer DLL mit C ++ oder .NET scheint mühsam oder schwierig zu sein
• Ich möchte ein in Python erstelltes Programm an Benutzer verteilen

Es ist für Leute gedacht, die mögen. Wenn Sie nur VBA verwendet haben, sind Sie möglicherweise nicht daran gewöhnt, in der in Pythons Beschreibung üblichen Befehlszeile oder py-Dateien direkt auszuführen. Mit der Python-Bibliothek xlwings können Sie Python jedoch auf VBA-ähnliche Weise verwenden.

Versuchen wir zunächst, Python zu verwenden.

1.2 Was ist Python?

Es ist eine Programmiersprache wie VBA. Der Unterschied zu VBA

• ** Einfache Grammatik **: Wird auch als Programmiersprache für Anfänger verwendet.
• ** Moderne Funktionen **: Auch Arrays unterscheiden sich in ihrer Benutzerfreundlichkeit völlig. VBA hat sich seit mehr als 20 Jahren nicht mehr von VBA6 im Jahr 1999 entwickelt, daher kann nichts dagegen unternommen werden.
• ** Rich Library **: Fortgeschrittene wissenschaftliche Berechnungen, Datenanalyse, KI-Entwicklung und vieles mehr.

Und so weiter.

1.3 Was sind xlwings?

Es ist ein Paket, das Python aus Excel aufruft und Excel aus Python bearbeitet. Andere Pakete, die Excel verarbeiten, umfassen openpyxl,

• Ähnlich wie VBA schriftlich
• Sie können vorhandene Makros effektiv verwenden

Ich denke, das ist geeignet, um VBA-Benutzer zu verstärken, die von ~~ VBA angewidert sind, es aber weiterhin ~~ verwenden müssen. Die Möglichkeit, Python mit xlwings aus Excel aufzurufen, umfasst auch Run main und Run Python. Da UDF diese enthält, werden wir nur UDF behandeln.

2. Vorbereitung

Mit VBA können Sie schnell mit Alt + F11 beginnen, aber mit Python müssen Sie viele Vorbereitungen treffen.

2.1 Python installieren

Installieren Sie die klassische Anaconda der Python-Distribution (Python-Body + Satz externer Bibliotheken). Laden Sie das Installationsprogramm von hier herunter und installieren Sie es mit Administratorrechten. Je mehr Sie googeln, desto mehr können Sie herausfinden.

Im folgenden Bildschirm ist es veraltet, aber es ist später einfacher, es zu überprüfen:

Auch wenn Sie dies nicht überprüfen, können Sie den Pfad festlegen, indem Sie in Powershell Folgendes ausführen:

> $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")

Setzen Sie bei der Einstellung für jedes Terminal "Benutzer" in der ersten und letzten Zeile auf "Maschine" (Administratorberechtigung erforderlich).

2.2. Xlwings installieren

Anaconda wird von Anfang an mitgeliefert, sodass keine Arbeit erforderlich ist. Wenn nicht, installieren Sie mit pip:

> pip install xlwings

2.3 Ändern Sie die Excel-Einstellungen

Öffnen Sie Datei> Optionen> Sicherheitscenter> Sicherheitscenter-Einstellungen> Makroeinstellungen` `. Aktivieren Sie Makros in Set Macros '' und setzen Sie ✓ in `` Trust Access to VBA Project Object Model ''.

2.4 Installation und Konfiguration des Add-Ins

2.4.1 Add-In-Installation

Installieren Sie es über die Befehlszeile oder manuell. Durch die Installation des Add-Ins wird Ihrem Excel-Menüband eine Registerkarte "xlwings" hinzugefügt.

Befehlszeile

Führen Sie die folgenden Schritte mit PowerShell usw. aus. Wenn der Pfad zum Python-Installationsziel (Anaconda ist standardmäßig "C: \ ProgramData \ Anaconda3") nicht übergeben wird, geben Sie xlwings.exe mit dem vollständigen Pfad an.

xlwings addin install

Wenn Sie xlwings aktualisieren, führen Sie "xlwings addin update" aus.

Handbuch

Wählen Sie in Excel "Excel-Add-In" aus dem Pulldown-Menü "Datei> Optionen> Add-Ins" und drücken Sie "Einstellungen". Wählen Sie xlwings.xlam im Dialogfeld aus, das angezeigt wird, wenn Sie auf die Schaltfläche "Durchsuchen" klicken. Verwenden Sie für xlwings.xlam eine der folgenden Optionen.

• Laden Sie die installierte Version von xlwings.xlam von der xlwings Github-Release-Seite herunter (https://github.com/xlwings/xlwings/releases). --Xlwings.xlam befindet sich in (Anaconda-Installationsziel) \ pkgs \ xlwings-0.16.0-py37_0 \ Lib \ site-packages \ xlwings \ addin

2.4.2 Add-In-Einstellungen (global)

Sie können es vorerst so lassen, wie es ist. Die Erklärung lautet wie folgt. Nach Bedarf einstellen. Die Einstellungen hier werden pro Benutzer angewendet.

• ** Interpreter **: Legen Sie den Pfad von Python.exe fest (z. B. C: \ ProgramData \ Anaconda \ python.exe). Wenn nichts eingegeben wird, wird es über die Umgebungsvariable durchsucht. Wenn Sie also den Pfad festgelegt haben, müssen Sie ihn nicht festlegen.
• ** UDF-Module **: Aufzurufender Python-Dateiname (ohne Erweiterung .py). Wenn Sie mehr als eine Einstellung vornehmen, trennen Sie diese mit ";". Wenn nichts eingegeben wird, wird eine Datei mit demselben Namen (Erweiterung .py) in dasselbe Verzeichnis wie die Excel-Tabelle importiert. Es ist einfacher, dieses Element zu verwenden, wenn Sie es in der später beschriebenen Arbeitsmappe festlegen.
• ** UDFs debuggen **: Überprüfen Sie dies, wenn Sie das später beschriebene Debuggen durchführen.
• ** UDF-Server neu starten **: Fahren Sie den Server herunter, auf dem UDF ausgeführt wird. Der Server wird beim nächsten UDF-Lauf neu gestartet. Änderungen in der aufgerufenen Python-Datei selbst werden automatisch übernommen, Änderungen in dem in die Python-Datei importierten Modul werden jedoch nicht übernommen. Drücken Sie diese Taste, um sie wiederzugeben.

Wenn eine Arbeitsmappeneinstellung vorhanden ist, wird dieser Priorität eingeräumt.

2.4.3 Add-In-Einstellungen (Arbeitsmappe)

Sie können es vorerst überspringen. Sie können Add-Ins auf Arbeitsmappenbasis im Blatt xlwings.conf in der Arbeitsmappe konfigurieren. Für das Blatt "xlwings.conf" befindet sich in der Arbeitsmappe, die durch den später beschriebenen Schnellstart erstellt wurde, ein Blatt "_xlwings.conf". Kopieren Sie das gesamte Blatt und benennen Sie es um. Geben Sie den Wert für das Element in Spalte A in Spalte B ein und legen Sie ihn fest. Unnötige Einstellungszeilen können gelöscht werden. Wenn eine Einstellungszeile vorhanden ist, wird die Einstellung der Arbeitsmappe wiedergegeben, andernfalls wird die Einstellung des Menübands wiedergegeben. Ist es praktisch, nur "UDF-Module" einzustellen und die anderen Zeilen zu löschen?

2.5 Texteditor

Bereiten Sie einen debuggbaren Texteditor vor, da Sie ihn später zum Debuggen benötigen. Wir empfehlen den Visual Studio Code (https://code.visualstudio.com/). Wenn Sie googeln, wie man dies installiert, werden Sie viel finden. Installieren Sie auch die Python-Erweiterung.

3. Lassen Sie uns verwenden

Wir werden in den folgenden drei Fällen erklären. Lassen Sie uns daran gewöhnen, UDF zu verwenden, indem wir es der Reihe nach betrachten.

3.1. Schnellstartbefehl

Mit dem folgenden Befehl wird ein Ordner erstellt, der die Excel- und Python-Dateien enthält (beide mit dem Namen myproject, benennen Sie myproject nach Belieben um).

> xlwings quickstart myproject

Der Inhalt von myproject.py lautet wie folgt (Auszüge und Kommentare zur Erläuterung hinzugefügt):

myproject.py

import xlwings as xw       #① Importieren Sie xlwings

@xw.func                   #② Dekorateur
def hello(name):           #③ Funktionsdeklaration
    return "hello {0}".format(name)

Um den Code kurz zu erklären ① Importieren Sie xlwings, damit es in Python verwendet werden kann. Stellen Sie es unter dem Alias "xw" mit "als xw" zur Verfügung. ② @ xw.func ist ein Dekorator, mit dem Sie Python-Funktionen als UDFs verwenden können. Denken Sie daran, der Funktion, die Sie in Excel verwenden möchten, "@ xw.func" voranzustellen. ③ Deklarieren Sie die Funktion mit def. Der eingerückte Teil unter : (return ... im obigen Fall) ist der Inhalt der Funktion. Es ist geworden. Nur der Dekorateur ist etwas schwierig anzubringen, aber ich denke, Sie können die Einfachheit von Python verstehen.

Öffnen Sie dann myproject.xlsm und drücken Sie auf der Multifunktionsleiste xlwigs auf Import Functions, um die Hallo-Funktion von myproject.py zu importieren.

Öffnen Sie den VBA-Editor mit Alt + F11. Wenn der Import erfolgreich ist, wurde dem Standardmodul xlwings_udfs hinzugefügt.

Probieren wir die Hallo-Funktion aus. Geben Sie = Hallo ("Python") in eine beliebige Zelle ein und es wird Hallo Python zurückgegeben.

Ich werde es ein wenig ändern. Öffnen Sie myproject.py und ändern Sie den Teil "return ..." wie folgt:

myproject.py

@xw.func
def hello(name):
    return "Hello {0}!".format(name)

Wenn Sie die Zelle neu berechnen, erhalten Sie Hello Python!

Fügen wir nun eine Funktion hinzu. Fügen Sie myproject.py Folgendes hinzu.

myproject.py

@xw.func
def double_sum(x, y):
    return 2 * (x + y)

Wenn Sie eine neue Funktion importieren möchten, drücken Sie erneut "Funktionen importieren". Geben Sie in eine beliebige Zelle = double_sum (2, 3) ein. Wenn 10 zurückgegeben wird, ist dies erfolgreich. Es ist einfach.

3.2. Offizielle Probe

Schauen wir uns ein Beispiel an, das mit der offiziellen Stichprobe etwas komplizierter ist als zuvor. Sie sehen die Stärke der Zusammenarbeit mit externen Bibliotheken.

Von GitHub udf.xlsm und udf.py Laden Sie /raw/master/examples/udf/udf.py herunter, speichern Sie es im selben Ordner und öffnen Sie udf.xlsm.

Lassen Sie mich einige erklären.

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]

Gibt den Wert jeder Zelle plus eins zurück. In der Arbeitsmappe wird der Zellbereich E11: G12 mit "Strg + Umschalt + Eingabetaste" eingegeben, einer Array-Formel. "@ xw.arg (" data ", ndim = 2)" ist ein Dekorator zum Steuern von Argumenten und ist immer eine zweidimensionale Liste (Array), selbst wenn das Argument "data" eine einzelne Zelle oder Zeile / Spalte ist. Ich werde es lesen als. Der Teil "[Zelle + 1 für Zelle in Zeile]" wird als List-Inward-Notation bezeichnet, und Sie können eine Liste (Array) präzise erstellen.

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)

Gibt das Produkt der Matrix zurück. Entspricht der MMULT-Funktion von Excel. @ xw.arg ('x', np.array, ndim = 2) bewirkt, dass das Argument x als zweidimensionales Numpy Array gelesen wird. Numpy Array ist in der wissenschaftlichen Berechnungsbibliothek Numpy definiert. In der Liste (Array) [Punktfunktion] von Numpy Array (https://numpy.org/doc/1.18/reference/generated/numpy.ndarray.dot.html) ) Wird verwendet, um das Produkt der Matrizen zu berechnen.

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

Gibt eine Korrelationsmatrix zwischen Zeitreihendaten zurück. @ xw.arg ('x', pd.DataFrame, index = False, header = False) lädt das Argument x als Pandas DataFrame ohne Index und Header. Pandas DataFrame ist eine Bibliothek zur Datenanalyse Pandas Es ist wie ein zweidimensionales Array, das in der [corr-Funktion] von Pandas DataFrame (https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.corr.html#pandas.DataFrame) definiert ist. Corr) wird zur Berechnung der Korrelationsmatrix verwendet. Dann entfernt "@ xw.ret (index = False, header = False)" den Index und den Header aus dem DataFrame des Berechnungsergebnisses der corr-Funktion und gibt nur den Wert an Excel zurück.

Wenn Sie versuchen, dasselbe mit VBA zu tun, dauert es lange, da Sie eine for-Schleife schreiben. Darüber hinaus haben Numpy und Pandas verschiedene andere Funktionen. Dies ist eine weitere Stärke, die VBA nicht hat.

@ xw.arg und @ xw.ret

@ Xw.arg und @ xw.ret, die in der obigen Funktion angezeigt werden, werden als Konverter bezeichnet und sind Daten zwischen Excel und Python. Führt eine Typkonvertierung usw. durch. Ich werde zu einem späteren Zeitpunkt über die Dateneingabe- / Ausgabemethode mit dem Konverter berichten. Ich freue mich darauf.

3.3. Zur vorhandenen Excel-Datei hinzufügen

Sie können Python auch von einer vorhandenen XLSM-Datei aus aufrufen. Es ist leicht zu vergessen, also schreibe ich es zuerst, aber Sie müssen ** die Referenzeinstellungen im VBA-Editor öffnen und xlwings überprüfen **.

Machen wir es möglich, udf.py aus der vorhandenen Excel-Datei "vorhandene XLSX" aufzurufen. Ändern Sie zunächst das Dateiformat. Drücken Sie F12 und wählen Sie Macro Enabled Workbook (* .xlsm) als Format.

Platzieren Sie die vorhandenen .xlsm und udf.py im selben Ordner. Öffnen Sie die vorhandene XLSM-Datei, kopieren Sie das Blatt _xlwings.conf aus dem in 3.1 erstellten Blatt myproject.sheet und benennen Sie das Blatt in xlwings.conf um. Geben Sie udf in UDF-Module ein.

Öffnen Sie den VBA-Editor mit Alt + F11, öffnen Sie "Extras> Referenzen" in der Menüleiste und überprüfen Sie xlwings.

Klicken Sie auf der Multifunktionsleiste auf die Schaltfläche "Importfunktionen". Wenn der Import erfolgreich ist, wird xlwings_udfs dem Standardmodul im VBA-Editor hinzugefügt.

Probieren wir die Funktion double_sum aus. Geben Sie = double_sum (2,3) in eine beliebige Zelle ein und 10 wird zurückgegeben.

Sie können die Python-Datei in "vorhandene .py" umbenennen, ohne das Blatt xlwings.conf zu kopieren. Wenn Sie jedoch dieselbe Python-Datei in mehreren Excel-Dateien verwenden oder die Version durch Hinzufügen von v2 am Ende der Excel-Datei teilen, ist es zweckmäßig, das Modul mithilfe des Blattes xlwings.conf anzugeben. ..

4. Debuggen

Lassen Sie uns udf.py debuggen. Im Fall von udf.py ist es von Anfang an enthalten. Wenn Sie jedoch anderen Code debuggen möchten, fügen Sie am Ende Folgendes hinzu.

if __name__ == '__main__':
    xw.serve()

Legen Sie als Nächstes Haltepunkte usw. für Visual Studio Code fest. Setzen wir es in der return-Anweisung der Funktion matrix_mult.

Wenn Sie im Editor F5 drücken, wird ein Pulldown-Menü zur Auswahl der Debug-Konfiguration angezeigt. Wählen Sie "Python-Datei", um das Debug auszuführen.

Wenn Sie auf die Zahnradmarkierung oben links klicken und auch "Python-Datei" auswählen, wird eine Debug-Konfigurationsdatei erstellt. Drücken Sie anschließend einfach F5, um das Debuggen auszuführen. (Die Registerkarte launch.json kann so wie sie ist geschlossen werden.)

Kehren Sie zum Excel-Bildschirm zurück und aktivieren Sie das Kontrollkästchen "UDFs debuggen" in der Multifunktionsleiste (wenn im Blatt "xlwings.conf" eine Zeile für "UDFs debuggen" vorhanden ist, legen Sie den Wert "True" fest).

Wenn Sie das Blatt neu berechnen (Alt + Umschalt + F9), wird es am Haltepunkt nicht mehr ausgeführt. Sie können den Inhalt der Variablen in der oberen linken Variablen überprüfen.

Es ist ein Trick, aber wenn Sie die Variablen über die Debug-Konsole serialisieren, können Sie den Inhalt mit Jupyter Notebook überprüfen, was praktisch ist. Beispielsweise kann die Pandas DataFrame-Variable df mit df.to_pickle ('df.pickle') in der Debug-Konsole serialisiert und mitdf = pd.read_pickle ('df.pickle')in der Jupyter Notebook-Zelle gelesen werden. Beispielsweise können Sie sehen, wie verschiedene Variablen in Jupyte Notebook debuggt werden.

5. Schlussfolgerung

In diesem Artikel habe ich erklärt, wie man xlwings benutzt. Beachten Sie, dass UDF nicht in einer großen Anzahl von Zellen wie Excel-Funktionen verwendet werden sollte. Es ist langsam, da es zellenweise mit dem UDF-Server kommuniziert und keine Multithread-Verarbeitung durchgeführt werden kann.

Anschließend können Sie es in der Schaltfläche registrieren. Zu diesem Zeitpunkt werden die Daten auf der Python-Seite innerhalb der Python-Funktion anstelle des UDF-Arguments eingegeben, und das Verarbeitungsergebnis wird in die Excel-Tabelle oder die CSV-Datei ausgegeben.

Ich habe unten einige erweiterte Inhalte veröffentlicht, einschließlich Geschichten über diesen Bereich (ich werde sie in Zukunft einzeln veröffentlichen, also bleiben Sie dran).

• Excel VBA-Makros von Python mit xlwings aufrufen
• [Erstellen] Dateneingabe / -ausgabe zwischen Excel-Python (Pandas DataFrame)
• [Im Aufbau] Praktisches Beispiel mit xlwings UDF

Referenz

• Offizielles Dokument
• [Ist Python in Excel installiert? Verwenden Sie dann xlwings](https://qiita.com/yniji/items/b38bc312e860027108ac#python-%E3%81%8B%E3%82%89-excel-%E3%82%92%E6%93% 8D% E4% BD% 9C% E3% 81% 99% E3% 82% 8B)