ABAP – CALL TRANSFORMATION to and from Excel using XLST

SAPThis article is here to show you how to export data from ABAP internal table to XML accepted by MS Excel using XSLT transformation. In second part I’ll show the backwards transformation – Excel to ABAP internal table.

This article is about manipulation with an Excel data but generally this approach can as well be used when you just need to export some data from ABAP to XML stored on local file (or share drive file) or vice versa – when you need to parse given XML file and process its data as an ABAP internal table.

XSLT Transformation from ABAP to XML (ZTRANS_ABAP2XLS)

Call transaction STRANS and create new XSLT Program where you put the following code

<?mso-application progid="Excel.Sheet"?>
<!-- 
  The following namespaces (xmlns) are required so 
  the resulting XML file is correctly recognized by MS Excel 
-->
<xsl:stylesheet
 xmlns:html="http://www.w3.org/TR/REC-html40"
 xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
 xmlns:sap="http://www.sap.com/sapxsl"
 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"
 version="1.0">
  <xsl:strip-space elements="*"/>
<!-- 
  EXCELDATA is the input parameter passed from ABAP 
-->
  <xsl:template match="EXCELDATA">
    <xsl:processing-instruction name="mso-application">
      <xsl:text>progid="Excel.Sheet"</xsl:text>
    </xsl:processing-instruction>
<!-- 
  We're going to create a Workbook with just one Worksheet 
  called 'SAP Data' 
-->
    <xsl:element name="Workbook">
      <xsl:element name="Worksheet">
        <xsl:attribute name="ss:Name">SAP Data</xsl:attribute>
<!-- 
  This part (element 'Names') is here to demonstrate how
  to define Named-Ranges in Excel.
  I'll use it to name the first row of each column according
  to the FIELDNAME used in the current column 
-->
        <xsl:element name="Names">
          <xsl:for-each select="COLUMNS/item">
            <xsl:element name="NamedRange">
              <xsl:attribute name="ss:Name">
                <xsl:value-of select="./FIELDNAME"/>
              </xsl:attribute>
<!-- 
  We are using relative cell names (R1C1 = Row 1, Column 1)
-->
              <xsl:attribute name="ss:RefersTo">'SAP Data'!R1C<xsl:value-of select="position()"/></xsl:attribute>
            </xsl:element>
          </xsl:for-each>
        </xsl:element>
<!-- Here comes the data part -->
        <xsl:element name="Table">
<!-- This template will generate column headers -->
          <xsl:apply-templates select="COLUMNS"/>
<!-- This template will generate table data -->
          <xsl:apply-templates select="DATA"/>
        </xsl:element>
      </xsl:element>
    </xsl:element>
  </xsl:template>
 
<!-- 
  Template generating column headers:
  This "METADATA" is sent from ABAP in sub-structure 'COLUMNS'
-->
  <xsl:template match="COLUMNS">
    <xsl:element name="Row">
      <xsl:for-each select="item">
        <xsl:element name="Cell">
          <xsl:element name="Data">
            <xsl:attribute name="ss:Type">String</xsl:attribute>
<!-- 
  The following XSLT tag (xsl:choose) is here to demonstrate branching
  possibilities in XSLT
-->
            <xsl:choose>
              <xsl:when test="./HEADERTEXT">
                <xsl:value-of select="./HEADERTEXT"/>
              </xsl:when>
              <xsl:otherwise>
                <xsl:value-of select="./FIELDNAME"/>
              </xsl:otherwise>
            </xsl:choose>
          </xsl:element>
        </xsl:element>
      </xsl:for-each>
    </xsl:element>
  </xsl:template>
 
<!-- Template used to generate table data -->
  <xsl:template match="DATA">
    <xsl:for-each select="item">
      <xsl:element name="Row">
        <xsl:variable name="record" select="."/>
<!-- 
  For each field defined in 'COLUMNS' we try to find the
  corresponding field here in DATA section and print out 
  its value and type (type is also defined in COLUMNS 
  structure for each field)
