xlwings doda Excelowi skrzydeł

Tak bardzo, jak Excel jest błogosławieństwem, może się on również okazać przekleństwem. Jeśli chodzi o małe dane i wystarczająco proste operacje, to Excel jest do nich idealny. Jeśli jednak zajmujesz się rzeczami, które są bardziej skomplikowane, to zaczynają pojawiać się problemy. Możecie oczywiście użyć Excel VBA, ale wcale nie musicie tego robić, bo jest jeszcze inne rozwiązanie. Excelowi można dodać skrzydeł przy pomocy pewnej pythonowej biblioteki — xlwings. Pozwala ona na wywoływanie skryptów Pythona przez VBA i przekazywanie danych między nimi.

Po co integrować Pythona z Excel VBA

Prawda jest taka, że w VBA można zrobić praktycznie wszystko. Jeśli tak, to dlaczego w ogóle używać Pythona? Istnieje ku temu kilka powodów. 

1. Możesz wtedy stworzyć swoją własną funkcję w Excelu bez potrzeby nauki języka VBA
2. Istnieją użytkownicy, którzy przyzwyczaili się do Excela i dobrze się z nim czują
3. Możesz znacznie przyspieszyć operacje na danych, dzięki Pythonowi
4. W Pythonie znajdują się biblioteki, których możesz użyć do prawie wszystkiego (Uczenie maszynowe, Data Science itp.)
5. I ostatni, najważniejszy powód: bo masz taką możliwość!

Przygotowanie do użycia xlwings

Najpierw musimy dokonać instalacji. Potrzebne są do tego zaledwie dwa polecenia:

pip install xlwings

Następnie trzeba zainstalować część, która pozwala na integrację z Excelem. Upewnij się, że zamknąłeś wszystkie instancje Excela i wpisz:

xlwings addin install

Zakładając, że nie ma żadnych błędów, możemy kontynuować. Niemniej jednak ci, którzy używają Excel 2016 na Windows 10, mogą zobaczyć następujący błąd:

xlwings 0.17.0
[Errno 2] No such file or directory: 'C:\\Users\\costa\\AppData\\Roaming\\Microsoft\\Excel\\XLSTART\\xlwings.xlam'

Jeśli tak się właśnie stało, to wystarczy, że utworzysz brakujący katalog. Możesz to łatwo zrobić za pomocą polecenia mkdir. Ja zrobiłem coś takiego:

mkdir C:\\Users\\costa\\AppData\\Roaming\\Microsoft\\Excel\\XLSTART

Jeśli instalacja przebiegła pomyślnie, powinniście dostrzec różnicę w Excelu:

Włączanie funkcji zdefiniowanych przez użytkownika dla xlwings

Najpierw musimy załadować Excel Add-in. Naciśnij alt, L, H, a następnie przejść do katalogu powyżej, aby załadować wtyczkę. Po zakończeniu powinniście zobaczyć następujące informacje:

Na koniec należy włączyć Trust access to the VBA project object model. Możesz to zrobić w następujący sposób: File> Options> Trust Center> Trust Center Settings> Macro Settings:

Przygotowujemy się do pracy z xlwings

Istnieją dwa główne sposoby przejścia z Excela do Pythona (i z powrotem). Pierwszym z nich jest wywołanie skryptu Pythona bezpośrednio z VBA. Drugi to przejście przez funkcję zdefiniowaną przez użytkownika. Rzućmy okiem na oba sposoby.

Aby uniknąć zamieszania i nie musieć za każdym razem konfigurować od nowa, xlwings oferuje utworzenie gotowego arkusza kalkulacyjnego. Skorzystajmy więc z tego. Korzystając z terminala, przechodzimy do katalogu i wpisujemy:

xlwings quickstart ProjectName

Nazywam tę funkcję MyFirstPythonXL. Powyższe polecenie utworzy nowy folder w katalogu z poprzedniego kroku z arkuszem Excel i plikiem Pythona.

