logo

10 июн. 2011 г.

BIP: XSLT шаблоны

Oracle BIPublisher позволяет генерировать отчеты в формате Excel разными способами:
1) RTF шаблон разметки -> XSL-FO шаблон + XML данных -> FO шаблон -> MHT/HTML файл, который открывается в Excel

2) Excel шаблон -> XSL шаблон + XML данных -> XML с данными и правилами их отображения на конкретный Excel шаблон -> native Excel

3) XSL-шаблон -> XSL шаблон + XML данных -> XML с данными и с учетом схемы, понимаемой Excel – "Таблица XML"

Сегодня я опишу процесс создания 3-го вида шаблонов.

В качестве примера я использую отчет "Quarterly Income Statement" из стандартной поставки BIPublisher 10.1.3.4.1.
Сформирую отчет в формате Excel используя готовый RTF шаблон.


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


Измененный отчет я сохраню в формате "Таблица XML".


Теперь полученный файл beauty_template.xml я переименую в beauty_template_from.xsl.
То есть создам исходную копию, а также определю расширением файла, что он действительно является XSL шаблоном.

Далее я покажу как из файла beauty_template_from.xsl создать beauty_template_to.xsl (все файлы, используемые в этом примере, приведены в конце поста).
Сразу оговорюсь, что приведенный метод крайне упрощен и направлен лишь на то, чтобы максимально быстро начать разрабатывать шаблоны данного типа. Для оптимизации создаваемых шаблонов да и для более глубокого понимания принципов их действия, настоятельно советую почитать какой-либо учебник по языкам XSLT и XPath.

Открываем файл beauty_template_from.xsl в своем любимом XML редакторе (я вот обожаю Altova XMLSpy – считайте это рекламой ;) ), сохраняем его как beauty_template_to.xsl и начинаем править.

Удаляем первые 2 строки:

<?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">
….
….
….
</Workbook>

Помещаем ПЕРЕД узлом Workbook следующий код:

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fo="http://www.w3.org/1999/XSL/Format" 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:ora="http://www.oracle.com/XSL/Transform/java/" xmlns:xdofo="http://xmlns.oracle.com/oxp/fo/extensions" xmlns:xdoxslt="http://www.oracle.com/XSL/Transform/java/oracle.apps.xdo.template.rtf.XSLTFunctions" xmlns:xdoxliff="urn:oasis:names:tc:xliff:document:1.1" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:msxsl="urn:schemas-microsoft-com:xslt">

<xsl:template match="/">
<xsl:processing-instruction name="mso-application">
<xsl:text>progid="Excel.Sheet"</xsl:text>
</xsl:processing-instruction>

И помещаем ПОСЛЕ узла Workbook код:

</xsl:template>
</xsl:stylesheet>

Этим мы добились:
1) Наш файл шаблона стал полноценным XSLT-документом (за счет корневого узла "xsl:stylesheet"; кстати, пусть версия 2.0 вас не обманывает – далеко не все конструкции спецификации XSL 2.0 поддерживаются используемым в BIPublisher XSL-трансформатором)
2) Создана "точка входа" для работы XSL-трансформатора – базовая функция "/".
3) Получившийся после XSL-трансформации документ будет содержать в начале инструкцию <?mso-application progid="Excel.Sheet"?>, что позволит автоматически открывать XML файл в MS Excel.

Итак, на данном этапе мы уже получили работоспособный XSL-шаблон, который правда не позволяет динамически менять выводимые данные – в нашем шаблоне еще нет инструкций подставления данных из XML-файла с данными для отчета.

Начнем править основной узел нашего шаблона – Workbook.
Оставьте без изменений узлы DocumentProperties, ExcelWorkbook, Styles

А вот узел Worksheet (или узлы, если у вас несколько Листов документа) рассмотрим детальнее.

Узел Names содержит список все именованных диапазонов в рамках текущего листа. Так как мы добавили автофильтр, то можем видеть именованный диапазон _FilterDatabase. Оставим его без изменений.

