Перейти к содержимому
M
Все статьи
Разработка 8 мин чтения

Excel из Moodle: экспорт ведомостей и отчётов из плагина, формат и подводные камни

Как выгрузить ведомость или отчёт Moodle в Excel из плагина: \core\dataformat, MoodleExcelWorkbook, CSV с кириллицей, ведущие нули, большие выборки и ошибки.

Как выгрузить ведомость или отчёт Moodle в Excel из плагина: \core\dataformat, MoodleExcelWorkbook, CSV с кириллицей, ведущие нули, большие выборки и ошибки.

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

Два пути выгрузки

СпособЧто делаетКогда подходит
\core\dataformatПринимает колонки и поток строк, формат выбирает пользователь (CSV, Excel, ODS, JSON и другие)Плоская таблица, нужна скорость разработки и разные форматы
MoodleExcelWorkbook из lib/excellib.class.phpДаёт полный контроль над книгой: листы, форматы, ширина колонокНужна ведомость с шапкой, итогами и несколькими листами

Если пользователю нужны сырые данные для анализа, выбирайте первый вариант. Если результат печатают или сдают как документ, второй. Для печатной версии часто делают оба и добавляют PDF, о нём я писала в статье про PDF из плагина.

Способ 1. dataformat

Идея в том, что вы описываете колонки и отдаёте итератор по записям. Всё остальное, то есть заголовки HTTP, формат файла и запись строк, делает ядро. Формат — это подключаемый плагин dataformat_*, поэтому выбор доступен администратору на странице управления плагинами.

$columns = [
    'fullname' => get_string('fullname'),
    'group'    => get_string('group'),
    'record'   => 'Номер зачётной книжки',
    'grade'    => 'Оценка',
];
$rs = $DB->get_recordset_sql($sql, $params);

\core\dataformat::download_data(
    'vedomost-' . date('Y-m-d'),
    $dataformat,
    $columns,
    $rs,
    function($record, $supportshtml) {
        return [
            'fullname' => fullname($record),
            'group'    => $record->groupname,
            'record'   => $record->idnumber,
            'grade'    => $record->grade,
        ];
    }
);

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

Способ 2. Книга Excel

Когда нужен документ, который открывают и печатают, используют класс книги. Он обёртка над PhpSpreadsheet, входящей в состав Moodle, и скрывает её детали.

require_once($CFG->libdir . '/excellib.class.php');

$workbook = new MoodleExcelWorkbook('-');
$workbook->send('vedomost.xlsx');
$sheet = $workbook->add_worksheet('Ведомость');

$head = $workbook->add_format(['bold' => 1]);
$sheet->write_string(0, 0, 'Студент', $head);
$sheet->write_string(0, 1, 'Зачётная книжка', $head);
$sheet->write_string(0, 2, 'Оценка', $head);

$row = 1;
foreach ($rows as $r) {
    $sheet->write_string($row, 0, $r->name);
    $sheet->write_string($row, 1, $r->idnumber);
    $sheet->write_number($row, 2, (float)$r->grade);
    $row++;
}
$workbook->close();

Строки и столбцы нумеруются с нуля. Тип файла (старый xls или xlsx) задаётся при создании книги; допустимые значения параметра смотрите в файле excellib.class.php вашей версии Moodle. Набор свойств формата тоже описан там, и я рекомендую заглянуть в него, а не угадывать названия. Ширину колонок задают методом листа, а объединение ячеек и формулы делаются отдельными вызовами.

Подводные камни

ПроблемаПричинаЧто делать
В CSV вместо кириллицы кракозябрыExcel открывает файл не в UTF-8Отдавать настоящий Excel; для CSV добавлять отметку порядка байтов
CSV открывается в одну колонкуРазделитель не совпадает с региональными настройкамиДля российских настроек Excel ждёт точку с запятой; предложить формат Excel
У номера пропали ведущие нулиЯчейка записана как числоПисать номера методом записи строки
Длинный номер превратился в запись с экспонентойЧисло из 16 и более цифр не помещается в числовой тип ExcelХранить как текст
Ячейка с текстом пользователя ведёт себя как формулаЗначение начинается с «=», «+», «-» или «@»Записывать как строку, при необходимости убирать опасный первый символ
Даты в виде числа 45000Записано число без формата датыИспользовать метод записи даты или задать формат ячейки
Ошибка при открытии файлаНазвание листа длиннее 31 символа или содержит запрещённые знакиОбрезать и заменять знаки [ ] : * ? / \
Выгрузка падает на большой выборкеДанные загружены в память целиком или исчерпан лимит строк старого форматаЧитать через набор записей, использовать xlsx, при необходимости разбивать на листы
Видны теги HTML или сущностиВ ячейку попал текст из редактора MoodleОчистить разметку перед записью

