Oracle XML Publisher

grouping

We need to create the hierarchy of groups

<dataQuery>
<sqlStatement name="LEVEL0">
<![CDATA[
             select level a from dual connect by level < 5]]>
</sqlStatement>
<sqlStatement name="LEVEL1">
<![CDATA[
             select level b from dual connect by level < :PRM]]>
</sqlStatement>
</dataQuery>
<group name="LEVEL0" source="LEVEL0">
<element name="PRM" value="a"/>
    <group name="LEVEL1" source="LEVEL1">
        <element name="VAL" value="b"/>
    </group>
</group>

Excel:

<?for-each: //GROUP_L0?> <?PRM?>

<?for-each: GROUP_L1?> <?VAL?> <?end for-each?> <?end for-each?>

Attention: there're no slashes in the child groups.
Important: this schema in the outcome XSL sholuld look like that (look at the slashes and dots!):

     <xsl:for-each select="//LEVEL0">
      <Row ss:AutoFitHeight="0">
       <Cell>
        <Data ss:Type="String">
         <!-- Data --><xsl:value-of select=".//PRM"/><!-- /Data -->
        </Data>
       </Cell>
      </Row>
      <xsl:for-each select=".//LEVEL1">
       <Row ss:AutoFitHeight="0">
        <Cell ss:Index="2">
         <Data ss:Type="String">
          <!-- Data --><xsl:value-of select=".//VAL"/><!-- /Data -->
         </Data>
        </Cell>
       </Row>
      </xsl:for-each>
     </xsl:for-each>

Result:

1

2
	1
3
	1
	2
4
	1
	2
	3

set the attribute of a cell or line

<xsl:attribute name="ss:MergeAcross">
 <xsl:value-of select="//RPER_CNT"/>
</xsl:attribute>

operators "if" and "cycle"

<xsl:if test="//PARAM1 = 'MY_VAL'">
  <xsl:for-each select="//ARRAY">
              ...
  </xsl:for-each>
 </xsl:if>

first setup of xslt-template

We have the template downloaded from Excel as XML.
1. Remove everything till

<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"

,insert

<?xml version="1.0"?>
<!DOCTYPE xsl:stylesheet  [
 <!ENTITY newline "&amp;#10;">
]>
<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">
 <xsl:param name="_XDOCTX">#</xsl:param>
 <xsl:template match="/">
  <xsl:processing-instruction name="mso-application">
   <xsl:text>progid="Excel.Sheet"</xsl:text>
  </xsl:processing-instruction>

, remove everything after

</Workbook>

, insert

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

2. Remove attributes ExpandedRowCount и ExpandedColumnCount from node Table

text wrapping with autoheight of line

Set in stylesheet ss:WrapText="1", in "Row" tag there's any value of height, for example ss:Height="43.199999999999996"

ceo@thomsonslegacy.com

We customize Oracle e-Business Suite R12 modules with Oracle Applications Framework and providing Oracle ADF based solutions

© Thomsonslegacy.com, 2025.
If you publish the materials of the site, the reference to the source is obligatory. The site uses cookies.