Tutorial Excel report with Transformation XSLT

[Former Member]

XSLT - is a language for transforming XML. In this article we take XML document with empty table then fill it and pass it to EXCEL. Technically, it is serialization.

Advantages: speed, flexibility.

XML - put it simply, is a syntax, which we use to mark data. In our case tables for Excel marked with Excel XML processor.

As it says in Wikipeida:

XSL (Extensible Stylesheet Language) is a family of languages used to transform and render XML docum...

XPath (XML Path Language), a non-XML language for addressing the components (elements, attributes, a...

Simple transformations, or ST, is a proprietary SAP programming language that describes the transformation of ABAP data to XML (serialization) and from XML to ABAP data (deserialization).

Comparision: !=, <=, <, =, >, >=. Logical operators: not(), or, and.

 

Content

1 Basic
2 Nested loops
3 Set row position
4 Dynamic columns
5 Rows merging
6 if–then-else
7 Formulas
8 External method call
9 Templates
10 Two tables associated by the key.
11 "select" with filter for structure
12 Advices
13 Annex 1. Classic Dynpro output
14 Annex 2. WebDynpro output
15 links

 

Basic

1) Lets create table in Excel:



 

Save it like "Tables XML 2003 (*.xml)". it is important.

We open this file and copy everything except first lines:

<?xml version="1.0"?>

<?mso-application progid="Excel.Sheet"?>

2) In SAP GUI we create XSLT: right mouse click at package

Create->Other(1)->Transformation->XSLT

<xsl:transform version="1.0"

  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"

  xmlns:sap="http://www.sap.com/sapxsl"

>



  <xsl:strip-space elements="*"/>



  <xsl:template match="/">

		  HERE WE INSERT OUR COPY OF EXCEL FILE

  </xsl:template>



</xsl:transform>

3)  Find this:

<Worksheet ss:Name="Лист1"> 

   <Table ss:ExpandedRowCount="2" >

In Table tag just remove rows limit attribute: ss:ExpandedRowCount="2"

4) Find required ROW and place it inside loop:

<xsl:for-each select="//TAB1/*">   - TAB1 is a SOURCE parameter for CALL TRANSFORMATION

   here <ROW></ROW> which we want to repeat after header.

</xsl:for-each>

We pass DATA as an internal table from ABAP:

"itab

DATA: lt_t TYPE TABLE OF ls_t.

...

CALL TRANSFORMATION ('ZCH_NESTED_ITABS')

        SOURCE

          tab1 = lt_t

        RESULT XML

          l_ostream.

Note: Dictionary objects is not required.

5) In our ROW we insert CELL with DATA tag and out tab1 - abap table:

<Cell>

<Data ss:Type="Number">

  <xsl:value-of select="INDEX"/>     - index is a field in out TAB1 internal table

</Data>

</Cell>

Note: field names must be in UPPER CASE.

6) See Output of XSLT in Annex 1,2.

 

Nested loops

For example: we have two itabs:  T1 and nested T2. In nested T2 we must not use "//".

         <xsl:for-each select="//T1/*">

            <xsl:for-each select="T2/*"> - here without // becouse of T1/T2

              <Row>

                <Cell>

                  <Data ss:Type="Number"><xsl:value-of select="INDEX"/></Data>

                </Cell>

              </Row>

            </xsl:for-each>

          </xsl:for-each>

Set row position

<xsl:for-each select="//DATA/*">

   <xsl:variable name="ops_row" select="2 + position()"/>    - that is how we create varibles

   <Row ss:Index="{$ops_row}">     <!-- {} - attribute value template -->

Dynamic columns

<Table>        <!-- without ss:ExpandedColumnCount -->

  <Column/>    <!-- Column tag set Style for columns, we loopt it first -->

  <xsl:for-each select="//T2/*">

    <Column/>

  </xsl:for-each>

<Row>                      <!-- one row with T2 columns -->

  <Cell>

  </Cell>

  <xsl:for-each select="//T2/*">

    <Cell>

    </Cell>

  </xsl:for-each>

</Row>

       <!-- dynamic rows and dynamic columns -->

<xsl:for-each select="//T1/*">  <!-- loop at rows -->

  <Row>

    <xsl:for-each select="//T2/*"> <!-- loop at columns -->

      <Cell ss:Index="{1 + position()}">  </Cell>

    </xsl:for-each>

  </Row>