Узел Table содержит непосредственно описание табличной части листа. Важно удалить атрибут ss:ExpandedRowCount данного узла – иначе впоследствии, если заданное атрибутом значение будет меньше реального кол-ва строк отчета, Excel не позволит открыть сформированный отчет.
Далее, внутри таблицы видим множество узлов Column и Row. Column оставляем без изменения (пока не рассматриваем случай кросс-таблицы с динамически формируемым кол-вом столбцов).
А вот часть узлов Row мы будем удалять:
Определите с какого узла Row начинается вывод непосредственно данных (то, что мы будем извлекать из XML-файла, формируемого BIPublisher’ом). Оставьте только один такой узел, остальные удаляйте.
Добавьте в оставшийся узел Row атрибут ss:AutoFitHeight="1" , это задаст автовыравнивание строк по высоте.
Также удалите из всех подузлов Cell строки Row с данными вхождение элемента <NamedCell ss:Name="_FilterDatabase"/> - он определяет именованный диапазон автофильтра, в нашем примере его лучше убрать для экономии объема формируемого отчета (Но оставьте этот элемент для узла Row заголовочной строки).

Узел Table теперь должен выглядеть так:

<Table ss:ExpandedColumnCount="5" x:FullColumns="1" x:FullRows="1">
<Column ss:Width="148.5"/>
<Column ss:AutoFitWidth="0" ss:Width="86.25"/>
<Column ss:AutoFitWidth="0" ss:Width="83.25"/>
<Column ss:AutoFitWidth="0" ss:Width="84"/>
<Column ss:AutoFitWidth="0" ss:Width="80.25"/>
<Row ss:Height="36">
<Cell ss:StyleID="s60"><Data ss:Type="String">Quarterly Income Statement</Data></Cell>
<Cell ss:StyleID="s60"/>
<Cell ss:StyleID="s61"><Data ss:Type="String">Date:27-NOV-2007 22:48:33</Data></Cell>
<Cell ss:StyleID="s62"/>
<Cell ss:StyleID="s62"/>
</Row>
<Row ss:AutoFitHeight="0" ss:Height="14.625">
<Cell ss:StyleID="s63"><Data ss:Type="String">Vision Operations (USA)</Data></Cell>
<Cell ss:StyleID="s63"/>
<Cell ss:StyleID="s61"><Data ss:Type="String">Page:1 </Data></Cell>
<Cell ss:StyleID="s62"/>
<Cell ss:StyleID="s62"/>
</Row>
<Row ss:AutoFitHeight="0" ss:Height="14.625">
<Cell ss:StyleID="s63"><Data ss:Type="String">Current Period: Nov-07</Data></Cell>
<Cell ss:StyleID="s63"/>
<Cell ss:StyleID="s61"/>
<Cell ss:StyleID="s62"/>
<Cell ss:StyleID="s62"/>
</Row>
<Row ss:AutoFitHeight="0" ss:Height="14.625">
<Cell ss:StyleID="s63"/>
<Cell ss:StyleID="s63"/>
<Cell ss:StyleID="s59"/>
<Cell ss:StyleID="s62"/>
<Cell ss:StyleID="s62"/>
</Row>
<Row ss:AutoFitHeight="0" ss:Height="16.875">
<Cell ss:StyleID="s64"><ss:Data ss:Type="String" xmlns="http://www.w3.org/TR/REC-html40"><Font html:Color="#000000">Currency: </Font><B><Font html:Color="#000000">USD</Font></B></ss:Data></Cell>
<Cell ss:StyleID="s64"/>
<Cell ss:StyleID="s64"/>
<Cell ss:StyleID="s64"/>
<Cell ss:StyleID="s64"/>
</Row>
<Row ss:Height="24.75">
<Cell ss:StyleID="s67"><NamedCell ss:Name="_FilterDatabase"/></Cell>
<Cell ss:StyleID="s68"><Data ss:Type="String">Same Quarter Ending Nov-06</Data><NamedCell ss:Name="_FilterDatabase"/></Cell>
<Cell ss:StyleID="s68"><Data ss:Type="String">Current Quarter Ending Nov-07</Data><NamedCell ss:Name="_FilterDatabase"/></Cell>
<Cell ss:StyleID="s68"><Data ss:Type="String">Year-to-Date  Ending Nov-06</Data><NamedCell ss:Name="_FilterDatabase"/></Cell>
<Cell ss:StyleID="s68"><Data ss:Type="String">Year-to-Date Ending Nov-07</Data><NamedCell ss:Name="_FilterDatabase"/></Cell>
</Row>
<Row ss:AutoFitHeight="1">
<Cell ss:StyleID="s65"><Data ss:Type="String">Revenue</Data></Cell>
<Cell ss:StyleID="s66"></Cell>
<Cell ss:StyleID="s66"></Cell>
<Cell ss:StyleID="s66"></Cell>
<Cell ss:StyleID="s66"></Cell>
</Row>
</Table>

Посмотрим на наши XML-данные:

<MasterReport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:fsg="http://www.oracle.com/fsg/2002-03-20/"
xsi:schemaLocation="http://www.oracle.com/2002-03-20/fsg.xsd">
….
….
….
<fsg:RptDef RptId="p1001" RptDetName="No specific Company requested">
<fsg:RptLine RptCnt="p1001" RowCnt="r100001" LinCnt="l100001">
<fsg:RptCell ColCnt="c1000">Revenue</fsg:RptCell>
</fsg:RptLine>
<fsg:RptLine RptCnt="p1001" RowCnt="r100002" LinCnt="l100002">
<fsg:RptCell ColCnt="c1000"> Hardware Revenue </fsg:RptCell>
<fsg:RptCell ColCnt="c1001">15,398,120</fsg:RptCell>
<fsg:RptCell ColCnt="c1002">24,150,192</fsg:RptCell>
<fsg:RptCell ColCnt="c1003">39,628,954</fsg:RptCell>
<fsg:RptCell ColCnt="c1004">73,614,532</fsg:RptCell>
</fsg:RptLine>
<fsg:RptLine RptCnt="p1001" RowCnt="r100003" LinCnt="l100003">
<fsg:RptCell ColCnt="c1000"> Support Revenue </fsg:RptCell>
<fsg:RptCell ColCnt="c1001">7,321,697</fsg:RptCell>
<fsg:RptCell ColCnt="c1002">1,034,440</fsg:RptCell>
<fsg:RptCell ColCnt="c1003">17,490,142</fsg:RptCell>
<fsg:RptCell ColCnt="c1004">3,695,656</fsg:RptCell>
</fsg:RptLine>
….
….
….
</fsg:RptDef>
</MasterReport>

Мы будем выводить в строках таблицы Excel данные по каждому узлу fsg:RptLine формируемого BIPublisher файла данных.
Для этого нужно узел Row нашего шаблона "обернуть" в конструкцию

<xsl:for-each select="MasterReport/fsg:RptDef/fsg:RptLine">

</xsl:for-each>

Так как наш XML файл данных содержит пространство имен в именах узлов, то следует его определить в рамках нашего шаблона: добавьте в верхний узел - xsl:stylesheet – новый атрибут, определяющий пространство имен fsg
xmlns:fsg=http://www.oracle.com/fsg/2002-03-20/


Сделанным мы добились того, что шаблон будет формировать строку таблицы столько раз, сколько узлов fsg:RptLine в файле данных.
Теперь определим подстановку значений в ячейки строк из соответствующих атрибутов/подузлов XML файла данных.
Для этого достаточно внутри узла Cell прописать следующую конструкцию:
<Data ss:Type="String"><xsl:value-of select="fsg:RptCell[1]"/></Data>

