Для использования в ячейке встроенной формулы/функции электронной таблицы совместно с модулем 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,)