Числовые форматы в openpyxl: рубли, проценты и разделители

Прайс собран, шапка отформатирована, ширины выставлены — но колонка «Цена» выглядит бедно: голые 1234.5 и 250. Человек же ждёт 1 234.50 ₽. За показ числа отвечает не значение, а числовой формат — маска-строка, которую Excel применяет при отрисовке ячейки. В openpyxl за неё отвечает атрибут number_format, и в этом уроке мы научимся писать такие маски: рубли, проценты, штучные счётчики и разделители тысяч.

Все примеры — живые: openpyxl — чистый Python, и песочница ставит его автоматически при импорте. Жми «Запустить» и смотри на настоящие значения и форматы.

Что такое number_format: маска поверх значения

У каждой ячейки есть значение — то, что лежит в файле, — и формат — то, как это значение показывают. Формат это обычная строка с кодами: #,##0.00 значит «разделяй тысячи пробелами и показывай два знака после точки». По умолчанию у ячейки стоит формат General — «показывай как есть».

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

ws["A1"] = "Капучино"
ws["B1"] = 250                        # число хранится как число

print("Значение:", ws["B1"].value)
print("Формат до:", ws["B1"].number_format)      # General

ws["B1"].number_format = "#,##0.00 ₽"            # маска: рубли с копейками

print("Формат после:", ws["B1"].number_format)
print("Значение не изменилось:", ws["B1"].value)

Значение: 250
Формат до: General
Формат после: #,##0.00 ₽
Значение не изменилось: 250

Разбираем маску по кусочкам. # — цифра, если она есть; , — разделитель групп разрядов; 0.00 — обязательная цифра и два знака после точки, хоть значение целое; ₽ — литерал, который просто дописывается к числу. Итог для 250: 250,00 ₽. А для 1234.5 маска даст 1 234.50 ₽ — именно то, чего мы хотели от прайса.

Почему нельзя просто записать в ячейку строку "250,00 ₽" и не возиться с форматами? Строка обманет глаз ровно до первого вычисления: SUM по столбцу с текстом посчитает ноль, сортировка выстроит цены как слова, а фильтры предложат список из сотни уникальных строк. Число под маской — это единственный вариант, при котором прайс и красиво выглядит, и остаётся рабочей таблицей.

• # — необязательная цифра: незначащие позиции в начале не рисуются;
• 0 — обязательная цифра: недостающие знаки дополняются нулями (0.00 для 5 даст 5.00);
• , — делит целую часть на группы разрядов;
• . — отделяет дробную часть; сколько нулей после неё — столько знаков видно;
• любой другой символ — литерал: ₽, шт, чел; процент % особый — он ещё и умножает показ на 100.

Формат ≠ значение: 0.15 выглядит как 15%

Самая коварная маска — процентная. Excel не хранит «пятнадцать процентов» как число 15: он хранит долю от единицы. Хочешь показать 15% — запиши 0.15 и поставь маску 0%. Формат не меняет значение ячейки: в ячейке по-прежнему 0.15, а человек видит 15%.

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

ws["A1"] = "Скидка"
ws["B1"] = 0.15                     # доля: пятнадцать сотых
ws["B1"].number_format = "0%"

print("Значение:", ws["B1"].value)
print("Маска:", ws["B1"].number_format)
# в Excel человек увидит: 15%

Значение: 0.15
Маска: 0%

Почему это удобно: формулы и сортировки работают с настоящим значением. Скидка 15% от цены считается как цена * 0.15 — и маска 0% тут ни при чём, она только про картинку. Запишешь вместо доли число 15 — получишь показательные 1500%.

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

ws["A1"] = 0.1567
ws["A1"].number_format = "0%"     # в Excel видно 16%

print("Значение под маской:", ws["A1"].value)   # не округлилось!

ws["A2"] = round(0.1567, 2)       # round() меняет само значение
print("После round:", ws["A2"].value)

Значение под маской: 0.1567
После round: 0.16

Разделитель тысяч и литералы в маске

На больших числах маска #,##0 окупается мгновенно: 42517 чашек за месяц глазами читаются как 42 517, а не как кашу из цифр. А литерал в конце — любой текст, который надо приставить к числу: ₽, шт, чел.

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

ws["A1"] = "Выручка за месяц"
ws["B1"] = 1234567.891
ws["B1"].number_format = "#,##0.00 ₽"

ws["A2"] = "Чашек продано"
ws["B2"] = 42517
ws["B2"].number_format = "#,##0"

print("B1:", ws["B1"].value, "->", ws["B1"].number_format)
print("B2:", ws["B2"].value, "->", ws["B2"].number_format)
# в Excel: 1 234 567.89 ₽ и 42 517

B1: 1234567.891 -> #,##0.00 ₽
B2: 42517 -> #,##0

У маски есть и вторая строка — для отрицательных чисел: секции разделяются точкой с запятой, и каждая описывает свой случай. Запись "#,##0.00 ₽;-#,##0.00 ₽" показывает долги и возвраты со знаком минус, а "#,##0.00 ₽;[Red]-#,##0.00 ₽" — ещё и красным цветом. openpyxl не разбирает маску по секциям — она сохраняет строку как есть, и Excel сам применит нужную секцию к каждому значению. Для убытков в отчёте это бесплатная читаемость.