Права и персональные данные

Выгрузка по определению увозит данные за пределы Moodle, поэтому проверка прав должна стоять прямо перед формированием файла, а не только на странице с кнопкой. Файл нельзя получить, зная адрес, если у человека нет права. Руководителю подразделения не нужны все слушатели, ему нужны свои, и фильтр должен накладываться на сервере. Выгрузки с персональными данными стоит записывать в журнал: кто, когда и что скачал. Это пригодится при разборе инцидента и в разговоре о 152-ФЗ.

Из практики

В зачётной книжке, которую я писала для колледжей, ведомость выгружается именно как документ: с шапкой, названием группы, дисциплиной и местом для подписи преподавателя, а не как «сырая» таблица. Там нужна полная книга и нужное форматирование, поэтому выбран второй способ. А отчёт для руководителей, наоборот, строится как плоская таблица с фильтрами, и ему подходит быстрый путь с выбором формата. Общая рекомендация: сначала сделайте плоскую выгрузку и покажите её заказчику, и только потом, если без оформления не обойтись, переходите к книге. Часто выясняется, что оформление нужно двум людям из двадцати.

О том, как это работает в зачётной книжке, есть страница электронной зачётной книжки.

Проверка перед выкладкой

  1. Откройте файл в Excel на рабочем компьютере заказчика, а не только на своём. Региональные настройки у них могут отличаться.
  2. Проверьте крайние случаи: пустая выборка, одна строка, фамилия с апострофом, текст с запятой и кавычкой, номер с ведущими нулями.
  3. Выгрузите максимальный реальный объём и посмотрите, сколько памяти и времени уходит.
  4. Убедитесь, что пользователь без права получает отказ, а не пустой файл.
  5. Сравните числа в файле с отчётом на экране на нескольких строках.

Большие выгрузки и фоновые задачи

Если отчёт собирается дольше времени, которое браузер готов ждать, веб-запрос закончится ошибкой, хотя сервер продолжит считать. Для длинных выгрузок делают так: пользователь нажимает кнопку, плагин ставит задачу в очередь разовых задач Moodle, задача записывает файл во временное хранилище или в файловую область плагина и сообщает пользователю уведомлением, что файл готов. Файл при этом скачивается по ссылке, которая проверяет право, а не лежит в открытом доступе. Старые файлы удаляют плановой задачей, иначе хранилище растёт.

Объём выгрузкиПодход
Сотни строкВыгрузка прямо в ответ на запрос
Тысячи строкПоток из базы, формат без жёсткого лимита, наблюдение за временем
Десятки тысяч строк и сложные расчётыФоновая задача и уведомление о готовности

Границы условные: их задают время запроса на вашем сервере и ограничения хостинга, поэтому проверяйте на реальном объёме.

Несколько листов и итоги

Книга позволяет разложить данные по листам: например, лист на группу или лист на дисциплину. Названия листов должны быть уникальными в пределах книги и не длиннее допустимого, поэтому для групп с одинаковыми длинными названиями придётся добавлять номер. Итоговые значения лучше писать формулами, а не вычислять в PHP: тогда преподаватель, поправив оценку в Excel, увидит пересчёт. Но помните, что формула вернёт число только после открытия файла в Excel, и программы чтения таблиц, которые формулы не считают, покажут пустую ячейку. Если файл нужен для автоматической обработки, пишите вычисленное значение.

Имена файлов и служебные сведения

Название файла выгрузки лучше строить из фиксированного слова и даты, а не из названия группы или ФИО: в имени файла недопустимы часть символов, и браузеры по-разному обрабатывают кириллицу. Внутри файла не оставляйте служебных идентификаторов вроде номеров записей базы, если пользователь о них не просил: они путают и создают лишние вопросы. А вот дату формирования и параметры отбора, например период и группу, стоит указать в шапке: через месяц по такому файлу можно понять, что именно в нём лежит и можно ли ему доверять.

Нужна выгрузка под вашу форму ведомости или регламент? Пришлите образец файла, как он выглядит сегодня, и укажите, откуда брать данные. Подробности о формате работы — на странице разработки плагинов Moodle.

Нужна помощь с Moodle?

Опишите задачу — расскажу, как решить её конкретно в вашем случае. Свяжитесь — разберём вместе.