-->
        <xsl:for-each select="//EXCELDATA/COLUMNS/item">
          <xsl:variable name="column" select="."/>
          <xsl:element name="Cell">
            <xsl:element name="Data">
              <xsl:attribute name="ss:Type">
                <xsl:choose>
                  <xsl:when test="$column/TYPE">
                    <xsl:value-of select="$column/TYPE"/>
                  </xsl:when>
                  <xsl:otherwise>String</xsl:otherwise>
                </xsl:choose>
              </xsl:attribute>
              <xsl:value-of select="$record/*[name(.)=$column/FIELDNAME]"/>
            </xsl:element>
          </xsl:element>
        </xsl:for-each>
      </xsl:element>
    </xsl:for-each>
  </xsl:template>
 
</xsl:stylesheet>

XSLT Transformation from XML to ABAP (ZTRANS_XLS2ABAP)

<!-- 
  We must mention the ':ss' namespace so we can work with Excel
  tags in given XML document (in Excel format)
-->
<xsl:transform 
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" 
  version="1.0">
 
  <xsl:output 
    encoding="iso-8859-1" 
    indent="yes" 
    method="xml" 
    version="1.0"/>
  <xsl:strip-space elements="*"/>
<!-- 
  Note the ':ss' namespace used for Workbook and Worksheet tags
  here and also later in the XSLT template - this is a MUST
  for correct accessing the Excel tags, otherwise no match will be
  done and no data will be processed!!!
-->
  <xsl:template match="/">
    <asx:abap xmlns:asx="http://www.sap.com/abapxml" version="1.0">
      <asx:values>
        <EXCELDATA>
          <xsl:apply-templates select="ss:Workbook/ss:Worksheet"/>
        </EXCELDATA>
      </asx:values>
    </asx:abap>
  </xsl:template>
 
 <xsl:template match="ss:Worksheet">
  <xsl:apply-templates select="ss:Table"/>
 </xsl:template>
 
<xsl:template match="ss:Table">
<!-- 
  Process all rows except the first one which contains 
  column header texts - it will be skipped
-->
  <xsl:apply-templates select="ss:Row[position() != 1]"/>
</xsl:template>
 
  <xsl:template match="ss:Row">
    <xsl:element name="item">
      <xsl:for-each select="ss:Cell">
        <xsl:choose>
          <xsl:when test="position() = 1">
            <MATNR><xsl:value-of select="ss:Data"/></MATNR>
          </xsl:when>
          <xsl:when test="position() = 2">
            <WERKS><xsl:value-of select="ss:Data"/></WERKS>
          </xsl:when>
        </xsl:choose>
      </xsl:for-each>
    </xsl:element>
  </xsl:template>
</xsl:transform>

Testing demo program

REPORT z_excel_demo.
SELECTION-SCREEN BEGIN OF BLOCK a1 WITH FRAME TITLE a1title.
  SELECTION-SCREEN BEGIN OF LINE.
    SELECTION-SCREEN COMMENT 1(15) a1fname FOR FIELD p_file.
    SELECTION-SCREEN POSITION 17.
    PARAMETERS:
      p_file TYPE localfile OBLIGATORY DEFAULT 'C:/Excel_Test.XML'.
  SELECTION-SCREEN END OF LINE.
  SELECTION-SCREEN BEGIN OF LINE.
    SELECTION-SCREEN COMMENT 1(15) a1up FOR FIELD p_up.
    SELECTION-SCREEN POSITION 17.
    PARAMETERS: 
      p_up   RADIOBUTTON GROUP g1 USER-COMMAND dummy_cmd.
  SELECTION-SCREEN END OF LINE.
  SELECTION-SCREEN BEGIN OF LINE.
    SELECTION-SCREEN COMMENT 1(15) a1down FOR FIELD p_down.
    SELECTION-SCREEN POSITION 17.
    PARAMETERS: 
      p_down RADIOBUTTON GROUP g1 DEFAULT 'X'.
  SELECTION-SCREEN END OF LINE.
  SELECTION-SCREEN BEGIN OF LINE.
    SELECTION-SCREEN COMMENT 1(15) a1maxln FOR FIELD p_maxln.
    SELECTION-SCREEN POSITION 17.
    PARAMETERS:
      p_maxln TYPE i OBLIGATORY DEFAULT 200.
  SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN END OF BLOCK a1.
 
