Manipuliranje Excel proračunskim tablicama pomoću Pythona

Microsoft Excel je program za proračunske tablice koji se koristi za pohranu i upravljanje tabličnim podacima. Nadalje, s programom Excel, izračuni se mogu izvoditi primjenom formula na podatke i izrađivati vizualizacije podataka.Mnogi zadaci izvedeni u proračunskim tablicama, poput matematičkih operacija, mogu se automatizirati programiranjem, a mnogi programski jezici imaju module za manipulaciju proračunskim tablicama Excel. U ovom vodiču pokazat ćemo vam kako koristiti Pythonov openpyxl modul za čitanje i izmjenu Excel proračunskih tablica.

Instaliranje openpyxla

Prije nego što možete instalirati openpyxl, morate instalirati pip. Pip se koristi za instaliranje Python paketa. Pokrenite sljedeću naredbu u naredbenom retku da biste vidjeli je li pip instaliran.

C: \ Users \ windows> pip help

Ako se vrati sadržaj pomoći pip, tada se pip instalira; u suprotnom, idite na sljedeću poveznicu i preuzmite get-pip.py datoteka:

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

Sada pokrenite sljedeću naredbu za instalaciju pipa:

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

Nakon instalacije pipa, sljedeća naredba može se koristiti za instalaciju openpyxla.

C: \ Users \ windows> pip install openpyxl

Izrada Excel dokumenta

U ovom ćemo odjeljku koristiti modul openpyxl za stvaranje Excel dokumenta. Prvo otvorite naredbeni redak tako da upišete 'cmd' u traku za pretraživanje; zatim unesite

C: \ Korisnici \ windows> python

Da bismo stvorili Excel radnu knjigu, uvezemo modul openpyxl, a zatim ćemo pomoću metode 'Workbook ()' stvoriti radnu knjigu.

>>> # uvoz modula openpyxl
>>> uvozi openpyxl
>>> # Inicijalizacija radne bilježnice
>>> radna_knjiga = openpyxl.Radna bilježnica ()
>>> # spremanje radne knjige kao 'primjer.xlsx '
>>> radna_knjiga.spremi ('primjer.xlsx ')

Gornje naredbe stvaraju Excel dokument koji se naziva primjer.xlsx. Dalje ćemo manipulirati ovim Excel dokumentom.

Manipuliranje listovima u Excel dokumentu

Stvorili smo Excel dokument koji se naziva primjer.xlsx. Sada ćemo manipulirati listovima ovog dokumenta pomoću Pythona. Modul openpyxl ima metodu 'create_sheet ()' koja se može koristiti za stvaranje novog lista. Ova metoda uzima dva argumenta: indeks i naslov. Indeks definira smještaj lista pomoću bilo kojeg negativnog cijelog broja (uključujući 0), a naslov je naslov lista. Popis svih listova u objektu work_book može se prikazati pozivanjem popisa imena listova.

>>> # uvoz openpyxl-a
>>> uvozi openpyxl
>>> # učitavanje postojećeg Excel dokumenta u objekt radne knjige
>>> radna_knjiga = openpyxl.load_workbook ('primjer.xlsx ')
>>> # Stvaranje novog lista s 0. indeksom
>>> radna_knjiga.create_sheet (index = 0, title = 'Prvi list')

>>> # Dohvaćanje svih listova
>>> radna_knjiga.imena listova
['Prvi list', 'List']
>>> # Spremanje Excel dokumenta
>>> radna_knjiga.spremi ('primjer.xlsx ')

U gornjem kodu izradili smo list s nazivom Prvi list i postavili ga na 0. indeks. List koji se prethodno nalazio na 0. indeksu premješten je na 1. indeks, kao što je prikazano u izlazu. Sada ćemo promijeniti naziv izvornog lista iz Sheet u Second Sheet.

Atribut naslova sadrži naziv lista. Da bismo preimenovali list, prvo moramo doći do tog lista kako slijedi.

