Статьи

Создание электронных отчетов с использованием ксилофона

В сфере ИТ есть задачи, которые выглядят совершенно непривлекательно на фоне успехов в области больших данных, машинного обучения, блокчейна и других модных достижений, но тем не менее остаются актуальными для целой армии разработчиков на протяжении десятилетий. Рассматриваемый случай — это древняя задача формирования и загрузки документов электронных таблиц (в основном MS Excel), с которой сталкивался любой, кто когда-либо писал бизнес-приложения.

Каковы общие параметры для формирования файлов Excel?

  1. VBA-макросы. В наши дни идея использования макросов чаще всего неуместна из соображений безопасности.

  2. Автоматизация Excel с помощью внешней программы через API. Это требует присутствия Excel на том же компьютере, что и программа, генерирующая отчеты Excel. Этот вариант работал бы в старые добрые времена, клиенты были в основном толстыми и были закодированы как настольное программное обеспечение. Этот вариант никогда не был особенно быстрым или надежным, и вряд ли это вариант в нынешних условиях.

  3. Прямая генерация файла XML-Excel. Как мы знаем, Excel поддерживает формат документа XML, который потенциально может быть сгенерирован / изменен любыми средствами поддержки XML. Этот файл может быть сохранен с расширением .xls, хотя, строго говоря, это не файл xls. Excel открывает его без проблем вообще. Этот подход довольно популярен, но отметим один из его недостатков — тот факт, что любое решение, основанное на прямом редактировании формата XML-Excel, является разовым взломом, которому не хватает согласованности.

  4. Наконец, генерация файлов Excel возможна с использованием библиотек с открытым исходным кодом, наиболее известным из которых является Apache POI . Разработчики Apache POI приложили большие усилия для реверс-инжиниринга двоичных форматов документов MS Office и много лет поддерживают и развивают эту библиотеку. Результат этого реверс-инжиниринга используется в Open Office для реализации возможности сохранения документов в форматах, совместимых с MS Office, среди прочего.

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

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

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

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

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

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

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

  1. Создайте процедуру вывода необходимых данных в формате XML.

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

  3. Убедитесь, что ячейки ссылаются на элементы XML-файла через выражения XPath.

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

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

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

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

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

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

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

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

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

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

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

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

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

<?xml version="1.0" encoding="UTF-8"?>
<element name="report">
  <!-- Create a worksheet -->
  <output worksheet="Report" sourcesheet="Sheet1"/>
  <!-- Column headers in left-to-right direction -->
  <iteration mode="horizontal">
    <element name="(before)">
      <!-- Empty cell in top left corner of the table -->
      <output range="A1"/>
    </element>
    <element name="column">
       <output range="B1"/>
    </element>
  </iteration>
  <!-- Rows output: top-to-bottom direction-->
  <iteration mode="vertical">
    <element name="item">
      <!-- Left-to-right inside the row -->
      <iteration mode="horizontal">
        <element name="(before)">
          <!-- Row header -->
          <output range="A2"/>
        </element>
        <!-- After the header, row data in left-to-right direction -->
        <element name="year">
          <output range="B2"/>
        </element>
      </iteration>
    </element>
  </iteration>
  <iteration>
    <element name="totals">
      <iteration mode="horizontal">
        <element name="(before)">
          <!-- Row header -->
          <output range="A3"/>
        </element>
        <!-- After the header, row data in left-to-right direction -->
        <element name="year">
          <output range="B3"/>
        </element>
      </iteration>
    </element>
  </iteration>
</element>

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

  •  element— переход в режим чтения элемента файла XML. Может быть либо корневым элементом дескриптора, либо находиться внутри iterationэлемента . Различные name атрибуты элементов могут быть установлены с использованием  атрибута, например:

    •  name="foo" — элементы с именем тега foo .

    •  name="*"  — все элементы.

    •  name="tagname[@]"  — элементы с определенным именем и значением атрибута.

    •  name="(before)" ,   name="(after)" — «виртуальные» элементы, которые предшествуют итерации и завершают итерацию.

  •  iteration— переход в режим итерации. Может быть расположен только внутри   element . Например, могут быть установлены разные параметры.

    •  mode="horizontal"  — горизонтальный режим вывода (по умолчанию вертикальный).

    •  index=0 — ограничить итерацию исключительно первым встреченным элементом.

  •  output— переход в режим вывода. Основные атрибуты следующие:

    •  sourcesheet— лист шаблона книги, который является источником выходного диапазона. Если он не определен, используется текущий (последний использованный) лист.

    •  range— диапазон шаблонов, который копируется в результирующий документ, то есть «A1: M10», или «5: 6», или «C: C». (Использование диапазонов строк типа «5: 6» в горизонтальном режиме или диапазонов столбцов типа «C: C» в вертикальном режиме приведет к ошибке).

    •  sheet — если он определен, в выходном файле создается новый лист, а выходная позиция смещается в ячейку А1 этого листа. Значение этого атрибута, равное константе или выражению XPath, подставляется в имя нового листа.

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

Что ж, пришло время скачать ксилофон и приступить к формированию отчета. Получите архив от Bintray или Maven Central (NB — возможно, будут более поздние версии, когда вы читаете эту статью). В папке / bin есть сценарий оболочки, и если вы запустите его без параметров, вы увидите подсказку о параметрах командной строки. Чтобы добиться результата, вам нужно подать все подготовленные ингредиенты на ксилофон:

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

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

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

@Grab('ru.curs:xylophone:6.1.10')
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 и SAX на этапе анализа файла с данными XML. Как мы знаем, анализатор DOM загружает весь файл в память, создает его объективное представление и позволяет вам случайным образом просматривать его содержимое (включая повторный возврат к одному и тому же элементу). Анализатор SAX никогда не помещает весь файл в память, вместо этого он обрабатывает его как «поток» элементов без возможности возврата к тому же элементу.

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

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

И, наконец, несколько ссылок для дороги: