Вивантажуємо дані в 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.

На мій погляд, саме останній з методів є кращим для генерації MS Office-сумісних документів. З одного боку, він не вимагає установки ніякого пропрієтарного програмного забезпечення на сервер, а з іншого боку, надає багатий API, що дозволяє використовувати всі функціональні можливості MS Office.

Але у прямого використання Apache POI є і недоліки. По-перше, це Java-бібліотека, і якщо ваш додаток написано не на одному з JVM-мов, ви навряд чи зможете скористатися. По-друге, це низькорівнева бібліотека, що працює з такими поняттями, як «осередок», «колонка», «шрифт». Тому «в лоб» написана процедура генерації документа швидко перетворюється в багату «локшину» трудночитаемого коду, де відсутній поділ на модель даних і подання, важко вносити зміни і взагалі — біль і сором. Чудовий привід делегувати завдання самому недосвідченому програмісту – нехай колупається.

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

Читайте також  Реверс інжиніринг протоколу пульта від інверторного кондиціонера Electrolux

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

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

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

Коли «оформлювальна робота виконана, розробнику залишається

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

З вивантаженням в XML все більш-менш зрозуміло: досить вибрати адекватне XML-представлення даних, необхідних для заповнення форми. Що таке дескриптор?

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

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

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

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

Червоними квадратиками відзначені клітинки, які будуть лівим верхнім кутом чергового прямокутного фрагмента, який пристиковує генератор звіту.

Є і ще один можливий варіант повторюваних елементів: аркуші книги Excel. Можливість організувати таку ітерацію теж є.

Розглянемо трохи більш складний приклад. Припустимо, нам треба отримати зведений звіт зразок наступного:

Нехай діапазон років для вивантаження вибирає користувач, тому в цьому звіті динамічно створюваними є як рядки, так і стовпці. XML-представлення даних для такого звіту може виглядати наступним чином:

Читайте також  Приклад розрахунку коефіцієнта готовності» для IT-системи

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-висловом, підставляється в ім’я нового аркуша.
Читайте також  Глава Apple заявив, що китайські шпигунські чіпи в серверах Supermicro — вигадка

Насправді всіляких опцій в дескрипторі набагато більше, дивіться документацію.

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

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.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 і SAX парсерами на етапі розбору файлу з XML-даними. Як відомо, DOM-парсер завантажує весь файл в пам’ять цілком, будує його об’єктне представлення і дає можливість обходити його вміст довільним чином (в тому числі повторно повертаючись в один і той же елемент). SAX-парсер ніколи не поміщає файл з даними цілком в пам’ять, замість цього обробляє його як «потік» елементів, не даючи можливості повернутися до елемента повторно.

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

Сподіваюся, що спосіб вивантаження в Excel через Xylophone вам сподобався і заощадить багато часу і нервів — як заощадив нам.

І наостанок ще раз посилання:

  • исходники — тут: github.com/CourseOrchestra/xylophone
  • документація — тут: corchestra.ru/wiki/index.php?title=Xylophone
  • всі приклади коду з цієї статті — тут: github.com/inponomarev/xylophone-example.

Степан Лютий

Обожнюю технології в сучасному світі. Хоча частенько і замислююся над тим, як далеко вони нас заведуть. Не те, щоб я прям і знаюся на ядрах, пікселях, коллайдерах і інших парсеках. Просто приходжу в захват від того, що може в творчому пориві вигадати людський розум.

You may also like...

Залишити відповідь

Ваша e-mail адреса не оприлюднюватиметься. Обов’язкові поля позначені *