Работа с электронными таблицами MS Excel. Тема 5

1. Тема 5: Работа с электронными таблицами MS Excel

Дисциплина:
Программирование
Тема 5: Работа с
электронными таблицами
MS Excel
Преподаватель: канд. техн. наук, доцент
Кромина Людмила Александровна

2.

Основные понятия
Документ электронной таблицы Excel называется РАБОЧЕЙ КНИГОЙ.
Каждая книга может хранить некоторое количество ЛИСТОВ.
Лист, просматриваемый пользователем в данный момент, называется АКТИВНЫМ.
Лист состоит из столбцов (адресуемых с помощью букв, начиная с A) и строк (адресуемых с
помощью цифр, начиная с 1)
Для работы с Excel файлами из Python применяют
следующие модули (библиотеки):
xlrd, xlwt, xlutils, openpyxl
Модуль openpyxl
Данный модуль не поставляется вместе с
Python, поэтому его предварительно нужно
установить:
cmd
Cd C:\Python 38>pip install openpyxl

3.

Открытие документов Excel при
помощи модуля openpyxl
Функция openpyxl.load_workbook() принимает имя файла в качестве аргумента и возвращает
значение типа workbook.
Объектом workbook является, в данном случае файл Excel
Если Вы обладаете правами
администратора, то можете
позволить себе разместить
файл в корневой папке , а
затем работать с ним:
Если Вы являетесь
обычным
пользователем, то Вам
необходимо применить
модуль
os
для
изменения
текущего
рабочего каталога с
помощью
метода
os.chdir ():
>>> import openpyxl # импорт модуля
>>> wb=penpyxl.load_workbook('my_file.xlsx') # открытие
рабочей книги
>>> type (wb) # проверка типа рабочей книги
<class 'openpyxl.workbook.workbook.Workbook‘>
>>> import openpyxl # импорт модуля
>>> import os # импорт модуля
>>> os.getcwd() # просмотр рабочего каталога
'C:\\Program Files\\Python38'
>>>
os.chdir("C:\\Users\\солнышко\\Desktop\\")
#
изменение
рабочего каталога
>>> os.getcwd() # просмотр рабочего каталога
'C:\\Users\\солнышко\\Desktop'
>>> wb = openpyxl.load_workbook('my_file.xlsx') # открытие
рабочей книги
>>> type (wb) # проверка типа рабочей книги
<class 'openpyxl.workbook.workbook.Workbook'>

4.