INITIALIZATION.
  PERFORM selection_screen_texts.
 
FORM selection_screen_texts.
  a1title = 'Excel handling demo'(001).
  a1fname = 'File Name'(002).
  a1up    = 'XLS Upload'(003).
  a1down  = 'XLS Download'(004).
  a1maxln = 'Max lines'(005).
ENDFORM.
 
CLASS lcl_excel DEFINITION.
  PUBLIC SECTION.
    METHODS:
      constructor
        IMPORTING
          iv_filename TYPE localfile
          iv_max_lines type i DEFAULT 200,
      download,
      upload.
  PRIVATE SECTION.
* Structure of one row in final Excel sheet
    TYPES:
      BEGIN OF mts_row,
        matnr TYPE matnr,
        werks TYPE werks_d,
      END OF mts_row,
 
* Header line of excel sheet with additional info about each column
      BEGIN OF mts_header,
        fieldname(18)  TYPE c,
        headertext(50) TYPE c,
        type(10)       TYPE c,
      END OF mts_header.
    DATA:
      BEGIN OF ms_data,
        columns TYPE TABLE OF mts_header,
        data    TYPE TABLE OF mts_row,
      END OF ms_data,
      mv_max_lines type i,
      mv_filename TYPE string.
ENDCLASS.
 
CLASS lcl_excel IMPLEMENTATION.
  METHOD constructor.
    mv_max_lines = iv_max_lines.
    mv_filename  = iv_filename.
  ENDMETHOD.
*
  METHOD download.
    DATA:
      lv_xml_string  TYPE string,
      lv_bin_string  TYPE xstring,
      lt_bin_tab     TYPE solix_tab,
      lv_file_length TYPE i.
 
    FIELD-SYMBOLS:
      <fs_header> TYPE mts_header.
 
*   Create first column - Material (mapping to MATNR, type Number)
    APPEND INITIAL LINE TO ms_data-columns ASSIGNING <fs_header>.
    <fs_header>-fieldname  = 'MATNR'.
    <fs_header>-headertext = 'Material'.
    <fs_header>-type       = 'Number'.
 
*   Create second column - Plant (mapping to WERKS, type String)
    APPEND INITIAL LINE TO ms_data-columns ASSIGNING <fs_header>.
    <fs_header>-fieldname  = 'WERKS'.
    <fs_header>-headertext = 'Plant'.
    <fs_header>-type       = 'String'.
 
*   Load some data
    SELECT matnr werks
      INTO CORRESPONDING FIELDS OF TABLE ms_data-data
      FROM marc
      UP TO mv_max_lines ROWS
      WHERE matnr <> ''.
 
*   Use out XSLT Program transformation with our source data
*   to transform ITAB to XML (Excel format)
    CALL TRANSFORMATION ZTRANS_ABAP2XML
      SOURCE exceldata = ms_data
      RESULT XML  lv_xml_string.
 
* Convert XML string to binary XString
    CALL FUNCTION 'SCMS_STRING_TO_XSTRING'
      EXPORTING
        text   = lv_xml_string
      IMPORTING
        buffer = lv_bin_string
      EXCEPTIONS
        failed = 1
        OTHERS = 2.
 
    IF sy-subrc <> 0.
      MESSAGE ID sy-msgid 
              TYPE sy-msgty 
              NUMBER sy-msgno 
              WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
    ENDIF.
 
*   Convert binary data to table-type binary output variable
    CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
      EXPORTING
        buffer        = lv_bin_string
      IMPORTING
        output_length = lv_file_length
      TABLES
        binary_tab    = lt_bin_tab.
 
