Excel как генератор отчетов
Статья предназначена в первую очередь для консультантов и архитекторов, работающих с продуктами SAP, перед которыми стоит задача проектирования и реализации решения по подготовке отчетности в формате Excel.
В настоящее время все большую популярность набирают облачные решения для визуализации данных, демонстрируя двузначный рост год-к-году по большинству показателей. Однако не все компании - клиенты поставщиков облачных решений могут позволить себе использовать “облака” по самым разным причинам: от требований безопасности данных до недостаточной функциональности или даже более высокой стоимости владения по сравнению с on-premise.
Поэтому время от времени возникают задачи подготовки отчетности для визуализации в on-premise-инструментах. Автор долгое время работал и продолжает работать с решениями SAP, поэтому именно решения SAP (SAP BW/4, SAP S/4), как поставщики данных для отчетности, наиболее близки. Однако предлагаемый подход может быть скопирован и на другие системы-источники. Никаких препятствий к этому нет.
Задача формулируется так: реализовать on-premise решение по автоматической и регулярной подготовке отчетов по бизнес-данным SAP-систем (BW/4 или S/4).
Технические требования к отчетам:
минимальное время отклика при открытии отчета пользователем (2-4 сек)
возможность смотреть отчеты как на настольном, так и на мобильном устройстве (планшет, телефон)
комбинация табличных и графических представления данных на одной странице
несколько страниц (вкладок) с отчетами
корректное разбиение на страницы при печати на принтере/PDF.
отдельные табличные представления могут содержать большое число строк и/или столбцов (более 1 млн ячеек совокупно).
реализуют кросс-табличный формат
содержат итоги, подитоги по строкам и столбцам.
содержат иерархические группировки
содержат данные нарастающим итогом по календарным аналитикам
поддерживают функции “автофильтр”, “фиксация прокрутки” и некоторые другие функции Excel (например, комментарии к ячейкам, merge cells)
добавление статических картинок
условное форматирование ячеек
построены по csv-выгрузкам данных из исходных систем. Это означает, что отчеты содержат данные, сформированные на момент выгрузки данных из исходных систем. Чем чаще выполняется выгрузка, тем более актуальные данные в отчетах.
Наиболее близкими стандартными инструментами SAP, подходящими для этой задачи, является a)SAP BW on HANA + Broadcaster или b)SAP BW/4 HANA + SAP BI Platform + SAP Crystal Report Enterprise + Publishing.
Вариант a) подходит для клиентов, остающихся пока на решении SAP BW on HANA.
Вариант b) - для клиентов, перешедших на новое перспективное решение SAP BW/4 HANA и имеющих лицензии SAP BI Platform
Однако, оба этих варианта имеют те или иные ограничения, которые может быть крайне непросто или даже невозможно преодолеть, в т.ч. даже с добавлением “точечной” разработки (макросы VBA, ABAP-программы или кода на встроенном script-языке в Crystal Reports). Даже если в первоначальной постановке задачи нерешаемых требований нет, это не означает, что в будущем такие требования не появятся. Это означает, что лучше с самого начала выбрать подход, достаточно гибкий для решения как известных сейчас, так и неизвестных, но ожидаемых будущих требований.
Очевидно, что реализация третьего варианта, варианта с) будет с высокой долей собственной разработки отдельных компонентов целевого решения. Какие же опции могут существовать для технологических решений SAP в этом случае?
Существуют классы из библиотеки ABAP2XLSX (Apache2 licence model). Написанные на ABAP, они содержат методы по генерации XLSX-файла без использования Excel: XLSX-файлы создаются напрямую из кода на ABAP. Создав программную “обертку” для этих классов внутри S/4 или BW, вы можете генерировать XLSX-файлы с требуемым форматированием. Запускать эту генерацию возможно как диалоговом, так и в пакетном (batch) режиме, что позволяет настроить автоматическую публикацию, рассылку и пр. по расписанию или событиям.
Таким образом, все стадии процесса: извлечение данных, преобразования в формат отчета и генерация XLSX реализуются на одном языке (ABAP), и являются неотъемлемой частью хорошо знакомой в SAP-мире инфраструктуры жизненного цикла и среды выполнения ABAP-кода.
Но представляет интерес и альтернативный подход, который при определенном масштабе более эффективен и функционален. Речь идет о генерации XLSX-файла в коде на Python. Существует минимум две очень популярные python-библиотеки генерации Excel-кода: openpyxl и xlsxwriter. Это дает больше гибкости при выборе и развитии вашего решения, чем использование единственно возможной библиотеки на ABAP.
Процессы целевого решения могут выглядеть, например, так:
Данные системы-источника выгружаются на регулярной основе в текстовые csv-файлы. Файлы выгружаются в каталоги сервера приложений SAP или на выделенный файловый сервер или даже в облачный каталог, если это позволяют политики безопасности.
csv-файлы считываются Python-программой, которая затем выполняет обработку данных (расчет подитогов, итогов, расчет нарастающих итогов, объединение нескольких наборов данных, подтягивание наименований к кодам аналитик и т.п и т.д.), генерацию и публикацию XLSX-файла
Архитектура целевого решения состоит из:
SAP-системы-источника данных, которая генерирует csv-файлы. Генератором файлов может быть:
В последнем случае легко настроить считывание данных из т.н. BW-запросов, в которых нередко реализуют сложную логику расчета бизнес-показателей пользовательских отчетов. Инструмент создания BW-моделей и -запросов (SAP HANA Studio BWMT) нельзя назвать инструментом “конечного пользователя”, но тем не менее он обладает мощным функционалом, позволяющим в создании даже сложных объектов во многих случаях обходиться без программирования. Однако, верно и то, что с помощью этого инструмента добиться желаемой производительности (см. требования выше) практически не представляется возможным. Поэтому результат работы BW-запроса - [кросс-]табличное представление данных - приходится визуализировать в Excel не стандартным инструментом SAP Analysis for Excel, а просто Excel. Но XLSX-файл нужно ещё подготовить и где-то разместить. А для этого применяются.
Python-программы, использующие библиотеки:
работы с данными - pandas;
генерации XLSX-файлов - openpyxl или/и xlsxwriter;
любые другие библиотеки Python для дополнительных преобразований считанных из файлов данных;
Сервер для запуска Python-программ. При наличии лицензий, можно рассмотреть SAP Data Intelligence, но подойдут и другие решения. На самых начальных этапах, при небольшом количестве Python-программ - генераторов XLSX-файлов можно обойтись средствами планирования задач операционной системы
Полученный XLSX-файл конечный пользователь может просмотреть как в Excel, так и его opensource-аналогах (LibreOffice, OpenOffice). Последнее особенно важно для компаний, в которых не используются продукты Microsoft Office или планируется от них отказаться. Использование же стандартного on-premise SAP-инструмента для визуализации данных BW-отчетов - SAP Analysis for Excel невозможно без Excel, т.к. технически он является его addOn.
Несколько слов про центральную часть архитектуры - Python-программы. Принимая во внимание, что генерировать XLSX-файл можно и с помощью ABAP-библиотеки, в Python это делать во многих аспектах удобнее:
существенно больше программистов на Python, чем на ABAP. Python изучают чуть ли не в школах, не говоря уже о ВУЗах. Нетрудно предположить, что и уровень зарплатных ожиданий для Python также ниже при прочих равных
Существенно больше готовых и бесплатных Python-библиотек, которые выполнят необходимые преобразования и расчеты.
возможно разрабатывать Python-код для генерации XLSX-файлов без доступа к SAP-системам, практически в offline-режиме. Так удобнее отдавать работу на outsource.
проще пользоваться всеми возможностями git-репозиториев. Да, в ABAP это тоже возможно, но это гораздо менее распространено.
Более лаконичный код, чем на ABAP.
Например, при таких исходных данных для визуализации
следующий компактный код
выполняет преобразование строк в исходном массиве: разбивает строку на поля, разделенные ;. Последние 2 поля, которые по смыслу есть “показатели”, сохраняются как числа float. Первые 4 поля - это аналитики и хранятся как строки.
На выходе кода - кросс-таблица, в которой по строкам - развертка по полям A и B, а по столбцам - развертка по полям C и D. Поле E - один (из двух в исходных данных) показателей, используемый в кросс-таблице.
Если добавить код для расчета подитогов и итогов по строкам и столбцам (еще 5 - 6 строк кода), получится
Очевидно, что это уже почти готовая “матрица” для публикации в XLSX, после чего останется только применить форматирование [опять же, методами Python-библиотеки генерации XLSX] и файл готов. Файл можно публиковать средствами Python или операционной системы, и затем открывать в Excel, LibreOffice и т п.
Что касается производительности и потребления системных ресурсов при выполнении всех шагов процесса, начиная от выгрузки данных в csv из SAP и заканчивая генерацией XSLX-файла в Python-программе, то предложенная архитектура позволяет управлять распределением нагрузки, перенося ее с одних компонент на другие.
Например, если Python-код потребляет слишком много памяти при выполнении подготовки данных (слишком сложные расчеты или/и миллионы строк в выгрузке), то можно, не меняя степени параллелизации выполнения Python, и не выполняя аппаратного масштабирования, рассмотреть перенос вычислений или/и большее уплотнение данных на стороне SAP-сервера. Возможно и обратное, когда уменьшать нагрузку следует уже на SAP-сервер.
Движок генератора написан на J2SE с использованием библиотеки Apache POI. Более вменяемого и бесплатного средства для работы с Excel на момент разработки не нашлось. Поэтому, при использовании генератора в системе требуется наличие JRE версии не 1.6 и новее.
Формирование отчета заключается в сборке данных, их сериализации, добавлении дополнительных настроечных параметров и отправке в java-часть генератора. Производится выполнение jar-файла и обмен данными между ним и Python’ом через STDIN/STDOUT и внешние файлы. Настройки не требуется, скомпилированный jar файл и необходимые файлы Apache POI лежат внутри пакета и запускается автоматически.
Исходным материалом является шаблон в формате Excel-97 (xls) со специальными тегами внутри. Этот шаблон открывается и сериализованные данные расставляются в соответствии с тегами в документе. Далее сохраняется новый файл, шаблон остается неизменным. Пользователю не нужно самому заниматься сериализаций, достаточно предоставить правильно сформированный объект.
Теги представляют собой текст, экранированный специальными символами, в самих ячейках или их комментариях. Теги регистронезависимы для генератора, но их дополнительные параметры зависимы. Теги удаляются из листа после формирования отчета.
Для работы с генератором нужно всего лишь импортировать модуль:
Есть 2 фундаментальных метода, с помощью которых работает весь python-код генератора:
Базовый класс для отчетов.¶
Самым удобным способом создания новых отчетов является определение их как классов. Для этого служит класс BaseReport.
Структура объекта данных¶
Структура объекта проектировалась таким образом, чтобы обеспечить максимальную совместимость со штатным движком шаблонов Django и легко использоваться в HTML шаблонах. Он представляет собой словарь, в который в виде значений могут быть вложены другие словари и списки.
Пример структуры объекта:
На примере мы видим одиночную таблицу currency, содержащую 3 строки. И 2 вложенных таблицы: street вложена в city, city вложена country.
TEMPLATE_FILE_PATH и OUTPUT_FILE_PATH - это зарезервированные имена параметров, которые добавляются генератором автоматически. Определяют путь к исходному шаблону и путь к результирующему файлу.
Приоритет операций¶
Чтобы лучше понимать показанные в дальнейшем примеры, нужно знать в какой последовательности выполняются операции при генерации отчета:
- Горизонтальная развертка с подстановкой
- Вертикальная развертка
- Подстановка значений в строках
- Проецирование матрицы
- Наложение повторяющихся строк
- Наложение смежных регионов
Подстановка значений в ячейки¶
Для подстановки значения в ячейку используется тег $ИмяПеременной$. Знак $ дает понять, что в этом месте нужно подставить значение переменной из JSON контекста с именем ИмяПеременной.
В случае с простыми типами этого достаточно. Но если нужно подставить дату, время или представить число в отформатированном виде, то в свойствах ячейки нужно заранее установить нужный формат и стиль.
Вертикальная развертка¶
Развертка приоритетнее чем подстановка. Поэтому теги подстановки копируются и обрабатываются после подстановки. Это позволяет легко строить таблицы с заведомо известным количеством колонок.
Пример данных для развертки:
Как видно из примера, регион currency состоит из одной строки. В JSON данных currency представляет из себя список из 3-х словарей. Следовательно строка будет скопирована 3 раза. Далее в строки будут подставлены значения из словаря с соответствующим номером в списке. Первой строке первый словарь, второй строке - второй словарь и т.д. Т.е. контекст строки изменяется в зависимости от вложенности развертки и номера строки.
На следующем примере показано как работают вложенные регионы. Регион country включает в себя регионы city и street. Причем внутри региона может находится что угодно, в нашем случае это дополнительная строка.
Горизонтальная развертка¶
Горизонтальная развертка работает аналогично вертикальной, но копируются не строки, а прямоугольные регионы (не колонки). Задается развертка тегами %ИмяРегиона Ширина и %%ИмяРегиона Ширина.
Где ИмяРегиона - переменная в контексте, а Ширина - ширина копируемого региона. Прямоугольный регион начинается со строки с тегом %, заканчивается строкой %% и имеет ширину в правую сторону Ширина.
Подстановка в копируемом регионе тоже отличается. Вместе с именем переменной нужно указвать имя региона с точкой. Например, вместо $Год$ писать $Годы.Год$.
Ширина колонок не копируется, потому что сверху и снизу могут находится другие таблицы и их внешний вид может нарушиться.
Как показала практика, горизонтальная развертка плохо подходит для создания таблиц с неизвестным количеством колонок. Только для построения шапки таблицы.
Проецирование матрицы¶
Наиболее простым и мощным способом создания таблиц заранее неизвестного размера, является проецирование прямоугольной матрицы значений непосредственно на ячейки в листе. Проецирование начинается от заданной ячейки (её координаты) и до тех пор пока позволяет размерность матрицы.
Обратите внимание, что вместо словарей в списке МоиДанные находятся списки. Это связано с тем, адресация идет не по ключу, как при подстановке, а по номеру колонки.
Список ЛПУ не отобразился в результирующем файл, он мог быть вообще пустым. Он был нужен лишь для вертикальной развертки таблицы, проще говоря чтобы появились рамки. Данные все равно взялись из матрицы. См. пункт приоритет операций.
Как видно из примера, в строке региона ЛПУ нет тегов подстановки $, зато есть матрица в комментарии. Горизонтальная развертка используется только для формирования шапки.
Автовысота¶
- Ни в коем случае нельзя трогать руками высоту строки или пытаться обнулить ее.
Лучше вставить новую строку. Иначе в Excel-файл сохраняется фиксированная высота и тег не будет работать. * В свойствах ячейки нужно включить перенос по словам. * Высота определяется в строке в момент открытия в Microsoft Office или Open Office, и может визуально отличаться. Внутри Apache POI её тоже точно определить нельзя. Связано это с тем, что все они используют разные библиотеки для рендеринга шрифтов.
Форматирование по условию¶
Можно изменять цвет и стиль шрифта в ячейке в зависимости от значения переменной контекта. Пока поддержиается крайне ограниченный набор изменений и из условий только равенство.
Поддерживаемые стили директивы ШРИФТ: ЖИРНЫЙ, ПОДЧЕРКНУТЫЙ, ЗАЧЕРКНУТЫЙ.
Поддерживаемые цвета директивы ЦВЕТ: КРАСНЫЙ, ЗЕЛЕНЫЙ, ЖЕЛТЫЙ, ЧЕРНЫЙ, СИНИЙ. Недостающие можно легко добавить в пределах палитры Office 97.
Смежные регионы¶
Далее координаты ячеек из списка тегов объединяются в прямоугольники, которые и являются созданными смежными областями. Например, у нас есть 3 ячейки с координатами (3,4), (5,6), (1,2) - их область будет иметь координаты (1,2)-(5,6).
Как видно из примера, смежная область получает такие же свойства как у самой верхней левой ячейки области.
Повторение строк для каждого листа¶
© Copyright 2014, БАРС ГРуп. Revision 1691208cba87+ .
Versions latest stable Downloads htmlzip epub On Read the Docs Project Home Builds Free document hosting provided by Read the Docs.
Успех бизнеса напрямую зависит от тщательного и своевременного контроля ключевых показателей. Для отслеживания метрик компании идеально подходят дашборды. Как приборная панель автомобиля, они позволяют увидеть самые важные результаты в одном месте. Появляется возможность следить за операционной деятельностью, находить в ней проблемные зоны и получать инсайты на основе реальных измерений. Главное, нет необходимости устанавливать дополнительные программы: в этой статье — инструкция для «чайников», как сделать дашборд в Excel.
Вся нужная информация на такой интерактивной панели подана в виде графиков и диаграмм: это помогает легко воспринимать ее и быстро получать ответы на главные вопросы бизнеса с помощью объективных показателей.
В качестве целей этого инструмента можно выделить следующие:
• объединение информации из разных источников;
• создание единой структурированной системы метрик;
• интерактивное представление результатов.
Кроме того, он позволяет более подробно изучить показатели без погружения в дебри аналитических выгрузок и не требует специальных навыков и знаний от своих пользователей. То есть, если сказать простыми словами, дашборд – это (в Эксель в том числе) продвинутый вариант отчета, который содержит ключевые данные в понятном и наглядном виде.
Как это помогает бизнесу
Создание дашборда требует определенных усилий и времени. Но они с лихвой окупаются, потому что у такого формата подачи данных есть неоспоримые достоинства.
Удобный мониторинг изменений в режиме онлайн — еще одно важное достоинство интерактивных сводок для любой компании. Своевременное создание дашборда в Эксель позволит, например, вовремя заметить падение продаж по сравнению с прошлым месяцем и оперативно принять меры. Или увидеть, по каким проектам план не выполняется, какие менеджеры лучше справляются с определенным этапом в ведении сделок и так далее. Все это становится реальным благодаря сжатию большого объема информации до одной страницы.
Соавтор(ы): Jack Lloyd. Джек Ллойд — автор и редактор, пишущий о технологиях для wikiHow. Имеет более двух лет опыта в написании и редактировании статей на технические темы. Энтузиаст всего связанного с технологиями и учитель английского языка.
Из этой статьи вы узнаете, как в Microsoft Excel автоматизировать создание отчетов о данных. Здесь описывается, как в таблице Excel запросить данные из внешнего источника (MySQL, Postgres, Oracle и так далее) и создать отчет с помощью специального плагина, который свяжет таблицу Excel с внешними источниками данных.
Чтобы создать отчет о данных в таблице Excel, воспользуемся макросом, который к тому же позволяет сохранять отчеты в различных файловых форматах одним нажатием клавиши. Имейте в виду, что в Excel встроена функция, которая позволяет создавать макросы без необходимости писать программный код.
Если данные, о которых необходимо составить отчет, уже находятся в таблице Excel, автоматизируйте процесс создания отчета с помощью макросов. Макросы поддерживаются встроенной в Excel функцией, которая позволяет автоматизировать сложные и повторяющиеся задачи.
- На компьютере Mac вам, возможно, придется нажать «Файл» > «Пустая книга».
- Если у вас уже есть отчет Excel, который нужно автоматизировать, дважды щелкните по файлу отчета, чтобы открыть его в Excel.
Введите данные своей электронной таблицы, если необходимо. Если вы еще не добавили заголовки столбцов и числа, о которых нужно составить отчет, сделайте это сейчас.
- Windows — нажмите «Файл» > «Параметры» > «Настроить ленту» (на левой панели), установите флажок у «Разработчик» в нижнем правом углу (если вы не видите эту опцию, прокрутите страницу вниз), а затем нажмите «ОК». [1] X Источник информации
- Mac — нажмите «Excel» > «Параметры» > «Лента и панель инструментов», установите флажок у «Разработчик» в списке «Основные», а затем нажмите «Сохранить». [2] X Источник информации
Нажмите Разработчик . Вы найдете эту вкладку вверху окна Excel. Откроется панель инструментов «Разработчик».
- Например, если макрос будет строить график на основе доступных данных, назовите его «График1» или как-то аналогично.
- На компьютере Mac сочетанием клавиш будет ⌥ Option + ⌘ Command и еще одна клавиша (например, ⌥ Option + ⌘ Command + T ).
- Файл Excel нужно сохранить в специальном формате с поддержкой макросов.
Щелкните по OK . Это кнопка внизу окна. Настройки макроса будут сохранены и включится режим записи. С этого момента все ваши действия будут записываться (до момента, когда вы остановите запись).
- Например, чтобы выбрать данные и создать диаграмму, выделите нужные данные, нажмите «Вставка» в верхней части окна Excel, выберите тип диаграммы и отредактируйте диаграмму так, как нужно.
- Другой пример: чтобы создать макрос для построения графика из данных, которые находятся в ячейках с A1 по A12, щелкните по пустой ячейке, введите =СУММ(A1:A12) и нажмите ↵ Enter .
Щелкните по Остановить запись . Эта опция находится на панели инструментов «Разработчик». Запись будет остановлена, а все ваши действия, которые вы совершили во время записи, будут сохранены в виде отдельного макроса.
Как известно MS Excel последних версий поддерживает описание структуры документа в формате xml. Это обстоятельство позволяет создавать отчеты в Excel с помощью генерации xml-файлов. В СУБД Caché существует несколько способов создания xml. В этой статье будут рассмотрены два, возможно наиболее удобных, способа эффективной программной генерации отчетов в MS Excel: с помощью Zen Reports и с использованием класса %XML.Writer.
В качестве примера отчета MS Excel взята печатная форма учебного плана из системы управления учебным планированием, о которой здесь уже писалось, поэтому перейдём непосредственно к постановке задачи и способам её решения. Требуется получить отчёт учебного плана в формате MS Excel, который должен состоять из графика учебного процесса (титульный лист) и содержания учебного плана (перечень всех дисциплин, их характеристик и вычисляемых параметров). Фрагмент отчёта учебного плана представлен на рисунке, готовый отчёт можно посмотреть здесь.
Общая схема формирования отчёта
В Cache существует несколько способов ручного изготовления отчётов (здесь не будут рассматрены возможности полуавтоматической сборки на базе DeepSee). Самый удобный способ реализован в ZEN и включает в себя набор средств, обеспечивающий полный цикл процесса формирования отчётов в формате XHTML и PDF. Описание этого процесса можно посмотреть в документации. Тем не менее, для решения нашей задачи этот способ можно задействовать только частично.
Рассмотрим общий механизм формирования отчёта в формате MS Excel с применением как технологии ZEN, так и других возможностей Caché (см. рисунок ниже).
Данная схема формирования xls документа включает три этапа: 1) данные из базы конвертируются при помощи технологии Zen Reports или стандартной технологии Caché в xml файл (входной xml); 2) посредством механизма трансформации XSL (eXtensible Stylesheet Language) модифицируется подготовленный заранее шаблон отчёта в формате xml; 3) генерируется документ Excel (xls) путём заполнения шаблона отчёта xml, расширенного вставками XSL, данными из входного xml.
Структура входного xml-файла
- Название учебного плана
- Сумма форм контроля за весь учебный план: экзаменов; зачётов; курсовых проектов; курсовых работ
- Сумма часов по всем дисциплинам учебного плана: всего с экзаменом; всего по ГОС (государственный образовательный стандарт); аудиторных часов; КСР (самостоятельная работа на курсовой проект или работу); часов по самостоятельной работе
- Сумма часов по каждой дисциплине за каждый семестр учебного плана: часы на лекции; часы на лабораторные работы; часы на практические занятия; часы на КСР
- Сумма зачётных единиц (ЗЕ) на весь учебный план
Формирование исходного xml
Рассмотрим два способа получения исходного xml файла: при помощи класса %XML.Writer и с использованием механизма Zen Reports.
Формирование исходного xml с использованием %XML.Writer
- Создавать корневой элемент
do fWriter . RootElement ( "имя корневого элемента" )
do fWriter . EndRootElement () - Создавать элемент
do fWriter . Element ( "имя элемента" )
do fWriter . Write ( значение элемента )
do fWriter . EndElement () - Создавать атрибут
do fWriter . WriteAttribute ( "имя атрибута" , "значение атрибута" )
В задаче формирования отчёта учебного плана метод RootObject не подошел, т.к. класс дисциплины имеет ссылку сам на себя, и работа этого метода была не корректна. В связи с этим все элементы выходного xml файла были созданы вручную. Для этого был создан класс sp.Report.spExcelWriter, включающий метод genWriterData (iDSelectCur As %Integer) для генерации xml-файла, в который передаётся id выбранного учебного плана. Используя данный метод, с помощью SQL-запросов извлекаются данные из БД, и в нужном месте выполняется их вставка. После этого генерируется выходной xml файл с помощью другого метода OutputToFile(«путь\имя файла.xml»).
Формирование исходного xml с использованием механизма Zen Reports
Zen Reports является высокоуровневым механизмом извлечения данных из базы Caché и преобразования их в xml, что накладывает определённые ограничения, о которых будет сказано ниже. Данный способ предполагает создание класса Zen-отчёта через Caché-студию, наследуемый от %ZEN.Report.reportPage, в котором необходимо заполнить блок XData ReportDefinition. Более подробно о правилах формирования блока XData ReportDefinition и выборке данных посредством SQL-запроса для XML-представления можно прочитать в документации.
Zen Report предлагает использование собственного синтаксиса для описания структуры данных для генерируемого xml — это накладывает некоторые ограничения на формат выходного xml. В результате структура полученного xml файла незначительно отличается от описанной выше: в генерируемый xml файл дополнительно добавляются узлы Cicls и Blocks, в которых содержатся подузлы Cicl и Block.
Покажем некоторые особенности вывода связанных данных.
Пример 1. Передача ID выбранного учебного плана в sql запрос элемента .
Далее на место «?» передается параметр со значением переменной ..idCurr
< parameter expression = '..idCurr' />
Переменная является свойством класса ZenReport и при вызове метода генерации отчета, значение idCurr принимает значение переданного в метод параметра id текущего учебного плана.
Блоки группируются аналогично.
Изменённый формат сгенерированного XML-файла теперь имеет следующий вид.
Также изменится вызов цикла при XSL трансформациях (общий способ применения XSL трансформаций описан ниже):
Создание шаблона Excel
Перед выполнением XSL-трансформации необходимо создать шаблон документа Excel, в который будут вставляться данные из xml. Порядок создания шаблона Excel состоит из трёх шагов.
Шаг №1. В Excel создаётся внешний вид отчёта.
Шаг №2. Шаблон сохраняется в формате таблицы xml.
В приведённом фрагменте видно, что вначале создаётся список стилей, который затем используется для форматирования ячеек. Например:
На этот стиль ссылается следующая ячейка:
Элемент «Worksheet» создаёт листы в книге Excel, например:
Элемент «Table» создаёт таблицу. Таблица состоит из строк «Row», а строки в свою очередь из ячеек «Cell».
Шаг 3. Посредством любого текстового редактора вносятся изменения в структуру xml путём удаления лишних атрибутов. В нашем случае удаляются атрибуты: ss:ExpandedColumnCount = «67»; ss:ExpandedRowCount = «45»; x:FullColumns = «1»; x:FullRows = «1», так как учебный план имеет произвольное количество дисциплин, и если у элемента «Table» сохранить эти атрибуты, возникнет ошибка при генерации документа Excel из-за несоответствия количества строк и столбцов. Также желательно удалить атрибут ss:Height у , так как если строка будет сильно длинная и в ячейке будет указано «переносить по словам», то переноса по словам не будет в сгенерированном Excel-документе.
XSL-трансформация
Для использования стандартного метода трансформации (в классе %XML.XSLT.Transformer) xml-данных в формат xls требуется подготовить специальный блок xml со встроенными конструкциями XSL. В нашем случае в качестве основы для XSL взят шаблон Excel, подготовленный в предыдущем пункте. Этот шаблон нужно доработать, используя следующие конструкции XSL:
Конструкция используется для выбора каждого xml элемента заданного набора. Конструкция позволяет выводить значения выбранного узла. Ниже приведён простой пример вставки XSL в Excel шаблон:
В приведённом примере показано, что в Excel таблице во вложенном цикле идёт обращение ко всем элементам «Cicl», затем в каждом цикле (укрупнённая группа дисциплин) ко всем элементам «Block», затем в каждом блоке к элементам /Disciplines/Discipline, и после этого выводится информация соответствующая указанному полю , т.е. названия дисциплин.
После того как выполнилась вставка элементов XSL в нужные места шаблона можно приступать к процессу генерации отчёта. Для этого можно создать специальный метод в некотором классе, который будет выполнять трансформацию данных из xml формата в xls, используя подготовленный шаблон Excel, который можно разместить в блоке XData этого же класса (в приведённом ниже примере блок XData называется «xsl»). Пример этого метода приведён ниже.
Читайте также: