Introduction à Python pour les utilisateurs de VBA - Appeler Python depuis Excel avec xlwings -
Je vais vous expliquer comment appeler Python depuis Excel en utilisant xlwings dans le but d'éliminer Excel VBA.
** Remarque **: xlwings lui-même est compatible avec macOS, mais au moment de la publication de cet article, UDF (User Defined Function) ne peut être utilisé que sous Windows.
1.Tout d'abord
1.1. Public cible
Je crée divers outils EUC avec Excel VBA,
――Je veux rendre les outils plus efficaces ――Je veux améliorer mes compétences en programmation
- J'en ai marre de VBA
- La création d'une DLL avec C ++ ou .NET semble être gênante ou difficile --Je souhaite distribuer un programme réalisé en Python aux utilisateurs
Il est destiné aux personnes qui aiment. Si vous n'avez utilisé que VBA, vous n'êtes peut-être pas habitué à exécuter sur la ligne de commande, ce qui est courant dans la description de Python, ou à exécuter directement des fichiers py. Mais avec la bibliothèque Python xlwings, vous pouvez commencer à utiliser Python à la manière de VBA.
Tout d'abord, essayons d'utiliser Python.
1.2 Qu'est-ce que Python?
C'est un langage de programmation comme VBA. La différence avec VBA
- ** Grammaire simple **: également utilisé comme langage de programmation pour les débutants.
- ** Fonctionnalités modernes **: même les baies sont complètement différentes en termes d'utilisation. VBA n'a pas évolué de VBA6 en 1999 depuis plus de 20 ans, donc il ne peut pas être aidé.
- ** Bibliothèque riche **: calculs scientifiques avancés, analyse de données, développement d'IA et bien plus encore.
Etc.
1.3 Que sont les xlwings?
C'est un package qui appelle Python depuis Excel et manipule Excel depuis Python. Les autres packages qui gèrent Excel incluent openpyxl,
- Similaire à VBA par écrit
- Vous pouvez utiliser efficacement les macros existantes
C'est pourquoi j'en ai marre de VBA, mais je dois continuer à l'utiliser ~ ~ Je pense que c'est approprié pour intensifier les utilisateurs de VBA. La possibilité d'appeler Python à partir d'Excel à l'aide de xlwings inclut également Run main et Run Python, mais comme UDF les inclut, nous ne discuterons que de UDF.
2. Préparation
Avec VBA, vous pouvez démarrer rapidement avec Alt + F11, mais avec Python, vous devez faire beaucoup de préparation.
2.1. Installer Python
Installez l'Anaconda classique de la distribution Python (corps Python + ensemble de bibliothèques externes). Téléchargez le programme d'installation depuis ici et installez-le avec les privilèges d'administrateur. Plus vous recherchez sur Google, plus vous pouvez en savoir.
Dans l'écran ci-dessous, il est obsolète, mais il est plus facile de le vérifier plus tard:
Même si vous ne le cochez pas, vous pouvez définir le chemin en exécutant ce qui suit dans Powershell:
> $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")
Lors du réglage pour chaque terminal, réglez "Utilisateur" sur la première et la dernière ligne sur "Machine" (autorisation d'administrateur requise).
2.2. Installation de xlwings
Anaconda l'accompagne depuis le début, aucun travail n'est donc nécessaire. Sinon, installez avec `` pip '':
> pip install xlwings
2.3 Modifier les paramètres d'Excel
Ouvrez Fichier> Options> Centre de sécurité> Paramètres du centre de sécurité> Paramètres des macros ''. Activez les macros dans Set Macros '' et mettez ✓ dans `` Trust access to VBA project object model ''.
2.4 Installation et configuration des compléments
2.4.1 Installation du complément
Installez-le en ligne de commande ou manuellement. L'installation du complément ajoutera un onglet xlwings à votre ruban Excel.
Ligne de commande
Exécutez ce qui suit avec PowerShell, etc. Si le chemin d'accès à la destination d'installation Python (Anaconda par défaut est `` C: \ ProgramData \ Anaconda3 '') ne passe pas, spécifiez xlwings.exe avec le chemin complet.
xlwings addin install
Lorsque vous mettez à jour xlwings, exécutez `` xlwings addin update ''.
Manuel
Sur Excel, sélectionnez Excel Add-in '' dans le menu déroulant en bas de Fichier> Options> Compléments '' et appuyez sur Paramètres ''. Sélectionnez xlwings.xlam dans la boîte de dialogue qui apparaît lorsque vous appuyez sur le bouton Parcourir ''. Utilisez l'un des éléments suivants pour xlwings.xlam.
--Téléchargez la version installée de xlwings.xlam à partir de la page de publication de xlwings Github (https://github.com/xlwings/xlwings/releases)
--Xlwings.xlam situé dans (destination d'installation d'Anaconda) \ pkgs \ xlwings-0.16.0-py37_0 \ Lib \ site-packages \ xlwings \ addin
2.4.2 Paramètres du complément (global)
Vous pouvez le laisser tel quel pour le moment.
L'explication est la suivante. Réglez si nécessaire. Les paramètres ici sont appliqués sur une base par utilisateur.
- ** ʻInterpreter
**: Définit le chemin de Python.exe (par exempleC: \ ProgramData \ Anaconda \ python.exe`). Si rien n'est entré, il sera recherché à partir de la variable d'environnement, donc si vous avez défini le chemin, vous n'avez pas besoin de le définir. - ** ʻUDF Modules` **: nom du fichier Python appelé (à l'exclusion de l'extension .py). Lorsque vous en définissez plusieurs, séparez-les par ";". Si rien n'est entré, il importera un fichier du même nom (extension .py) dans le même répertoire que la feuille de calcul Excel. Il est plus facile d'utiliser cet élément si vous le définissez dans le classeur décrit plus loin.
- **
Debug UDFs**: Cochez ceci lors de l'exécution du débogage décrit plus loin. - **
Redémarrer le serveur UDF**: Arrête le serveur exécutant UDF. Le serveur redémarrera à la prochaine exécution de l'UDF. Les changements dans le fichier Python appelé lui-même seront automatiquement reflétés, mais les changements dans le module importé dans le fichier Python ne seront pas reflétés, donc appuyez sur ce bouton pour le refléter.
S'il existe un paramètre de classeur, celui-ci sera prioritaire.
2.4.3 Paramètres du complément (classeur)
Vous pouvez l'ignorer pour le moment.
Vous pouvez configurer des compléments sur une base de classeur dans la feuille xlwings.conf '' dans le classeur. En ce qui concerne la feuille xlwings.conf, il y a une feuille _xlwings.conf '' dans le classeur créé par le démarrage rapide décrit plus loin, alors copiez et renommez la feuille entière.
Saisissez la valeur correspondant à l'élément dans la colonne A de la colonne B et définissez-la. Les lignes de paramètres inutiles peuvent être supprimées et s'il existe une ligne de paramètres, le paramètre du classeur sera reflété, sinon le paramètre du ruban sera reflété. Est-ce une utilisation pratique de définir uniquement les «modules UDF» et de supprimer les autres lignes?
2.5. Éditeur de texte
Préparez un éditeur de texte déboguable car vous en aurez besoin pour le débogage plus tard. Nous vous recommandons le code Visual Studio (https://code.visualstudio.com/). Si vous cherchez comment installer cela sur Google, vous en trouverez beaucoup. Installez également l'extension Python.
3. Utilisons
Nous expliquerons dans les trois cas suivants. Habituons-nous à utiliser UDF en le regardant dans l'ordre.
3.1 Commande de démarrage rapide
La commande suivante créera un dossier contenant les fichiers Excel et Python (tous deux nommés myproject, renommez myproject comme vous le souhaitez).
> xlwings quickstart myproject
Le contenu de myproject.py est le suivant (extraits et commentaires ajoutés pour explication):
myproject.py
import xlwings as xw #① Importer des xlwings
@xw.func #② Décorateur
def hello(name): #③ Déclaration de fonction
return "hello {0}".format(name)
Pour expliquer brièvement le code
① Importez xlwings pour qu'il puisse être utilisé en Python. ʻAs xwle rend disponible sous l'aliasxw. ② @ xw.funcest un décorateur qui vous permet d'utiliser des fonctions Python comme UDF. N'oubliez pas de préfixer la fonction que vous souhaitez utiliser dans Excel avec `` @ xw.func ''. ③ Déclarez la fonction avecdef. La partie en retrait ci-dessous : (return ...` dans le cas ci-dessus) est le contenu de la fonction.
Il est devenu. Seul le décorateur est un peu difficile à attacher, mais je pense que vous pouvez comprendre la simplicité de Python.
Ensuite, ouvrez myproject.xlsm et appuyez sur ʻImport Functions sur le ruban xlwigs pour importer la fonction hello de myproject.py.
Ouvrez l'éditeur VBA avec Alt + F11. Si l'importation réussit, xlwings_udfs a été ajouté au module standard.
Essayons la fonction bonjour. Tapez = hello ("Python") dans n'importe quelle cellule et il retournera bonjour Python.
Je vais le modifier un peu. Ouvrez myproject.py et modifiez la partie return ... comme suit:
myproject.py
@xw.func
def hello(name):
return "Hello {0}!".format(name)
Si vous recalculez la cellule, vous obtenez Hello Python!.
Ajoutons maintenant une fonction. Ajoutez ce qui suit à myproject.py.
myproject.py
@xw.func
def double_sum(x, y):
return 2 * (x + y)
Si vous souhaitez importer une nouvelle fonction, appuyez à nouveau sur «Importer des fonctions». Entrez = double_sum (2, 3) dans n'importe quelle cellule et si 10 est renvoyé, cela réussit. C'est facile.
3.2. Échantillon officiel
Regardons un exemple un peu plus compliqué qu'avant avec l'échantillon officiel. Vous pouvez voir la force de la coopération avec des bibliothèques externes.
Depuis GitHub udf.xlsm et udf.py Téléchargez /raw/master/examples/udf/udf.py), enregistrez-le dans le même dossier et ouvrez udf.xlsm.
Laissez-moi vous en expliquer.
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]
Renvoie la valeur de chaque cellule plus un. Dans le classeur, la plage de cellules E11: G12 est entrée avec «Ctrl + Maj + Entrée», qui est une formule matricielle. @ xw.arg ('data', ndim = 2) est un décorateur pour contrôler les arguments, et est toujours une liste bidimensionnelle (tableau) même si l'argument data est une seule cellule ou ligne / colonne. Je vais le lire comme. La partie «[cell + 1 for cell in row]» est appelée notation de liste vers l'intérieur, et vous pouvez créer une liste (tableau) de manière concise.
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)
Renvoie le produit de la matrice. Identique à la fonction MMULT d'Excel. @ xw.arg ('x', np.array, ndim = 2) provoque la lecture de l'argument x comme un tableau Numpy à deux dimensions. Numpy Array est défini dans la bibliothèque de calculs scientifiques Numpy Dans la liste (tableau), [fonction point] de Numpy Array (https://numpy.org/doc/1.18/reference/generated/numpy.ndarray.dot.html
) Permet de calculer le produit des matrices.
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()
Renvoie une matrice de corrélation entre les données de séries chronologiques. @ xw.arg ('x', pd.DataFrame, index = False, header = False) charge l'argument x en tant que Pandas DataFrame sans index ni en-tête. Pandas DataFrame est une bibliothèque d'analyse de données Pandas C'est comme un tableau à deux dimensions défini dans la [fonction corr] de Pandas DataFrame (https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.corr.html#pandas.DataFrame. Corr) est utilisé pour calculer la matrice de corrélation. Ensuite, @ xw.ret (index = False, header = False) supprime l'index et l'en-tête du DataFrame du résultat du calcul de la fonction corr et ne renvoie que la valeur à Excel.
Si vous essayez de faire la même chose avec VBA, ce sera long car vous écrivez une boucle for. De plus, Numpy et Pandas ont diverses autres fonctions. C'est une autre force que VBA n'a pas.
@ xw.arg et @ xw.ret
@ Xw.arg et @ xw.ret qui apparaissent dans la fonction ci-dessus sont appelés convertisseurs et sont des données entre Excel et Python. Effectue la conversion de type et ainsi de suite. Je publierai ultérieurement sur la méthode d'entrée / sortie de données à l'aide du convertisseur. impatient de.
3.3. Ajouter au fichier Excel existant
Vous pouvez également permettre d'appeler Python à partir d'un .xlsm existant. C'est facile à oublier, donc je vais l'écrire en premier, mais vous devez ** ouvrir les paramètres de référence dans l'éditeur VBA et vérifier xlwings **.
Rendons possible d'appeler udf.py à partir du fichier Excel existant ".xlsx existant". Commencez par changer le format de fichier. Appuyez sur F12 et sélectionnez Classeur activé pour les macros (* .xlsm) comme format.
Placez les fichiers .xlsm et udf.py existants dans le même dossier. Ouvrez un fichier .xlsm existant, copiez la feuille _xlwings.conf à partir de la feuille myproject.sheet créée dans 3.1., Et renommez la feuille en xlwings.conf. Entrez udf dans les modules UDF.
Ouvrez l'éditeur VBA avec Alt + F11, ouvrez Outils> Références dans la barre de menu et vérifiez xlwings.
Appuyez sur le bouton «Fonctions d'importation» sur le ruban. Si l'importation réussit, xlwings_udfs sera ajouté au module standard de l'éditeur VBA.
Essayons la fonction double_sum. Entrez = double_sum (2,3) dans n'importe quelle cellule et 10 sera renvoyé.
Vous pouvez renommer le fichier Python en ".py existant" sans copier la feuille xlwings.conf. Cependant, lorsque vous utilisez le même fichier Python dans plusieurs fichiers Excel, ou lorsque vous divisez la version en ajoutant v2 à la fin du fichier Excel, il est pratique de spécifier le module à l'aide de la feuille xlwings.conf. ..
4. Déboguer
Déboguons udf.py. Dans le cas de udf.py, il est inclus depuis le début, mais si vous souhaitez déboguer un autre code, ajoutez ce qui suit à la fin.
if __name__ == '__main__':
xw.serve()
Ensuite, définissez des points d'arrêt, etc. sur Visual Studio Code. Définissons-le dans l'instruction return de la fonction matrix_mult.
Lorsque vous appuyez sur F5 dans l'éditeur, un menu déroulant qui sélectionne la configuration de débogage apparaît. Sélectionnez "Fichier Python" pour exécuter le débogage.
Si vous cliquez sur la marque d'engrenage en haut à gauche et sélectionnez également "Fichier Python", un fichier de configuration de débogage sera créé. Après cela, appuyez simplement sur F5 pour exécuter le débogage. (L'onglet launch.json peut être fermé tel quel)
Revenez à l'écran Excel et cochez «Déboguer les UDF» sur le ruban (s'il y a une ligne pour «Déboguer les UDF» dans la feuille xlwings.conf, définissez la valeur True).
Si vous recalculez la feuille (Alt + Maj + F9), elle cessera de fonctionner au point d'arrêt. Vous pouvez vérifier le contenu de la variable dans la variable supérieure gauche.
C'est une astuce, mais si vous sérialisez les variables depuis la console de débogage, vous pouvez vérifier le contenu avec Jupyter Notebook, ce qui est pratique. Par exemple, la variable Pandas DataFrame df peut être sérialisée avec df.to_pickle ('df.pickle') dans la console de débogage et lue avecdf = pd.read_pickle ('df.pickle')dans la cellule Jupyter Notebook. Par exemple, vous pouvez voir diverses variables en cours de débogage sur Jupyte Notebook.
5. Conclusion
Dans cet article, j'ai expliqué comment utiliser xlwings. Notez que UDF ne doit pas être utilisé dans un grand nombre de cellules comme les fonctions Excel. Il est lent car il communique avec le serveur UDF cellule par cellule et le traitement multi-thread ne peut pas être effectué.
Ensuite, comment l'utiliser est de l'enregistrer dans le bouton. À ce stade, les données sont entrées du côté Python dans la fonction Python au lieu de l'argument UDF, et le résultat du traitement est sorti dans le tableau Excel ou le fichier CSV.
J'ai publié du contenu avancé ci-dessous, y compris des histoires sur ce domaine (je les publierai un par un à l'avenir, alors restez à l'écoute).
- Appeler des macros Excel VBA depuis Python en utilisant xlwings
- [Création] Entrée / sortie de données entre Excel-Python (Pandas DataFrame)
- [En construction] Exemple pratique utilisant xlwings UDF
référence
- Document officiel
- [Python est-il installé dans Excel? Ensuite, utilisez 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)