Где ss:Type задает тип данных ячейки, для простоты воспользуемся Текстовым типом;
xsl:value-of select – конструкция XSL для обращения к конкретным узлам XML файла, по отношению к которому применяется данный шаблон, а значение "fsg:RptCell[1]" (по сути – Xpath-инструкция) говорит что нужно взять значение первого по порядку узла fsg:RptCell, вложенного в узел текущей итерации xsl:for-each.
Таким образом, узел Row с данными будет выглядеть так:

<xsl:for-each select="MasterReport/fsg:RptDef/fsg:RptLine">
<Row ss:AutoFitHeight="1">
<Cell ss:StyleID="s65"><Data ss:Type="String"><xsl:value-of select="fsg:RptCell[1]"/></Data></Cell>
<Cell ss:StyleID="s66"><Data ss:Type="String"><xsl:value-of select="fsg:RptCell[2]"/></Data></Cell>
<Cell ss:StyleID="s66"><Data ss:Type="String"><xsl:value-of select="fsg:RptCell[3]"/></Data></Cell>
<Cell ss:StyleID="s66"><Data ss:Type="String"><xsl:value-of select="fsg:RptCell[4]"/></Data></Cell>
<Cell ss:StyleID="s66"><Data ss:Type="String"><xsl:value-of select="fsg:RptCell[5]"/></Data></Cell>
</Row>
</xsl:for-each>

На этом шаблон отчета можно считать завершенным.

Выложим созданный XSL шаблон в BIPublisher.


Важно указать верный тип шаблона – "Таблица стилей XSL (XML)". "(XML)" говорит о том, что после XSL-трансформации будет создан XML-документ.

Запустим отчет на выполнение.


Укажем, что сформированный файл нужно открывать в MS Excel (если у вас IE, то файл откроется сразу в MS Excel; я пользуюсь браузером Firefox, который полученный XML несмотря на инструкции mso-progid открывает как обычный XML, чтобы это изменить – попробуйте это решение)


Вуаля, симпатичный экселевский файл.

Из минусов такого подхода:
1) При большом количестве строк с данными отчет сильно "разбухает" в объеме – помогает после открытия отчета сохранить его как "Книга MS Excel".
2) Нет возможности применять ряд специфичных функций Экселя (макросы, графики, диаграммы, таблицы среза).

Из плюсов:
1) Полная управляемость логикой формирования разметки отчета – так как используется открытая спецификация XSL (преимущества перед native Excel шаблонами BIPublisher, которые являются закрытым стандартом).
2) Значительные преимущества перед RTF шаблонами BIPublisher, за счет использования "родного" для Экселя типа документа – "Таблица XML", в отличие от MHT/HTML-формата, получаемого из RTF шаблона и неявно преобразуемого в формат книги Экселя.

P.S. Ссылка на архив с файлами примера.

6 комментариев:

  1. Все очень круто! Спасибо!
    Говорят так же что некоторые ручные операции можно сделать в XLS Processor Engine for Oracle® BI Publisher Free!
    Но меня больше интересует, шаблоны 2-ого вида! Был бы очень признателен статье или ссылке!!

    ОтветитьУдалить
  2. Например, тут - http://obi2ru.blogspot.com/2010/06/bip-real-excel-template.html

    ОтветитьУдалить
  3. Где ж вы этот Free Edition нарыть-то умудрились, ему года 3 уже и выложен он был недели на 2 :D

    ОтветитьУдалить
  4. Добрый день. подскажите если я не использую в своем xml fsg?

    ОтветитьУдалить
  5. Добрый день!
    Да, дурацкие данные взял в качестве примера...
    Смело удаляйте и сам namespace и все вхождения его в XSL-шаблоне.
    Если будут трудности - пишите в личку.

    ОтветитьУдалить
    Ответы
    1. не работает. 11 BIp может влиять? хотя скорей всег оу меня в коде трабл

      Удалить