Манипулиране на електронни таблици на Excel с помощта на Python

Microsoft Excel е софтуер за електронни таблици, който се използва за съхраняване и управление на таблични данни. Освен това, с Excel, изчисленията могат да се извършват чрез прилагане на формули към данните и могат да бъдат създадени визуализации на данни.Много задачи, изпълнявани в електронни таблици, като математически операции, могат да бъдат автоматизирани чрез програмиране, а много програмни езици имат модули за манипулиране на електронни таблици на Excel. В този урок ще ви покажем как да използвате модула openpyxl на Python за четене и модифициране на електронни таблици на Excel.

Инсталиране на openpyxl

Преди да можете да инсталирате openpyxl, трябва да инсталирате pip. Pip се използва за инсталиране на пакети на Python. Изпълнете следната команда в командния ред, за да видите дали pip е инсталиран.

C: \ Users \ windows> pip help

Ако се върне помощното съдържание на pip, тогава pip е инсталиран; в противен случай отидете на следната връзка и изтеглете get-pip.py файл:

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

Сега изпълнете следната команда, за да инсталирате pip:

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

След инсталиране на pip може да се използва следната команда за инсталиране на openpyxl.

C: \ Users \ windows> pip install openpyxl

Създаване на документ на Excel

В този раздел ще използваме модула openpyxl, за да създадем документ на Excel. Първо отворете командния ред, като напишете 'cmd' в лентата за търсене; след това въведете

C: \ Потребители \ windows> python

За да създадем работна книга на Excel, ще импортираме модула openpyxl и след това ще използваме метода 'Workbook ()', за да създадем работна книга.

>>> # импортиране на модул openpyxl
>>> импортиране на openpyxl
>>> # Инициализиране на работна книга
>>> работна книга = openpyxl.Работна книга ()
>>> # запазване на работна книга като 'пример.xlsx '
>>> работна книга.save ('пример.xlsx ')

Горните команди създават документ на Excel, наречен пример.xlsx. След това ще манипулираме този документ на Excel.

Манипулиране на листове в документ на Excel

Създадохме документ на Excel, наречен пример.xlsx. Сега ще манипулираме листовете на този документ с помощта на Python. Модулът openpyxl има метод 'create_sheet ()', който може да се използва за създаване на нов лист. Този метод взема два аргумента: индекс и заглавие. Индексът определя разположението на листа, като се използва всяко неотрицателно цяло число (включително 0), а заглавието е заглавието на листа. Списък на всички листове в обекта на work_book може да се покаже чрез извикване на списъка с имена на листове.

>>> # импортиране на openpyxl
>>> импортиране на openpyxl
>>> # зареждане на съществуващ документ на Excel в Обект на работна книга
>>> работна книга = openpyxl.load_workbook ('пример.xlsx ')
>>> # Създаване на нов лист с 0-ти индекс
>>> работна книга.create_sheet (index = 0, title = 'First Sheet')

>>> # Получаване на всички листове
>>> работна книга.имена на листове
['Първи лист', 'Лист']
>>> # Запазване на документ на Excel
>>> работна книга.save ('пример.xlsx ')

В горния код създадохме лист с име First Sheet и го поставихме на 0-ти индекс. Листът, разположен преди това на 0-ия индекс, беше преместен на 1-ви индекс, както е показано в изхода. Сега ще променим името на оригиналния лист от Лист на Втори лист.

Атрибутът заглавие съдържа името на листа. За да преименуваме лист, първо трябва да отидем до него, както следва.

>>> # Получаване на активен лист от документ на Excel
>>> лист = работна книга.активен
>>> # Име на листа за печат
>>> печат (лист.заглавие)
Първи лист >>> # Придвижване до втори лист (при индекс 1)
>>> работна книга.активен = 1
>>> # Получаване на активен лист
>>> лист = работна книга.активен
>>> # име на лист за печат
>>> печат (лист.заглавие)
Лист >>> # Промяна на заглавието на листа
>>> лист.title = 'Втори лист'
>>> # Заглавие на лист за печат
>>> печат (лист.заглавие)
Втори лист

По същия начин можем да премахнем лист от документа на Excel. Модулът openpyxl предлага метода remove () за премахване на лист. Този метод приема името на листа, който трябва да бъде премахнат като аргумент, и след това премахва този лист. Можем да премахнем втори лист, както следва:

>>> # премахване на лист по име
>>> работна книга.премахване (work_book ['Second Sheet'])
>>> # получаване на всички листове
>>> работна книга.имена на листове
['Първи лист']
>>> # запазване на документ на Excel
>>> работна книга.save ('пример.xlsx ')

Добавяне на данни към клетки

Досега сме ви показвали как да създавате или изтривате листове в документ на Excel. Сега ще добавим данни към клетките на различни листове. В този пример имаме един лист с име Първи лист в нашия документ и искаме да създадем още два листа.

