Python ширина столбца excel
I have following script which is converting a CSV file to an XLSX file, but my column size is very narrow. Each time I have to drag them with mouse to read data. Does anybody know how to set column width in openpyxl ?
Here is the code I am using.
19 Answers 19
You could estimate (or use a mono width font) to achieve this. Let's assume data is a nested array like
We can get the max characters in each column. Then set the width to that. Width is exactly the width of a monospace font (if not changing other styles at least). Even if you use a variable width font it is a decent estimation. This will not work with formulas.
A bit of a hack but your reports will be more readable.
when I have int as a cell value, this will bump into an error since int has no len property, is there a way to avoid this? thanks!
might be usefull to add that you still need to save the workbook with wb.save(filename = dest_filename)
This fails in version 3.0.9 (and likely before) on len(cell) , with TypeError object of type 'Cell' has no len()
My variation of Bufke's answer. Avoids a bit of branching with the array and ignores empty cells / columns.
Now fixed for non-string cell values.
As of openpyxl version 3.0.3 you need to use
as the openpyxl library will raise a TypeError if you pass column_dimensions a number instead of a column letter, everything else can stay the same.
line 6 can be improved to use column letter: dims[cell.column_letter] = max((dims.get(cell.column_letter, 0), len(str(cell.value))))
Even more pythonic way to set the width of all columns that works at least in openpyxl version 2.4.0:
The as_text function should be something that converts the value to a proper length string, like for Python 3:
@IrinaVelikopolskaya if cell.value does not implement __len__ , this will throw exception ( int or NoneType for example)
@IrinaVelikopolskaya datetime is another example of where one gets an exception. The as_text function seems to work best for me.
With openpyxl 3.0.3 the best way to modify the columns is with the DimensionHolder object, which is a dictionary that maps each column to a ColumnDimension object. ColumnDimension can get parameters as bestFit, auto_size (which is an alias of bestFit) and width. Personally, auto_size doesn't work as expected and I had to use width and figured out that the best width for the column is len(cell_value) * 1.23 .
To get the value of each cell it's necessary to iterate over each one, but I personally didn't use it because in my project I just had to write worksheets, so I got the longest string in each column directly on my data.
Модуль openpyxl обеспечивает довольно гибкое управление стилями, относительно простую работу с ними. Стили в электронных таблицах XLSX используются для изменения внешнего вида данных при отображении на экране. Они также используются для определения форматирования чисел.
Содержание:
Аспекты применения стилей модулем openpyxl .
Стили могут быть применены к следующим аспектам:
- font : устанавливает размер шрифта, цвет, стиль подчеркивания и т. д.
- fill : устанавливает шаблон или градиент цвета заливки ячейки.
- border : устанавливает стиль границы ячейки.
- alignment : устанавливает выравнивание ячейки.
Ниже приведены значения по умолчанию установленные модулем openpyxl :
Cтили ячеек электронной таблицы.
Существует два типа стилей: стили ячеек и именованные стили, также известные как шаблоны стилей.
Стили ячеек являются общими для объектов, и после того, как они были назначены, их нельзя изменить. Это предотвращает нежелательные побочные эффекты, такие как изменение стиля для большого количества ячеек при изменении только одной.
Создания нового стиля на основе другого.
Модуль openpyxl поддерживает копирование стилей.
Пример создания нового стиля на основе другого:
Цвета для шрифтов, фона, границ.
Цвета для шрифтов, фона, границ и т.д. Можно задать тремя способами: индексированный, aRGB или тема. Индексированные цвета являются устаревшей реализацией, и сами цвета зависят от индекса, предоставленного в рабочей книге или в приложении по умолчанию. Цвета темы полезны для дополнительных оттенков цветов, но также зависят от темы, присутствующей в рабочей книге. Поэтому рекомендуется использовать цвета aRGB.
Цвета aRGB.
Цвета RGB устанавливаются с использованием шестнадцатеричных значений красного, зеленого и синего.
Альфа-значение теоретически относится к прозрачности цвета, но это не относится к стилям ячеек. Значение по умолчанию 00 будет добавлено к любому простому значению RGB:
Применение стилей.
Стили применяются непосредственно к ячейкам.
Стили также могут применяться к столбцам и строкам, но обратите внимание, что это относится только к ячейкам, созданным (в Excel) после закрытия файла. Если необходимо применить стили ко всем строкам и столбцам, то нужно применить стиль к каждой ячейке самостоятельно.
Это ограничение формата файла:
Горизонтальное и вертикальное выравнивание текста.
Горизонтальное и вертикальное выравнивание в ячейках выставляется атрибутом ячейки .alignment и классом Alignment() .
Пример горизонтального выравнивания текста:
Вертикальное выравнивание в основном применяется когда изменена высота строки или были объединены несколько ячеек.
Пример вертикального выравнивания данных в ячейке:
Оформление границ ячеек.
Цвет и стиль границ/бордюров ячеек выставляется атрибутом ячейки .border и классом Border() совместно с классом Side() .
При этом аргумент стиля границ ячеек border_style может принимать ОДИН из следующих значений: ‘dashDotDot’ , ‘medium’ , ‘dotted’ , ‘slantDashDot’ , ‘thin’ , ‘hair’ , ‘mediumDashDotDot’ , ‘dashDot’ , ‘double’ , ‘mediumDashed’ , ‘dashed’ , ‘mediumDashDot’ и ‘thick’ .
Пример стилизации границ одной ячейки:
Пример стилизации границ нескольких ячеек:
Заливка ячеек цветом и цвет текста.
Цвет заливки ячеек выставляется атрибутом ячейки .fill и классом PatternFill() .
Обязательный аргумент fill_type (по умолчанию равен None ) класса PatternFill() может принимать значения:
- если fill_type='solid' , то нужно обязательно указывать аргумент цвета заливки fgColor .
- следующие значения аргумента fill_type применяются самостоятельно (без аргумента fgColor ) и представляют собой предустановленные цвета заливки : ‘darkHorizontal’ , ‘lightDown’ , ‘lightGray’ , ‘darkDown’ , ‘darkGrid’ , ‘darkUp’ , ‘darkGray’ , ‘darkVertical’ , ‘darkTrellis’ , ‘mediumGray’ , ‘lightVertical’ , ‘lightTrellis’ , ‘lightGrid’ , ‘lightHorizontal’ , ‘gray0625’ , ‘lightUp’ , ‘gray125’ .
Внимание: если аргумент fill_type не указан, то fgColor не будет иметь никакого эффекта!
Пример заливки одной ячейки:
Именованные стили NamedStyle .
В отличие от простых стилей ячеек, именованные стили изменяемы и используется для объединения в себе нескольких стилей, таких как шрифты, границы, выравнивание и т. д. Они имеют смысл, когда необходимо применить форматирование к множеству разных ячеек одновременно. Об именованных стилях можно думать как о классах CSS при оформлении HTML-разметки. Именованные стили регистрируются в рабочей книге.
Примечание. После назначения ячейке именованного стиля, дальнейшие/дополнительные изменения этого стиля не повлияют на стиль ячейки.
Создание именованного стиля.
После создания именованного стиля его нужно зарегистрировать в рабочей книге:
После регистрации стиля в рабочей книге, применять его можно только по имени:
Встроенные стили в Excel.
Спецификация включает в себя некоторые встроенные стили, которые также могут быть использованы. К сожалению, имена для этих стилей хранятся в их локализованных формах. OpenPyxl узнает только английские имена и только так, как они записаны в официальной документации.
Использование встроенных в Excel стилей здесь не рассматривается, так как при их применении могу возникать существенные искажения.
В материале рассказывается о методах объектов модуля openpyxl , которые отвечают за такие свойства документа XLSX как изменение размеров строки и столбца, а также их сворачивание/скрытие при открытии электронной таблицы в программе Excel.
Содержание:
Изменение ширины столбца, высоты строки модулем 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 .
Смотрим пример изменения высоты строки и ширины ячейки:
Внимание! Если высота строки не изменялась программно или вручную (через программу Excel) то для этих строк, атрибуты ws.row_dimensions[i].height будут возвращать None . При этом, модуль openpyxl , устанавливает высоту строки по умолчанию: defaultRowHeight=15
Следовательно, что бы узнать все высоты строк/колонок, нужно выполнить что-то подобное:
- openpyxl.utils.cell.get_column_letter(idx) - преобразует индекс столбца в букву столбца (например, столбец 'C' преобразуется в цифру 3).
- ws.max_row - возвращает целое число, которое означает количество строк с данными.
- ws.max_column - возвращает целое число, которое означает количество столбцов с данными.
Проблема автоматической подгонки ширины колонок.
Модуль openpyxl не поддерживает автоматическую подгонку ширины столбца, под данные, записанные в ячейку. В принципе, в автоматической подгонке нет ничего сложного, но есть одна большая проблема. Эта проблема заключается в том, что бы найти зависимость между количеством символов записанных в ячейку и переменной шириной шрифта TrueType, которая зависит от его величины (кегеля) и плюс ко всему от используемых в ячейке цифр, символов и знаков препинания.
Автоматическая подгонка ширины столбцов в документе XLSX при помощи модуля openpyxl могла бы выглядеть как-то так, если выше описанная проблема будет решена.
Скрытие/сворачивание группы строк/столбцов.
Так же сами словарные объекты 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 : должна ли группа быть свернута/скрыта в рабочей книге.
Смотрим пример сворачивания группы колонок и строк:
Теперь, открыв сохраненный документ, строки можно открывать и сворачивать в 2 этапа, нажимая на соответствующий элемент управления. Колонки, с именами с 'C' до 'F' открываются/сворачиваются в один этап.
Объект ячейки Cell управляет отдельными ячейками электронной таблицы, а также описывает ее свойства. Класс Cell() должен знать свое значение и тип, параметры отображения и любые другие функции ячейки Excel.
Не создавайте объекты ячейки самостоятельно. Доступ к произвольной ячейке можно получить, обратившись к объекту рабочего листа Worksheet как к словарю, при этом ключом будет служить строка с именем ячейки, например ws['A1'] . Это вернет ячейку на 'A4' или создаст ее, если она еще не существует. Значения могут быть присвоены напрямую:
Для доступа к ячейке с использованием индексов столбцов и строк, можно использовать метод Worksheet.cell() .
Пример работы с объектом ячейки через переменную:
Содержание:
Cell.base_date :
Свойство Cell.base_date содержит базовый класс обработки даты. По умолчанию это datetime .
Cell.col_idx :
Атрибут Cell.col_idx возвращает числовой индекс столбца. Нумерация столбцов в модуле openpyxl начинается с 1, а не с 0 как в списке.
Cell.column :
Атрибут Cell.column возвращает номер столбца этой ячейки. Нумерация столбцов в модуле openpyxl начинается с 1, а не с 0 как в списке.
Cell.column_letter :
Атрибут Cell.column_letter возвращает букву столбца ячейки.
Если назначить один и тот же комментарий нескольким ячейкам, то openpyxl автоматически создаст копии.
Cell.coordinate :
Атрибут Cell.coordinate возвращает координаты этой ячейки.
Cell.encoding :
Атрибут Cell.encoding возвращает кодировку этой ячейки.
Cell.hyperlink :
Свойство Cell.hyperlink устанавливает целевую гиперссылку.
Некоторые говорят что код, представленный выше - не работает. В таком случае установить ссылку в ячейку можно используя встроенную в Excel функцию '=HYPERLINK(link, linkName)`, например:
Cell.is_date :
Свойство Cell.is_date возвращает True , если значение отформатировано как дата.
Cell.offset(row=0, column=0) :
Метод Cell.offset() возвращает объект ячейки, смещенный на кол-во строк row и столбцов column относительно этой ячейки.
- row - количество смещения строк.
- column - количество смещения столбцов.
Cell.parent :
Свойство Cell.parent возвращает объект рабочего листа, на котором расположена эта ячейка.
Cell.row :
Свойство Cell.row возвращает номер строки этой ячейки. Нумерация строк в модуле openpyxl начинается с 1, а не с 0 как в списке.
Cell.value :
Свойство Cell.value возвращает или устанавливает значение, хранящееся в ячейке.
Ячейка может хранить следующие значения: str , float , int или datetime.datetime .
Cell.alignment :
Свойство Cell.alignment отвечает за выравнивание значения в ячейке. Значение должно быть объектом Alignment() .
Cell.font :
Свойство Cell.font задает стиль шрифта в ячейке (имя, высота, цвет и т.д.). Значение должно быть объектом Font() .
Cell.border :
Свойство Cell.border задает стиль оформления границ ячейки. Значение должно быть объектом Border() .
Пример стилизации границ одной ячейки:
Cell.fill :
Свойство Cell.fill задает цвет заливки ячейки. Значение должно быть объектом PatternFill() .
Документ электронной таблицы Excel называется рабочей книгой. Каждая книга может хранить некоторое количество листов. Лист, просматриваемый пользователем в данный момент, называется активным. Лист состоит из из столбцов (адресуемых с помощью букв, начиная с A) и строк (адресуемых с помощью цифр, начиная с 1).
Модуль OpenPyXL не поставляется вместе с Python, поэтому его предварительно нужно установить:
Чтение файлов Excel
А теперь небольшой скрипт:
Как получить другой лист книги:
Как сделать лист книги активным:
Как задать имя листа:
Объект Cell имеет атрибут value , который содержит значение, хранящееся в ячейке. Объект Cell также имеет атрибуты row , column и coordinate , которые предоставляют информацию о расположении данной ячейки в таблице.
К отдельной ячейке можно также обращаться с помощью метода cell() объекта Worksheet , передавая ему именованные аргументы row и column . Первому столбцу или первой строке соответствует число 1, а не 0:
Размер листа можно получить с помощью атрибутов max_row и max_column объекта Worksheet :
Чтобы преобразовать буквенное обозначение столбца в цифровое, следует вызвать функцию
Чтобы преобразовать цифровое обозначение столбуа в буквенное, следует вызвать функцию
Для вызова этих функций загружать рабочую книгу не обязательно.
Используя срезы объектов Worksheet , можно получить все объекты Cell , принадлежащие определенной строке, столбцу или прямоугольной области.
Выводим значения второй колонки:
Выводим строки с первой по третью:
Для доступа к ячейкам конкретной строки или столбца также можно воспользоваться атрибутами rows и columns объекта Worksheet .
Выводим значения всех ячеек листа:
Выводим значения второй строки (индекс 1):
Выводим значения второй колонки (индекс 1):
Запись файлов Excel
Метод create_sheet() возвращает новый объект Worksheet , который по умолчанию становится последним листом книги. С помощью именованных аргументов title и index можно задать имя и индекс нового листа.
Метод remove() принимает в качестве аргумента не строку с именем листа, а объект Worksheet . Если известно только имя листа, который надо удалить, используйте wb[sheetname] . Еще один способ удалить лист — использовать инструкцию del wb[sheetname] .
Не забудьте вызвать метод save() , чтобы сохранить изменения после добавления или удаления листа рабочей книги.
Запись значений в ячейки напоминает запись значений в ключи словаря:
Заполняем таблицу 3x3:
Можно добавлять строки целиком:
Стилевое оформление
Для настройки шрифтов, используемых в ячейках, необходимо импортировать функцию Font() из модуля openpyxl.styles :
Ниже приведен пример создания новой рабочей книги, в которой для шрифта, используемого в ячейке A1 , устанавливается шрифт Arial , красный цвет, курсивное начертание и размер 24 пункта:
Именованные стили применяются, когда надо применить стилевое оформление к большому количеству ячеек.
Добавление формул
Формулы, начинающиеся со знака равенства, позволяют устанавливать для ячеек значения, рассчитанные на основе значений в других ячейках.
Эта инструкция сохранит =SUM(B1:B8) в качестве значения в ячейке B9 . Тем самым для ячейки B9 задается формула, которая суммирует значения, хранящиеся в ячейках от B1 до B8 .
Формула Excel — это математическое выражение, которое создается для вычисления результата и которое может зависеть от содержимого других ячеек. Формула в ячейке Excel может содержать данные, ссылки на другие ячейки, а также обозначение действий, которые необходимо выполнить.
Использование ссылок на ячейки позволяет пересчитывать результат по формулам, когда происходят изменения содержимого ячеек, включенных в формулы. Формулы Excel начинаются со знака = . Скобки () могут использоваться для определения порядка математических операции.
Примеры формул Excel: =27+36 , =А1+А2-АЗ , =SUM(А1:А5) , =MAX(АЗ:А5) , =(А1+А2)/АЗ .
Хранящуюся в ячейке формулу можно читать, как любое другое значение. Однако, если нужно получить результат расчета по формуле, а не саму формулу, то при вызове функции load_workbook() ей следует передать именованный аргумент data_only со значением True .
Настройка строк и столбцов
С помощью модуля OpenPyXL можно задавать высоту строк и ширину столбцов таблицы, закреплять их на месте (чтобы они всегда были видны на экране), полностью скрывать из виду, объединять ячейки.
Настройка высоты строк и ширины столбцов
Объекты Worksheet имеют атрибуты row_dimensions и column_dimensions , которые управляют высотой строк и шириной столбцов.
Атрибуты row_dimension s и column_dimensions представляют собой значения, подобные словарю. Атрибут row_dimensions содержит объекты RowDimensions , а атрибут column_dimensions содержит объекты ColumnDimensions . Доступ к объектам в row_dimensions осуществляется с использованием номера строки, а доступ к объектам в column_dimensions — с использованием буквы столбца.
Для указания высоты строки разрешено использовать целые или вещественные числа в диапазоне от 0 до 409. Для указания ширины столбца можно использовать целые или вещественные числа в диапазоне от 0 до 255. Столбцы с нулевой шириной и строки с нулевой высотой невидимы для пользователя.
Объединение ячеек
Ячейки, занимающие прямоугольную область, могут быть объединены в одну ячейку с помощью метода merge_cells() рабочего листа:
Чтобы отменить слияние ячеек, надо вызвать метод unmerge_cells() :
Закрепление областей
Если размер таблицы настолько велик, что ее нельзя увидеть целиком, можно заблокировать несколько верхних строк или крайних слева столбцов в их позициях на экране. В этом случае пользователь всегда будет видеть заблокированные заголовки столбцов или строк, даже если он прокручивает таблицу на экране.
У объекта Worksheet имеется атрибут freeze_panes , значением которого может служить объект Cell или строка с координатами ячеек. Все строки и столбцы, расположенные выше и левее, будут заблокированы.
Значение атрибута freeze_panes | Заблокированные строки и столбцы |
---|---|
sheet.freeze_panes = 'A2' | Строка 1 |
sheet.freeze_panes = 'B1' | Столбец A |
sheet.freeze_panes = 'C1' | Столбцы A и B |
sheet.freeze_panes = 'C2' | Строка 1 и столбцы A и B |
sheet.freeze_panes = None | Закрепленные области отсутствуют |
Диаграммы
Модуль OpenPyXL поддерживает создание гистогорамм, графиков, а также точечных и круговых диаграмм с использование данных, хранящихся в электронной таблице. Чтобы создать диаграмму, необходимо выполнить следующие действия:
- создать объект Reference на основе ячеек в пределах выделенной прямоугольной области;
- создать объект Series , передав функции Series() объект Reference ;
- создать объект Chart;
- дополнительно можно установить значения переменных drawing.top , drawing.left , drawing.width , drawing.height объекта Chart , определяющих положение и размеры диаграммы;
- добавить объект Chart в объект Worksheet .
- Объект Worksheet , содержащий данные диаграммы.
- Два целых числа, представляющих верхнюю левую ячейку выделенной прямоугольной области, в которых содержатся данные диаграммы: первое число задает строку, второе — столбец; первой строке соответствует 1, а не 0.
- Два целых числа, представляющих нижнюю правую ячейку выделенной прямоугольной области, в которых содержатся данные диаграммы: первое число задает строку, второе — столбец.
Аналогично можно создавать графики, точечные и круговые диаграммы, вызывая методы:
Читайте также: