"""Build the small, human-readable Excel example used for the basic block. Run: py scripts/build_simple_example.py Optional check against an independent recalculation: py scripts/build_simple_example.py --recalculated """ from pathlib import Path from datetime import datetime, timedelta import argparse import openpyxl import xlsxwriter ROOT = Path(__file__).resolve().parents[1] OUT = ROOT / 'materials' / 'excel-prostoy-primer.xlsx' CUTOFF = datetime(2026, 10, 20) BLUE, RED, GREEN = '1F3B73', 'FFE3EA', 'DDFAEC' # (заявитель, тема, дата обращения, срок в днях, дата исполнения, статус) RECORDS = [ ('Иванов И. И.', 'Справка', datetime(2026, 10, 1), 10, datetime(2026, 10, 8), 'Выполнено'), ('Петрова А. С.', 'Консультация', datetime(2026, 10, 3), 15, datetime(2026, 10, 16), 'Выполнено'), ('Сидоров П. Н.', 'Жалоба', datetime(2026, 10, 5), 30, None, 'В работе'), ('Кузнецова М. В.', 'Справка', datetime(2026, 10, 6), 10, datetime(2026, 10, 14), 'Выполнено'), ('Смирнов Д. А.', 'Обращение', datetime(2026, 10, 8), 10, None, 'В работе'), ('Волкова Е. П.', 'Консультация', datetime(2026, 10, 9), 15, datetime(2026, 10, 19), 'Выполнено'), ('Морозов В. И.', 'Справка', datetime(2026, 10, 12), 7, None, 'В работе'), ('Новикова О. С.', 'Обращение', datetime(2026, 10, 13), 10, None, 'В работе'), ('Фёдоров К. М.', 'Жалоба', datetime(2026, 10, 14), 30, None, 'В работе'), ('Гусева Т. А.', 'Справка', datetime(2026, 10, 15), 7, None, 'В работе'), ] HEADERS = ['№', 'Заявитель', 'Тема обращения', 'Дата обращения', 'Срок, дней', 'Срок исполнения', 'Дата исполнения', 'Статус', 'Проверка срока', 'Дней на исполнение'] FIRST, LAST = 3, 12 # Excel rows of the ten records CUTOFF_CELL = 'C13' # holds the fixed cut-off date FIRST_TOTAL = 16 # Excel row of the first summary line EPOCH = datetime(1899, 12, 30) # Excel serial-number origin def serial(value): """Excel serial number, required for a cached numeric result of a date formula.""" return (value - EPOCH).days def as_date(value): """Normalise a cached or recalculated cell to a date for comparison.""" if value is None: return None return value if isinstance(value, datetime) else EPOCH + timedelta(days=int(value)) def expected(): """Independent expectation for every calculated column and summary.""" deadline, control, days = [], [], [] for _, _, received, term, done, status in RECORDS: end = received + timedelta(days=term) deadline.append(end) control.append('Закрыто' if status == 'Выполнено' else ('Просрочено' if end < CUTOFF else 'В срок')) days.append((done - received).days if done else None) totals = [ ('Всего обращений', 10), ('Выполнено', 4), ('В работе', 6), ('Просрочено', 2), ('Доля просроченных среди открытых', 2 / 6), ('Среднее число дней на исполнение', 9.5), ('Суммарный срок, дней', 144), ] return deadline, control, days, totals def check(totals, recalculated=None): deadline, control, days, _ = expected() books = [(OUT, openpyxl.load_workbook(OUT, data_only=True)['Пример'], 'cached')] if recalculated: books.append((recalculated, openpyxl.load_workbook(recalculated, data_only=True)['Пример'], 'recalculated')) for path, sheet, mode in books: got_deadline = [as_date(sheet.cell(r, 6).value) for r in range(FIRST, LAST + 1)] got_days = [v if v not in ('', None) else None for v in (sheet.cell(r, 10).value for r in range(FIRST, LAST + 1))] assert got_deadline == deadline, (mode, path, 'срок исполнения', got_deadline, deadline) assert got_days == days, (mode, path, 'дней на исполнение', got_days, days) if mode == 'cached': assert [sheet.cell(r, 9).value for r in range(FIRST, LAST + 1)] == control for n, (label, value) in enumerate(totals): got = sheet.cell(FIRST_TOTAL + n, 3).value close = isinstance(got, (int, float)) and isinstance(value, (int, float)) and abs(got - value) < 1e-9 assert close or got == value, (mode, path, label, got, value) if mode == 'cached': assert as_date(sheet.cell(13, 3).value) == CUTOFF # The reference sheet must keep formulas as readable text, never as live formulas. ref = openpyxl.load_workbook(OUT, data_only=False)['Формулы'] expected_text = ['=D3+E3', '=ЕСЛИ(H3="Выполнено";"Закрыто";\nЕСЛИ(F3<$C$13;"Просрочено";"В срок"))', '=ЕСЛИ(G3<>"";G3-D3;"")', '=СЧЁТ(A3:A12)', '=СЧЁТЕСЛИ(H3:H12;"Выполнено")', '=СЧЁТЕСЛИ(I3:I12;"Просрочено")', '=СРЗНАЧ(J3:J12)'] got_text = [ref.cell(r, 3).value for r in range(3, 10)] assert got_text == expected_text, got_text return True def build(): deadline, control, days, totals = expected() book = xlsxwriter.Workbook(OUT, {'strings_to_formulas': False}) book.set_properties({ 'title': 'Простой пример с формулами', 'author': 'Бойко Кирилл', 'comments': 'Учебные вымышленные данные. Не реальные обращения граждан.', }) head = book.add_format({'bold': True, 'bg_color': '#' + BLUE, 'font_color': 'white', 'text_wrap': True, 'valign': 'vcenter', 'align': 'center'}) text = book.add_format({'font_name': 'Calibri', 'font_size': 12, 'valign': 'vcenter'}) date = book.add_format({'num_format': 'dd.mm.yyyy', 'font_size': 12}) num = book.add_format({'num_format': '0', 'font_size': 12, 'align': 'center'}) avg = book.add_format({'num_format': '0.0', 'font_size': 12}) share = book.add_format({'num_format': '0%', 'font_size': 12}) bold = book.add_format({'bold': True, 'font_size': 12}) note = book.add_format({'font_size': 11, 'text_wrap': True, 'valign': 'top', 'font_color': '#555566'}) mono = book.add_format({'font_name': 'Consolas', 'font_size': 11, 'valign': 'vcenter', 'text_wrap': True}) red = book.add_format({'bg_color': '#' + RED, 'font_color': '#9D123E'}) green = book.add_format({'bg_color': '#' + GREEN, 'font_color': '#086B40'}) sheet = book.add_worksheet('Пример') sheet.set_zoom(115) for col, width in zip('ABCDEFGHIJ', [5, 18, 15, 15, 10, 15, 15, 12, 15, 18]): sheet.set_column(col + ':' + col, width) sheet.freeze_panes(2, 0) sheet.set_row(0, 24) sheet.merge_range(0, 0, 0, 9, 'ЖУРНАЛ ОБРАЩЕНИЙ — УЧЕБНЫЙ ПРИМЕР НА 10 СТРОК', head) sheet.write_row(1, 0, HEADERS, head) sheet.set_row(1, 34) for n, (person, topic, received, term, done, status) in enumerate(RECORDS): row = FIRST + n sheet.set_row(row - 1, 20) sheet.write(row - 1, 0, n + 1, num) sheet.write(row - 1, 1, person, text) sheet.write(row - 1, 2, topic, text) sheet.write_datetime(row - 1, 3, received, date) sheet.write(row - 1, 4, term, num) sheet.write_formula(row - 1, 5, f'=D{row}+E{row}', date, serial(deadline[n])) if done: sheet.write_datetime(row - 1, 6, done, date) else: sheet.write(row - 1, 6, '', text) sheet.write(row - 1, 7, status, text) sheet.write_formula(row - 1, 8, f'=IF(H{row}="Выполнено","Закрыто",IF(F{row}<${CUTOFF_CELL[0]}${CUTOFF_CELL[1:]},' f'"Просрочено","В срок"))', text, control[n]) if days[n] is None: sheet.write_formula(row - 1, 9, f'=IF(G{row}<>"",G{row}-D{row},"")', text, '') else: sheet.write_formula(row - 1, 9, f'=IF(G{row}<>"",G{row}-D{row},"")', avg, days[n]) sheet.data_validation(f'H{FIRST}:H{LAST}', {'validate': 'list', 'source': ['Выполнено', 'В работе'], 'error_title': 'Выберите значение', 'error_message': 'Только «Выполнено» или «В работе».'}) sheet.set_row(12, 22) sheet.merge_range(12, 0, 12, 1, 'Дата среза →', bold) sheet.write_datetime(12, 2, CUTOFF, date) sheet.set_row(14, 22) sheet.write(14, 0, 'ИТОГИ', bold) for n, (label, value) in enumerate(totals): row = FIRST_TOTAL + n sheet.set_row(row - 1, 20) sheet.merge_range(row - 1, 0, row - 1, 1, label, text) fmt = share if 'Доля' in label else (avg if 'Среднее' in label else num) sheet.write(row - 1, 2, value, fmt) sheet.conditional_format(f'I{FIRST}:I{LAST}', {'type': 'text', 'criteria': 'containing', 'value': 'Просрочено', 'format': red}) sheet.conditional_format(f'I{FIRST}:I{LAST}', {'type': 'text', 'criteria': 'containing', 'value': 'Закрыто', 'format': green}) sheet.merge_range(23, 0, 23, 9, 'Все записи вымышленные. Формулы в файле записаны по стандарту (IF, COUNTIF, AVERAGE); ' 'в русском Excel они отображаются как ЕСЛИ, СЧЁТЕСЛИ, СРЗНАЧ.', note) sheet.set_row(23, 30) ref = book.add_worksheet('Формулы') for col, width in zip('ABCD', [4, 30, 34, 52]): ref.set_column(col + ':' + col, width) ref.merge_range('A1:D1', 'ТРИ ФОРМУЛЫ И ЧЕТЫРЕ ПОДСЧЁТА — ШПАРГАЛКА', head) ref.set_row(0, 26) ref.write_row(1, 0, ['№', 'Что считаем', 'Формула', 'Словами'], head) ref.set_row(1, 24) rows = [ ('Срок исполнения', '=D3+E3', 'Дата обращения плюс срок в днях.'), ('Проверка срока', '=ЕСЛИ(H3="Выполнено";"Закрыто";\nЕСЛИ(F3<$C$13;"Просрочено";"В срок"))', 'Если выполнено — «Закрыто». Иначе сравнить срок с датой среза.'), ('Дней на исполнение', '=ЕСЛИ(G3<>"";G3-D3;"")', 'Если есть дата исполнения, посчитать разницу. Иначе пусто.'), ('Сколько всего', '=СЧЁТ(A3:A12)', 'Число обращений в списке.'), ('Сколько выполнено', '=СЧЁТЕСЛИ(H3:H12;"Выполнено")', 'Считает строки со статусом «Выполнено».'), ('Сколько просрочено', '=СЧЁТЕСЛИ(I3:I12;"Просрочено")', 'Считает строки со значением «Просрочено».'), ('Среднее время', '=СРЗНАЧ(J3:J12)', 'Среднее по заполненным значениям. Пустые строки не учитываются.'), ] for n, (what, formula, words) in enumerate(rows, start=2): ref.set_row(n, 44 if '\n' in formula else 22) ref.write(n, 0, n - 1, num) ref.write(n, 1, what, text) ref.write(n, 2, formula, mono) ref.write(n, 3, words, note) ref.merge_range('A11:D12', 'Правило: сначала формулируем словами, потом пишем формулу. Если словами объяснить нельзя — ' 'формула будет считать «как-то не так».', note) ref.set_row(10, 30) ref.set_row(11, 30) book.close() return totals if __name__ == '__main__': parser = argparse.ArgumentParser() parser.add_argument('--recalculated', type=Path) args = parser.parse_args() result = build() check(result, args.recalculated) state = 'cached values + independent recalculation' if args.recalculated else 'cached values' print(f'OK: 10 rows and 7 summary values verified ({state}).')