Открытие документов Excel при
помощи модуля openpyxl
Каждый лист рабочей книги является объектом worksheet.
Для получения списка листов, входящих в состав рабочей книги применяется метод:
wb.sheetnames ИЛИ wb.get_sheet_names()
>>> import os # импорт модуля
>>> os.chdir("C:\\Users\\солнышко\\Desktop\\") # изменение рабочего каталога
>>> import openpyxl # импорт модуля
>>> wb = openpyxl.load_workbook('my_file.xlsx') # открытие рабочей книги
>>> wb.sheetnames # получение списка листов рабочей книги
[' фрукты', сессия ']
>>> import os # импорт модуля
>>> os.chdir("C:\\Users\\солнышко\\Desktop\\") # изменение рабочего каталога
>>> import openpyxl # импорт модуля
>>> wb = openpyxl.load_workbook('my_file.xlsx') # открытие рабочей книги
>>> sheets = wb.sheetnames
>>> for sheet in sheets:
print(sheet)
фрукты
сессия

5.

Открытие документов Excel при
помощи модуля openpyxl
Метод wb.active позволяет получить активный лист книги;
print(sheet['имя ячейки'].value) выводит значение ячейки, имя которой указано в параметре
>>> import os
>>> os.chdir("C:\\Users\\солнышко\\Desktop\\")
>>> import openpyxl
>>> wb = openpyxl.load_workbook('my_file.xlsx')
>>> wb.sheetnames # печать списка листов
['фрукты', 'сессия']
>>> sheet = wb.active # получение активного листа
>>> sheet
<Worksheet "фрукты">
>>> print(sheet['A1'].value) # печать значения ячейки A1
Яблоки
>>> print(sheet['B1'].value) # печать значения ячейки B1
75

6.

Работа с ячейками рабочей книги Excel
при помощи модуля openpyxl
Объект cell имеет атрибут value, который содержит значение, хранящееся в ячейке.
Объект cell также имеет атрибуты row, column и coordinate, которые
предоставляют информацию о расположении данной ячейки в таблице.
>>> cell = sheet['B2'] # получение ячейки
активного листа B2
>>> print('Строка: ' + str(cell.row))
Строка: 2
>>> print('Столбец: ' + str(cell.column))
Столбец: 2
>>> print('Ячейка: ' + cell.coordinate)
Ячейка: B2
>>> print('Значение: ' + cell.value)
Значение: с 1 января по 30 июня

7.

Работа с ячейками рабочей книги Excel
при помощи модуля openpyxl
К отдельной ячейке можно также обращаться с помощью
метода cell() объекта worksheet, передавая ему именованные аргументы row и column.
Первому столбцу или первой строке соответствует число 1, а не 0:
>>> cell = sheet.cell(row = 1,
column = 1)
>>> print(cell.value)
первый семестр

8.

Работа с ячейками рабочей книги Excel
при помощи модуля openpyxl
Размер листа можно получить с помощью
атрибутов max_row и max_column объекта worksheet:
>>> rows = sheet.max_row
>>> cols = sheet.max_column
>>> for i in range(1, rows + 1):
string = ''
for j in range(1, cols + 1):
cell = sheet.cell(row = i, column = j)
string = string + str(cell.value) + ' '
print(string)
первый семестр с 1 сентября по 31 декабря
второй семестр с 1 января по 30 июня

9.

Работа с ячейками рабочей книги Excel
при помощи модуля openpyxl
Чтобы преобразовать буквенное обозначение столбца в цифровое, следует вызвать
функцию:
openpyxl.utils.column_index_from_string()
Чтобы преобразовать цифровое обозначение столбца в буквенное, следует вызвать
функцию:
openpyxl.utils.get_column_letter()
ДЛЯ ВЫЗОВА ЭТИХ ФУНКЦИЙ ЗАГРУЖАТЬ РАБОЧУЮ КНИГУ НЕ
ОБЯЗАТЕЛЬНО
>>> from openpyxl.utils import get_column_letter, column_index_from_string
>>> get_column_letter(1)
'A'
>>> get_column_letter(2)
'B'
>>> column_index_from_string('A')
1
>>> column_index_from_string('C')
3

10.

Работа с ячейками рабочей книги Excel
при помощи модуля openpyxl
Используя срезы объектов worksheet, можно получить все объекты cell, принадлежащие
определенному диапазону (строке, столбцу или прямоугольной области)
>>> sheet['A1':'B2']
((<Cell 'Третий лист'.A1>, <Cell 'Третий лист'.B1>), (<Cell 'Третий лист'.A2>, <Cell 'Третий
лист'.B2>))
>>> for row in sheet['A1':'B2']:
string = ''
for cell in row:
string = string + str(cell.value) + ' '
print(string)
первый семестр с 1 сентября по 31 декабря
второй семестр с 1 января по 30 июня

11.

Работа с ячейками рабочей книги Excel
при помощи модуля openpyxl
Используя срезы объектов worksheet, можно получить все объекты cell, принадлежащие
определенному диапазону (строке, столбцу или прямоугольной области)
>>> sheet['A1':'B2']
((<Cell 'Третий лист'.A1>, <Cell 'Третий лист'.B1>),
(<Cell 'Третий лист'.A2>, <Cell 'Третий лист'.B2>))
>>> for row in sheet['A1':'B2']:
string = ''
for cell in row:
string = string + str(cell.value) + ' '
print(string)
первый семестр с 1 сентября по 31 декабря
второй семестр с 1 января по 30 июня
ВЫВЕДЕМ ЗНАЧЕНИЯ
ПЕРВОЙ КОЛОНКИ:
ВЫВЕДЕМ ЗНАЧЕНИЯ
ДИАПАЗОНА A1:B2:
>>> sheet['A']
(<Cell 'Третий лист'.A1>, <Cell
'Третий лист'.A2>)
>>> for cell in sheet['B']:
print(cell.value)
с 1 сентября по 31 декабря
с 1 января по 30 июня

12.

Работа с ячейками рабочей книги Excel
при помощи модуля openpyxl
Используя срезы объектов worksheet, можно получить все объекты cell, принадлежащие
определенному диапазону (строке, столбцу или прямоугольной области)
>>> sheet[1:2]
((<Cell 'Третий лист'.A1>, <Cell 'Третий лист'.B1>),
(<Cell 'Третий лист'.A2>, <Cell 'Третий лист'.B2>))
>>> for row in sheet[1:2]:
string = ''
for cell in row:
string = string + str(cell.value) + ' '
print(string)
первый семестр с 1 сентября по 31 декабря
второй семестр с 1 января по 30 июня
ВЫВЕДЕМ СТРОКИ С
ПЕРВОЙ ПО ВТОРУЮ:

13.

Работа с ячейками рабочей книги Excel
при помощи модуля openpyxl
Для доступа к ячейкам конкретной строки или столбца также можно воспользоваться
атрибутами rows и columns объекта worksheet.
>>> list(sheet.rows)
[(<Cell 'Третий лист'.A1>, <Cell 'Третий лист'.B1>), (<Cell 'Третий лист'.A2>, <Cell 'Третий
лист'.B2>), (<Cell 'Третий лист'.A3>, <Cell 'Третий лист'.B3>)]
>>> for row in sheet.rows:
print(row)
(<Cell 'Третий лист'.A1>, <Cell 'Третий лист'.B1>)
(<Cell 'Третий лист'.A2>, <Cell 'Третий лист'.B2>)
(<Cell 'Третий лист'.A3>, <Cell 'Третий лист'.B3>)
>>> list(sheet.columns)
[(<Cell 'Третий лист'.A1>, <Cell 'Третий лист'.A2>, <Cell 'Третий лист'.A3>), (<Cell 'Третий
лист'.B1>, <Cell 'Третий лист'.B2>, <Cell 'Третий лист'.B3>)]
>>> for column in sheet.columns:
print(column)
(<Cell 'Третий лист'.A1>, <Cell 'Третий лист'.A2>, <Cell 'Третий лист'.A3>)
(<Cell 'Третий лист'.B1>, <Cell 'Третий лист'.B2>, <Cell 'Третий лист'.B3>)

14.

Работа с ячейками рабочей книги Excel
при помощи модуля openpyxl
>>> for row in sheet.rows:
string = ''
for cell in row:
string = string + str(cell.value) + ' '
print(string)
первый семестр с 1 сентября по 31 декабря
второй семестр с 1 января по 30 июня
ВЫВЕДЕМ ЗНАЧЕНИЯ
ВСЕХ ЯЧЕЕК ЛИСТА:

15.

Работа с ячейками рабочей книги Excel
при помощи модуля openpyxl
>>> for cell in list(sheet.rows)[1]:
print(str(cell.value))
второй семестр
с 1 января по 30 июня
>>> for row in sheet.rows:
print(str(row[1].value))
с 1 сентября по 31 декабря
с 1 января по 30 июня
ВЫВЕДЕМ ЗНАЧЕНИЯ
ВТОРОЙ СТРОКИ
(ИНДЕКС 1):
ВЫВЕДЕМ ЗНАЧЕНИЯ
ВТОРОЙ КОЛОНКИ
(ИНДЕКС 1):

16.

Запись файлов в Excel при помощи
модуля openpyxl
Метод create_sheet() возвращает новый объект worksheet, который по умолчанию становится
последним листом книги. С помощью именованных аргументов title и index можно задать имя и
индекс нового листа.
Метод remove() принимает в качестве аргумента не строку с именем листа, а объект worksheet.
Если известно только имя листа, который надо удалить, нужно применять метод wb[sheetname].
Еще один способ удалить лист – использовать инструкцию del wb[sheetname].
Метод save() применяется для сохранения изменения после добавления или удаления листа
рабочей книги.
>>> import os
>>> os.chdir("C:\\Users\\солнышко\\Desktop\\")
>>> import openpyxl
>>> wb = openpyxl.Workbook()
>>> wb.sheetnames
['Sheet']
>>> wb.create_sheet(title = 'Первый лист', index = 0)
<Worksheet "Первый лист">
>>> wb.sheetnames
['Первый лист', 'Sheet']
>>> wb.remove(wb['Первый лист'])
>>> wb.sheetnames
['Sheet']
>>> wb.save('12345.xlsx')

17.

Запись значений в ячейки в Excel при
помощи модуля openpyxl
>>> wb = openpyxl.Workbook()
>>> wb.create_sheet(title = 'Вновь переименованный лист', index = 0)
<Worksheet "Вновь переименованный лист">
>>> sheet = wb['Вновь переименованный лист']
>>> sheet['A1'] = 'первое значение'
>>> sheet['A1'].value
'первое значение'

18.

Запись значений в ячейки в Excel при
помощи модуля openpyxl
ЗАПОЛНИМ ТАБЛИЦУ 3X3:
>>> import openpyxl
>>> wb = openpyxl.Workbook() # создание нового excelфайла
>>> wb.create_sheet(title = 'Пример', index = 0) # добавление
нового листа
<Worksheet "Пример">
>>> sheet = wb['Пример'] # получение лист, с которым
будем работать
>>> for row in range(1, 4):
for col in range(1, 4):
value = str(row) + str(col)
cell = sheet.cell(row = row, column = col)
cell.value = value
>>> wb.save('rules.xlsx')

19.

Стилевое оформление
в Excel при помощи модуля openpyxl
Для настройки шрифтов, используемых в ячейках, необходимо импортировать
функцию Font() из модуля openpyxl.styles:
from openpyxl.styles import Font
>>> import openpyxl
>>> from openpyxl.styles import Font
>>> wb = openpyxl.Workbook() # создание нового
excel-файла
>>> wb.create_sheet(title = 'Первый лист', index = 0)
# добавление нового листа
<Worksheet "Первый лист«>
>>> sheet = wb['Первый лист'] # получение листа, с
которым будем работать
>>> font = Font(name='Arial', size=16, italic=True,
color='AA0000')
>>> sheet['A1'].font = font
>>> sheet['A1'] = 'Стилевое оформление'
>>> wb.save('555.xlsx') # запись файла
Создание новой рабочей книги, в
которой для шрифта, используемого в
ячейке
A1,
устанавливается
шрифт Arial, цвет вишни, курсивное
начертание и размер 16 пункта

20.

Настройка строк и столбцов
в Excel при помощи модуля openpyxl
С помощью модуля OpenPyXL можно задавать высоту строк и ширину
столбцов таблицы
>>> import openpyxl
>>> from openpyxl.styles import Font
>>> import openpyxl
>>> from openpyxl.styles import Font
>>> wb = openpyxl.Workbook() # создание нового excelфайла
>>> wb.create_sheet(title = 'Первый лист', index = 0)
<Worksheet "Первый лист">
>>> sheet = wb['Первый лист'] # получение листа, с
которым будем работать
>>> font = Font(name='Arial', size=16, italic=True,
color='AA0000')
>>> sheet['A1'].font = font
>>> sheet['A1'] = 'Стилевое оформление'
>>> sheet['A1'] = 'Высокая строка'
>>> sheet['B2'] = 'Широкий столбец'
>>> sheet.row_dimensions[1].height = 70
>>> sheet.column_dimensions['B'].width = 30
>>> wb.save('555.xlsx') # запись файла
Объекты Worksheet имеют атрибуты:
row_dimensions;
column_dimensions,
которые управляют высотой строк и
шириной столбцов.

21.

Настройка строк и столбцов
в Excel при помощи модуля openpyxl
С помощью модуля OpenPyXL можно объединять ячейки, указав диапазон
объединения
>>> import openpyxl
>>> from openpyxl.styles import Font
>>> wb = openpyxl.Workbook() # создание нового excel-файла
>>> wb.create_sheet(title = 'Первый лист', index = 0)
<Worksheet "Первый лист">
>>> sheet = wb['Первый лист'] # получение листа, с которым будем работать
>>> sheet.merge_cells('A1:B2')
>>> sheet['A1'] = 'Объединены четыре ячейки'
>>> sheet.merge_cells('C1:C4')
>>> sheet['C5'] = 'Объединены четыре ячейки'
>>> wb.save('333.xlsx') # запись файла
Ячейки,
занимающие
прямоугольную
область, могут быть объединены в одну
ячейку
с
помощью
метода
merge_cells() рабочего листа:

22.

Построение диаграмм
в Excel при помощи модуля openpyxl
Модуль
OpenPyXL
поддержива
ет создание
гистограмм,
графиков, а
также
точечных и
круговых
диаграмм с
использован
ие данных,
хранящихся
в
электронной
таблице
>>> from openpyxl import Workbook
>>> from openpyxl.chart import BarChart, Reference
>>> wb = Workbook() # создание нового excel-файла
>>> wb.create_sheet(title = 'фрукты', index = 0) # добавление нового листа
<Worksheet "фрукты">
>>> sheet = wb['фрукты'] # получение листа, с которым будем работать
>>> sheet['A1'] = 'наименование фрукта' # это колонка с данными
>>> for i in range(1, 11):
cell = sheet.cell(row = i + 1, column = 1)
cell.value = i * I
>>> chart = BarChart() # создание диаграммы
>>> chart.title = 'Первая серия данных'
>>> data = Reference(sheet, min_col = 1, min_row = 1, max_col = 1, max_row = 11)
>>> chart.add_data(data, titles_from_data = True)
>>> sheet.add_chart(chart, 'C2') # добавление диаграммы на лист
>>> wb.save('фрукты.xlsx') # запись файла
Аналогично можно создавать графики, точечные и круговые диаграммы, вызывая методы:
openpyxl.chart.LineChart()
openpyxl.chart.ScatterChart()
openpyxl.chart.PieChart()

23.

Построение диаграмм
в Excel при помощи модуля openpyxl