Po otwarciu pliku .xlsm natychmiast pojawia się nowy arkusz Excela o nazwie _xlwings.conf. Jeśli chcesz zastąpić domyślne ustawienia xlwings, wszystko, co musisz zrobić, to zmienić nazwę tego arkusza i usunąć początkowy znak podkreślenia. Dzięki temu jesteśmy już gotowi do korzystania z xlwings.

Z VBA do Pythona

Zanim przejdziemy do kodowania, upewnijmy się, że wszyscy mamy to samo. Aby wyświetlić nasz edytor Excel VBA, naciśnij klawisze alt + f11. Powinno to zwrócić następujący ekran:

Edytor VBA z xlwings

Należy tutaj zwrócić uwagę na to, co ten kod robi:

1. Szuka skryptu Pythona w tej samej lokalizacji, w której znajduje się arkusz kalkulacyjny
2. Szuka skryptu Pythona o takiej samej nazwie jak arkusz kalkulacyjny (ale z rozszerzeniem .py)
3. Wywołuje funkcję main() w skrypcie Pythona

Bez zbędnych ceregieli spójrzmy na kilka przykładów użycia tego kodu.

Przykład 1: Przetwarzanie poza Excelem i zwracanie wyniku

Zobaczymy tutaj, w jaki sposób można przeprowadzić operacje poza Excelem. Wyniki zwrócimy jednak w arkuszu kalkulacyjnym. Przykład ten może mieć nieskończoną liczbę przypadków użycia.

Pozyskamy i zmodyfikujemy dane z pliku CSV, a następnie przekażemy je do programu Excela. Zobaczmy, jakie to proste:

Najpierw kod VBA:

Nic tutaj właściwie nie zmieniałem.

Następnie kod Pythona:

import xlwings as xw
import pandas as pd
def main():
    wb = xw.Book.caller()
    df = pd.read_csv(r'C:\temp\TestData.csv')
    df['total_length'] =  df['sepal_length_(cm)'] + df['petal_length_(cm)']
    wb.sheets[0].range('A1').value = df

Oto rezultat:

xlwings w akcji

Przykład 2: Używanie wartości wejściowej Excela do przeprowadzenia operacji

Odczytamy tutaj dane wejściowe z Excela, zrobimy coś z nimi w Pythonie, a następnie przekażemy wynik z powrotem do Excela. Mówiąc dokładniej, przeczytamy Greeting, Name oraz lokalizację pliku, w którym możemy znaleźć żarty. Nasz skrypt w Pythonie pobierze losową linijkę kodu z pliku i zwróci nam dany żart.

Najpierw kod VBA:

Nic tutaj nie zmieniałem.

Kod Pythona:

import xlwings as xw
import random
def random_line(afile):
    line = next(afile)
    for num, aline in enumerate(afile, 2):
      if random.randrange(num): continue
      line = aline
    return line
'Function from: stackoverflow
def main():
    wb = xw.Book.caller()
    listloc = str(wb.sheets[0].range('B3').value)
    fhandle = open(listloc, encoding = 'utf-8')
wb.sheets[0].range('A5').value = wb.sheets[0].range('B2').value + ' ' + wb.sheets[0].range('B1').value + ' here is a joke for you'
    wb.sheets[0].range('A6').value = random_line(fhandle)

Co daje:

Funkcje zdefiniowane przez użytkownika z xlwings

W prawie taki sam sposób, jak poprzednio, zmienimy kod w pliku Pythona. W celu zmiany czegoś w funkcję zdefiniowaną przez użytkownika w programie Excel wystarczy dodać @xw.func przed wierszem, w którym dana funkcja się znajduje:

Kod Pythona:

import xlwings as xw
@xw.func
def joke(x):
    wb = xw.Book.caller()
    fhandle = open(r'C:\Temp\list.csv')
    for i, line in enumerate(fhandle):
        if i == x:
            return(line)

Rezultat:

 
Jak widać, biblioteka xlwings jest bardzo przydatna, jeśli, tak jak ja, nie lubisz pracować w VBA, ale potrzebujesz arkuszy kalkulacyjnych Excela.

Oryginał tekstu w języku angielskim przeczytasz tutaj.