*   Save data to local file
    cl_gui_frontend_services=>gui_download(
      EXPORTING
        bin_filesize              = lv_file_length
        filename                  = mv_filename
        filetype                  = 'BIN'
      CHANGING
        data_tab                  = lt_bin_tab
      EXCEPTIONS
        file_write_error          = 1
        no_batch                  = 2
        gui_refuse_filetransfer   = 3
        invalid_type              = 4
        no_authority              = 5
        unknown_error             = 6
        header_not_allowed        = 7
        separator_not_allowed     = 8
        filesize_not_allowed      = 9
        header_too_long           = 10
        dp_error_create           = 11
        dp_error_send             = 12
        dp_error_write            = 13
        unknown_dp_error          = 14
        access_denied             = 15
        dp_out_of_memory          = 16
        disk_full                 = 17
        dp_timeout                = 18
        file_not_found            = 19
        dataprovider_exception    = 20
        control_flush_error       = 21
        not_supported_by_gui      = 22
        error_no_gui              = 23
        OTHERS                    = 24
    ).
 
    IF sy-subrc <> 0.
      MESSAGE ID sy-msgid 
              TYPE sy-msgty 
              NUMBER sy-msgno 
              WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
    ENDIF.
  ENDMETHOD.
 
  METHOD upload.
    TYPES:
      BEGIN OF lts_data,
        matnr TYPE matnr,
        werks TYPE werks_d,
      END OF lts_data.

    DATA:
      lt_xml_data TYPE STANDARD TABLE OF char2048,
      lt_data     TYPE TABLE OF lts_data,
      gs_rif_ex   TYPE REF TO cx_root.
 
    FIELD-SYMBOLS:
      <fs_data> LIKE LINE OF lt_data.
 
    CALL METHOD cl_gui_frontend_services=>gui_upload
      EXPORTING
        filename                = mv_filename
      CHANGING
        data_tab                = lt_xml_data
      EXCEPTIONS
        file_open_error         = 1
        file_read_error         = 2
        no_batch                = 3
        gui_refuse_filetransfer = 4
        invalid_type            = 5
        no_authority            = 6
        unknown_error           = 7
        bad_data_format         = 8
        header_not_allowed      = 9
        separator_not_allowed   = 10
        header_too_long         = 11
        unknown_dp_error        = 12
        access_denied           = 13
        dp_out_of_memory        = 14
        disk_full               = 15
        dp_timeout              = 16
        not_supported_by_gui    = 17
        error_no_gui            = 18
        OTHERS                  = 19.
 
    IF sy-subrc <> 0.
      MESSAGE ID sy-msgid 
              TYPE sy-msgty 
              NUMBER sy-msgno
              WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
    ENDIF.
    TRY.
*     Use XSLT Program transformation with our source data
      CALL TRANSFORMATION ZTRANS_XLS2ABAP
        SOURCE XML lt_xml_data
        RESULT exceldata = lt_data.
      CATCH cx_root INTO gs_rif_ex.
        DATA: lv_msg TYPE string.
        lv_msg = gs_rif_ex->get_text( ).
        MESSAGE lv_msg TYPE 'E'.
    ENDTRY.
 
    WRITE:5 'Material', 30 'Plant'.
    LOOP AT lt_data ASSIGNING <fs_data>.
      WRITE:/5 <fs_data>-matnr, 30 <fs_data>-werks.
      IF sy-tabix = mv_max_lines.
        EXIT.
      ENDIF.
    ENDLOOP.
 
  ENDMETHOD.
ENDCLASS.
 
DATA:
  lo_excel TYPE REF TO lcl_excel.
 
START-OF-SELECTION.
  CREATE OBJECT lo_excel
    EXPORTING
      iv_filename  = p_file
      iv_max_lines = p_maxln.
 
  IF p_down = 'X'.
    lo_excel->download( ).
  ELSE.
    lo_excel->upload( ).
  ENDIF.
 
  MESSAGE 'Done' TYPE 'S'.

