This 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
<?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>
Output of ABAP internal table
I executed the demo program with Import option using the XLS file previously generated by the same program
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.
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
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.