Get Even More Visitors To Your Blog, Upgrade To A Business Listing >>

Tutorial Excel report with Transformation XLST

XLST – is a language for transforming XML. In this article we take XML document with empty Table and fill it then 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 documents, split into: XSLT (XSL Transformations), XSL-FO (XSL Formatting Objects), XPath (XML Path Language).

XPath (XML Path Language), a non-XML language for addressing the components (elements, attributes, and so on) of an XML document. XPath is widely used in other core-XML specifications and in programming libraries for accessing XML-encoded data.

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

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

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

3)  Find this:

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

4) Find required ROW and place it inside loop:

   - TAB1 is a SOURCE parameter for CALL TRANSFORMATION
   here  which we want to repeat after header.

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:

     - index is a field in out TAB1 internal table

Note: field names must be in UPPER CASE.

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

Nested loops

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

          - here without // becouse of T1/T2
              

Set row position

    - that is how we create varibles
   

Dynamic columns

Rows merging

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

In XLST 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 .
      LOOP AT -T2 ASSIGNING .
        -count = lines( -T3 ).    "for T2 just all nested lines
        IF -count  0.
          -count = -count + -count.   "for T1 we sum all T2 tables, empty = 1 line.
        ELSE.
          -count = -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:

If-else:

    

Formulas

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:



This post first appeared on Web Service Testing A Blackjack GUI And API, please read the originial post: here

Share the post

Tutorial Excel report with Transformation XLST

×

Subscribe to Web Service Testing A Blackjack Gui And Api

Get updates delivered right to your inbox!

Thank you for your subscription

×