Хабрахабр

Выгружаем данные в Excel. Цивилизованно

Есть в IT-отрасли задачи, которые на фоне успехов в big data, machine learning, blockchain и прочих модных течений выглядят совершенно непривлекательно, но на протяжении десятков лет не перестают быть актуальными для целой армии разработчиков. Речь пойдёт о старой как мир задаче формирования и выгрузки Excel-документов, с которой сталкивался каждый, кто когда-либо писал приложения для бизнеса.

Какие возможности построения файлов Excel существуют в принципе?

  1. VBA-макросы. В наше время по соображениям безопасности идея использовать макросы чаще всего не подходит.
  2. Автоматизация Excel внешней программой через API. Требует наличия Excel на одной машине с программой, генерирующей Excel-отчёты. Во времена, когда клиенты были толстыми и писались в виде десктопных приложений Windows, такой способ годился (хотя не отличался скоростью и надёжностью), в нынешних реалиях это с трудом достижимый случай.
  3. Генерация XML-Excel-файла напрямую. Как известно, Excel поддерживает XML-формат сохранения документа, который потенциально можно сгенерировать/модифицировать с помощью любого средства работы с XML. Этот файл можно сохранить с расширением .xls, и хотя он, строго говоря, при этом не является xls-файлом, Excel его хорошо открывает. Такой подход довольно популярен, но к недостаткам следует отнести то, что всякое решение, основанное на прямом редактировании XML-Excel-формата, является одноразовым «хаком», лишенным общности.
  4. Наконец, возможна генерация Excel-файлов с использованием open source библиотек, из которых особо известна Apache POI. Разработчики Apache POI проделали титанический труд по reverse engineering бинарных форматов документов MS Office, и продолжают на протяжении многих лет поддерживать и развивать эту библиотеку. Apache POI, например, используется в Libre Office для реализации сохранения документов в форматах, совместимых с MS Office.

С одной стороны, он не требует установки никакого проприетарного ПО на сервер, а с другой стороны, предоставляет богатый API, позволяющий использовать все функциональные возможности MS Office. На мой взгляд, именно последний из способов является сейчас предпочтительным для генерации MS Office-совместимых документов.

Во-первых, это Java-библиотека, и если ваше приложение написано не на одном из JVM-языков, вы ей вряд ли сможете воспользоваться. Но у прямого использования Apache POI есть и недостатки. Поэтому «в лоб» написанная процедура генерации документа быстро превращается в обильную «лапшу» трудночитаемого кода, где отсутствует разделение на модель данных и представление, трудно вносить изменения и вообще — боль и стыд. Во-вторых, это низкоуровневая библиотека, работающая с такими понятиями, как «ячейка», «колонка», «шрифт». И прекрасный повод делегировать задачу самому неопытному программисту – пусть ковыряется.

Проект Xylophone под лицензией LGPL, построенный на базе Apache POI, основан на идее, которая имеет примерно 15-летнюю историю. Но всё может быть совершенно иначе. Это Java-проект, который может работать как в качестве утилиты командной строки, так и в качестве библиотеки (если у вас код на JVM-языке — вы можете подключить её как Maven-зависимость). В проектах, где я участвовал, он использовался в комбинации с самыми разными платформами и языками – а счёт разновидностей форм, сделанных с его помощью в самых разнообразных проектах, идёт, наверное, уже на тысячи.

В процедуре выгрузки необходимо сформировать данные в формате XML (не беспокоясь о ячейках, шрифтах и разделительных линиях), а Xylophone, при помощи Excel-шаблона и дескриптора, описывающего порядок обхода вашего XML-файла с данными, сформирует результат, как показано на диаграмме: Xylophone реализует принцип отделения модели данных от их представления.

Шаблон документа (xls/xlsx template) выглядит примерно следующим образом:

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

Когда «оформительская» работа выполнена, разработчику остаётся

  1. Создать процедуру выгрузки необходимых данных в формате XML.
  2. Создать дескриптор, описывающий порядок обхода элементов XML-файла и копирования фрагментов шаблона в результирующий отчёт
  3. Обеспечить привязку ячеек шаблона к элементам XML-файла с помощью XPath-выражений.

Что такое дескриптор? С выгрузкой в XML всё более-менее понятно: достаточно выбрать адекватное XML-представление данных, необходимых для заполнения формы.

