Manipulowanie arkuszami kalkulacyjnymi Excel za pomocą Pythona

Microsoft Excel to oprogramowanie do arkuszy kalkulacyjnych, które służy do przechowywania i zarządzania danymi tabelarycznymi. Ponadto w programie Excel można przeprowadzać obliczenia, stosując formuły do danych, a także tworzyć wizualizacje danych.Wiele zadań wykonywanych w arkuszach kalkulacyjnych, takich jak operacje matematyczne, można zautomatyzować za pomocą programowania, a wiele języków programowania ma moduły do manipulowania arkuszami kalkulacyjnymi Excel. W tym samouczku pokażemy, jak używać modułu openpyxl Pythona do czytania i modyfikowania arkuszy kalkulacyjnych Excel.

Instalowanie openpyxl

Zanim będziesz mógł zainstalować openpyxl, musisz zainstalować pip. Pip służy do instalowania pakietów Pythona. Uruchom następujące polecenie w wierszu poleceń, aby sprawdzić, czy zainstalowano pip.

C:\Users\windows> pip help

Jeśli zwrócona zostanie zawartość pomocy pip, to zostanie zainstalowany pip; w przeciwnym razie przejdź do poniższego linku i pobierz get-pip.plik py:

https://bootstrap.pypa.io/get-pip.py

Teraz uruchom następujące polecenie, aby zainstalować pip:

C:\Users\windows> python get-pip.py

Po zainstalowaniu pip można użyć następującego polecenia do zainstalowania openpyxl command.

C:\Users\windows> pip install openpyxl

Tworzenie dokumentu Excel

W tej sekcji użyjemy modułu openpyxl do stworzenia dokumentu Excel. Najpierw otwórz wiersz polecenia, wpisując „cmd” w pasku wyszukiwania; następnie wejdź

C:\Users\windows> Python>

Aby utworzyć skoroszyt Excela, zaimportujemy moduł openpyxl, a następnie użyjemy metody 'Workbook()' do utworzenia skoroszytu.

>>> # importowanie modułu openpyxl
>>> importuj openpyxl
>>> # Inicjowanie skoroszytu
>>> zeszyt_pracy = openpyxl.Zeszyt ćwiczeń()
>>> # zapisywanie skoroszytu jako 'przykład.xlsx
>>> zeszyt_pracy.zapisz('przykład.xlsx')

Powyższe polecenia tworzą dokument Excel o nazwie przykład.xlsx. Następnie będziemy manipulować tym dokumentem Excel.

Manipulowanie arkuszami w dokumencie Excel

Stworzyliśmy dokument Excel o nazwie przykład.xlsx. Teraz będziemy manipulować arkuszami tego dokumentu za pomocą Pythona. Moduł openpyxl ma metodę 'create_sheet()', której można użyć do utworzenia nowego arkusza. Ta metoda przyjmuje dwa argumenty: indeks i tytuł. Indeks określa położenie arkusza przy użyciu dowolnej nieujemnej liczby całkowitej (włącznie z 0), a tytuł jest tytułem arkusza. Listę wszystkich arkuszy w obiekcie work_book można wyświetlić, wywołując listę nazw arkuszy.

>>> # importowanie openpyxl
>>> importuj openpyxl
>>> # ładowanie istniejącego dokumentu Excel do obiektu work_book
>>> zeszyt_pracy = openpyxl.load_workbook('przykład.xlsx')
>>> # Tworzenie nowego arkusza w 0. indeksie
>>> zeszyt_pracy.create_sheet(index=0, title='Pierwszy arkusz')

>>> # Zdobycie wszystkich arkuszy
>>> zeszyt_pracy.nazwy arkuszy
['Pierwszy arkusz', 'Arkusz']
>>> # Zapisywanie dokumentu Excel
>>> zeszyt_pracy.zapisz('przykład.xlsx')

W powyższym kodzie utworzyliśmy arkusz o nazwie Pierwszy arkusz i umieściliśmy go w 0-tym indeksie. Arkusz poprzednio znajdujący się w 0. indeksie został przeniesiony do 1. indeksu, jak pokazano na wyjściu. Teraz zmienimy nazwę oryginalnego arkusza z Arkusza na Drugi arkusz.

Atrybut tytuł zawiera nazwę arkusza. Aby zmienić nazwę arkusza, musimy najpierw przejść do tego arkusza w następujący sposób.

