Операции с Excel документами

Excel (v4.3)

Маршрутизация

XML-справочник: ooxml_cheatsheet.md

Принципы

• Анализ существующего → pandas в repl_execute. Один проход чтения, один проход агрегации.
• Новый файл → пиши библиотекой (XlsxWriter / openpyxl .save() / cw.save_output()); контейнер не теряется. Не собирай xlsx руками из XML и не правь <sheetData> регэкспом. Много листов = add_worksheet() / create_sheet(), не ручная синхронизация workbook.xml+rels+[Content_Types].xml.
• Изменение существующего → unpack-edit-pack (сохраняет VBA/pivot/sparklines, openpyxl round-trip их сбрасывает).
• Вычисляемая ячейка → формула + кэш-значение (XlsxWriter write_formula(..., value=...) / кэш <v> в openpyxl), чтобы значение было видно до пересчёта; не пустой <v>.
• Адреса строк → именованные переменные (data_start, total_row).

.agg() и .rename() — имена результирующих колонок

Кварг-имя в .agg(name=('col','func')) обязано быть валидным Python-идентификатором: латиница/кириллица + цифры + _. Дефис ломает парсер: Кол-во= читается как Кол - во=, и интерпретатор валится с SyntaxError: expression cannot contain assignment, perhaps you meant "=="?.

Английский snake_case в .agg(), человекочитаемые имена через .rename():

agg = df.groupby('vin').agg(
    cnt=('zn_num', 'count'),
    total=('total_cost', 'sum'),
).rename(columns={'cnt': 'Кол-во', 'total': 'Сумма, ₽'})

Альтернатива — дикт-распак, принимает любые символы сразу:

agg = df.groupby('vin').agg(**{
    'Кол-во': ('zn_num', 'count'),
    'Сумма, ₽': ('total_cost', 'sum'),
})

ASCII-операторы в исходнике

Сравнения и форматирование внутри Python-кода — только ASCII: >=, <=, !=. Юникод ≥ ≤ ≠ ловится парсером как SyntaxError, особенно в f-string'ах для красивого вывода. Юникод-символы — в финальный markdown/текст для пользователя, не в исходник.

Чтение xlsx без потери чисел

pd.read_excel(...).fillna('').values сохраняет dtype: числа остаются float, текст — str. После .astype(str) все числа становятся пустыми строками, и любой float(s) даёт NaN. Конвертируй точечно, не массово.

arr = pd.read_excel(path, header=None).fillna('').values

def to_num(v):
    try:
        return float(str(v).replace(' ', '').replace(',', '.'))
    except ValueError:
        return float('nan')

1С: иерархические отчёты («История по заказ-нарядам» и аналоги)

Структура: клиент → VIN → заказ-наряд → строки работ/запчастей. Все четыре уровня живут в col[0], числовые суммы — в правых колонках, индекс зависит от шаблона отчёта. Колонки Работ / Запчастей / Всего обычно в индексах 5 / 6 / 7, проверяй на образце.

Алгоритм: один проход, state-machine с тремя переменными cur_vin / cur_zn / cur_customer. Порядок проверок: skip-заголовки → VIN → ЗН → детальная строка → клиент. Клиент проверяется последним, иначе VIN или ЗН-строка попадает в клиенты.

import re, pandas as pd, numpy as np

arr = pd.read_excel(path, sheet_name=0, header=None).fillna('').values

RE_VIN = re.compile(r'^VIN:\s*([A-HJ-NPR-Z0-9]{17})')
RE_ZN  = re.compile(r'^Заказ-наряд\s+№(\S+)\s+от\s+(\d{2}\.\d{2})\s+/\s+(\S+)')
SKIP_EQ = {'Заказчик','Итого','Сведения об автомобиле','Заказ-наряд',
           'История по заказ-нарядам','Параметры:',''}
SKIP_PRE = ('Начало периода:','Конец периода:','Валюта отчёта:','Отбор:',
            'Работа-Номенклатура')
WORK_COL, PARTS_COL, TOTAL_COL = 5, 6, 7

cur_vin = cur_zn = None
zns, items = [], []
for row in arr:
    col0 = str(row[0]).strip()
    if col0 in SKIP_EQ or any(col0.startswith(p) for p in SKIP_PRE):
        continue
    m = RE_VIN.match(col0)
    if m:
        cur_vin, cur_zn = m.group(1), None
        continue
    m = RE_ZN.match(col0)
    if m and cur_vin:
        if cur_zn:
            zns.append(cur_zn)
        cur_zn = {
            'zn_num': m.group(1), 'date': m.group(2), 'status': m.group(3),
            'vin': cur_vin,
            'works_sum': to_num(row[WORK_COL]),
            'parts_sum': to_num(row[PARTS_COL]),
            'total':     to_num(row[TOTAL_COL]),
        }
        continue
    if cur_zn and col0 and len(col0) > 3:
        wc, pc, tc = to_num(row[WORK_COL]), to_num(row[PARTS_COL]), to_num(row[TOTAL_COL])
        if pc > 0 and (np.isnan(wc) or wc == 0):
            kind = 'part'
        elif wc > 0:
            kind = 'work'
        else:
            kind = 'other'
        items.append({
            'zn_num': cur_zn['zn_num'], 'vin': cur_vin,
            'name': col0.rstrip(', '),
            'work_cost': wc, 'parts_cost': pc, 'total_cost': tc,
            'kind': kind,
        })
if cur_zn:
    zns.append(cur_zn)