>>> # импортиране на openpyxl
>>> импортиране на openpyxl
>>> # зареждане на работна книга
>>> работна книга = openpyxl.load_workbook ('пример.xlsx ')
>>> # Създаване на нов лист на 1-ви индекс
>>> работна книга.create_sheet (index = 1, title = 'Second Sheet')

>>> # създаване на нов лист на 2-ри индекс
>>> работна книга.create_sheet (index = 2, title = 'Трети лист')

>>> # получаване на всички листове
>>> работна книга.имена на листове
['Първи лист', 'Втори лист', 'Трети лист']

Сега имаме три листа и ще добавим данни към клетките на тези листове.

>>> # Първи лист
>>> sheet_1 = work_book ['First Sheet']
>>> # Добавяне на данни към клетка 'A1' на първия лист
>>> sheet_1 ['A1'] = 'Име'
>>> # Получаване на втори лист
>>> лист_2 = работна книга ['Втори лист']
>>> # Добавяне на данни към клетка „A1“ на втори лист
>>> sheet_2 ['A1'] = 'ID'
>>> # Получаване на трети лист
>>> sheet_3 = work_book ['Трети лист']
>>> # Добавяне на данни към клетка „A1“ на трети лист
>>> sheet_3 ['A1'] = 'Оценки'
>>> # Запазване на работната книга на Excel
>>> работна книга.save ('пример.xlsx ')

Четене на Excel Sheets

Модулът openpyxl използва атрибута value на клетка, за да съхранява данните на тази клетка. Можем да четем данните в клетка, като извикаме атрибута стойност на клетката. Сега имаме три листа и всеки лист съдържа някои данни. Можем да четем данните, като използваме следните функции в openpyxl:

>>> # импортиране на openpyxl
>>> импортиране на openpyxl
>>> # зареждане на работна книга
>>> работна книга = openpyxl.load_workbook ('пример.xlsx ')
>>> # Първи лист
>>> sheet_1 = work_book ['First Sheet']
>>> # Получаване на втори лист
>>> лист_2 = работна книга ['Втори лист']
>>> # Получаване на трети лист
>>> sheet_3 = work_book ['Трети лист']
>>> # отпечатване на данни от клетка „A1“ на Първи лист
>>> печат (лист_1 ['A1'].стойност)
Име
>>> # отпечатване на данни от клетка „A1“ на втори лист
>>> печат (лист_2 ['A1'].стойност)
документ за самоличност
>>> # отпечатване на данни от клетка „A1“ на Третия лист
>>> печат (лист_3 ['A1'].стойност)
Класове

Смяна на шрифтове и цветове

След това ще ви покажем как да промените шрифта на клетка с помощта на функцията Font (). Първо, импортирайте openpyxl.обект на стилове. Методът Font () взема списък с аргументи, включително:

име (низ): името на шрифта
размер (int или float): размера на шрифта
подчертаване (низ): тип подчертаване
цвят (низ): шестнадесетичният цвят на текста
курсив (бук): дали шрифтът е курсив
удебелен (бук): дали шрифтът е получер

За да приложим стилове, първо трябва да създадем обект, като предадем всички параметри на метода Font (). След това избираме листа и вътре в листа избираме клетката, към която искаме да приложим стила. След това прилагаме стил към избраната клетка.

>>> # импортиране на openpyxl
>>> импортиране на openpyxl
>>> # импортиране на метод на шрифт от openpyxl.стилове
>>> от openpyxl.стилове импортиране на шрифт
>>> # зареждане на работна книга
>>> работна книга = openpyxl.load_workbook ('пример.xlsx ')
>>> # Създаване на стил обект
>>> стил = Шрифт (name = 'Consolas', размер = 13, получер = True,
... курсив = Невярно)
>>> # Избиране на лист от работна книга
>>> sheet_1 = work_book ['First Sheet']
>>> # Избиране на клетката, която искаме да добавим стилове
>>> a1 = лист_1 ['A1']
>>> # Прилагане на стилове към клетката
>>> a1.шрифт = стил
>>> # Запазване на работна книга
>>> работна книга.save ('пример.xlsx ')

Прилагане на граници към клетки

Можем да приложим граници към клетките в лист на Excel, като използваме методите Border () и Side () на openpyxl.стилове.модул граници. Можем да предадем различни функции като параметри на метода Border (). Следват някои от функциите, които се предават като параметри на метода Border () за определяне на размерите на границата.

наляво: приложете граница към лявата страна на клетка
нали: нанесете граница от дясната страна на клетка
Горна част: нанесете граница в горната част на клетка
отдолу: нанесете граница към дъното на клетка

Тези функции вземат атрибути на стил като параметри. Атрибутът style определя стила на границата (напр.ж., плътно, пунктирано). Параметрите на стила могат да имат една от следните стойности.