>>> # Pobieranie aktywnego arkusza z dokumentu Excel
>>> arkusz = zeszyt_pracy.aktywny
>>> # Nazwa arkusza drukowania
>>> drukuj (arkusz).tytuł)
Pierwszy arkusz >>> # Przechodzenie do drugiego arkusza (w indeksie 1)
>>> zeszyt_pracy.aktywny = 1
>>> # Uzyskiwanie aktywnego arkusza
>>> arkusz = zeszyt_pracy.aktywny
>>> # drukowanie nazwy arkusza
>>> drukuj (arkusz).tytuł)
Arkusz >>> # Zmiana tytułu arkusza
>>> arkusz.title = „Drugi arkusz”
>>> # Drukowanie tytułu arkusza
>>> drukuj (arkusz).tytuł)
Drugi arkusz

Podobnie możemy usunąć arkusz z dokumentu Excel. Moduł openpyxl oferuje metodę remove() do usunięcia arkusza. Ta metoda przyjmuje nazwę arkusza do usunięcia jako argument, a następnie usuwa ten arkusz. Drugi arkusz możemy usunąć w następujący sposób:

>>> # usuwanie arkusza według nazwy
>>> zeszyt_pracy.remove(work_book['Drugi arkusz'])
>>> # pobranie wszystkich arkuszy
>>> zeszyt_pracy.nazwy arkuszy
['Pierwszy arkusz']
>>> # zapisywanie dokumentu Excel
>>> zeszyt_pracy.zapisz('przykład.xlsx')

Dodawanie danych do komórek

Do tej pory pokazaliśmy, jak tworzyć lub usuwać arkusze w dokumencie Excel. Teraz dodamy dane do komórek różnych arkuszy. W tym przykładzie w naszym dokumencie mamy jeden arkusz o nazwie Pierwszy arkusz i chcemy utworzyć jeszcze dwa arkusze.

>>> # importowanie openpyxl
>>> importuj openpyxl
>>> # ładowanie skoroszytu
>>> zeszyt_pracy = openpyxl.load_workbook('przykład.xlsx')
>>> # Tworzenie nowego arkusza w pierwszym indeksie
>>> zeszyt_pracy.create_sheet(index=1, title='Drugi arkusz')

>>> # tworzenie nowego arkusza w drugim indeksie
>>> zeszyt_pracy.create_sheet(index=2, title='Trzeci arkusz')

>>> # pobranie wszystkich arkuszy
>>> zeszyt_pracy.nazwy arkuszy
['Pierwszy arkusz', 'Drugi arkusz', 'Trzeci arkusz']

Teraz mamy trzy arkusze i dodamy dane do komórek tych arkuszy.

>>> # Pierwsze arkusze
>>> arkusz_1 = zeszyt_pracy['Pierwszy arkusz']
>>> # Dodawanie danych do komórki „A1” pierwszego arkusza
>>> arkusz_1['A1'] = 'Nazwa'
>>> # Zdobycie drugiego arkusza
>>> arkusz_2 = zeszyt_pracy['Drugi arkusz']
>>> # Dodawanie danych do komórki „A1” drugiego arkusza
>>> arkusz_2['A1'] = 'ID'
>>> # Zdobycie trzeciego arkusza
>>> arkusz_3 = zeszyt_pracy['Trzeci arkusz']
>>> # Dodawanie danych do komórki „A1” trzeciego arkusza
>>> arkusz_3['A1'] = 'Oceny'
>>> # Zapisywanie skoroszytu Excel
>>> zeszyt_pracy.zapisz('przykład.xlsx')

Czytanie arkuszy Excel

Moduł openpyxl używa atrybutu wartości komórki do przechowywania danych tej komórki. Możemy odczytać dane w komórce, wywołując atrybut wartości komórki. Teraz mamy trzy arkusze, a każdy arkusz zawiera pewne dane. Dane możemy odczytać za pomocą następujących funkcji w openpyxl:

>>> # importowanie openpyxl
>>> importuj openpyxl
>>> # ładowanie skoroszytu
>>> zeszyt_pracy = openpyxl.load_workbook('przykład.xlsx')
>>> # Pierwsze arkusze
>>> arkusz_1 = zeszyt_pracy['Pierwszy arkusz']
>>> # Zdobycie drugiego arkusza
>>> arkusz_2 = zeszyt_pracy['Drugi arkusz']
>>> # Zdobycie trzeciego arkusza
>>> arkusz_3 = zeszyt_pracy['Trzeci arkusz']
>>> # drukowanie danych z komórki „A1” pierwszego arkusza
>>> drukuj(arkusz_1['A1'].wartość)
Nazwa
>>> # drukowanie danych z komórki „A1” drugiego arkusza
>>> drukuj(arkusz_2['A1'].wartość)
ID
>>> # drukowanie danych z komórki „A1” trzeciego arkusza
>>> drukuj(arkusz_3['A1'].wartość)
Klas

Zmiana czcionek i kolorów

Następnie pokażemy, jak zmienić czcionkę komórki za pomocą funkcji Font(). Najpierw zaimportuj openpyxl.style obiektu. Metoda Font() przyjmuje listę argumentów, w tym:

nazwa (ciąg): nazwa czcionki
rozmiar (int lub float): rozmiar czcionki
podkreślenie (ciąg): typ podkreślenia
kolor (ciąg): szesnastkowy kolor tekstu
kursywa (bool): czy czcionka jest pisana kursywą
pogrubienie (bool): czy czcionka jest pogrubiona

Aby zastosować style, musimy najpierw utworzyć obiekt, przekazując wszystkie parametry do metody Font(). Następnie wybieramy arkusz, a wewnątrz arkusza wybieramy komórkę, do której chcemy zastosować styl. Następnie stosujemy styl do wybranej komórki.

>>> # importowanie openpyxl
>>> importuj openpyxl
>>> # importowanie metody Font z openpyxl.style
>>> z openpyxl.import stylów Czcionka
>>> # ładowanie skoroszytu
>>> zeszyt_pracy = openpyxl.load_workbook('przykład.xlsx')
>>> # Tworzenie obiektu stylu
>>> styl = Czcionka(nazwa='Konsola', rozmiar=13, pogrubienie=Prawda,
… kursywa=Fałsz)
>>> # Wybór arkusza ze skoroszytu
>>> arkusz_1 = zeszyt_pracy['Pierwszy arkusz']
>>> # Wybór komórki do której chcemy dodać style
>>> a1 = arkusz_1['A1']
>>> # Stosowanie stylów do komórki
>>> a1.czcionka = styl
>>> # Zapisywanie skoroszytu
>>> zeszyt_pracy.zapisz('przykład.xlsx')

Stosowanie granic do komórek

Możemy zastosować obramowanie do komórek w arkuszu Excela za pomocą metod Border() i Side() programu openpyxl.style.moduł granic. Możemy przekazać różne funkcje jako parametry do metody Border(). Poniżej przedstawiono niektóre funkcje, które są przekazywane jako parametry do metody Border() w celu zdefiniowania wymiarów obramowania.

lewo: zastosuj obramowanie po lewej stronie komórki
dobrze: zastosuj obramowanie po prawej stronie komórki
Top: zastosuj obramowanie na górze komórki
Dolny: zastosuj obramowanie na dole komórki

Te funkcje przyjmują atrybuty stylu jako parametry. Atrybut style definiuje styl obramowania (e.sol., stałe, przerywane). Parametry stylu mogą mieć dowolną z następujących wartości.

podwójnie: podwójna granica linii
przerywany: przerywana granica
chudy: cienka ramka
średni: średnia granica
mediumDashDot: obwódka przerywana i kropkowana o średniej wadze
gruby: gruba ramka
kreskaKropka: linia przerywana i kropkowana
włosy: bardzo cienka ramka
kropkowany: kropkowana ramka

Teraz zastosujemy różne rodzaje obramowań do różnych komórek naszych arkuszy kalkulacyjnych. Najpierw wybieramy komórki, a następnie definiujemy style obramowania i stosujemy te style do różnych komórek.

>>> # importowanie openpyxl
>>> importuj openpyxl
>>> # importowanie klas Border i Side
>>> z openpyxl.style.import granic obramowanie, bok,
>>> # ładowanie skoroszytu
>>> zeszyt_pracy = openpyxl.load_workbook('przykład.xlsx')
>>> # Wybór arkusza
>>> arkusz_1 = zeszyt_pracy['Pierwszy arkusz']
>>> # Wybieranie różnych komórek z arkusza
>>> komórka_1 = arkusz_1['A1']
>>> komórka_2 = arkusz_1['B2']
>>> komórka_3 = arkusz_1['C3']
>>> # Definiowanie różnych stylów obramowania
>>> styl_1 = obramowanie(bottom=bok(styl='dotted'))
>>> styl_2 = obramowanie(prawo=bok(styl='cienki'))
>>> style_3 = Obramowanie(góra=bok(styl='kreska kropka'))
>>> # stosowanie stylów obramowania do komórek
>>> komórka_1.obramowanie = styl_1
>>> komórka_2.obramowanie = styl_2
>>> komórka_3.obramowanie = styl_3
>>> # Zapisywanie skoroszytu
>>> zeszyt_pracy.zapisz('przykład.xlsx')

