Объект Worksheet представляет собой рабочий лист, который в электронных таблицах Excel служит контейнером 2-го уровня.
Не создавайте рабочие листы самостоятельно, для этого используйте Workbook.create_sheet(). При создании новой рабочей книги средствами модуля openpyxl, по умолчанию уже создается один рабочий лист, доступ к которому можно получить через свойство Workbook.active.
>>> from openpyxl import Workbook # создаем объект книги >>> wb = Workbook() # список имен листов в только # что созданной книге >>> wb.sheetnames # ['Sheet'] # доступ к созданному по # умолчанию листу 'Sheet' >>> ws = wb.active # переименовываем >>> ws.title = 'MySheet' # или создаем новый >>> ws1 = wb.create_sheet('NewSheet') # список имен листов в книге >>> wb.sheetnames # ['MySheet', 'NewSheet']
Worksheet.active_cell имя активной ячейки,Worksheet.add_chart() добавляет объект диаграммы ,Worksheet.add_data_validation() добавляет объект проверки данных,Worksheet.add_image() добавляет изображение,Worksheet.add_table() добавляет объект таблицы,Worksheet.append() добавляет группу значений в последнюю строку,Worksheet.calculate_dimension() возвращают минимальный диапазон ячеек с данными,Worksheet.cell() возвращает объект ячейки,Worksheet.columns генератор всех объектов ячеек с данными по столбцам,Worksheet.column_dimensions информация о свойствах отображения столбца,Worksheet.delete_cols() удаляет столбец или столбцы,Worksheet.delete_rows() удаляет строку или строки,Worksheet.freeze_panes фиксирует строки и колонки,Worksheet.insert_cols() вставляет столбец или столбцы,Worksheet.insert_rows() вставляет строку или строки,Worksheet.iter_cols() читает объекты ячеек,Worksheet.iter_rows() читает объекты строк,Worksheet.max_column максимальный индекс столбца с данными,Worksheet.max_row максимальный индекс строки с данными,Worksheet.merge_cells устанавливает слияние в диапазоне ячеек,Worksheet.min_column минимальный индекс столбца,Worksheet.min_row минимальный индекс строки,Worksheet.move_range() перемещает диапазон ячеек,Worksheet.print_area устанавливает область печати,Worksheet.print_title_cols устанавливает колонки, которые печатаются на каждой странице,Worksheet.print_title_rows устанавливает строки, которые печатаются на каждой странице,Worksheet.row_dimensions информация о свойствах отображения строки,Worksheet.rows генератор всех объектов ячеек с данными по строкам,Worksheet.set_printer_settings() задает параметры принтера,Worksheet.tables все объекты таблиц текущего листа,Worksheet.unmerge_cells() удаляет слияние ячеек,Worksheet.values генератор всех значений ячеек по строкам.Worksheet.active_cell:Свойство Worksheet.active_cell возвращает имя активной ячейки листа Worksheet.
>>> ws.active_cell # 'A1'
Worksheet.add_chart(chart, anchor=None):Метод Worksheet.add_chart() добавляет объект диаграммы chart на текущий рабочий лист. При необходимости можно указать ячейку anchor для привязки к ней диаграммы. Диаграмма привязывается к левому верхнему углу ячейки.
Worksheet.add_data_validation(data_validation):Метод Worksheet.add_data_validation() добавляет на рабочий лист электронной таблицы объект проверки данных data_validation.
Объект проверки данных определяет тип применяемой проверки данных и ячейку или диапазон ячеек, к которым она должна применяться.
Worksheet.add_image(img, anchor=None):Метод Worksheet.add_image() добавляет изображение img на лист электронной таблицы.
При желании можно указать ячейку anchor для привязки изображения. Изображение привязывается к левому верхнему углу ячейки.
Worksheet.add_table(table):Метод Worksheet.add_table() добавляет объект таблицы table на лист электронной таблицы.
Объект таблицы рабочего листа представляет собой ссылку на группу ячеек, что облегчает работу с данными, если на одном листе представлены несколько таблиц.
Перед добавлением объекта таблицы рабочего листа Table, необходимо сначала проверить наличие имени таблицы displayName в определенных именах и других таблицах рабочего листа во избежании получения исключения.
# проверим имя таблицы if not ws.tables.get('MyTable'): # создаем объект таблицы table = Table(displayName="MyTable", ref="A1:E5") # добавляем таблицу ws.add_table(table)
Worksheet.append(iterable):Метод Worksheet.append() добавляет группу значений в последнюю строку, которая не содержит данных.
Варианты использования:
.append([‘ячейка A1’, ‘ячейка B1’, ‘ячейка C1’]).append({‘A’ : ‘ячейка A1’, ‘C’ : ‘ячейка C1’}), в качестве ключей используются буквы столбцов..append({1 : ‘ячейка A1’, 3 : ‘ячейка C1’}), в качестве ключей используются цифры столбцов.Пример добавление данных из списка:
# существующие листы рабочей книги >>> wb.sheetnames # ['MySheet', 'NewSheet'] # добавим данные в лист с именем `Mysheet2` >>> ws = wb["MySheet"] # создадим произвольные данные, используя # вложенный генератор списков >>> data = [[row*col for col in range(1, 10)] for row in range(1, 31)] >>> data # [ # [1, 2, 3, 4, 5, 6, 7, 8, 9], # [2, 4, 6, 8, 10, 12, 14, 16, 18], # ... # ... # [30, 60, 90, 120, 150, 180, 210, 240, 270] # ] # добавляем данные в выбранный лист >>> for row in data: ... ws.append(row) ...
Вот и все, данные добавлены...
Worksheet.calculate_dimension()Worksheet.dimensions:Метод Worksheet.calculate_dimension() и свойство Worksheet.dimensions возвращают минимальный диапазон для всех ячеек текущего рабочего листа, которые содержат данные (например, ‘A1:M24’).
>>> ws.calculate_dimension() # 'A1:I35' >>> ws.dimensions # 'A1:I35'
Worksheet.cell(row, column, value=None):Метод Worksheet.cell() возвращает объект ячейки Cell на основе заданных координат.
Аргументы:
row - индекс строки ячейки, целое число.column - индекс столбца ячейки, целое число.value - значение ячейки, может быть число или дата/время или строка или bool или None.Пример:
# доступ к объекту ячейки >>> cell = ws.cell(row=4, column=2) >>> cell # <Cell 'Sheet'.B4> # доступ с присваиваем значения ячейки >>> cell = ws.cell(row=4, column=2, value=10)
Важно! Ячейки создаются при первом доступе к ним. Из-за такого поведения, простой перебор ячеек в цикле, создаст объекты этих ячеек в памяти, даже если не присваивать им значения.
Этот код создаст в памяти 100x100=10000 пустых объектов ячеек, просто так израсходовав оперативную память.
>>> for x in range(1,101): ... for y in range(1,101): ... # доступ к объекту ячейки ... ws.cell(row=x, column=y)
Worksheet.columns:Свойство Worksheet.columns создает генератор всех объектов ячеек текущего рабочего листа, в которых есть данные, по столбцам. Дополнительно смотрите метод Worksheet.iter_cols().
>>> ws.columns # [(<Cell 'Sheet'.A1>, <Cell 'Sheet'.A2>, <Cell 'Sheet'.A3>, ...), # (<Cell 'Sheet'.B1>, <Cell 'Sheet'.B2>, <Cell 'Sheet'.B3>, ...), # ... >>> for col in ws.columns: ... for cell in col: ... print(cell) # <Cell 'Sheet'.A1> # <Cell 'Sheet'.A2> # <Cell 'Sheet'.A3> # ... # <Cell 'Sheet'.B1> # <Cell 'Sheet'.B2> # <Cell 'Sheet'.B3> ...
Worksheet.column_dimensions:Свойство Worksheet.column_dimensions содержит информацию о свойствах отображения столбцов и представляет собой словарный объект. Ключами объекта служат буквы столбцов рабочего листа. Например, что бы обратиться к группе ячеек определенного столбца, необходимо указать букву этого столбца: Worksheet.column_dimensions['D'].
Этот словарный объект имеет полезные атрибуты .width при помощи которого можно изменять ширину столбца.
Пример изменения высоты строки и ширины ячейки:
>>> from openpyxl import Workbook >>> wb = Workbook() >>> ws = wb.active >>> ws.title = 'RowColDimension' # изменяем ширину колонки >>> ws.column_dimensions['B'].width = 30 >>> ws.column_dimensions['D'].width = 50 # изменяем высоту строки >>> ws.row_dimensions[5].height = 50 # для наглядности запишем в ячейки значения >>> ws['B5'] = 'ширина = 30, высота = 50' >>> ws['D5'] = 'ширина = 50, высота = 50' # ну и выравним >>> from openpyxl.styles import Alignment >>> for cell in ws[5]: ... if cell.value: ... cell.alignment = Alignment(horizontal="center", vertical="center") >>> wb.save('width-height.xlsx')
Примечание: в коде используется свойство рабочего листа электронной таблицы Worksheet.row_dimensions.
Свойство Worksheet.column_dimensions имеет метод .group(start, end=None, outline_level=1, hidden=False), который разрешает группировку ряда последовательных строк или столбцов вместе, что бы свернуть/скрыть или показать их.
Принимаемые аргументы .group():
start: первый столбец для группировки (обязательно).end=None: последний столбец для группировки (необязательно).outline_level=1: уровень вложенности сворачивания. Например можно свернуть с 1 по 10 строки с уровнем 1, а потом свернуть строки с 5 по 10 с уровнем 2. В этом случае, при открытии документа появиться возможность показать/развернуть скрытые строки в 2 этапа. Так же это работает и с колонками.hidden=False: должна ли группа быть свернута/скрыта на рабочем листе.Пример сворачивания группы колонок и строк:
>>> from openpyxl import Workbook >>> wb = Workbook() >>> ws = wb.active >>> ws.title = 'RowColFold' # свернем колонки с буквами с 'C' до 'F' >>> ws.column_dimensions.group('C','F', hidden=True) # свернем строки с номерами с 3 по 10 >>> ws.row_dimensions.group(3, 10, hidden=True) # теперь свернем строки с номерами с 7 по 10, с уровнем 2 >>> ws.row_dimensions.group(7,10, outline_level=2, hidden=True) >>> wb.save('fold_row-col.xlsx')
Worksheet.delete_cols(idx, amount=1):Метод Worksheet.delete_cols() удаляет столбец или столбцы из col==idx, где idx числовой индекс столбца. Аргумент amount - количество удаляемых столбцов. По умолчанию удаляется один столбец.
Например, чтобы удалить столбцы в диапазоне F:H необходимо вызвать ws.delete_cols(6, 3).
# удалим 3 столбцы в диапазоне `F:H` >>> ws.delete_cols(6, 3)
Worksheet.delete_rows(idx, amount=1):Метод Worksheet.delete_rows() удаляет строку или строки из row==idx, где idx числовой индекс строки. Аргумент amount - количество удаляемых строк. По умолчанию удаляется одна строка.
Например, чтобы удалить строки в диапазоне 5:8 необходимо вызвать ws.delete_rows(5, 3).
# удалим 3 строки с 5 по 8 >>> ws.delete_rows(5, 3)
Worksheet.freeze_panes:Свойство Worksheet.freeze_panes фиксирует строки и колонки находящиеся выше и левее указанной ячейки. Другими словами, это свойство фиксирует все что выше и левее указанной ячейки.
Например, если указать ws.freeze_panes = 'A3', то это заставит программу Excel зафиксировать только две верхние строки от прокрутки по вертикали. А если указать ws.freeze_panes = 'B3', то это зафиксирует две верхние строки от прокрутки по вертикали и колонку 'A' от прокрутки по горизонтали.
Worksheet.insert_cols(idx, amount=1):Метод Worksheet.insert_cols() вставляет столбец или столбцы перед col==idx, где idx числовой индекс столбца. Аргумент amount - количество вставляемых столбцов. По умолчанию вставляется один столбец.
Например, чтобы вставить 3 столбца перед существующим столбцом D необходимо вызвать ws.insert_cols(4, 3).
# вставим 3 новых столбца >>> ws.insert_cols(4, 3)
Worksheet.insert_rows(idx, amount=1):Метод Worksheet.insert_rows() вставляет строку или строки перед row==idx, где idx числовой индекс строки. Аргумент amount - количество вставляемых строк. По умолчанию вставляется одна строка.
Например, чтобы вставить 3 строки перед существующей 7-ой строкой необходимо вызвать ws.insert_rows(7, 3).
# вставим 3 новые строки перед # существующей 7-ой строкой >>> ws.insert_rows(7, 3)
Worksheet.iter_cols(min_col=None, max_col=None, min_row=None, max_row=None, values_only=False):Метод Worksheet.iter_cols() читает объекты ячеек на рабочем листе по столбцам (только те, которые имеют данные). Диапазон итерации задается с помощью индексов строк и столбцов. Возвращает генератор объектов из прочитанных ячеек.
A1.Аргументы:
min_col - наименьший индекс столбца.min_row - наименьший индекс строки.max_col - наибольший индекс столбца.max_row - наибольший индекс строки.values_only - следует ли возвращать только значения ячеек.>>> for col in ws.iter_cols(max_col=2, min_row=1, max_row=2): ... for cell in col: ... print(cell) # <Cell Sheet1.A1> # <Cell Sheet1.A2> # <Cell Sheet1.B1> # <Cell Sheet1.B2>
Примечание. Из соображений производительности метод Worksheet.iter_cols() недоступен в режиме только для чтения.
Worksheet.iter_rows(min_row=None, max_row=None, min_col=None, max_col=None, values_only=False):Метод Worksheet.iter_rows() читает объекты ячеек на рабочем листе по строкам (только те, которые имеют данные). Диапазон итерации задается с помощью индексов строк и столбцов. Возвращает генератор объектов из прочитанных ячеек.
A1.Аргументы:
min_col - наименьший индекс столбца.min_row - наименьший индекс строки.max_col - наибольший индекс столбца.max_row - наибольший индекс строки.values_only - следует ли возвращать только значения ячеек.>>> for col in ws.iter_rows(min_col=1, max_col=2, max_row=2): ... for cell in col: ... print(cell) # <Cell 'Sheet'.A1> # <Cell 'Sheet'.B1> # <Cell 'Sheet'.A2> # <Cell 'Sheet'.B2>
Worksheet.max_column:Свойство Worksheet.max_column возвращает максимальный индекс столбца, содержащий данные. Индексы столбцов начинаются с 1, а не с 0, как в списке.
>>> ws.max_column # 12
Worksheet.max_row:Свойство Worksheet.max_row возвращает максимальный индекс строки, содержащий данные. Индексы строк начинаются с 1, а не с 0, как в списке.
>>> ws.max_row # 35
Worksheet.merge_cells(range_string=None, start_row=None, start_column=None, end_row=None, end_column=None):Свойство Worksheet.merge_cells устанавливает слияние в диапазоне ячеек range_string. Диапазон range_string - это диапазон ячеек, который задается строкой, например, 'A1:E1'.
Диапазон ячеек, также можно установить при помощи аргументов start_row, start_column, end_row и end_column.
>>> from openpyxl import Workbook >>> wb = Workbook() >>> ws = wb.active # объединить ячейки, находящиеся # в диапазоне `B2 : E2` >>> ws.merge_cells('B2:E2')
Worksheet.min_column:Свойство Worksheet.min_column возвращает минимальный индекс столбца, содержащий данные. Индексы столбцов начинаются с 1, а не с 0, как в списке.
>>> ws.min_column # 1
Worksheet.min_row:Свойство Worksheet.min_row возвращает минимальный индекс строки, содержащий данные. Индексы строк начинаются с 1, а не с 0, как в списке.
>>> ws.min_row # 1
Worksheet.move_range(cell_range, rows=0, cols=0, translate=False):Метод Worksheet.move_range() перемещает диапазон ячеек cell_range на количество строк rows и/или столбцов cols:
rows > 0, и вверх, если rows < 0, cols > 0, и влево, если cols < 0. Существующие ячейки будут перезаписаны. Формулы и ссылки обновляться не будут.
Пример:
>>> ws.move_range("D4:F10", rows=-1, cols=2)
Это приведет к перемещению ячеек в диапазоне ячеек D4:F10 вверх на одну строку и вправо на два столбца. Ячейки будут перезаписаны всеми существующими ячейками.
Если ячейки содержат формулы, то openpyxl может транслировать их, но этот функционал умолчанию отключен. Кроме того, будут транслированы только формулы в самих ячейках. Ссылки на ячейки из других ячеек или определенные имена обновляться не будут. Для этого можно использовать переводчик формул синтаксического анализа:
>>> ws.move_range("G4:H10", rows=1, cols=1, translate=True)
Это приведет к перемещению относительных ссылок в формулах в диапазоне на одну строку и один столбец.
Worksheet.print_area:Свойство Worksheet.print_area возвращает/устанавливает область печати "по умолчанию" для текущего рабочего листа электронной таблицы. Если область печати не задана, то возвращается None.
Для установки области печати "по умолчанию", необходимо указать диапазон, например 'A1:D4', или список диапазонов.
>>> print(ws.print_area) # None # установим диапазон печати >>> ws.print_area = 'A1:F10' >>> ws.print_area # ['$A$1:$F$10'] # несколько диапазонов для печати по умолчанию >>> ws.print_area = ['A1:B2', 'C3:F5'] >>> ws.print_area # ['$A$1:$a$2', '$a$3:$f$10']
Worksheet.print_title_cols:Свойство Worksheet.print_title_cols устанавливает колонки, которые должны быть напечатаны слева на каждой странице, например: 'A:C'.
# заставляем Excel печатать # колонку `A` на каждом листе >>> ws.print_title_cols = 'A:A'
Worksheet.print_title_rows:Свойство Worksheet.print_title_rows устанавливает строки, которые должны быть напечатаны вверху каждой страницы, например, '1:3'.
# заставляем Excel печатать диапазон # строк со 2 по 3 на каждом листе >>> ws.print_title_rows = '2:3'
Worksheet.rows:Свойство Worksheet.rows создает генератор всех объектов ячеек текущего рабочего листа, в которых есть данные, по строкам. Дополнительно смотрите метод Worksheet.iter_rows().
>>> list(ws.rows) # [(<Cell 'Sheet'.A1>, <Cell 'Sheet'.B1>, <Cell 'Sheet'.C1>, # (<Cell 'Sheet'.A2>, <Cell 'Sheet'.B2>, <Cell 'Sheet'.C2>, , ...), # ... >>> for row in ws.rows: ... for cell in row: ... print(cell) # <Cell 'Sheet'.A1> # <Cell 'Sheet'.B1> # <Cell 'Sheet'.C1> # ... # <Cell 'Sheet'.A2> # <Cell 'Sheet'.B2> # <Cell 'Sheet'.C2> ...
Worksheet.row_dimensions:Свойство Worksheet.row_dimensions содержит информацию о свойствах отображения строк и представляет собой словарный объект. Ключами объекта служат номера строк.
Этот словарный объект имеет полезные атрибут .height при помощи которого можно менять высоту строки и метод .group, который служит для группировки строк, для дальнейшего их сворачивания.
Так же смотрите свойство Worksheet.column_dimensions и материал "Изменение размеров строки/столбца модулем openpyxl в Python"
Worksheet.set_printer_settings(paper_size, orientation):Метод Worksheet.set_printer_settings() устанавливает размер бумаги paper_size и ориентацию страницы orientation по умолчанию для текущего листа электронной таблицы.
Аргумент paper_size может принимать значения:
Worksheet.PAPERSIZE_A3 = '8'.Worksheet.PAPERSIZE_A4= '9'.Worksheet.PAPERSIZE_A4_SMALL= '10'.Worksheet.PAPERSIZE_A5= '11'.Worksheet.PAPERSIZE_EXECUTIVE= '7'.Worksheet.PAPERSIZE_LEDGER= '4'.Worksheet.PAPERSIZE_LEGAL= '5'.Worksheet.PAPERSIZE_LETTER= '1'.Worksheet.PAPERSIZE_LETTER_SMALL= '2'.Worksheet.PAPERSIZE_STATEMENT= '6'.Worksheet.PAPERSIZE_TABLOID= '3'.Ориентация страницы, так же настраивается при помощи констант объекта Worksheet
Worksheet.ORIENTATION_LANDSCAPE= 'landscape'.Worksheet.ORIENTATION_PORTRAIT= 'portrait'.Примечание: В коде не обязательно использовать имена констант, можно напрямую использовать их значения.
# формат `А4`, ориентированный как `landscape` >>> set_printer_settings('9', 'landscape')
Worksheet.tables:Свойство Worksheet.tables возвращает все объекты таблиц текущего листа документа XLSX, добавленные методом Worksheet.add_table().
Worksheet.unmerge_cells(range_string=None, start_row=None, start_column=None, end_row=None, end_column=None):Метод Worksheet.unmerge_cells() удаляет слияние ячеек в диапазоне range_string. Аргумент range_string - это диапазон ячеек, который ранее был объединен при помощи метода Worksheet.merge_cells() и задается строкой, например, 'A1:E1'.
Диапазон ячеек, также можно установить при помощи аргументов start_row, start_column, end_row и end_column.
>>> from openpyxl import Workbook >>> wb = Workbook() >>> ws = wb.active # объединим ячейки >>> ws.merge_cells('B2:E2') # а теперь разъединим ячейки, # в диапазоне `B2 : E2` >>> ws.unmerge_cells('B2:E2')
Worksheet.values:Свойство Worksheet.values создает генератор, который содержит все значения ячеек, которые имеются на текущем листе по строкам.
for row in ws.values: for value in row: print(value)
Этот код напечатает все значения ячеек текущего рабочего листа, которые имеют какие-то данные.