двойно: граница с двойна линия
тире: пунктирана граница
тънък: тънка граница
средно: средна граница
mediumDashDot: пунктирана и пунктирана граница със средно тегло
дебел: дебела граница
dashDot: пунктирана и пунктирана граница
коса: много тънка граница
пунктирана: пунктирана граница

Сега ще приложим различни видове граници към различни клетки на нашите електронни таблици. Първо избираме клетки и след това дефинираме стилове на граници и прилагаме тези стилове към различни клетки.

>>> # импортиране на openpyxl
>>> импортиране на openpyxl
>>> # импортиране на Border и Side класове
>>> от openpyxl.стилове.граници внос Border, Side
>>> # зареждане на работна книга
>>> работна книга = openpyxl.load_workbook ('пример.xlsx ')
>>> # Избор на лист
>>> sheet_1 = work_book ['First Sheet']
>>> # Избиране на различни клетки от листа
>>> клетка_1 = лист_1 ['A1']
>>> клетка_2 = лист_1 ['B2']
>>> клетка_3 = лист_1 ['C3']
>>> # Определяне на различни стилове на граници
>>> style_1 = Border (отдолу = Side (style = 'dotted'))
>>> style_2 = Граница (вдясно = Side (style = 'thin'))
>>> style_3 = Border (top = Side (style = 'dashDot'))
>>> # прилагане на гранични стилове към клетките
>>> клетка_1.граница = стил_1
>>> клетка_2.граница = стил_2
>>> клетка_3.граница = стил_3
>>> # Запазване на работна книга
>>> работна книга.save ('пример.xlsx ')

Регулиране на размерите на реда и колоната

Височината на реда и ширината на колоната в документ на Excel също могат да бъдат коригирани с помощта на Python. Модулът openpyxl има два вградени метода, които могат да се използват за извършване на тези действия. Първо избираме листа, на който искаме да променим ширината на колоната или височината на реда. След това прилагаме метод към конкретния ред или колона.

>>> # импортиране на openpyxl
>>> импортиране на openpyxl
>>> # зареждане на работна книга
>>> работна книга = openpyxl.load_workbook ('пример.xlsx ')
>>> # лист за избор
>>> sheet_1 = work_book ['First Sheet']
>>> # промяна на височината на първия ред
>>> лист_1.редове_размери [1].височина = 50
>>> # Запазване на работна книга
>>> работна книга.save ('пример.xlsx ')

По същия начин можем да променим ширината на колона, като използваме следния код

>>> # избор на лист от работната книга на Excel
>>> лист_2 = работна книга ['Втори лист']
>>> # промяна на ширината на колона A
>>> лист_2.колона_размери ['A'].ширина = 50
>>> # Запазване на работна книга
>>> работна книга.save ('пример.xlsx ')

Горният код ще промени височината на първия ред на 50 точки и ширината на колона А на 50 точки.

Сливане и обединяване на клетки

Когато работим с електронни таблици на Excel, често се налага да обединяваме и обединяваме клетки. За обединяване на клетки в Python може да се използва проста функция, базирана на openpyxl. Модулът openpyxl предлага метода merge_cells (), който може да се използва за обединяване на клетки в Excel. Новата клетка ще приеме името на горната лява клетка. Например, ако искаме да обединим клетките от клетка А1 в клетка В2, то новосформираната клетка ще бъде посочена като А1. За да обединим клетки с помощта на openpyxl, първо избираме листа и след това прилагаме метода merge_cells () към листа.

>>> # импортиране на модул openpyxl
>>> импортиране на openpyxl
>>> # зареждане на работна книга
>>> работна книга = openpyxl.load_workbook ('пример.xlsx ')
>>> # избор на първи лист от работната книга на Excel
>>> sheet_1 = work_book ['First Sheet']
>>> # обединяване на клетки от А1 до В2 в Лист 1
>>> лист_1.merge_cells ('A1: B2')
>>> # запазване на работна книга
>>> работна книга.save ('пример.xlsx ')

По същия начин методът unmerge_cells () може да се използва за обединяване на клетки в електронна таблица на Excel. Следният код може да се използва за обединяване на клетки:

>>> # избор на лист от работната книга
>>> sheet_1 = work_book ['First Sheet']
>>> # обединяване на клетки от А1 до В2
>>> лист_1.unmerge_cells ('A1: B2')
>>> # запазване на работна книга
>>> работна книга.save ('пример.xlsx ')

Заключение

Електронните таблици на Excel обикновено се използват за манипулиране на данни. Такива задачи обаче могат да бъдат еднообразни. Следователно в такива случаи програмирането може да се използва за автоматизиране на манипулирането с електронни таблици.

В тази статия обсъдихме някои полезни функции на модула openpyxl на Python. Показахме ви как да създавате, четете, премахвате и модифицирате електронни таблици на Excel, как да променяте стила, да прилагате шрифт, граници и размери на клетките и как да обединявате и обединявате клетки. Прилагайки тези функции, можете да автоматизирате много задачи за манипулиране на електронни таблици, използвайки Python.