От Excel-ведомости до XML: как сервис собирает приложение к диплому по шагам

В предыдущем разборе архитектура сервиса рассматривалась в целом — три модуля, разделение по языкам, запуск через Docker. Здесь разбирается сам алгоритм обработки: что происходит с данными на пути от загруженного Excel до готового XML. Изложение следует порядку конвейера.

Шаг 0. Что на входе

Сервису требуются два Excel-файла:

Ведомость с оценками — отдельный лист на каждого студента (выгрузка из деканата).
Список дисциплин — эталонный учебный план («обязательная часть»), задающий правильный порядок и названия предметов.

Задача алгоритма — наложить оценки из первого файла на структуру второго и привести результат к строгому формату.

Шаг 1. Разбор ведомости по листам

Каждый лист книги соответствует одному студенту. Функция process_student_workbook проходит по листам и для каждого:

берёт имя студента из пятого столбца;
очищает названия колонок и отсекает «шапку» — реальные данные начинаются с седьмой строки;
отбрасывает служебные строки: Всего, ПГТУ -, пустые наименования предметов;
переводит часы в зачётные единицы по правилу зач.ед = часы / 36;
нормализует зачёт: символ V интерпретируется как оценка 6.

На выходе формируется единая таблица df_stud_scores, где строки — это предметы, а столбцы — студенты.

# часы в зачётные единицы
df['часы учр'] = (df['часы учр'].astype(int) / 36).astype(int)
df.rename(columns={"часы учр": "зач ед"}, inplace=True)

# зачёт 'V' трактуем как оценку 6
df['зачет'] = df['зачет'].apply(lambda x: 6 if x == 'V' else x)

Шаг 2. Кодирование типа контроля в имя строки

Это ключевой приём алгоритма. Вместо отдельной колонки «тип» сервис записывает тип контроля прямо в индекс строки, добавляя суффикс (функция parse_rating):

зачёт или экзамен → _дисциплина_
практика → _практика_
курсовой → _курсовая_

Итоговый ключ строки выглядит так:

Математика_дисциплина_6
   ^предмет    ^тип    ^зач.ед

Дальше по всему конвейеру достаточно вызвать split('_'), чтобы определить предмет, его тип и объём, не обращаясь к дополнительным таблицам. Простое решение, которое удерживает целостность всего конвейера.

Шаг 3. Сопоставление с учебным планом

Названия в ведомости и в плане почти никогда не совпадают дословно, поэтому match_row представляет собой набор эвристик, применяемых по порядку:

прямое совпадение базового имени;
элективы вида Дисциплина (модуль) — берётся часть до скобки;
коды с точкой Б1.В.01 — сравнение по части до точки;
курсовые и факультативы — по ключевым словам;
префиксные группы вида Дисциплина * 3 — означают, что далее идут три дисциплины этой группы; счётчик count_of_prefix распределяет их по очереди.

То, что не удалось сопоставить, помечается пустым значением. Так план остаётся полным, а пропуски сразу заметны проверяющему.

Шаг 4. Подсветка проблемных ячеек

Перед тем как доверять данным, сервис выделяет подозрительные значения (highlight_problematic_cells) двумя цветами:

if cell_value is None or str(cell_value).strip() == '':
    cell.fill = yellow_fill      # пропущенное значение
elif '!' in str(cell_value) or '?' in str(cell_value):
    cell.fill = red_fill         # спорное значение

Жёлтый отмечает пустую ячейку, красный — значение со знаком ! или ?. Проверяющий работает только с выделенными ячейками, а не со всей таблицей.

Шаг 5. Сборка XML в формате «КиберДиплом 3.5.1»

Финальный этап выполняет DiplomaXMLGenerator. Здесь используется суффикс из шага 2: ключ предмет_тип_зач.ед разбирается, и запись помещается в нужную секцию XML.

Тип в индексе	Секция XML
`дисциплина`	`<Дисциплины>`
`практика`	`<Практики>`
`курсовая`	`<Курсовые>`
`факультатив`	`<Факультативы>`
`госэкзамен`	`<Госэкзамены>`

Параллельно разбирается ФИО с аккуратной обработкой составных отчеств (оглы, кызы, углы), а общие параметры — квалификация, срок обучения, председатель ГЭК — берутся из конфигурации. Дерево документа собирается через xml.etree.ElementTree и возвращается готовой строкой.

Конвейер целиком

2 Excel-файла
   ↓ разбор листов, очистка, часы → з.е.
df_stud_scores (предметы × студенты)
   ↓ кодирование типа в индекс (предмет_тип_з.е.)
   ↓ сопоставление с учебным планом (эвристики match_row)
df_final
   ↓ подсветка проблемных ячеек
   ↓ раскладка по секциям → ElementTree
XML «КиберДиплом 3.5.1»

Выводы

Кодирование метаданных в ключ строки (предмет_тип_з.е.) избавляет от лишних структур данных на всём пути обработки.
Нечёткое сопоставление названий решается не «магией», а упорядоченным списком простых эвристик.
Подсветка перед доверием к данным — отдельный осознанный шаг валидации.
Нормализация на входе (часы → з.е., V → 6) делает последующие шаги предсказуемыми.

Доступность

Проект открыт под лицензией MIT. Можно запустить собственный файл, увидеть подсветку и разобрать алгоритм по исходному коду.

Демонстрация: пгту-ртф.рф
Исходный код: github.com/nikita-konkin/diploma_supplement_service