Если бы в форме, которую мы создаём, не было повторяющихся элементов с разным количеством (таких, как строки накладной, которых разное количество у разных накладных), то дескриптор выглядел бы следующим образом:

<element name="root"> <output range="A1:Z100"/>
</element>

При этом, как можно видеть из предыдущей иллюстрации, подстановочные поля, значения которых заменяются на данные из XML-файла, имеют формат ~ (тильда, фигурная скобка, XPath-выражение относительно текущего элемента XML, закрывающая фигурная скобка). Здесь root – название корневого элемента нашего XML-файла с данными, а диапазон A1:Z100 – это прямоугольный диапазон ячеек из шаблона, который будет скопирован в результат.

Естественным образом их можно представить в виде элементов XML-файла с данными, а помочь проитерировать по ним нужным образом помогает дескриптор. Что делать, если в отчёте нам нужны повторяющиеся элементы? При этом мы можем пользоваться вложенностью элементов XML, чтобы отразить сколь угодно глубокую вложенность повторяющихся элементов отчёта, как показано на диаграмме: Повторение элементов в отчёте может иметь как вертикальное направление (когда мы вставляем строки накладной, например), так и горизонтальное (когда мы вставляем столбцы аналитического отчёта).

Красными квадратиками отмечены ячейки, которые будут являться левым верхним углом очередного прямоугольного фрагмента, который пристыковывает генератор отчёта.

Возможность организовать такую итерацию тоже имеется. Есть и ещё один возможный вариант повторяющихся элементов: листы в книге Excel.

Допустим, нам надо получить сводный отчёт наподобие следующего: Рассмотрим чуть более сложный пример.

XML-представление данных для такого отчёта может выглядеть следующим образом: Пусть диапазон лет для выгрузки выбирает пользователь, поэтому в этом отчёте динамически создаваемыми являются как строки, так и столбцы.

testdata.xml

<?xml version="1.0" encoding="UTF-8"?>
<report> <column year="2016"/> <column year="2017"/> <column year="2018"/> <item name="Товар 1"> <year amount="365"/> <year amount="286"/> <year amount="207"/> </item> <item name="Товар 2"> <year amount="95"/> <year amount="606"/> <year amount="840"/> </item> <item name="Товар 3"> <year amount="710"/> <year amount="437"/> <year amount="100"/> </item> <totals> <year amount="1170"/> <year amount="1329"/> <year amount="1147"/> </totals>
</report>

Например, выводимые на лист значения я обычно записываю в атрибуты, потому что это упрощает XPath-выражения (удобно, когда они имеют вид @имяатрибута). Мы вольны выбирать названия тэгов по своему вкусу, структура также может быть произвольной, но с оглядкой на простоту конвертации в отчёт.

Шаблон такого отчёта будет выглядеть так (сравните XPath-выражения с именами атрибутов соответствующих тэгов):

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

descriptor.xml

<?xml version="1.0" encoding="UTF-8"?>
<element name="report"> <!-- Создаём лист --> <output worksheet="Отчет" sourcesheet="Лист1"/> <!-- И за ним слева направо заголовки столбцов --> <iteration mode="horizontal"> <element name="(before)"> <!-- Выводим пустую ячейку в ЛВУ сводной таблицы --> <output range="A1"/> </element> <element name="column"> <output range="B1"/> </element> </iteration> <!-- Выводим строки: итерация с режимом вывода умолчанию, сверху вниз --> <iteration mode="vertical"> <element name="item"> <!-- И по строке - слева направо --> <iteration mode="horizontal"> <element name="(before)"> <!-- Заголовок строки --> <output range="A2"/> </element> <!-- И за ним слева направо строку с данными --> <element name="year"> <output range="B2"/> </element> </iteration> </element> </iteration> <iteration> <element name="totals"> <iteration mode="horizontal"> <element name="(before)"> <!-- Заголовок строки --> <output range="A3"/> </element> <!-- И за ним слева направо строку с данными --> <element name="year"> <output range="B3"/> </element> </iteration> </element> </iteration>
</element>

