Skip to Content

How to - Add Custom XML Parts to Microsoft Excel using ABAP

This document explains how to:

Add Custom XML Parts to Excel using ABAP

    1. Prepare XML data

METHOD prepare_customxml.
*    Exporting  EV_CUSTOMXML4XLS  TYPE XSTRING
*    Exception  XSLT_ERROR
  CONSTANTS:  lc_encode        TYPE abap_encod VALUE 'UTF-8'.
  DATA:
        lv_customxml4xls              TYPE string,
        l_convout                          TYPE REF TO cl_abap_conv_out_ce,
        lv_len                                TYPE i.
  l_convout = cl_abap_conv_out_ce=>create( encoding = lc_encode ).
  lv_customxml4xls = '<xmlXLS value="This is an example of valid xml" />'. "You can add a valid xml here
  l_convout->convert(
      EXPORTING
              data  = lv_customxml4xls
      IMPORTING
              buffer = ev_customxml4xls
              len    = lv_len ).

ENDMETHOD.

    2. Get the Excel template

              The template can be uploaded to the MIME repository and can be read from there.
METHOD get_xls_template.
*    Exporting EV_XLS TYPE XSTRING
  DATA:
  lo_mr_api                  TYPE REF TO if_mr_api.
  lo_mr_api = cl_mime_repository_api=>get_api( ).
  lo_mr_api->get(
        EXPORTING
              i_url    = xls_template_url          "Give the template url in MIME repository here
        IMPORTING
              e_content = ev_xls
        EXCEPTIONS
              OTHERS    = 1
  ).

ENDMETHOD.

    3. Add Custom XML to Excel template

METHOD add_customxml2xls.
*    Importing      IV_XLS                    TYPE XSTRING
*    Importing      IV_CUSTOMXML    TYPE XSTRING
*    Exporting      EV_XLS                    TYPE XSTRING   
*    Exception      XSLT_ERROR
  DATA:
    xls                              TYPE REF TO cl_xlsx_document,
    workbookpart            TYPE REF TO cl_xlsx_workbookpart,
    l_packagecontent        TYPE string,
    customxmlpartcoll      TYPE REF TO cl_openxml_partcollection,
    customxmlpart            TYPE REF TO cl_oxml_customxmlpart,
    customxmlpropspart  TYPE REF TOTO cl_oxml_customxmlpropspart,
    propertyxml                TYPE xstring,
    preguid                        TYPE string,
    guid                            TYPE string.
  TRY.
    xls = cl_xlsx_document=>load_document( iv_data = iv_xls ).
      l_packagecontent = xls->get_content_type( ).
* get the workbook part
      workbookpart = xls->get_workbookpart( ).
* get collection of customXML parts
      customxmlpartcoll = workbookpart->get_customxmlparts( ).
* create a customXML part here
      customxmlpart = workbookpart->add_customxmlpart( ).
* insert xml data
      customxmlpart->feed_data( iv_customxml  ).
* add customXML properties part
      customxmlpropspart = customxmlpart->add_customxmlpropspart( ).
* create GUID string
      preguid = cl_openxml_helper=>create_guid_string( ).
* enclose with {...} brackets
      CONCATENATE '{' preguid '}' INTO guid.
* create custom XML property content
      CALL TRANSFORMATION docx_create_custompropscontent
            PARAMETERS guid = guid
            SOURCE XML iv_customxml
            RESULT XML propertyxml.
* insert propertyxml
      customxmlpropspart->feed_data( propertyxml ).
      ev_xls = xls->get_package_data( ).
    CATCH cx_openxml_format.
    CATCH cx_openxml_not_allowed.
    CATCH cx_openxml_not_found.
    CATCH cx_transformation_error.
      RAISE xslt_error.
  ENDTRY.

ENDMETHOD.

    4. Integrate the above steps - XML, Excel template, Custom XML

METHOD get_xls_download.
*      Exporting EV_XML_XSTRING_XLS      TYPE XSTRING
*      Exception ERROR_OCCURRED
  DATA:
              lv_customxml4xls      TYPE xstring,
              lv_xls_template          TYPE xstring.
  prepare_customxml(
    IMPORTING
      ev_customxml4xls = lv_customxml4xls
    EXCEPTIONS
      xslt_error          = 1
      OTHERS          = 2
                        ).
  IF sy-subrc = 1.
    RAISE error_occurred.
  ELSEIF sy-subrc = 2.
    RAISE error_occurred.
  ENDIF.
  get_xls_template(
    IMPORTING
      ev_xls          = lv_xls_template
                  ).

  add_customxml2xls(
    EXPORTING
      iv_xls                = lv_xls_template
      iv_customxml    = lv_customxml4xls
    IMPORTING
      ev_xls                = ev_xml_xstring_xls
    EXCEPTIONS
      xslt_error        = 1
      OTHERS        = 2
                    ).

  IF sy-subrc <> 0.
    RAISE error_occurred.
  ENDIF.

ENDMETHOD.

Related Articles:

Tags:

No comments