>>> # Preuzimanje aktivnog lista iz programa Excel Document
>>> list = radna_knjiga.aktivan
>>> # Naziv lista za ispis
>>> ispis (list.titula)
Prvi list >>> # Navigacija do drugog lista (na indeksu 1)
>>> radna_knjiga.aktivan = 1
>>> # Dobivanje aktivnog lista
>>> list = radna_knjiga.aktivan
>>> # ispis naziva lista
>>> ispis (list.titula)
List >>> # Promjena naslova lista
>>> list.title = 'Drugi list'
>>> # Naslov tiskarskog lista
>>> ispis (list.titula)
Drugi list

Slično tome, iz Excel dokumenta možemo ukloniti list. Modul openpyxl nudi metodu remove () za uklanjanje lista. Ova metoda kao argument uzima ime lista koji se uklanja, a zatim uklanja taj list. Drugi list možemo ukloniti na sljedeći način:

>>> # uklanjanje lista po imenu
>>> radna_knjiga.ukloni (radna knjiga ['Drugi list'])
>>> # dobivanje svih listova
>>> radna_knjiga.imena listova
['Prvi list']
>>> # spremanje Excel dokumenta
>>> radna_knjiga.spremi ('primjer.xlsx ')

Dodavanje podataka u stanice

Do sada smo vam pokazali kako stvoriti ili izbrisati listove u Excel dokumentu. Sada ćemo dodati podatke u ćelije različitih listova. U ovom primjeru imamo jedan list s imenom First Sheet u našem dokumentu i želimo stvoriti još dva lista.

>>> # uvoz openpyxl-a
>>> uvozi openpyxl
>>> # učitavanje radne knjige
>>> radna_knjiga = openpyxl.load_workbook ('primjer.xlsx ')
>>> # Izrada novog lista s 1. indeksom
>>> radna_knjiga.create_sheet (indeks = 1, naslov = 'Drugi list')

>>> # stvaranje novog lista na 2. indeksu
>>> radna_knjiga.create_sheet (index = 2, title = 'Treći list')

>>> # dobivanje svih listova
>>> radna_knjiga.imena listova
['Prvi list', 'Drugi list', 'Treći list']

Sada imamo tri lista i dodavat ćemo podatke u ćelije tih listova.

>>> # Dobivanje prvog lista
>>> list_1 = radna knjiga ['Prvi list']
>>> # Dodavanje podataka u ćeliju 'A1' prvog lista
>>> sheet_1 ['A1'] = 'Ime'
>>> # Dobivanje drugog lista
>>> list_2 = radna knjiga ['Drugi list']
>>> # Dodavanje podataka u ćeliju 'A1' drugog lista
>>> list_2 ['A1'] = 'ID'
>>> # Dobivanje trećeg lista
>>> list_3 = radna knjiga ['Treći list']
>>> # Dodavanje podataka u ćeliju 'A1' trećeg lista
>>> list_3 ['A1'] = 'Ocjene'
>>> # Spremanje Excel radne knjige
>>> radna_knjiga.spremi ('primjer.xlsx ')

Čitanje Excel tabela

Modul openpyxl koristi atribut vrijednosti ćelije za pohranu podataka te ćelije. Podatke u ćeliji možemo čitati pozivanjem atributa vrijednosti ćelije. Sada imamo tri lista, a svaki list sadrži neke podatke. Podatke možemo čitati pomoću sljedećih funkcija u openpyxlu:

>>> # uvoz openpyxl-a
>>> uvozi openpyxl
>>> # učitavanje radne knjige
>>> radna_knjiga = openpyxl.load_workbook ('primjer.xlsx ')
>>> # Dobivanje prvog lista
>>> list_1 = radna knjiga ['Prvi list']
>>> # Dobivanje drugog lista
>>> list_2 = radna knjiga ['Drugi list']
>>> # Dobivanje trećeg lista
>>> list_3 = radna knjiga ['Treći list']
>>> # ispis podataka iz ćelije 'A1' prvog lista
>>> ispis (list_1 ['A1'].vrijednost)
Ime
>>> # ispis podataka iz ćelije 'A1' drugog lista
>>> ispis (list_2 ['A1'].vrijednost)
iskaznica
>>> # ispis podataka iz ćelije 'A1' Trećeg lista
>>> ispis (list_3 ['A1'].vrijednost)
Ocjene