zn_df = pd.DataFrame(zns)
it_df = pd.DataFrame(items)

Контрольная сумма: zn_df['works_sum'].sum() должна сходиться с суммой work_cost по it_df[it_df.kind=='work'] (±ровные комплексные строки — норма). Расходится в 2-3 раза — индексы WORK_COL / PARTS_COL / TOTAL_COL не те, проверь образец строки рядом с первым ЗН.

Умные таблицы (Excel Tables)

Заголовок таблицы — только явные строки; ref начинается со строки текстовых заголовков. Если header-ячейка содержит формулу или дату, Excel открывает файл только через «восстановление» — для пользователя это «файл повреждён». openpyxl при этом лишь warning'ует (column headings must be strings) и сохраняет битый XML.

ws.add_table('A1:H8', {
    'name': 'tblData',
    'columns': [{'header': h} for h in ['Дата', 'Смена', 'Гос. номер']],
})

В openpyxl: сначала запиши текстовые заголовки в ячейки первой строки ref, потом ws.add_table(Table(displayName=..., ref=...)). Сводку с формулами в таблицу не заворачивай — формулы живут в строках данных, заголовок отдельной текстовой строкой. formula_check.py валидирует заголовки таблиц и знает их имена (tblData[...] — не warning).

Имена листов

Excel запрещает в именах листов символы: \ * ? : / [ ]

• Максимальная длина: 31 символ.
• В <sheet name="..."> (workbook.xml) всегда заменяй запрещённые символы на - или _.
• Пример: ОСАГО/КАСКО → ОСАГО-КАСКО, Отчёт: итоги → Отчёт - итоги.

Анализ (CLI)

python3 $SKILLS_ROOT/excel_operations_ru/scripts/xlsx_reader.py input.xlsx
python3 $SKILLS_ROOT/excel_operations_ru/scripts/explore.py input.xlsx

Создание

python3 $SKILLS_ROOT/excel_operations_ru/scripts/minimal_template.py /tmp/xlsx_work/
python3 $SKILLS_ROOT/excel_operations_ru/scripts/shared_strings_builder.py "Заголовок1" "Заголовок2" > /tmp/xlsx_work/xl/sharedStrings.xml
python3 $SKILLS_ROOT/excel_operations_ru/scripts/xlsx_pack.py /tmp/xlsx_work/ $OUTPUT_ROOT/output.xlsx
python3 $SKILLS_ROOT/excel_operations_ru/scripts/formula_check.py $OUTPUT_ROOT/output.xlsx

Редактирование

python3 $SKILLS_ROOT/excel_operations_ru/scripts/xlsx_unpack.py input.xlsx /tmp/xlsx_work/
python3 $SKILLS_ROOT/excel_operations_ru/scripts/xlsx_add_column.py /tmp/xlsx_work/ --col G --sheet "Лист1" --header "Итого" --formula '=E{row}*F{row}' --formula-rows 2:9 --numfmt '#\ ##0'
python3 $SKILLS_ROOT/excel_operations_ru/scripts/xlsx_insert_row.py /tmp/xlsx_work/ --at 5 --sheet "Лист1" --text A=Коммуналка --values B=3000 --formula 'F=SUM(B{row}:E{row})' --copy-style-from 4
python3 $SKILLS_ROOT/excel_operations_ru/scripts/xlsx_pack.py /tmp/xlsx_work/ $OUTPUT_ROOT/output.xlsx
python3 $SKILLS_ROOT/excel_operations_ru/scripts/formula_check.py $OUTPUT_ROOT/output.xlsx

Ремонт структуры (битый файл)

xlsx не открывается / BadZipFile / XMLSyntaxError / Excel сообщает о повреждении — сначала структурный ремонт, потом обычные операции. Это другой класс, чем починка формул (fix_guide.md): тут разорваны сами XML-теги.

Причина: предыдущий шаг или экспорт разорвал пары тегов в xl/worksheets/sheetN.xml / xl/workbook.xml / sharedStrings.xml. xlsx_repair.py балансирует висящие теги по всем XML-частям пакета и пересобирает контейнер.

python3 $SKILLS_ROOT/excel_operations_ru/scripts/xlsx_repair.py input.xlsx
python3 $SKILLS_ROOT/excel_operations_ru/scripts/xlsx_repair.py input.xlsx $OUTPUT_ROOT/fixed.xlsx
python3 $SKILLS_ROOT/excel_operations_ru/scripts/formula_check.py $OUTPUT_ROOT/fixed.xlsx

• Первый вызов без output — диагностика, файл не трогаем; второй — ремонт в output.
• Output ≠ input по имени (fixed.xlsx).
• Битые теги (zip цел) чинятся; битый zip-контейнер (обрыв/CRC) — exit 2, нерекаверабелен.
• После ремонта дальнейшие правки — на *_fixed.xlsx через unpack→edit→pack.

Валидация

python3 $SKILLS_ROOT/excel_operations_ru/scripts/formula_check.py file.xlsx
python3 $SKILLS_ROOT/excel_operations_ru/scripts/libreoffice_recalc.py file.xlsx /tmp/recalc.xlsx
python3 $SKILLS_ROOT/excel_operations_ru/scripts/formula_check.py /tmp/recalc.xlsx

RU-форматы

1С-экспорт: encoding="utf-8-sig", sep=";", decimal=",".

Пути

Перед отдачей

• formula_check.py -> exit code 0
• Нет ошибок формул
• Для EDIT: те же листы что на входе
• present_files