В материале рассказывается о методах объектов модуля openpyxl, которые отвечают за такие свойства документа XLSX как изменение размеров строки и столбца, а также их сворачивание/скрытие при открытии электронной таблицы в программе Excel.
openpyxl.openpyxl.Объект листа Worksheet (далее будем обозначать его как ws) модуля openpyxl имеет свойства, представляющие собой словарные объекты ws.row_dimensions и ws.column_dimensions. Эти словарные объекты хранят в себе массив ячеек определенной строки RowDimension и определенного столбца ColumnDimension соответственно, которые в свою очередь содержат информацию о свойствах отображения, в том числе высоту строки RowDimension.height и ширину столбца ColumnDimension.width. Получать эти массивы ячеек можно обращаясь к словарным объектам по ключам, которые имеют значения номера строки и букву колонки, например:
ws.row_dimensions[2] будет содержать в себе массив ячеек RowDimension, которые расположены в строке с номером 2.ws.column_dimensions['B'] будет содержать в себе массив ячеек ColumnDimension, которые расположены в столбце с буквой 'B'.Так вот, установить высоту, например пятой строки документа XLSX можно, если присвоить атрибуту ws.row_dimensions[5].height целое число. Соответственно, изменить ширину столбца с буквой 'A', также можно, присвоив целое число атрибуту ws.column_dimensions[1].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')
Внимание! Если высота строки не изменялась программно или вручную (через программу Excel) то для этих строк, атрибуты ws.row_dimensions[i].height будут возвращать None. При этом, модуль openpyxl, устанавливает высоту строки по умолчанию: defaultRowHeight=15
Следовательно, что бы узнать все высоты строк/колонок, нужно выполнить что-то подобное:
from openpyxl import load_workbook wb = load_workbook('width-height.xlsx') ws = wb.active # для строк for i in range(1, ws.max_row+1): # если высота строки не изменялась программно # или вручную то `rh` будет присваиваться `None` rh = ws.row_dimensions[i].height # по умолчанию высота строки равна 15 единицам row_heights = 15 if rh is None else rh print(f'Строка {i} имеет высоту {row_heights}') # ну и для колонок from openpyxl.utils.cell import get_column_letter for i in range(1, ws.max_column+1): # преобразовываем индекс столбца в его букву letter = get_column_letter(i) # получаем ширину столбца col_width = ws.column_dimensions[letter].width print(f'Столбец {letter} имеет ширину {col_width}') # Строка 1 имеет высоту 15 # Строка 2 имеет высоту 15 # Строка 3 имеет высоту 15 # Строка 4 имеет высоту 15 # Строка 5 имеет высоту 50.0 # Столбец A имеет ширину 13.0 # Столбец B имеет ширину 30.0 # Столбец C имеет ширину 13.0 # Столбец D имеет ширину 50.0
Примечание:
openpyxl.utils.cell.get_column_letter(idx) - преобразует индекс столбца в букву столбца (например, столбец 'C' преобразуется в цифру 3).ws.max_row - возвращает целое число, которое означает количество строк с данными.ws.max_column - возвращает целое число, которое означает количество столбцов с данными.Модуль openpyxl не поддерживает автоматическую подгонку ширины столбца, под данные, записанные в ячейку. В принципе, в автоматической подгонке нет ничего сложного, но есть одна большая проблема. Эта проблема заключается в том, что бы найти зависимость между количеством символов записанных в ячейку и переменной шириной шрифта TrueType, которая зависит от его величины (кегеля) и плюс ко всему от используемых в ячейке цифр, символов и знаков препинания.
Автоматическая подгонка ширины столбцов в документе XLSX при помощи модуля openpyxl могла бы выглядеть как-то так, если выше описанная проблема будет решена.
from openpyxl import Workbook from openpyxl.styles import Font wb = Workbook() ws = wb.active # заполняем ячейки ws['c2'] = 'Это большая ячейка' ws['c3'] = 'Эта ячейка чуть больше' ws['f2'] = 'Это большая верхняя ячейка' ws['f3'] = 'Эта ячейка чуть меньше' # размер шрифта документа font_size = 16 # словарь с размерами столбцов cols_dict = {} # проходимся по всем строкам документа for row in ws.rows: # теперь по ячейкам каждой строки for cell in row: # получаем букву текущего столбца letter = cell.column_letter # если в ячейке записаны данные if cell.value: # устанавливаем в ячейке размер шрифта cell.font = Font(name='Calibri', size=font_size) # вычисляем количество символов, записанных в ячейку len_cell = len(str(cell.value)) # длинна колонки по умолчанию, если буква # текущего столбца отсутствует в словаре `cols_dict` len_cell_dict = 0 # смотрим в словарь c длинами столбцов if letter in cols_dict: # если в словаре есть буква текущего столбца # то извлекаем соответствующую длину len_cell_dict = cols_dict[letter] # если текущая длина данных в ячейке # больше чем длинна из словаря if len_cell > len_cell_dict: # записываем новое значение ширины этого столбца cols_dict[letter] = len_cell ###!!! ПРОБЛЕМА АВТОМАТИЧЕСКОЙ ПОДГОНКИ !!!### ###!!! расчет новой ширины колонки (здесь надо подгонять) !!!### new_width_col = len_cell * font_size**(font_size*0.009) # применение новой ширины столбца ws.column_dimensions[cell.column_letter].width = new_width_col # пишем электронную таблицу в # файл и смотрим что получилось wb.save('autifit.xlsx')
Так же сами словарные объекты ws.row_dimensions и ws.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')
Теперь, открыв сохраненный документ, строки можно открывать и сворачивать в 2 этапа, нажимая на соответствующий элемент управления. Колонки, с именами с 'C' до 'F' открываются/сворачиваются в один этап.