Read data from XLSX provided as XSTRING

Question

An Excel file (.xlsx) is uploaded on the frontend which is UI5 Fiori.

The file contents come to SAP ABAP backend via ODATA in XSTRING format.

I need to store that XSTRING into an internal table and then in a DDIC table. Eg: Suppose the Excel has 5 columns then I want to store that data of 5 columns in the corresponding columns in the DDIC table.

I have tried various Function Modules like:

• SCMS_XSTRING_TO_BINARY
• SCMS_BINARY_TO_STRING

and following Classes & methods:

• cl_bcs_convert=>raw_to_string
• cl_soap_xml_helper=>xstring_to_string

but none were able to convert the XSTRING to STRING.

Can you please suggest which function module or class/method can be used to solve the problem?

Answer 1

For most comfort, use abap2xlsx.

If you cannot or do not want to use that, you can alternatively parse the Excel file on your own. .xlsx files are basically .zip files with a different file ending. Use cl_abap_zip->load to open the xstring you receive and ->get to extract the individual files from the zip. Afterwards, use XML parsers like cl_ixml or transformations to parse the XML content of the files.

Note that Excel's XML is a complicated file format, with several files that work together to form the worksheets. Refer to Microsoft's File format reference for Word, Excel, and PowerPoint for details. It's non-trivial to interpret this, so you will usually be a lot happier with abap2xlsx.

Answer 2

abap2xlsx is the most powerful and feature-rich way of doing this, as said by Florian, it supports styles, charts, complex tables, however it may not be always available due to the system limitations, restrictions to install custom packages in system or whatever.

Here is the way how to accomplish this with pure standard without using custom frameworks.

Since Netweaver 7.02 SAP supports Open Microsoft formats natively and provides classes for handling them: CL_XLSX_DOCUMENT, CL_DOCX_DOCUMENT and CL_PPTX_DOCUMENT, abap2xlsx is built at these classes too, yes. So let's start a bit of reinventing the wheel.

XLSX file is an OpenXML archive of files, of which the most interesting: sheet1.xml and sharedStrings.xml. Let's build a sample based on MARC table fields

Now you want to transfer this table to internal table with the same structure. The steps would be:

1. Extract needed files from XLSX archive
2. Read worksheet structure from sheet1.xml
3. Read sheet values from sharedStrings.xml
4. Map them together and write the result to the internal table

Here is the sample class that handles the job, I used the cl_openxml_helper applet to load XLSX, but you can receive XSTRINGed XLSX in whatever way.

CLASS xlsx_reader DEFINITION.
  PUBLIC SECTION.

    TYPES: BEGIN OF ty_marc,
             matnr TYPE char20,
             werks TYPE char20,
             disls TYPE char20,
             ekgrp TYPE char20,
             dismm TYPE char20,
           END OF ty_marc,
           tt_marc TYPE STANDARD TABLE OF ty_marc WITH EMPTY KEY.

    METHODS: read RETURNING VALUE(tab) TYPE tt_marc,
             extract_xml IMPORTING index   TYPE i
                                   xstring TYPE xstring
             RETURNING VALUE(rv_xml_data)  TYPE xstring.
ENDCLASS.

CLASS xlsx_reader IMPLEMENTATION.
  METHOD read.
    TYPES: BEGIN OF ty_row,
                 value TYPE string,
                 index TYPE abap_bool,
               END OF ty_row,
               BEGIN OF ty_worksheet,
                 row_id TYPE i,
                 row    TYPE TABLE OF ty_row WITH EMPTY KEY,
               END OF ty_worksheet,
               BEGIN OF ty_si,
                 t TYPE string,
               END OF ty_si.

    DATA: data  TYPE TABLE OF ty_si,
          sheet TYPE TABLE OF ty_worksheet.

    TRY.
        DATA(xstring_xlsx)  = cl_openxml_helper=>load_local_file( 'C:\marc.xlsx' ).
      CATCH cx_openxml_not_found.
    ENDTRY.

    "Read the sheet XML
    DATA(xml_sheet) = extract_xml( EXPORTING xstring = xstring_xlsx iv_xml_index = 2 ).
    "Read the data XML
    DATA(xml_data)  = extract_xml( EXPORTING xstring = xstring_xlsx iv_xml_index = 3 ).

    TRY.