Promjena fontova i boja

Zatim ćemo vam pokazati kako promijeniti font ćelije pomoću funkcije Font (). Prvo uvezite openpyxl.stilovi objekt. Metoda Font () uzima popis argumenata, uključujući:

ime (niz): naziv fonta
veličina (int ili float): veličina fonta
podcrtavanje (niz): podcrtani tip
boja (niz): heksadecimalna boja teksta
kurziv (bool): je li font u kurzivu
podebljano (bool): je li font podebljan

Da bismo primijenili stilove, prvo moramo stvoriti objekt prosljeđivanjem svih parametara na metodu Font (). Zatim odabiremo list, a unutar lista odabiremo ćeliju na koju želimo primijeniti stil. Zatim primjenjujemo stil na odabranu ćeliju.

>>> # uvoz openpyxl-a
>>> uvozi openpyxl
>>> # uvoz metode fonta iz openpyxl-a.stilova
>>> iz openpyxl.stilovi uvoz font
>>> # učitavanje radne knjige
>>> radna_knjiga = openpyxl.load_workbook ('primjer.xlsx ')
>>> # Stvaranje stilskog objekta
>>> style = Font (name = 'Consolas', veličina = 13, podebljano = True,
… Kurziv = Lažno)
>>> # Odabir lista iz radne knjige
>>> list_1 = radna knjiga ['Prvi list']
>>> # Odabir ćelije kojoj želimo dodati stilove
>>> a1 = list_1 ['A1']
>>> # Primjena stilova na ćeliju
>>> a1.font = stil
>>> # Spremanje radne knjige
>>> radna_knjiga.spremi ('primjer.xlsx ')

Primjena granica na ćelije

Možemo primijeniti obrube na ćelije u Excel listu pomoću metoda Border () i Side () openpyxla.stilova.modul granica. Različite funkcije kao parametre možemo proslijediti metodi Border (). Slijede neke od funkcija koje se prosljeđuju kao parametri metodi Border () za definiranje dimenzija obruba.

lijevo: primijeni obrub na lijevu stranu ćelije
pravo: primijeni obrub na desnu stranu ćelije
vrh: primijeni obrub na vrh ćelije
dno: primijeni obrub na dno ćelije

Te funkcije uzimaju atribute stila kao parametre. Atribut style definira stil obruba (npr.g., solidno, isprekidano). Parametri stila mogu imati bilo koju od sljedećih vrijednosti.

dvostruko: obod dvostruke crte
isprekidana: crtkana granica
tanka: tanka granica
srednji: srednja granica
mediumDashDot: crtkana i točkasta granica srednje težine
gusta: debela granica
dashDot: crtkana i točkasta granica
dlaka: vrlo tanka granica
točkasta: točkasta granica

Sada ćemo primijeniti različite vrste obruba na različite stanice naših proračunskih tablica. Prvo odabiremo stanice, a zatim definiramo stilove obruba i primjenjujemo te stilove na različite stanice.

>>> # uvoz openpyxl-a
>>> uvozi openpyxl
>>> # uvoz rubnih i bočnih klasa
>>> iz openpyxl.stilova.granice uvoz Border, Side
>>> # učitavanje radne knjige
>>> radna_knjiga = openpyxl.load_workbook ('primjer.xlsx ')
>>> # Odabir lista
>>> list_1 = radna knjiga ['Prvi list']
>>> # Odabir različitih ćelija s lista
>>> ćelija_1 = list_1 ['A1']
>>> ćelija_2 = list_1 ['B2']
>>> ćelija_3 = list_1 ['C3']
>>> # Definiranje različitih stilova obruba
>>> style_1 = Obrub (dno = Bočno (style = 'točkano'))
>>> style_2 = Obrub (desno = Bočno (style = 'tanko'))
>>> style_3 = Obrub (gornji = Bočni (style = 'dashDot'))
>>> # primjena obruba na ćelije
>>> ćelija_1.obrub = stil_1
>>> ćelija_2.obrub = stil_2
>>> ćelija_3.obrub = stil_3
>>> # Spremanje radne knjige
>>> radna_knjiga.spremi ('primjer.xlsx ')

