В качестве примера отчета 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-файла нет.
Исходя из специфики нашей задачи и структуры базы данных, корневым элементом входного xml является «Curriculum», содержащий всю информацию об учебном плане и включающий следующие элементы:
- Название учебного плана
- Сумма форм контроля за весь учебный план: экзаменов; зачётов; курсовых проектов; курсовых работ
- Сумма часов по всем дисциплинам учебного плана: всего с экзаменом; всего по ГОС (государственный образовательный стандарт); аудиторных часов; КСР (самостоятельная работа на курсовой проект или работу); часов по самостоятельной работе
- Сумма часов по каждой дисциплине за каждый семестр учебного плана: часы на лекции; часы на лабораторные работы; часы на практические занятия; часы на КСР
- Сумма зачётных единиц (ЗЕ) на весь учебный план
Также в «Curriculum» содержатся циклы «Cicl», каждый из которых состоит из своих собственных элементов. Аналогично описываются остальные ветви xml-файла вплоть до дисциплин и их характеристик.
Формирование исходного xml
Рассмотрим два способа получения исходного xml файла: при помощи класса %XML.Writer и с использованием механизма Zen Reports.
Формирование исходного xml с использованием %XML.Writer
Описанная выше структура xml может быть получена посредством класса XML.Writer, который позволяет:
- Создавать корневой элемент
do fWriter.RootElement(«имя корневого элемента»)
do fWriter.EndRootElement() - Создавать элемент
do fWriter.Element(«имя элемента»)
do fWriter.Write(значение элемента)
do fWriter.EndElement() - Создавать атрибут
do fWriter.WriteAttribute(«имя атрибута», «значение атрибута»)
Кроме того, XML.Writer обладает методом, позволяющим извлекать все данные из переданного в него объекта.
Writer.RootObject(«имя объекта»)
В задаче формирования отчёта учебного плана метод 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 запрос элемента .
<report sql = «SELECT * FROM sp.cCurriculum WHERE ID = ?»>
Далее на место «?» передается параметр со значением переменной ..idCurr
<parameter expression = ‘..idCurr’/>
Переменная является свойством класса ZenReport и при вызове метода генерации отчета, значение idCurr принимает значение переданного в метод параметра id текущего учебного плана.
Пример 2. Передача параметра зависящего от результата выполнения SQL запроса, например связь Цикл – Блок:
<group sql=«SELECT * FROM sp.cCicl WHERE Curriculum = ?»>
<parameter expression = ‘..idCurr’/>
<group sql = «SELECT * FROM sp.cBlock WHERE Cicl = ?» breakOnField = «ID»>
<parameter field = «ID» />
</group>
</group>
Здесь передача «ID» осуществляется с использованием атрибута breakOnField = «ID».
Покажем выполнение группировки для «Циклов».
<group name=«Cicls» sql=«SELECT * FROM sp.cCicl WHERE Curriculum = ?» >
<parameter expression=‘..idCurr’/>
<group name=«Cicl» >
<attribute name=«CiclName» field=«Name»/>
<attribute name=«CodeOfCicl» field=«CodeOfCicl» />
…
</group>
…
</group>
Блоки группируются аналогично.
Изменённый формат сгенерированного XML-файла теперь имеет следующий вид.
<?xml version="1.0" encoding='utf-8'?> … <Cicls> <Cicl> <Blocks> <Block> <Disciplines>…</Disciplines> </ Block> </ Blocks> </Cicl> … <Cicl> <Blocks> <Block> <Disciplines>…</Disciplines> </ Block> </ Blocks> </Cicl> … </Cicls>
Также изменится вызов цикла при XSL трансформациях (общий способ применения XSL трансформаций описан ниже):
<xsl:for-each select = ”./Cicls/Cicl”><xsl:for-each>
Сформулируем некоторые правила, которые следует учитывать при проектировании структуры выходных данных.
- Поле Name будет взято из Table1:
<report sql=«SELECT Name FROM Table1»>
<element name=«A» field=«Name»/> - Поле Name выдаст ошибку:
<report sql=«SELECT Name FROM Table1»>
<attribute name=«A» field=«Name»/> - Поле Name получится из Table2:
<report sql=«SELECT Name FROM Table1»>
<group name=«Name» sql=«SELECT Name FROM Table2 WHERE…»>
<element name=«A» field=«Name»/> - Поле Name получится из Table1:
<report sql=«SELECT Name FROM Table1»>
<group name=«Name» sql=«SELECT Name FROM Table2 WHERE…»>
<attribute name=«A» field=«Name»/>
Создание шаблона Excel
Перед выполнением XSL-трансформации необходимо создать шаблон документа Excel, в который будут вставляться данные из xml. Порядок создания шаблона Excel состоит из трёх шагов.
Шаг №1. В Excel создаётся внешний вид отчёта.
Шаг №2. Шаблон сохраняется в формате таблицы xml.
В приведённом фрагменте видно, что вначале создаётся список стилей, который затем используется для форматирования ячеек. Например:
<Style ss:ID="s374"> <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/> <Font ss:FontName="Arial Cyr" x:CharSet="204" x:Family="Swiss" ss:Bold="1" ss:Italic="1"/> <Protection/> </Style>
На этот стиль ссылается следующая ячейка:
<Cell ss:MergeAcross="66" ss:StyleID="s374"><Data ss:Type="String">МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ</Data></Cell>
Элемент «Worksheet» создаёт листы в книге Excel, например:
<Worksheet ss:Name="Титул"> . . . </Worksheet>
Элемент «Table» создаёт таблицу. Таблица состоит из строк «Row», а строки в свою очередь из ячеек «Cell».
Шаг 3. Посредством любого текстового редактора вносятся изменения в структуру xml путём удаления лишних атрибутов. В нашем случае удаляются атрибуты: ss:ExpandedColumnCount = «67»; ss:ExpandedRowCount = «45»; x:FullColumns = «1»; x:FullRows = «1», так как учебный план имеет произвольное количество дисциплин, и если у элемента «Table» сохранить эти атрибуты, возникнет ошибка при генерации документа Excel из-за несоответствия количества строк и столбцов. Также желательно удалить атрибут ss:Height у <Rowss:AutoFitHeight=«0» ss:Height=«13.5»>, так как если строка будет сильно длинная и в ячейке будет указано «переносить по словам», то переноса по словам не будет в сгенерированном Excel-документе.
XSL-трансформация
Для использования стандартного метода трансформации (в классе %XML.XSLT.Transformer) xml-данных в формат xls требуется подготовить специальный блок xml со встроенными конструкциями XSL. В нашем случае в качестве основы для XSL взят шаблон Excel, подготовленный в предыдущем пункте. Этот шаблон нужно доработать, используя следующие конструкции XSL:
<xsl:for-each select = ""> </xsl:for-each>
<xsl:value-of select = ""/>
Конструкция <xsl:for-each select = «»> </xsl:for-each> используется для выбора каждого xml элемента заданного набора. Конструкция <xsl:value-of select = «»/> позволяет выводить значения выбранного узла. Ниже приведён простой пример вставки XSL в Excel шаблон:
<Table> <xsl:for-each select="Curriculum"> <xsl:for-each select="./Cicl"> <Row> <Cell> <Data ss:Type="String"><xsl:value-of select="./CiclName"/></Data> </Cell> </Row> <xsl:for-each select="./Block"> <Row> <Cell> <Data ss:Type="String"><xsl:value-of select="./BlocName"/></Data> </Cell> </Row> <xsl:for-each select="./Disciplines/Discipline"> <Row> <Cell> <Data ss:Type="String"><xsl:value-of select="./DiscName"/></Data> </Cell> </Row> </xsl:for-each> </xsl:for-each> </xsl:for-each> </xsl:for-each> </Table>
В приведённом примере показано, что в Excel таблице во вложенном цикле идёт обращение ко всем элементам «Cicl», затем в каждом цикле (укрупнённая группа дисциплин) ко всем элементам «Block», затем в каждом блоке к элементам /Disciplines/Discipline, и после этого выводится информация соответствующая указанному полю <xsl:value-ofselect=»./DiscName»/>, т.е. названия дисциплин.
После того как выполнилась вставка элементов XSL в нужные места шаблона можно приступать к процессу генерации отчёта. Для этого можно создать специальный метод в некотором классе, который будет выполнять трансформацию данных из xml формата в xls, используя подготовленный шаблон Excel, который можно разместить в блоке XData этого же класса (в приведённом ниже примере блок XData называется «xsl»). Пример этого метода приведён ниже.
ClassMethod generateReportStadyPlan(outFileName As %String) As %Status
{
set xslStream = ##class(%Dictionary.CompiledXData).%OpenId(..%ClassName(1)_ «||xsl»).Data
set xmlStream = ##class(%FileBinaryStream).%New()
set xmlStream.Filename = «Путь к файлу xml»
set outStream = ##class(%FileCharacterStream).%New()
set outStream.TranslateTable = «UTF8»
set outStream.Filename = outFileName
set sc = ##class(%XML.XSLT.Transformer).TransformStream(xmlStream, xslStream, .outStream)
if $$$ISERR(sc) quit sc
quit outStream.%Save()
}
XData xsl
{
<xsl:stylesheet version=«1.0» xmlns:xsl=«www.w3.org/1999/XSL/Transform»
xmlns=«urn:schemas-microsoft-com:office:spreadsheet»
xmlns:o=«urn:schemas-microsoft-com:office:office»
xmlns:x=«urn:schemas-microsoft-com:office:excel»
xmlns:ss=«urn:schemas-microsoft-com:office:spreadsheet»>
<xsl:template match=«/»>
<xsl:processing-instruction name=«mso-application»>
<xsl:text>progid=»Excel.Sheet»</xsl:text>
</xsl:processing-instruction>
<!-—Место вставки шаблона Excel—>
</xsl:template>
</xsl:stylesheet>
}
Сравнение Zen Reports и %XML.Writer
Механизм | Преимущества | Недостатки |
---|---|---|
Zen Reports | 1. Избавляет от лишней рутинной работы 2. Описание структуры получается более лаконичным, нет излишних нагромождений 3. Простота восприятия | Структура выходного xml хуже контролируется, приходится соблюдать определенные правила |
%XML.Writer | Можно создавать абсолютно любую структуру xml | Большая трудоёмкость описания структуры |
Исходя из специфики архитектуры МАС УУП, в которой создаются java-проекции для классов Caché, к дополнительным преимуществам %XML.Writer можно добавить возможность проецирования класса sp.Report.spExcelWriter, который формирует отчёт. Напротив, в Zen Reports получить проекцию класса отчёта, наследуемого от %ZEN.Report.reportPage, невозможно в силу того, что его методы работают с потоками.
Таким образом, использование XML.Writer целесообразно в случае жёстких требований к структуре выходного xml файла, а использование механизма Zen Reports рекомендуется при создании сложных отчётов, где в первую очередь требуется понятное описание и снижение трудоёмкости.