# Excel (v4.3)

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

| Задача | Метод | Гайд |
|--------|-------|------|
| Анализ табличных данных | `xlsx_reader.py` + pandas | -- |
| Анализ иерархических 1С-отчётов | inline state-machine в repl | см. ниже |
| Новый xlsx (данные/формулы/много листов) | библиотека: XlsxWriter или openpyxl `.save()` / `save_output` | `create_guide.md` |
| Новый xlsx с точным OOXML-контролем | XML-шаблон -> pack | `create_guide.md` |
| Редактирование xlsx | unpack -> XML -> pack | `edit_guide.md` |
| Починка формул | unpack -> fix `<f>` -> pack | `fix_guide.md` |
| Битый xlsx не открывается (битые теги, INVALID_XLSX) | `xlsx_repair.py` → *_fixed.xlsx | «Ремонт структуры» ниже |
| Проверка формул | `formula_check.py` | `validate_guide.md` |

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()`:

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

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

```python
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. Конвертируй точечно, не массово.

```python
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 или ЗН-строка попадает в клиенты.

```python
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.

```python
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)

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

## Создание

```bash
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
```

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

```bash
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-частям пакета и пересобирает контейнер.

```bash
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.

## Валидация

```bash
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-форматы

| Формат | numFmt |
|--------|--------|
| 1 234 567 | `#\ ##0` |
| 1 234,56 | `#\ ##0.00` |
| 1 234 руб. | `#\ ##0\ "руб."` |
| 12,5% | `0.0%` |
| 05.04.2026 | `DD.MM.YYYY` |

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

## Пути

| Что | Путь |
|-----|------|
| Входные файлы | `/tmp/` |
| Результат | `$OUTPUT_ROOT/` |

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

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