Podešavanje dimenzija redaka i stupaca

Visina retka i širina stupca u Excel dokumentu također se mogu prilagoditi pomoću Pythona. Modul openpyxl ima dvije ugrađene metode koje se mogu koristiti za izvođenje ovih radnji. Prvo odaberemo list kojem želimo promijeniti širinu stupca ili visinu retka. Zatim primjenjujemo metodu na određeni redak ili stupac.

>>> # uvoz openpyxl-a
>>> uvozi openpyxl
>>> # učitavanje radne knjige
>>> radna_knjiga = openpyxl.load_workbook ('primjer.xlsx ')
>>> # list za odabir
>>> list_1 = radna knjiga ['Prvi list']
>>> # promjena visine prvog reda
>>> list_1.dimenzije_reda [1].visina = 50
>>> # Spremanje radne knjige
>>> radna_knjiga.spremi ('primjer.xlsx ')

Slično tome, možemo promijeniti širinu stupca pomoću sljedećeg koda

>>> # odabir lista iz excelove radne knjige
>>> list_2 = radna knjiga ['Drugi list']
>>> # promjena širine stupca A
>>> list_2.dimenzije_stupaca ['A'].širina = 50
>>> # Spremanje radne knjige
>>> radna_knjiga.spremi ('primjer.xlsx ')

Gornji kod promijenit će visinu prvog retka na 50 bodova, a širinu stupca A na 50 bodova.

Spajanje i spajanje stanica

Kada radimo s proračunskim tablicama Excel, često moramo spajati i razdvajati stanice. Za spajanje stanica u Pythonu može se koristiti jednostavna funkcija koja se temelji na openpyxlu. Modul openpyxl nudi metodu merge_cells () koja se može koristiti za spajanje ćelija u Excelu. Nova ćelija poprimit će ime gornje lijeve ćelije. Na primjer, ako želimo spojiti stanice iz ćelije A1 u ćeliju B2, tada će se novonastala ćelija nazivati A1. Za spajanje ćelija pomoću openpyxla prvo odabiremo list, a zatim na list primjenjujemo metodu merge_cells ().

>>> # uvoz modula openpyxl
>>> uvozi openpyxl
>>> # učitavanje radne knjige
>>> radna_knjiga = openpyxl.load_workbook ('primjer.xlsx ')
>>> # odabir prvog lista iz excelove radne knjige
>>> list_1 = radna knjiga ['Prvi list']
>>> # spajanje ćelija od A1 do B2 u listu 1
>>> list_1.merge_cells ('A1: B2')
>>> # spremanje radne knjige
>>> radna_knjiga.spremi ('primjer.xlsx ')

Slično tome, metoda unmerge_cells () može se koristiti za razdvajanje ćelija u Excel proračunskoj tablici. Sljedeći se kôd može koristiti za razdvajanje ćelija:

>>> # odabir lista iz radne knjige
>>> list_1 = radna knjiga ['Prvi list']
>>> # razdvajanja ćelija od A1 do B2
>>> list_1.unmerge_cells ('A1: B2')
>>> # spremanje radne knjige
>>> radna_knjiga.spremi ('primjer.xlsx ')

Zaključak

Excel proračunske tablice obično se koriste za manipulaciju podacima. Međutim, takvi zadaci mogu biti jednolični. Stoga se u takvim slučajevima programiranje može koristiti za automatizaciju manipulacije proračunskim tablicama.

U ovom smo članku razgovarali o nekim korisnim funkcijama Pythonovog openpyxl modula. Pokazali smo vam kako stvoriti, čitati, uklanjati i mijenjati Excel proračunske tablice, kako promijeniti stil, primijeniti font, obrube i dimenzije ćelija te kako spojiti i razdvojiti stanice. Primjenom ovih funkcija možete automatizirati mnoge zadatke manipulacije proračunskim tablicama pomoću Pythona.