</xsl:for-each>

Rows merging

We will look at the case with two levels merging. We have itab T1(num) <- nested T2(org) <- nested T3(plans).

<xsl:for-each select="//T1/*">

  <xsl:variable name="t1_num" select="NUM"/>  <!-- inner loop T2 hide fields of T1 -->

    <xsl:variable name="t1_count" select="COUNT"/>

  <xsl:for-each select="T2/*">

    <xsl:variable name="t2_count" select="COUNT"/>

    <xsl:variable name="t2_pos" select="position()"/>

    <xsl:variable name="t2_org" select="ORG"/>

  <xsl:for-each select="T3/*">

    <Row>

      <xsl:if test="($t2_pos=1) and (position()=1)"> <!-- top cell with merge and skip below ones-->

        <Cell ss:MergeDown="{$t1_count - 1}"><Data ss:Type="String"><xsl:value-of select="$t1_num"/></Data></Cell>

      </xsl:if>

      <xsl:if test="position()=1"> <!-- for cell after skipped ones we must specify ss:Index-->

        <Cell ss:MergeDown="{$t2_count - 1}" ss:Index="2"><Data ss:Type="String"><xsl:value-of select="$t2_org"/></Data></Cell>

      </xsl:if>

    <Cell ss:Index="3"><xsl:value-of select="PLANS"/></Cell>

  </Row>

</xsl:for-each>

</xsl:for-each>

</xsl:for-each>

In XSLT it is impossible to accumulate or change variable, that is why we need to calculate rows number  in advance with ABAP like this:

    LOOP AT T1 ASSIGNING <T1>.

      LOOP AT <T1>-T2 ASSIGNING <T2>.

        <T2>-count = lines( <T2>-T3 ).    "for T2 just all nested lines

        IF <T2>-count <> 0.

          <T1>-count = <T1>-count + <T2>-count.   "for T1 we sum all T2 tables, empty = 1 line.

        ELSE.

          <T1>-count = <T1>-count + 1.

        ENDIF.

      ENDLOOP.

     ENDLOOP.

Note: helpful function count(//T1/*) - return rows count for T1 table.

if–then-else

If field has numeric value 1. If-then here:

<xsl:if test="INDEX=1">

</xsl:if>

If-else:

    <xsl:choose>

          <xsl:when test="SYMB='+'">

            <Cell ss:StyleID="s26"><Data ss:Type="String"><xsl:value-of select="SYMB"/></Data></Cell>

          </xsl:when>

          <xsl:otherwise>

            <Cell ss:StyleID="s18"><Data ss:Type="String"></Data></Cell>

          </xsl:otherwise>

     </xsl:choose>

Formulas

<Cell ss:StyleID="s133" ss:Formula="=SUM(R[-{$t1_count}]C:R[-1]C)">

   <Data ss:Type="Number"></Data></Cell>

Here we sym sells up at a distance of $t1_count to curret cell. [] is a Relative Notation for Excel cells.

R for Rows, and C for Columns.

RC[-1] refers to the cell one column to the left: R without a number means same row.

External method call

Static method of a global class call:

<sap:call-external class="ZMYCLASS" method="GET_XML">

                  <sap:callvalue param="IV_STR" select="string(position())"/>  <!-- export parameter -->

                  <sap:callvariable name="fromclass" param="RET_VAL"/>         <!-- return $fromclass

</sap:call-external>

<xsl:value-of select="$fromclass"/>

To call method of object instance, it is required to get instance from one of the static class method.

method SIMP.

  CREATE OBJECT EX_OBJ.

endmethod.

<sap:call-external class=”ZCAL” method=”SIMP”>    “calling static method

  <sap:callvariable param=”EX_OBJ” name=”plant”/>  ” Hold the objec

</sap:call-external>



<sap:call-external name=”plant” method=”ZPERIOD”> “calling instacne method

  <sap:callvalue param=”IM_MHDHB”  select=”string(Order/MHDHB)”/>

  <sap:callvalue param=”IM_IPRKZ”  select=”string(Order/IPRKZ)”/>

  <sap:callvariable param=”EX_MHDHB” name=”period”/>

</sap:call-external>

Templates

Element xsl:apply-templates is an alternative way for doing xsl:for-each. This element useful if the structure of a subnode is not defined, appropriate template selected at runtime. In example we have node-set PLANETS, for wich we want to make loop.

<xsl:template match="PLANETS">

  <HTML>

    <xsl:apply-templates/>

  </HTML>

</xsl:tempiate>



<xsl:template match="PLANET">

    <P>

      <xsl:value-of select="NAME"/>

    </P>

</xsl:tempiate> 

Two tables associated by the key

If we have  two tables T1 (key LV_ID), T2 (key LV_ID_F) for output, then we can use followind approach based on filter:

<xsl:for-each select="//T1/*">

  <xsl:variable name="id" select="LV_ID"/>

  <xsl:for-each select="//T2/*[LV_ID_F=$id]">

  </xsl:for-each>

</xsl:for-each>

"select" with filter for structure

Let us say we have table T_LGART with fields PERNR, ID_FK, BETRG. We need to select BETRG from line with PERNR=$pernr and ID_FK=$id.

<xsl:value-of select="//T_LGART/*[PERNR=$pernr and ID_FK=$id]/*[name()='BETRG']"/>

Advices

1. Keep track for changes in XSLT if you forget your last change and meet failure it will be really fail.


2. Pretty Printer work bad, it is better to structure yourself.


3. When you have many types of rows in template it will be good to cut them all and past in xslt one by one.


4. Overall steps: we create two xml template minimal and rich; it will be good to have comment with every column; one row with dynamic columns; one by one rows surround by loops; surround rows by loops one by one; cells fillings; cell merging; formulas; separate cell filings.

Annex 1. Classic Dynpro output

 DATA lt_data_xml      TYPE swxmlcont.

    DATA l_ostream        TYPE REF TO if_ixml_ostream.

    DATA(l_xml) = cl_ixml=>create( ).

    DATA(l_stream_factory) = l_xml->create_stream_factory( ).

    l_ostream = l_stream_factory->create_ostream_itable( table = lt_data_xml[] ).



    CALL TRANSFORMATION ZXLST_TRANSFORMATION

        SOURCE

          data1 = lt_t "internal table lt_t transfered by name data1. Be careful with this names.

        RESULT XML

          l_ostream.



    DATA workdir  TYPE string.

    cl_gui_frontend_services=>get_sapgui_workdir( CHANGING sapworkdir = workdir ).



    cl_gui_cfw=>flush( ).

    CONCATENATE workdir '\' 'ZXSLT_TRANSFORMATION' sy-datum sy-uzeit '.xml' INTO DATA(filename).

*  "remove special characters

    REPLACE ALL OCCURRENCES OF '*' IN filename WITH space.



    CALL METHOD cl_gui_frontend_services=>gui_download

      EXPORTING

        filename         = filename

        filetype         = 'BIN'

      CHANGING

        data_tab         = lt_data_xml[]

      EXCEPTIONS

        file_write_error = 1

        OTHERS           = 99.



    CONCATENATE '"' filename '"' INTO filename.



    CALL METHOD cl_gui_frontend_services=>execute

      EXPORTING

        application = 'Excel'

        parameter   = filename

        operation   = 'OPEN'

      EXCEPTIONS

        OTHERS = 1.

Annex 2. WebDynpro output

DATA lv_result  TYPE xstring.

  CALL TRANSFORMATION ZXSLT_TRANSFORMATION

      SOURCE

        data      = lt_t

      RESULT XML lv_result

    .



  CHECK lv_result IS NOT INITIAL.



  CALL METHOD cl_wd_runtime_services=>attach_file_to_response

    EXPORTING

      i_filename  = 'a.xml'

      i_content   = lv_result

      i_mime_type = 'application/msexcel'

      i_inplace   = abap_true.

URL sources:

https://blogs.sap.com/2007/08/07/calling-instance-object-method-of-abap-class-into-xslt-mapping-by-u...

https://help.sap.com/saphelp_erp60_sp/helpdata/de/dd/ae463c36a30319e10000000a114084/frameset.htm

https://help.sap.com/erp2005_ehp_04/helpdata/en/a8/824c3c66177414e10000000a114084/frameset.htm

https://bettersolutions.com/excel/formulas/cell-references-a1-r1c1-notation.htm