Вкратце, основные элементы дескриптора означают следующее:
Полностью элементы дескриптора описаны в документации.

  • element — переход в режим чтения элемента XML-файла. Может или являться корневым элементом дескриптора, или находиться внутри iteration. С помощью атрибута name могут быть заданы разнообразные фильтры для элементов, например
    • name="foo" — элементы с именем тэга foo
    • name="*" — все элементы
    • name="tagname[@attribute='value']" — элементы с определённым именем и значением атрибута
    • name="(before)", name="(after)" — «виртуальные» элементы, предшествующие итерации и закрывающие итерацию.
  • iteration — переход в режим итерации. Может находиться только внутри element. Могут быть выставлены различные параметры, например
    • mode="horizontal" — режим вывода по горизонтали (по умолчанию — vertical)
    • index=0 — ограничить итерацию только самым первым встреченным элементом
  • output — переход в режим вывода. Основные атрибуты следующие:
    • sourcesheet —лист книги шаблона, с которого берётся диапазон вывода. Если не указывать, то применяется текущий (последний использованный) лист.
    • range – диапазон шаблона, копируемый в результирующий документ, например “A1:M10”, или “5:6”, или “C:C”. (Применение диапазонов строк типа “5:6” в режиме вывода horizontal и диапазонов столбцов типа “C:C” в режиме вывода vertical приведёт к ошибке).
    • worksheet – если определён, то в файле вывода создаётся новый лист и позиция вывода смещается в ячейку A1 этого листа. Значение этого атрибута, равное константе или XPath-выражению, подставляется в имя нового листа.

В действительности всевозможных опций в дескрипторе гораздо больше, смотрите документацию.

В папке /bin находится shell-скрипт, при запуске которого без параметров вы увидите подсказку о параметрах командной строки. Ну что же, настало время скачать Xylophone и запустить формирование отчёта.
Возьмите архив с bintray или Maven Central (NB: на момент прочтения этой статьи возможно наличие более свежих версий). Для получения результата нам надо «скормить» ксилофону все приготовленные ранее ингредиенты:

xylophone -data testdata.xml -template template.xlsx -descr descriptor.xml -out report.xlsx

Открываем файл report.xlsx и убеждаемся, что получилось именно то, что нам нужно:

Пожалуй, самый компактный полностью рабочий пример получается на языке Groovy, код в комментариях не нуждается: Так как библиотека ru.curs:xylophone доступна на Maven Central под лицензией LGPL, её можно без проблем использовать в программах на любом JVM-языке.

@Grab('ru.curs:xylophone:6.1.3')
import ru.curs.xylophone.XML2Spreadsheet
baseDir = '.'
new File(baseDir, 'testdata.xml').withInputStream { input -> new File(baseDir, 'report.xlsx').withOutputStream { output -> XML2Spreadsheet.process(input, new File(baseDir, 'descriptor.xml'), new File(baseDir, 'template.xlsx'), false, output) }
}
println 'Done.'

У класса XML2Spreadsheet есть несколько перегруженных вариантов статического метода process, но все они сводятся к передаче всё тех же «ингредиентов», необходимых для подготовки отчёта.

Как известно, DOM-парсер загружает весь файл в память целиком, строит его объектное представление и даёт возможность обходить его содержимое произвольным образом (в том числе повторно возвращаясь в один и тот же элемент). Важная опция, о которой я до сих пор не упомянул — это возможность выбора между DOM и SAX парсерами на этапе разбора файла с XML-данными. SAX-парсер никогда не помещает файл с данными целиком в память, вместо этого обрабатывает его как «поток» элементов, не давая возможности вернуться к элементу повторно.

За счёт скорости и экономичности к ресурсам SAX-парсера скорость генерации файлов возрастает многократно. Использование SAX-режима в Xylophone (через параметр командной строки -sax или установкой в true параметра useSax метода XML2Spreadsheet.process) бывает критически полезно в случаях, когда необходимо генерировать очень большие файлы. Это даётся ценой некоторых небольших ограничений на дескриптор (описано в документации), но в большинстве случаев отчёты удовлетворяют этим ограничениям, поэтому я бы рекомендовал использование SAX-режима везде, где это возможно.

Надеюсь, что способ выгрузки в Excel через Xylophone вам понравился и сэкономит много времени и нервов — как сэкономил нам.

И напоследок ещё раз ссылки:

Показать больше

Похожие публикации

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Кнопка «Наверх»