* transforming structure into ABAP
        CALL TRANSFORMATION zsheet
          SOURCE XML xml_sheet
          RESULT root = sheet.
* transforming data into ABAP
        CALL TRANSFORMATION zxlsx_data
          SOURCE XML xml_data
          RESULT root = data.

      CATCH cx_xslt_exception.
      CATCH cx_st_match_element.
      CATCH cx_st_ref_access.
    ENDTRY.
* mapping structure and data
    LOOP AT sheet ASSIGNING FIELD-SYMBOL(<fs_row>).
      APPEND INITIAL LINE TO tab ASSIGNING FIELD-SYMBOL(<line>).
      LOOP AT <fs_row>-row ASSIGNING FIELD-SYMBOL(<fs_cell>).
        ASSIGN COMPONENT sy-tabix OF STRUCTURE <line> TO FIELD-SYMBOL(<fs_field>).
        CHECK sy-subrc = 0.
        <fs_field> = COND #( WHEN <fs_cell>-index = abap_false THEN <fs_cell>-value ELSE VALUE #( data[ <fs_cell>-value + 1 ]-t OPTIONAL ) ).
      ENDLOOP.
    ENDLOOP.
  ENDMETHOD.

  METHOD extract_xml.
    TRY.
        DATA(lo_package) = cl_xlsx_document=>load_document( iv_data = xstring ).
        DATA(lo_parts)      = lo_package->get_parts( ).
        CHECK lo_parts IS BOUND AND lo_package IS BOUND.
        DATA(lv_uri) = lo_parts->get_part( 2 )->get_parts( )->get_part( index )->get_uri( )->get_uri( ).
        DATA(lo_xml_part)     = lo_package->get_part_by_uri( cl_openxml_parturi=>create_from_partname( lv_uri ) ).
        rv_xml_data     = lo_xml_part->get_data( ).
      CATCH cx_openxml_format cx_openxml_not_found.
    ENDTRY.
  ENDMETHOD.
ENDCLASS.

zsheet transformation:

<?sap.transform simple?>
<tt:transform xmlns:tt="http://www.sap.com/transformation-templates" template="main">
  <tt:root name="root"/>
  <tt:template name="main">
    <worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:x14ac=
"http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision" xmlns:xr2="http://schemas.microsoft.com/office/spreadsheetml/2015/revision2" xmlns:xr3=
"http://schemas.microsoft.com/office/spreadsheetml/2016/revision3">
      <tt:skip count="4"/>
      <sheetData>
        <tt:loop name="row" ref="root">
          <row>
            <tt:attribute name="r" value-ref="row_id"/>
            <tt:loop name="cells" ref="$row.ROW">
              <c>
                <tt:cond><tt:attribute name="t" value-ref="index"/><tt:assign to-ref="index" val="C('X')"/></tt:cond>
                <v><tt:value ref="value"/></v>
              </c>
            </tt:loop>
          </row>
        </tt:loop>
      </sheetData>
      <tt:skip count="2"/>
    </worksheet>
  </tt:template>
</tt:transform>

zxlsx_data transformation

<?sap.transform simple?>
<tt:transform xmlns:tt="http://www.sap.com/transformation-templates" template="main">
  <tt:root name="ROOT"/>
  <tt:template name="main">
      <sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
        <tt:loop name="line" ref=".ROOT">
          <si>
            <t>
              <tt:value ref="t"/>
            </t>
          </si>
        </tt:loop>
      </sst>
  </tt:template>
</tt:transform>

Here is how to call it:

START-OF-SELECTION.

  DATA(reader) = NEW xlsx_reader( ).
  DATA(marc)   = reader->read( ).

The code is pretty self-explanatory, but let's put a couple of notes:

• File sheet1.xml contains a special attribute t in each cell which denotes either the value should be treated as a literal or a reference to sharedStrings.xml
• I used two simple transformations but XSLT can be used as well, possibly allowing you to reduce all XML stuff to single transformation
• I deliberately used generic char20 types to be able to handle headers. If you wanna preserve native types, then you cannot read table header (skip the first line in sheet LOOP), because you'll receive type violation and dump. If you receive table without headers, then it is fine to declare structure with native types
• If you don't want to use transformations then sXML is your friend. You can parse XML with classes as well, but ST transformation are considerably faster
• With some additional effort you can make this snippet dynamic and parse XLSX with any structure

You can read more about this approach in this doc.