Zen Reports и %XML.Writer для генерации отчётов Excel в Caché

Как известно 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) данные из базы конвертируются при помощи технологии ZenReport или стандартной технологии Caché в xml файл (входной xml); 2) посредством механизма трансформации XSL (eXtensible Stylesheet Language) модифицируется подготовленный заранее шаблон отчёта в формате xml; 3) генерируется документ Excel (xls) путём заполнения шаблона отчёта xml, расширенного вставками XSL, данными из входного xml.

Структура входного xml-файла

Поскольку xml используется в качестве источника данных для нашего отчёта, то структура xml-файла должна быть максимально удобной для прохождения описанных выше этапов и, в конечном счёте, для формирования отчёта. Никаких дополнительных специальных ограничений на структуру xml-файла нет.
Исходя из специфики нашей задачи и структуры базы данных, входной xml-файл должен иметь следующую структуру. Корневым элементом входного xml является – учебный план. в свою очередь содержит в себе всю информацию об учебном плане и включает следующие элементы:

• Название учебного плана
• Сумма форм контроля за весь учебный план: экзаменов; зачётов; курсовых проектов; курсовых работ
• Сумма часов по всем дисциплинам учебного плана: всего с экзаменом; всего по ГОС (государственный образовательный стандарт); аудиторных часов; КСР (самостоятельная работа на курсовой проект или работу); часов по самостоятельной работе
• Сумма часов по каждой дисциплине за каждый семестр учебного плана: часы на лекции; часы на лабораторные работы; часы на практические занятия; часы на КСР
• Сумма зачётных единиц (ЗЕ) на весь учебный план

Также в содержатся циклы , каждый из которых состоит из своих собственных элементов. Аналогично описываются остальные ветви xml-файла вплоть до дисциплин и их характеристик.

<?xmlversion="1.0" encoding="UTF-8"?>
<Curriculum>
  <CurrName>Название учебного плана</CurrName>
  <Cicl>
    <CiclName>Название цикла 1</CiclName>
    <CodeOfCicl>Код цикла</CodeOfCicl>
      <Block>
        <BlocName>Названиеблока 1</BlocName>
        <Disciplines>
          <Discipline>
            <DiscName>Названиедисциплины 1</DiscName>
            <Exam></Exam>
            <Zachet></Zachet>
            <KR></KR>
            <KP></KP>
            <chAll></chAll>
            <chGos></chGos>
            <chKsr></chKsr>
            <chAud></chAud>
            <chSamRab></chSamRab>
            <naBlock></naBlock>
            <Zet></Zet>

            <semestr1>
              <Lec></Lec>
              <Lab></Lab>
              <Pra></Pra>
              <KSR></KSR>
            </semestr1>
            . . .
          </Discipline>

          <Discipline>
            <DiscName>Названиедисциплины 2</DiscName>
            <Exam></Exam>
            <Zachet></Zachet>
            <KR></KR>
            <KP></KP>
            <chAll></chAll>
            <chGos></chGos>
            <chKsr></chKsr>
            <chAud></chAud>
            <chSamRab></chSamRab>
            <naBlock></naBlock>
            <Zet></Zet>

            <semestr1>
              <Lec></Lec>
              <Lab></Lab>
              <Pra></Pra>
              <KSR></KSR>
            </semestr1>
            . . .
          </Discipline>
          . . .
        </Disciplines>
      </Block>
      . . .
    </Cicl>
    . . .     
</Curriculum>

Формирование исходного xml

Рассмотрим два способа получения исходного xml файла: при помощи класса %XML.Writer и с использованием механизма Zen Reports.

Формирование исходного xml с использованием %XML.Writer

Описанная выше структура xml может быть получена посредством класса XML.Writer, который позволяет:

1. Создавать корневой элемент
   do fWriter.RootElement("имя корневого элемента")
   do fWriter.EndRootElement()
2. Создавать элемент
   do fWriter.Element("имя элемента")
   do fWriter.Write(значение элемента)
   do fWriter.EndElement()
3. Создавать атрибут
   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> 

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

1. Поле Name будет взято из Table1:
   <report sql="SELECT Name FROM Table1">
   <element name="A" field="Name"/>
2. Поле Name выдаст ошибку:
   <report sql="SELECT Name FROM Table1">
   <attribute name="A" field="Name"/>
3. Поле Name получится из Table2:
   <report sql="SELECT Name FROM Table1">
   <group name="Name" sql="SELECT Name FROM Table2 WHERE...">
   <element name="A" field="Name"/>
4. Поле 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.

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook 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"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <Author>Microsoft Corporation</Author>
  <LastAuthor>AlexandeR</LastAuthor>
  <LastPrinted>2012-10-31T10:28:49Z</LastPrinted>
  <Created>1996-10-08T23:32:33Z</Created>
  <LastSaved>2012-11-24T12:30:48Z</LastSaved>
  <Version>11.9999</Version>
 </DocumentProperties>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>7320</WindowHeight>
  <WindowWidth>9720</WindowWidth>
  <WindowTopX>120</WindowTopX>
  <WindowTopY>120</WindowTopY>
  <RefModeR1C1/>
  <AcceptLabelsInFormulas/>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
  <DisplayInkNotes>False</DisplayInkNotes>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <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>
  . . . 
 <Worksheet ss:Name="Титул">
  <Table>
   <Column ss:AutoFitWidth="0" ss:Width="14.25" ss:Span="66"/>
   <Row>
    <Cell ss:MergeAcross="66" ss:StyleID="s374"><Data ss:Type="String">МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ</Data></Cell>
   </Row>
   . . . 
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <PageSetup>
    <PageMargins x:Bottom="0.984251969" x:Left="0.78740157499999996"
     x:Right="0.78740157499999996" x:Top="0.984251969"/>
   </PageSetup>
   <Print>
    <ValidPrinterInfo/>
    <PaperSizeIndex>9</PaperSizeIndex>
    <HorizontalResolution>600</HorizontalResolution>
    <VerticalResolution>600</VerticalResolution>
   </Print>
   <Selected/>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>25</ActiveRow>
     <ActiveCol>74</ActiveCol>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
   <AllowSort/>
   <AllowFilter/>
  </WorksheetOptions>
 </Worksheet>
 <Worksheet ss:Name="План">
   . . . 
 </Worksheet>
</Workbook>

В приведённом фрагменте видно, что вначале создаётся список стилей, который затем используется для форматирования ячеек. Например:

  <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:

1. <xsl:for-each select = ""> </xsl:for-each>
   

2. <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 таблице во вложенном цикле идёт обращение ко всем элементам , затем в каждом цикле ко всем элементам , затем в каждом блоке к элементам /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

Исходя из специфики архитектуры МАС УУП, в которой создаются java-проекции для классов Caché, к дополнительным преимуществам %XML.Writer можно добавить возможность проецирования класса sp.Report.spExcelWriter, который формирует отчёт. Напротив, в Zen Reports получить проекцию класса отчёта, наследуемого от %ZEN.Report.reportPage, невозможно в силу того, что его методы работают с потоками.
Таким образом, использование XML.Writer целесообразно в случае жёстких требований к структуре выходного xml файла, а использование механизма Zen Reports рекомендуется при создании сложных отчётов, где в первую очередь требуется понятное описание и снижение трудоёмкости.

Автор: yakuninyy

Источник