Output XML (Excel format)

I executed the demo program with Export option with up to 5 records from MARC table

Process data in ABAP and download into Excel

<?xml version="1.0" encoding="UTF-8"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook 
  xmlns="urn:schemas-microsoft-com:office:spreadsheet" 
  xmlns:html="http://www.w3.org/TR/REC-html40" 
  xmlns:o="urn:schemas-microsoft-com:office:office" 
  xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" 
  xmlns:x="urn:schemas-microsoft-com:office:excel">
  <Worksheet ss:Name="SAP Data">
    <Names>
      <NamedRange ss:Name="MATNR" ss:RefersTo="'SAP Data'!R1C1" />
      <NamedRange ss:Name="WERKS" ss:RefersTo="'SAP Data'!R1C2" />
    </Names>
    <Table>
      <Row>
        <Cell>
          <Data ss:Type="String">Material</Data>
        </Cell>
        <Cell>
          <Data ss:Type="String">Plant</Data>
        </Cell>
      </Row>
      <Row>
        <Cell>
          <Data ss:Type="Number">000000000000123123</Data>
        </Cell>
        <Cell>
          <Data ss:Type="String">0050</Data>
        </Cell>
      </Row>
      <Row>
        <Cell>
          <Data ss:Type="Number">000000000001231234</Data>
        </Cell>
        <Cell>
          <Data ss:Type="String">0050</Data>
        </Cell>
      </Row>
      <Row>
        <Cell>
          <Data ss:Type="Number">000000000007654321</Data>
        </Cell>
        <Cell>
          <Data ss:Type="String">0050</Data>
        </Cell>
      </Row>
      <Row>
        <Cell>
          <Data ss:Type="Number">000000000007777741</Data>
        </Cell>
        <Cell>
          <Data ss:Type="String">0050</Data>
        </Cell>
      </Row>
      <Row>
        <Cell>
          <Data ss:Type="Number">000000000007777742</Data>
        </Cell>
        <Cell>
          <Data ss:Type="String">0050</Data>
        </Cell>
      </Row>
    </Table>
  </Worksheet>
</Workbook>

Exported data displayed in MS Excel

Output of ABAP internal table

I executed the demo program with Import option using the XLS file previously generated by the same program

Excel - Upload and process in ABAP

Imported data from MS Excel processed in ABAP

 

5 thoughts on “ABAP – CALL TRANSFORMATION to and from Excel using XLST

  1. Hi! Thank you for your article!
    I’m just starting to learn ABAP, so i would be very pleased if you could explain me something. I copied your program code and XSLT transformations and tried to execute. But when i open downloaded XML by Notepad it goes in one row (not formatted like output XML in your example), so when there are more than 12 entries downloaded, error occurs while uploading. I think ’cause file doesn’t fit in row of table type char2048. How can i solve it and upload bigger data (more than 12 rows)? Should i change type for uploaded table (lt_xml_data in your example)? Which type should i choose? Or there’s a way to download XML divided by rows? Thank you for answer.

    • When any XML file is opened in a Notepad then it is not well formatted please open with any xml relevant application or the simplest way using web browser then you can see a well formatted XML.

  2. Hi ! Thank you so much for a wonderful article..

    I have followed the same procedure explained above. But I am unable to download the XML file. File is getting downloaded into Excel format only. Kindly help me in this..

    Thanks,
    Venkat

    • The file produced by this code is definitely a XML output, I’m 100% sure about it :-).

      But your computer is most probably setup in a way that it always opens XML files in MS Excel automatically (default program for opening XML files).
      Just try to manually open the same XML file with notepad or any XML editing software (e.g. XML Marker)
      Br
      — Miroslav

  3. Your transformation, at least the upload part fails with error: No valid source context supplied, so it probably does not recognize valid XLSX.
    The file is generate using Excel 2019.

Leave a Reply to astashonokCancel reply