Проценты в прайсе живут в связке с числами: скидка 0.15 лежит в своей ячейке под маской 0%, а цена со скидкой считается формулой =B2*(1-C2) — Excel перемножает настоящие доли, не глядя на то, что человек видит проценты. Ещё одна причина держать в ячейках значения, а не оформленный текст.

Как применить формат к целому столбцу прайса?

Формат — атрибут ячейки, а не столбца, поэтому на диапазон его навешивают циклом — ровно так же, как в уроке про шрифты и заливки мы раскладывали Font по ячейкам шапки. Пройдись iter_rows по нужным колонкам и поставь каждой ячейке свою маску.

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

data = [
    ["Товар", "Цена", "Скидка", "Остаток"],
    ["Капучино", 250, 0.1, 120],
    ["Латте", 280, 0.15, 80],
]
for row in data:
    ws.append(row)

for row in ws.iter_rows(min_row=2):
    row[1].number_format = "#,##0.00 ₽"   # Цена
    row[2].number_format = "0%"           # Скидка
    row[3].number_format = "#,##0 шт"     # Остаток

for row in ws.iter_rows(min_row=2):
    for cell in row[1:]:
        print(cell.coordinate, "|", cell.value, "|", cell.number_format)

B2 | 250 | #,##0.00 ₽
C2 | 0.1 | 0%
D2 | 120 | #,##0 шт
B3 | 280 | #,##0.00 ₽
C3 | 0.15 | 0%
D3 | 80 | #,##0 шт

Вот он, живой прогон «формат против значения»: в строке C2 значение 0.1, а увидит человек 10%. Печать показывает сырое содержимое файла — Excel покажет то же самое, но уже наряженное в маски. Обрати внимание на индексацию в row[1], row[2], row[3]: iter_rows отдаёт кортеж ячеек строки, и счёт в нём начинается с нуля, от колонки A. И маски едут в файл вместе с книгой:

from openpyxl import Workbook, load_workbook
import os

NAME = "price_format.xlsx"

wb = Workbook()
ws = wb.active
ws.append(["Товар", "Цена"])
ws.append(["Эспрессо", 120])
ws["B2"].number_format = "#,##0.00 ₽"

wb.save(NAME)
print("Файл создан:", os.path.exists(NAME))

wb2 = load_workbook(NAME)
print("Формат из файла:", wb2.active["B2"].number_format)
print("Значение из файла:", wb2.active["B2"].value)

os.remove(NAME)
print("Файл удалён:", os.path.exists(NAME))

Файл создан: True
Формат из файла: #,##0.00 ₽
Значение из файла: 120
Файл удалён: False

Маска хранится в самом xlsx — это не property Python-объекта, а часть файла. Поэтому файл с форматами можно отправить кому угодно: Excel, Google Sheets и LibreOffice покажут 120,00 ₽ без единой строки кода с их стороны.

Когда формат можно не ставить вовсе? Если данные — служебные идентификаторы: артикулы, штрихкоды, номера строк отчёта. Общий формат покажет их как есть, и это честно. Правило хорошего прайса другое: у каждого числа, которое увидит человек, должна быть своя маска — цена в рублях, скидка в процентах, количество в штуках. Пустая ячейка без формата в оформленной таблице выглядит не экономией, а недоделкой.

Собираем прайс с форматами целиком

Финальный прогон: прайс кофейни с жирной шапкой из урока 5 и рублёвой колонкой цен. Два стилевых инструмента — шрифт и числовой формат — живут в одном файле и не мешают друг другу.

from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font
import os

NAME = "pricelist_fmt.xlsx"

wb = Workbook()
ws = wb.active
ws.append(["Позиция", "Цена, шт"])
ws["A1"].font = Font(bold=True)
ws["B1"].font = Font(bold=True)

price = [("Эспрессо", 120), ("Капучино", 250), ("Латте", 280),
         ("Раф", 320), ("Какао", 260)]
for item, cost in price:
    ws.append([item, cost])

for row in ws.iter_rows(min_row=2, min_col=2, max_col=2):
    for cell in row:
        cell.number_format = "#,##0.00 ₽"

wb.save(NAME)
print("Прайс сохранён:", os.path.exists(NAME))

wb2 = load_workbook(NAME)
print("Формат B6:", wb2.active["B6"].number_format)
print("Жирная шапка:", wb2.active["A1"].font.bold)
os.remove(NAME)

Прайс сохранён: True
Формат B6: #,##0.00 ₽
Жирная шапка: True

ws["B2"] = 250
ws["B2"].number_format = "#,##0.00 ₽"

print(ws["B2"].value)
# то же самое в Excel:  250,00 ₽

Что дальше

Ты умеешь наряжать числа: рубли с копейками, проценты из долей, тысячи с пробелами. Следующий логичный гость прайса — даты: «действует с … по …» и сроки годности зёрен. В следующем уроке запишем в ячейку настоящий datetime, поставим маску DD.MM.YYYY и посчитаем, сколько дней между двумя датами. А если захочешь обрабатывать столбцы цен массово — загляни в groupby из Pandas: после Excel его сводные кажутся волшебством.

Числовой формат — это костюм на числе: значение под ним не меняется, меняется то, что видит человек.