Dostosowywanie wymiarów wierszy i kolumn

Wysokość wiersza i szerokość kolumny dokumentu Excel można również dostosować za pomocą Pythona. Moduł openpyxl ma dwie wbudowane metody, które można wykorzystać do wykonania tych czynności. Najpierw wybieramy arkusz, którego szerokość kolumny lub wysokość wiersza chcemy zmienić. Następnie stosujemy metodę do konkretnego wiersza lub kolumny.

>>> # importowanie openpyxl
>>> importuj openpyxl
>>> # ładowanie skoroszytu
>>> zeszyt_pracy = openpyxl.load_workbook('przykład.xlsx')
>>> # wybór arkusza
>>> arkusz_1 = zeszyt_pracy['Pierwszy arkusz']
>>> # zmiana wysokości pierwszego rzędu
>>> arkusz_1.row_dimensions[1].wysokość = 50
>>> # Zapisywanie skoroszytu
>>> zeszyt_pracy.zapisz('przykład.xlsx')

Podobnie możemy zmienić szerokość kolumny za pomocą poniższego kodu

>>> # wybór arkusza ze skoroszytu programu Excel
>>> arkusz_2 = zeszyt_pracy['Drugi arkusz']
>>> # zmiana szerokości kolumny A
>>> arkusz_2.wymiary_kolumny['A'].szerokość = 50
>>> # Zapisywanie skoroszytu
>>> zeszyt_pracy.zapisz('przykład.xlsx')

Powyższy kod zmieni wysokość pierwszego wiersza na 50 pkt, a szerokość kolumny A na 50 pkt.

Łączenie i rozłączanie komórek

Podczas pracy z arkuszami kalkulacyjnymi Excela często musimy łączyć i rozłączać komórki. Aby scalić komórki w Pythonie, można użyć prostej funkcji opartej na openpyxl. Moduł openpyxl oferuje metodę merge_cells(), której można użyć do scalania komórek w Excelu. Nowa komórka przyjmie nazwę górnej lewej komórki. Na przykład, jeśli chcemy scalić komórki z komórki A1 do komórki B2, to nowo utworzona komórka będzie określana jako A1. Aby scalić komórki za pomocą openpyxl, najpierw zaznaczamy arkusz, a następnie stosujemy metodę merge_cells() do arkusza.

>>> # importowanie modułu openpyxl
>>> importuj openpyxl
>>> # ładowanie skoroszytu
>>> zeszyt_pracy = openpyxl.load_workbook('przykład.xlsx')
>>> # wybór pierwszego arkusza ze skoroszytu programu Excel
>>> arkusz_1 = zeszyt_pracy['Pierwszy arkusz']
>>> # scalanie komórek od A1 do B2 w arkuszu 1
>>> arkusz_1.merge_cells('A1:B2')
>>> # zapisywanie skoroszytu
>>> zeszyt_pracy.zapisz('przykład.xlsx')

Podobnie metoda unmerge_cells() może być użyta do rozłączenia komórek w arkuszu kalkulacyjnym Excel. Do rozłączenia komórek można użyć następującego kodu:

>>> # wybór arkusza ze skoroszytu
>>> arkusz_1 = zeszyt_pracy['Pierwszy arkusz']
>>> # rozłączenie komórek od A1 do B2
>>> arkusz_1.unmerge_cells('A1:B2')
>>> # zapisywanie skoroszytu
>>> zeszyt_pracy.zapisz('przykład.xlsx')

Wniosek

Arkusze kalkulacyjne Excel są powszechnie używane do manipulacji danymi. Jednak takie zadania mogą być monotonne. Dlatego w takich przypadkach można wykorzystać programowanie do automatyzacji manipulacji arkuszem kalkulacyjnym.

W tym artykule omówiliśmy kilka przydatnych funkcji modułu openpyxl Pythona. Pokazaliśmy, jak tworzyć, czytać, usuwać i modyfikować arkusze kalkulacyjne Excel, jak zmieniać styl, stosować czcionkę, obramowanie i wymiary komórek oraz jak scalać i rozłączać komórki. Stosując te funkcje, możesz zautomatyzować wiele zadań związanych z manipulacją arkuszami kalkulacyjnymi za pomocą Pythona.