Сообщить об ошибке.

Встроенные в Excel формулы и модуль openpyxl в Python

Содержание:


Вставка встроенных формул при создании электронной таблицы.

Для использования в ячейке встроенной формулы/функции электронной таблицы совместно с модулем openpyxl необходимо использовать английское имя этой формулы/функции, а аргументы формулы должны быть разделены запятыми, а не другими пунктуационными знаками, такими как точка с запятой.

>>> from openpyxl import Workbook
>>> wb = Workbook()
>>> ws = wb.active
>>> # в ячейку A1 запишем сумму (5 + 3)
>>> ws["A1"] = "=SUM(5, 4)"
>>> ws["A1"].value
# '=SUM(5, 4)'

Теперь, в качестве аргументов встроенной в Excel формулы/функции '=SUM()' будем использовать имена ячеек:

# запишем в ячейку A2 число
>>> ws["A2"] = 10
# в ячейке A3 выведем сумму ячеек (A1 + A2)
>>> ws["A3"] = "=SUM(A1, A2)"
>>> ws["A3"].value
# '=SUM(A1, A2)'

А как подставить в формулу/функцию диапазон ячеек? Очень просто, для этого нужно использовать срез ячеек, например '=SUM(A1:A3)'

# в ячейке A4 выведем сумму ячеек (A1 + A2 + A3)
>>> ws["A4"] = "=SUM(A1:A3)"
>>> ws["A4"].value
# '=SUM(A1:A3)'

# далее сохраним и откроем файл в Excel
>>> wb.save("formula.xlsx")

В открытом документе, переходя на используемые ячейки можно видеть, что все расчеты ведутся при помощи встроенных формул/функций электронных таблиц.

Важно! Модуль openpyxl никогда не оценивает встроенную формулу/функцию, но есть возможность проверить корректность имени формулы/функции.

Что бы посмотреть список формул, которые знает модуль openpyxl, нужно просто распечатать множество openpyxl.utils.FORMULAE.

>>> from openpyxl.utils import FORMULAE
# проверка корректности функции `AVERAGE`
>>> "AVERAGE" in FORMULAE
# True

# список всех формул, которые знает `openpyxl`
>>> FORMULAE
# frozenset({'RIGHTB', 'PMT', 'MULTINOMIAL', 'MROUND', 
# ...
# 'SUM', 'GEOMEAN', 'MEDIAN', 'SUBTOTAL', 'ISEVEN',
# 'REPLACE', 'HOUR', 'TODAY', 'YEAR', 'DATEVALUE', 
# ...
# 'RAND', 'TAN', 'CONFIDENCE', 'NORMSINV', 'DEC2HEX'})

Извлечение данных, рассчитанных встроенными формулами.

Как быть с данными, которые рассчитываются внутри документа электронных таблиц встроенными формулами? При чтении такого документа модулем openpyxl, из соответствующих ячеек извлекаются формулы, а не данные.

>>> from openpyxl import load_workbook
>>> wb = load_workbook(filename='formula.xlsx')
>>> ws = wb.active
>>> for row in ws.rows:
...     print(row[0].value)

# =SUM(5, 4)
# 10
# =SUM(A1, A2)
# =SUM(A1:A3)

Упс. Неожиданно. Как быть? Для этого, в модуле openpyxl предусмотрен особый режим загрузки электронной таблицы. Функция openpyxl.load_workbook() имеет аргумент data_only=True, который позволяет загружать рассчитанные данные вместо встроенных формул.

Если просто нужны значения из рабочего листа, то можно использовать свойство активного листа Worksheet.values. Это свойство перебирает все строки на листе, но возвращает только значения ячеек, минуя объекты ячеек:

>>> from openpyxl import load_workbook
# аргумент `data_only=True` позволяет загружать данные
>>> wb = load_workbook(filename='formula.xlsx' , data_only=True)
>>> ws = wb.active
>>> for row in ws.values:
...     for cell_val in row:
...         print(cell_val)
# 9
# 10
# 19
# 38

Для возврата только значения ячейки, методы Worksheet.iter_rows() и Worksheet.iter_cols(), представленные в ознакомительном материале могут принимать аргумент values_only:

>>> for row in ws.iter_rows(min_row=1, max_col=3, max_row=2, values_only=True):
...   print(row)
# (9,)
# (10,)
# (19,)
# (38,)