Skip to Content
Technical Articles

Create an Excel worksheet (XLS file) using structures of the data dictionary

This blog demonstrates how to create an Excel worksheet based on structures of the data dictionary dynamically. The development consists in create an Extensible Markup Language (XML) file using Document Object Model (DOM) API that can be read on Excel program.

The process is executed following below steps:

  • Create the object and set attributes
  • Get the data reference from header/item structure of the data dictionary
  • Create and render a XML document, defined using DOM API, with the necessary structure that is interpreted by Excel
  • Save a file with XLS extension

Creating a worksheet with the following attributes/functionalities:

  • Author
  • Worksheet name
  • Title
  • Header
  • Item table
  • Sum formula of the numeric fields from the item table
  • Styles
  • Format Cells (Alignment, Border, Font, Number)

The main idea was to create a class that could be inherited by others developments and redefined accordingly with the requirements of them.

The class code and the report sample code to execute the process are on the bottom of the page.

Available methods on the local class:

METHOD DESCRIPTION
CONSTRUCTOR Create object and set attributes: author, title and worksheet
EXECUTE Execute the process to create a XML document and convert it into a XLS file
SAVE_XLS_FILE Display save dialog window and save the XML file as a binary file with XLS extension
RENDER_XML_DOCUMENT Render the document
CREATE_XLS_FILE Create a binary file from a XML table
GET_DATA_REFERENCE Dynamically get the input data
CREATE_XML_DOCUMENT Create the XML document
CREATE_DOCUMENT Instantiate DOM object and create Workbook node
CREATE_STYLE Create a style
CREATE_STYLES Create styles node
DOCUMENT_PROPERTIES Create document properties node and set author value attribute tag
FORMAT_FONT Set font attributes of a style
FORMAT_INTERIOR Set interior attributes of a style
FORMAT_ALIGNMENT Set alignment attributes of a style
ADD_BORDER_TO_STYLE Add border node to a style
FORMAT_BORDER Set border attributes of border node
FORMAT_NUMBER Set number format attributes of a style
ADD_WORKSHEET Add a new worksheet node on the XML document
ADD_TABLE Add a new table node on the XML document
FORMAT_WIDTH_COLUMN Set columns width of the table node based on the structure data elements length
ADD_ROW Add a new row node on the XML document
FORMAT_HEIGHT_ROW Set row height
ADD_CELL Add a new cell tag on the row node
FILL_DATA_CELL Fill the value of a cell
MERGE_CELL Merge cells
DDIF_FIELDINFO_GET Get data dictionary information
FILL_HEADER_ROWS Fill the header values
FILL_ITEM_ROWS Fill the item values
FILL_TOTAL Create Sum formula for each numeric column of the item table
FILL_TITLE Fill the title value on a cell
SET_GRIDLINES Disable grid layout

 

DOM/XML document Interfaces:

METHOD DESCRIPTION
IF_IXML Interface of Factory Object
IF_IXML_STREAM_FACTORY Factory for Streams
IF_IXML_OSTREAM Output Streams
IF_IXML_RENDERER Renderer
IF_IXML_DOCUMENT XML Document in DOM Representation
IF_IXML_ELEMENT Element of an XML Document

 

Further information

DOM – https://www.w3.org/TR/DOM-Level-2-Core/introduction.html

XML – https://www.w3.org/standards/xml/core

 

This is the layout of the output files after the sample execution:

User Roles (Example using tables: V_USR_NAME/ AGR_USERS)

 

Purchase Order (Example using tables: EKKO/EKPO)

 

Delivery (Example using tables: LIKP/LIPS)

 

Material Document (Example using tables: MKPF/MSEG)

 

To execute the SAMPLE REPORT:

Create a INCLUDE Z_LOCAL_CLASS_CREATE_XLS.

Create a REPORT Z_TESTE_CREATE_XLS.

Run the Report.

 

PS.: When you open the XLS file, a warning message is shown as below:

If you want to avoid this message, the extension must be saved as xml instead of the xls extension.

 

Class code:

*&---------------------------------------------------------------------*
*& Include          Z_LOCAL_CLASS_CREATE_XLS
*&---------------------------------------------------------------------*
CLASS zcl_local_create_xls DEFINITION.

  PUBLIC SECTION.

    DATA t_return TYPE bapiret2_tab .

    METHODS constructor
      IMPORTING
        VALUE(im_title)     TYPE string OPTIONAL
        VALUE(im_worksheet) TYPE string OPTIONAL
        VALUE(im_author)    TYPE string OPTIONAL .
    METHODS execute
      IMPORTING
        VALUE(im_header)   TYPE any OPTIONAL
        VALUE(im_item)     TYPE any OPTIONAL
        VALUE(im_item_tab) TYPE ANY TABLE OPTIONAL
        !im_do_sum         TYPE boolean OPTIONAL .
    METHODS save_xls_file
      IMPORTING
        VALUE(im_xls_filename) TYPE string .
  PROTECTED SECTION.
  PRIVATE SECTION.

    TYPES:
      ty_xmlline TYPE x LENGTH 255 .
    TYPES:
      tt_xmlline TYPE STANDARD TABLE OF ty_xmlline .
    TYPES ty_dref TYPE REF TO data .
    TYPES:
      tt_dref TYPE TABLE OF ty_dref .
    TYPES:
      BEGIN OF ty_total,
        fieldname TYPE fieldname,
        value     TYPE REF TO data,
      END OF ty_total .

    DATA s_xls_header TYPE ty_dref .
    DATA t_dfies_header TYPE dfies_tab .
    DATA t_xls_item TYPE ty_dref .
    DATA s_xls_item TYPE ty_dref .
    DATA t_dfies_item TYPE dfies_tab .
    DATA v_workbook TYPE string .
    DATA v_worksheet TYPE string VALUE 'Worksheet' ##NO_TEXT.
    DATA v_author TYPE string .
    DATA v_company TYPE string .
    DATA v_title TYPE string .
    DATA v_xls_filename TYPE string .
    DATA v_current_row TYPE i VALUE 0 ##NO_TEXT.
    DATA v_do_sum TYPE boolean .
    CONSTANTS c_path TYPE string VALUE 'C:\TEMP\' ##NO_TEXT.
    CONSTANTS c_file_extension TYPE string VALUE 'xls' ##NO_TEXT.
    CONSTANTS c_file_filter TYPE string VALUE '*.xls' ##NO_TEXT.
    DATA s_xls_bin TYPE solix .
    DATA t_xls_bin TYPE solix_tab .
    DATA xml_size TYPE i .
    DATA xml_table TYPE tt_xmlline .
    DATA ixml TYPE REF TO if_ixml .
    DATA streamfactory TYPE REF TO if_ixml_stream_factory .
    DATA ostream TYPE REF TO if_ixml_ostream .
    DATA renderer TYPE REF TO if_ixml_renderer .
    DATA document TYPE REF TO if_ixml_document .
    DATA element_root TYPE REF TO if_ixml_element .
    DATA r_worksheet TYPE REF TO if_ixml_element .
    DATA r_style TYPE REF TO if_ixml_element .
    DATA r_styles TYPE REF TO if_ixml_element .
    DATA r_border TYPE REF TO if_ixml_element .
    DATA r_table TYPE REF TO if_ixml_element .
    DATA r_row TYPE REF TO if_ixml_element .
    DATA r_cell TYPE REF TO if_ixml_element .

    METHODS render_xml_document .
    METHODS create_xls_file .
    METHODS get_data_reference
      IMPORTING
        !im_header   TYPE any
        !im_item     TYPE any
        !im_item_tab TYPE ANY TABLE .
    METHODS create_xml_document .
    METHODS create_document .
    METHODS create_style
      IMPORTING
        !im_style TYPE string .
    METHODS create_styles .
    METHODS document_properties .
    METHODS format_font
      IMPORTING
        VALUE(im_fontname)  TYPE string OPTIONAL
        VALUE(im_size)      TYPE string OPTIONAL
        VALUE(im_bold)      TYPE string OPTIONAL
        VALUE(im_italic)    TYPE string OPTIONAL
        VALUE(im_underline) TYPE string OPTIONAL .
    METHODS format_interior
      IMPORTING
        VALUE(im_color)   TYPE string OPTIONAL
        VALUE(im_pattern) TYPE string OPTIONAL .
    METHODS format_alignment
      IMPORTING
        VALUE(im_h_align)  TYPE string OPTIONAL
        VALUE(im_v_align)  TYPE string OPTIONAL
        VALUE(im_wraptext) TYPE string OPTIONAL .
    METHODS add_border_to_style .
    METHODS format_border
      IMPORTING
        VALUE(im_position)  TYPE string OPTIONAL
        VALUE(im_linestyle) TYPE string OPTIONAL
        VALUE(im_weight)    TYPE string OPTIONAL
        VALUE(im_color)     TYPE string OPTIONAL .
    METHODS format_number
      IMPORTING
        !im_numberformat TYPE string .
    METHODS add_worksheet
      IMPORTING
        !im_worksheet TYPE string .
    METHODS add_table .
    METHODS format_width_column .
    METHODS add_row .
    METHODS format_height_row
      IMPORTING
        !im_autofitheight TYPE string .
    METHODS add_cell
      IMPORTING
        !im_style         TYPE string
        VALUE(im_formula) TYPE string OPTIONAL .
    METHODS fill_data_cell
      IMPORTING
        !im_cell_name TYPE string
        !im_datatype  TYPE string .
    METHODS merge_down_cell
      IMPORTING
        !im_style       TYPE string
        !im_merge_value TYPE string .
    METHODS merge_cell
      IMPORTING
        !im_style       TYPE string
        !im_merge_value TYPE string .
    METHODS ddif_fieldinfo_get .
    METHODS fill_header_rows .
    METHODS fill_item_rows .
    METHODS fill_total .
    METHODS fill_title .
    METHODS set_gridlines .
ENDCLASS.



CLASS zcl_local_create_xls IMPLEMENTATION.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->ADD_BORDER_TO_STYLE
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD add_border_to_style.
    CLEAR r_border.
    r_border = document->create_simple_element( name = 'Borders' parent = r_style ).
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->ADD_ROW
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD add_row.
    CLEAR: r_row.
    r_row = document->create_simple_element( name = 'Row' parent = r_table ).

    ADD 1 TO v_current_row.
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->ADD_TABLE
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD add_table.
    CLEAR r_table.
    r_table = document->create_simple_element( name = 'Table' parent = r_worksheet ).
    r_table->set_attribute_ns( name = 'FullColumns' prefix = 'x' value = '1' ).
    r_table->set_attribute_ns( name = 'FullRows' prefix = 'x' value = '1' ).
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->ADD_WORKSHEET
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_WORKSHEET                   TYPE        STRING
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD add_worksheet.
    r_worksheet = document->create_simple_element( name = 'Worksheet' parent = element_root ).
    r_worksheet->set_attribute_ns( name = 'Name' prefix = 'ss' value = im_worksheet ).
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method zcl_local_create_xls->CONSTRUCTOR
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_TITLE                       TYPE        STRING(optional)
* | [--->] IM_WORKSHEET                   TYPE        STRING(optional)
* | [--->] IM_AUTHOR                      TYPE        STRING(optional)
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD constructor.
    v_title        = im_title.
    v_worksheet    = COND #( WHEN im_worksheet IS NOT INITIAL
                             THEN im_worksheet ).
    v_author       = im_author.
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->CREATE_DOCUMENT
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD create_document.
    CLEAR: ixml, document, element_root.

    " Creating a ixml Factory
    ixml = cl_ixml=>create( ).

    " Creating the DOM Object Model
    document = ixml->create_document( ).

    " Create Root Node 'Workbook'
    element_root = document->create_simple_element( name = 'Workbook' parent = document ).
    element_root->set_attribute( name = 'xmlns' value = 'urn:schemas-microsoft-com:office:spreadsheet' ).

    DATA(ns_attribute) = document->create_namespace_decl( name = 'ss' prefix = 'xmlns' uri = 'urn:schemas-microsoft-com:office:spreadsheet ' ).
    element_root->set_attribute_node( ns_attribute ).

    ns_attribute = document->create_namespace_decl( name = 'x' prefix = 'xmlns' uri = 'urn:schemas-microsoft-com:office:excel' ).
    element_root->set_attribute_node( ns_attribute ).
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->CREATE_STYLE
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_STYLE                       TYPE        STRING
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD create_style.
    CHECK im_style IS NOT INITIAL.
    CLEAR: r_style.
    r_style = document->create_simple_element( name = 'Style' parent = r_styles ).
    r_style->set_attribute_ns( name = 'ID' prefix = 'ss' value = im_style ).
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->CREATE_STYLES
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD create_styles.
    " Styles
    r_styles = document->create_simple_element( name = 'Styles' parent = element_root ).

    " No Style
    create_style( im_style = 'NoStyle' ).

    " Title
    create_style( im_style = 'Title' ).
    format_font( im_bold = '1' ).
    format_alignment( im_h_align = 'Center' im_v_align = 'Center' im_wraptext = '1' ).

    " Style for HeaderTop
    create_style( im_style = 'HeaderTop' ).
    format_font( im_bold = '1' ).
*    format_interior( im_color = '#BFBFBF' im_pattern = 'Solid' ).
    format_alignment( im_h_align = 'Center' im_v_align = 'Center' im_wraptext = '1' ).
    add_border_to_style( ).
    format_border( im_position = 'Top' im_linestyle = 'Continuous' im_weight = '1' ).
    format_border( im_position = 'Left' im_linestyle = 'Continuous' im_weight = '1' ).
    format_border( im_position = 'Right' im_linestyle = 'Continuous' im_weight = '1' ).

    " Style for HeaderLine
    create_style( im_style = 'HeaderLine' ).
    format_font( im_bold = '1' ).
*    format_interior( im_color = '#BFBFBF' im_pattern = 'Solid' ).
    format_alignment( im_h_align = 'Center' im_v_align = 'Center' im_wraptext = '1' ).
    add_border_to_style( ).
    format_border( im_position = 'Top' im_linestyle = 'Continuous' im_weight = '1' ).
    format_border( im_position = 'Left' im_linestyle = 'Continuous' im_weight = '1' ).
    format_border( im_position = 'Right' im_linestyle = 'Continuous' im_weight = '1' ).
    format_border( im_position = 'Bottom' im_linestyle = 'Continuous' im_weight = '1' ).

    " Style for HeaderLineDate
    create_style( im_style = 'HeaderLineDate' ).
    format_number( im_numberformat = 'Short Date' ).
    format_font( im_bold = '1' ).
*    format_interior( im_color = '#BFBFBF' im_pattern = 'Solid' ).
    format_alignment( im_h_align = 'Center' im_v_align = 'Center' im_wraptext = '1' ).
    add_border_to_style( ).
    format_border( im_position = 'Top' im_linestyle = 'Continuous' im_weight = '1' ).
    format_border( im_position = 'Left' im_linestyle = 'Continuous' im_weight = '1' ).
    format_border( im_position = 'Right' im_linestyle = 'Continuous' im_weight = '1' ).
    format_border( im_position = 'Bottom' im_linestyle = 'Continuous' im_weight = '1' ).

    " Style for Item Header
    create_style( im_style = 'ItemTop' ).
    format_font( im_bold = '1' ).
*    format_interior( im_color = '#BFBFBF' im_pattern = 'Solid' ).
    format_alignment( im_h_align = 'Center' im_v_align = 'Center' im_wraptext = '1').
    add_border_to_style( ).
    format_border( im_position = 'Top' im_linestyle = 'Continuous' im_weight = '1' ).
    format_border( im_position = 'Left' im_linestyle = 'Continuous' im_weight = '1' ).
    format_border( im_position = 'Right' im_linestyle = 'Continuous' im_weight = '1' ).
    format_border( im_position = 'Bottom' im_linestyle = 'Continuous' im_weight = '1' ).

    " Style for Data
    create_style( im_style = 'Data' ).
    format_alignment( im_h_align = 'Left' im_v_align = 'Center' im_wraptext = '1').
    add_border_to_style( ).
    format_border( im_position = 'Top' im_linestyle = 'Dot' im_weight = '1' ).
    format_border( im_position = 'Left' im_linestyle = 'Continuous' im_weight = '1' ).
    format_border( im_position = 'Right' im_linestyle = 'Continuous' im_weight = '1' ).
    format_border( im_position = 'Bottom' im_linestyle = 'Dot' im_weight = '1' ).

    " Style for DataShortDate
    create_style( im_style = 'DataShortDate' ).
    format_number( im_numberformat = 'Short Date' ).
    format_alignment( im_h_align = 'Left' im_v_align = 'Center' im_wraptext = '1').
    add_border_to_style( ).
    format_border( im_position = 'Top' im_linestyle = 'Dot' im_weight = '1' ).
    format_border( im_position = 'Left' im_linestyle = 'Continuous' im_weight = '1' ).
    format_border( im_position = 'Right' im_linestyle = 'Continuous' im_weight = '1' ).
    format_border( im_position = 'Bottom' im_linestyle = 'Dot' im_weight = '1' ).

    " Style for DataNumber
    create_style( im_style = 'DataNumber' ).
    format_number( im_numberformat = 'Fixed' ).
    format_alignment( im_h_align = 'Right' im_v_align = 'Center' im_wraptext = '1' ).
    add_border_to_style( ).
    format_border( im_position = 'Top' im_linestyle = 'Dot' im_weight = '1' ).
    format_border( im_position = 'Left' im_linestyle = 'Continuous' im_weight = '1' ).
    format_border( im_position = 'Right' im_linestyle = 'Continuous' im_weight = '1' ).
    format_border( im_position = 'Bottom' im_linestyle = 'Dot' im_weight = '1' ).

    " Style for DataNumberAmount
    create_style( im_style = 'DataNumberAmount' ).
    format_number( im_numberformat = 'Standard' ).
    format_alignment( im_h_align = 'Right' im_v_align = 'Center' im_wraptext = '1' ).
    add_border_to_style( ).
    format_border( im_position = 'Top' im_linestyle = 'Dot' im_weight = '1' ).
    format_border( im_position = 'Left' im_linestyle = 'Continuous' im_weight = '1' ).
    format_border( im_position = 'Right' im_linestyle = 'Continuous' im_weight = '1' ).
    format_border( im_position = 'Bottom' im_linestyle = 'Dot' im_weight = '1' ).

    " Style for DataNumber3Dec
    create_style( im_style = 'DataNumber3Dec' ).
    format_number( im_numberformat = '# ##0.000' ).
    format_alignment( im_h_align = 'Right' im_v_align = 'Center' im_wraptext = '1' ).
    add_border_to_style( ).
    format_border( im_position = 'Top' im_linestyle = 'Dot' im_weight = '1' ).
    format_border( im_position = 'Left' im_linestyle = 'Continuous' im_weight = '1' ).
    format_border( im_position = 'Right' im_linestyle = 'Continuous' im_weight = '1' ).
    format_border( im_position = 'Bottom' im_linestyle = 'Dot' im_weight = '1' ).

    " Style for DataNumber4Dec
    create_style( im_style = 'DataNumber4Dec' ).
    format_number( im_numberformat = '# ##0.0000' ).
    add_border_to_style( ).
    format_border( im_position = 'Top' im_linestyle = 'Continuous' im_weight = '1' ).
    format_border( im_position = 'Left' im_linestyle = 'Continuous' im_weight = '1' ).
    format_border( im_position = 'Right' im_linestyle = 'Continuous' im_weight = '1' ).
    format_border( im_position = 'Bottom' im_linestyle = 'Continuous' im_weight = '1' ).

    " Style for Total
    create_style( im_style = 'Total' ).
    format_font( im_bold = '1' ).
    format_alignment( im_h_align = 'Center' im_v_align = 'Center' im_wraptext = '1' ).
    format_interior( im_color = '#FADA5E' im_pattern = 'Solid' ).
    add_border_to_style( ).
    format_border( im_position = 'Top' im_linestyle = 'Continuous' im_weight = '1' ).
    format_border( im_position = 'Left' im_linestyle = 'Continuous' im_weight = '1' ).
    format_border( im_position = 'Right' im_linestyle = 'Continuous' im_weight = '1' ).
    format_border( im_position = 'Bottom' im_linestyle = 'Continuous' im_weight = '1' ).

    " Style for TotalNum
    create_style( im_style = 'TotalNum' ).
    format_font( im_bold = '1' ).
    format_interior( im_color = '#FADA5E' im_pattern = 'Solid' ).
    format_number( im_numberformat = 'Fixed' ).
    format_alignment( im_h_align = 'Right' im_v_align = 'Center' im_wraptext = '1' ).
    add_border_to_style( ).
    format_border( im_position = 'Top' im_linestyle = 'Continuous' im_weight = '1' ).
    format_border( im_position = 'Left' im_linestyle = 'Continuous' im_weight = '1' ).
    format_border( im_position = 'Right' im_linestyle = 'Continuous' im_weight = '1' ).
    format_border( im_position = 'Bottom' im_linestyle = 'Continuous' im_weight = '1' ).

    " Style for TotalNumAmount
    create_style( im_style = 'TotalNumAmount' ).
    format_font( im_bold = '1' ).
    format_interior( im_color = '#FADA5E' im_pattern = 'Solid' ).
    format_number( im_numberformat = 'Standard' ).
    format_alignment( im_h_align = 'Right' im_v_align = 'Center' im_wraptext = '1' ).
    add_border_to_style( ).
    format_border( im_position = 'Top' im_linestyle = 'Continuous' im_weight = '1' ).
    format_border( im_position = 'Left' im_linestyle = 'Continuous' im_weight = '1' ).
    format_border( im_position = 'Right' im_linestyle = 'Continuous' im_weight = '1' ).
    format_border( im_position = 'Bottom' im_linestyle = 'Continuous' im_weight = '1' ).

    " Style for TotalNum3Dec
    create_style( im_style = 'TotalNum3Dec' ).
    format_font( im_bold = '1' ).
    format_interior( im_color = '#FADA5E' im_pattern = 'Solid' ).
    format_number( im_numberformat = '# ##0.000' ).
    format_alignment( im_h_align = 'Right' im_v_align = 'Center' im_wraptext = '1' ).
    add_border_to_style( ).
    format_border( im_position = 'Top' im_linestyle = 'Continuous' im_weight = '1' ).
    format_border( im_position = 'Left' im_linestyle = 'Continuous' im_weight = '1' ).
    format_border( im_position = 'Right' im_linestyle = 'Continuous' im_weight = '1' ).
    format_border( im_position = 'Bottom' im_linestyle = 'Continuous' im_weight = '1' ).

    " Style for TotalNum4Dec
    create_style( im_style = 'TotalNum4Dec' ).
    format_font( im_bold = '1' ).
    format_interior( im_color = '#FADA5E' im_pattern = 'Solid' ).
    format_number( im_numberformat = '# ##0.0000' ).
    format_alignment( im_h_align = 'Right' im_v_align = 'Center' im_wraptext = '1' ).
    add_border_to_style( ).
    format_border( im_position = 'Top' im_linestyle = 'Continuous' im_weight = '1' ).
    format_border( im_position = 'Left' im_linestyle = 'Continuous' im_weight = '1' ).
    format_border( im_position = 'Right' im_linestyle = 'Continuous' im_weight = '1' ).
    format_border( im_position = 'Bottom' im_linestyle = 'Continuous' im_weight = '1' ).

    " Style for FooterNote
    create_style( im_style = 'FooterNote' ).
    format_font( im_bold = '1' ).
    format_number( im_numberformat = 'Fixed' ).
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->CREATE_XLS_FILE
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD create_xls_file.
    LOOP AT xml_table INTO DATA(wa_xml).
      s_xls_bin-line = wa_xml.
      APPEND s_xls_bin TO t_xls_bin.
    ENDLOOP.
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->CREATE_XML_DOCUMENT
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD create_xml_document.
    create_document( ).
    document_properties( ).
    create_styles( ).

    add_worksheet( im_worksheet = v_worksheet ).
    add_table( ).

    " Format columns width based on the data length
    format_width_column( ).

    " TITLE **************************
    fill_title( ).
    " HEADER *************************
    fill_header_rows( ).
    " ITEM ***************************
    fill_item_rows( ).
    " TOTAL **************************
    fill_total( ).

    " Disable grid layout
    set_gridlines( ).
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->DDIF_FIELDINFO_GET
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD ddif_fieldinfo_get.
    IF s_xls_header IS NOT INITIAL.
      FIELD-SYMBOLS: <fs_xls_header> TYPE any.
      ASSIGN s_xls_header->* TO <fs_xls_header>.
      DATA(lv_header_name) = CONV string( cl_abap_typedescr=>describe_by_data( <fs_xls_header> )->absolute_name+6 ).

      CALL FUNCTION 'DDIF_FIELDINFO_GET'
        EXPORTING
          tabname        = CONV tabname( lv_header_name )
        TABLES
          dfies_tab      = t_dfies_header
        EXCEPTIONS
          not_found      = 1
          internal_error = 2
          OTHERS         = 3.
    ENDIF.

    IF s_xls_item IS NOT INITIAL.
      FIELD-SYMBOLS: <fs_xls_item> TYPE any.
      ASSIGN s_xls_item->* TO <fs_xls_item>.
      DATA(lv_item_name)   = CONV string( cl_abap_typedescr=>describe_by_data( <fs_xls_item> )->absolute_name+6 ).

      CALL FUNCTION 'DDIF_FIELDINFO_GET'
        EXPORTING
          tabname        = CONV tabname( lv_item_name )
        TABLES
          dfies_tab      = t_dfies_item
        EXCEPTIONS
          not_found      = 1
          internal_error = 2
          OTHERS         = 3.
    ENDIF.
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->DOCUMENT_PROPERTIES
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD document_properties.
    DATA(lr_element_properties) = document->create_simple_element( name = 'DocumentProperties' parent = element_root ).
    lr_element_properties->set_attribute( name = 'xmlns' value = 'urn:schemas-microsoft-com:office:office' ).
    document->create_simple_element( name = 'Author' value = v_author parent = lr_element_properties ).
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method zcl_local_create_xls->EXECUTE
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_HEADER                      TYPE        ANY
* | [--->] IM_ITEM                        TYPE        ANY
* | [--->] IM_ITEM_TAB                    TYPE        ANY TABLE
* | [--->] IM_DO_SUM                      TYPE        BOOLEAN(optional)
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD execute.
    TRY.
        v_do_sum = im_do_sum.

        " Dynamically get the input data
        get_data_reference( im_header   = im_header
                            im_item     = im_item
                            im_item_tab = im_item_tab ).

        " XML schema
        create_xml_document( ).

        " Rendering the Document
        render_xml_document( ).

        " Creation of the Binary file
        create_xls_file( ).

      CATCH cx_root INTO DATA(lo_root).
        APPEND VALUE bapiret2(
                               type       = sy-abcde+4(1)
                               message    = lo_root->get_text( )
                             ) TO t_return.
    ENDTRY.
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->ADD_CELL
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_STYLE                       TYPE        STRING
* | [--->] IM_FORMULA                     TYPE        STRING(optional)
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD add_cell.
    CLEAR r_cell.
    r_cell = document->create_simple_element( name = 'Cell' parent = r_row ).
    r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = im_style ).

    IF im_formula IS NOT INITIAL.
      r_cell->set_attribute_ns( name = 'Formula' prefix = 'ss' value = im_formula ).
    ENDIF.
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->FILL_DATA_CELL
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_CELL_NAME                   TYPE        STRING
* | [--->] IM_DATATYPE                    TYPE        STRING
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD fill_data_cell.
    DATA(lv_aux_str) = im_cell_name.

    " Remove sign "-" from the end and put it on the beginning.
    IF  im_datatype EQ 'Number'
    AND im_cell_name CA '-'.
      REPLACE ALL OCCURRENCES OF '-' IN lv_aux_str WITH ''.
      CONCATENATE '-' lv_aux_str INTO lv_aux_str.
    ENDIF.

    DATA(lr_data) = document->create_simple_element( name = 'Data' value = lv_aux_str parent = r_cell ).
    lr_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = im_datatype ).
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->FILL_HEADER_ROWS
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD fill_header_rows.
    CHECK t_dfies_header IS NOT INITIAL.

    " Row Header Top
    add_row( ).
    format_height_row( im_autofitheight = '1' ).

    LOOP AT t_dfies_header INTO DATA(ls_dfies_header).
      add_cell( im_style = 'NoStyle' ).
      " Field label: Short - Medium - long - Heading
      fill_data_cell( im_cell_name = CONV #( ls_dfies_header-scrtext_l ) im_datatype = 'String' ).
    ENDLOOP.

    " Row Header Line
    add_row( ).
    format_height_row( im_autofitheight = '1' ).

    FIELD-SYMBOLS: <fs_xls_header> TYPE any.
    ASSIGN s_xls_header->* TO <fs_xls_header>.

    LOOP AT t_dfies_header INTO ls_dfies_header.
      ASSIGN COMPONENT sy-tabix OF STRUCTURE <fs_xls_header> TO FIELD-SYMBOL(<fs_header_value>).

      DATA(descr_ref) = cl_abap_typedescr=>describe_by_data( <fs_header_value> ).
      CASE descr_ref->type_kind.
        WHEN cl_abap_typedescr=>typekind_date.
          DATA: lv_date_out(10).
          WRITE <fs_header_value> TO lv_date_out DD/MM/YYYY.
          REPLACE ALL OCCURRENCES OF '.' IN lv_date_out WITH '/'.
          add_cell( im_style = 'NoStyle' ).
          fill_data_cell( im_cell_name = CONV #( lv_date_out ) im_datatype = 'String' ).

        WHEN OTHERS.
          add_cell( im_style = 'NoStyle' ).
          fill_data_cell( im_cell_name = CONV #( <fs_header_value> ) im_datatype = 'String' ).
      ENDCASE.
    ENDLOOP.

    add_row( ).
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->FILL_ITEM_ROWS
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD fill_item_rows.
    DATA: lv_date_out(10),
          lv_num_out TYPE char20.

    CHECK t_dfies_item IS NOT INITIAL.

    " Row Item Top
    add_row( ).
    format_height_row( im_autofitheight = '1' ).

    LOOP AT t_dfies_item INTO DATA(ls_dfies_item).
      add_cell( im_style = 'ItemTop' ).
      " Field label: Short - Medium - long - Heading
      fill_data_cell( im_cell_name = CONV #( ls_dfies_item-scrtext_l ) im_datatype = 'String' ).
    ENDLOOP.

    FIELD-SYMBOLS: <fs_xls_item> TYPE ANY TABLE.
    ASSIGN t_xls_item->* TO <fs_xls_item>.

    " Row Item Line
    LOOP AT <fs_xls_item> ASSIGNING FIELD-SYMBOL(<fs_xls_item_line>).
      add_row( ).
      format_height_row( im_autofitheight = '1' ).

      LOOP AT t_dfies_item INTO ls_dfies_item.
        ASSIGN COMPONENT sy-tabix OF STRUCTURE <fs_xls_item_line> TO FIELD-SYMBOL(<fs_item_value>).
        DATA(lr_descr_ref) = cl_abap_typedescr=>describe_by_data( <fs_item_value> ).

        CASE lr_descr_ref->type_kind.
          WHEN cl_abap_typedescr=>typekind_char.
            add_cell( im_style = 'Data' ).
            fill_data_cell( im_cell_name = CONV #( <fs_item_value> ) im_datatype = 'String' ).

          WHEN cl_abap_typedescr=>typekind_date.
            WRITE <fs_item_value> TO lv_date_out DD/MM/YYYY.
            REPLACE ALL OCCURRENCES OF '.' IN lv_date_out WITH '/'.
            add_cell( im_style = 'DataShortDate' ).
            fill_data_cell( im_cell_name = CONV #( lv_date_out ) im_datatype = 'String' ).

          WHEN cl_abap_typedescr=>typekind_int.
            lv_num_out = |{ <fs_item_value> ALPHA = IN }|.
            add_cell( im_style = 'DataNumber' ).
            fill_data_cell( im_cell_name = CONV #( lv_num_out ) im_datatype = 'Number' ).

          WHEN cl_abap_typedescr=>typekind_packed.
            lv_num_out = |{ <fs_item_value> ALPHA = IN }|.

            CASE lr_descr_ref->decimals.
              WHEN 2.
                add_cell( im_style = 'DataNumberAmount' ).
              WHEN 3.
                add_cell( im_style = 'DataNumber3Dec' ).
              WHEN 4.
                add_cell( im_style = 'DataNumber4Dec' ).
              WHEN OTHERS.
                add_cell( im_style = 'DataNumber' ).
            ENDCASE.

            fill_data_cell( im_cell_name = CONV #( lv_num_out ) im_datatype = 'Number' ).

          WHEN OTHERS.
            add_cell( im_style = 'Data' ).
            fill_data_cell( im_cell_name = CONV #( <fs_item_value> ) im_datatype = 'String' ).
        ENDCASE.
      ENDLOOP.
    ENDLOOP.
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->merge_CELL
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_STYLE                       TYPE        STRING
* | [--->] IM_MERGE_VALUE                 TYPE        STRING
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD merge_cell.
    add_cell( im_style = im_style ).
    r_cell->set_attribute_ns( name = 'MergeAcross' prefix = 'ss' value = im_merge_value ).
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->merge_DOWN_CELL
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_STYLE                       TYPE        STRING
* | [--->] IM_MERGE_VALUE                 TYPE        STRING
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD merge_down_cell.
    add_cell( im_style ).
    r_cell->set_attribute_ns( name = 'MergeDown' prefix = 'ss' value = im_merge_value ).
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->FILL_TITLE
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD fill_title.
    CHECK v_title IS NOT INITIAL.
    add_row( ).
    DATA(lv_lines) = lines( t_dfies_item ) - 1.
    merge_cell( im_style = 'Title' im_merge_value = CONV #( lv_lines ) ).
    fill_data_cell( im_cell_name = v_title im_datatype = 'String' ).
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->FILL_TOTAL
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD fill_total.
    CHECK v_do_sum IS NOT INITIAL.

    add_row( ).
    format_height_row( im_autofitheight = '1' ).

    FIELD-SYMBOLS: <fs_xls_item> TYPE ANY TABLE.
    ASSIGN t_xls_item->* TO <fs_xls_item>.

    DATA(lv_item_lines) = lines( <fs_xls_item> ).

    " Create Sum formula for each numeric column of the item table
    LOOP AT t_dfies_item INTO DATA(ls_dfies_item).
      DATA(lv_tabix) = sy-tabix.

      DATA(lv_formula) = '=SUM(R[-' && lv_item_lines && ']' && 'C:R[-1]C)'.

      DATA(lr_descr_ref) = cl_abap_typedescr=>describe_by_name( ls_dfies_item-rollname ).

      IF lr_descr_ref->type_kind EQ lr_descr_ref->typekind_int.
        add_cell( im_style = 'TotalNum' im_formula = lv_formula ).
        fill_data_cell( im_cell_name = space im_datatype = 'Number' ).

      ELSEIF lr_descr_ref->type_kind EQ lr_descr_ref->typekind_packed.

        CASE lr_descr_ref->decimals.
          WHEN 2.
            add_cell( im_style = 'TotalNumAmount' im_formula = lv_formula ).
          WHEN 3.
            add_cell( im_style = 'TotalNum3Dec' im_formula = lv_formula ).
          WHEN 4.
            add_cell( im_style = 'TotalNum4Dec' im_formula = lv_formula ).
          WHEN OTHERS.
            add_cell( im_style = 'TotalNum' im_formula = lv_formula ).
        ENDCASE.

        fill_data_cell( im_cell_name = space im_datatype = 'Number' ).
      ELSE.
        IF lv_tabix = 1.
          " Total text
          add_cell( im_style = 'Total' ).
          fill_data_cell( im_cell_name = 'TOTAIS' im_datatype = 'String' ).
        ELSE.
          add_cell( im_style = 'Total' ).
          fill_data_cell( im_cell_name = '' im_datatype = 'String' ).
        ENDIF.
      ENDIF.
    ENDLOOP.
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->FORMAT_ALIGNMENT
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_H_ALIGN                     TYPE        STRING(optional)
* | [--->] IM_V_ALIGN                     TYPE        STRING(optional)
* | [--->] IM_WRAPTEXT                    TYPE        STRING(optional)
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD format_alignment.
    DATA(lr_format) = document->create_simple_element( name = 'Alignment' parent = r_style ).

    " Horizontal Alignment: Center - CenterAcrossSelection - Distributed - Fill - General - Justify - Left - Right
    IF im_h_align IS NOT INITIAL.
      lr_format->set_attribute_ns( name = 'Horizontal' prefix = 'ss' value = im_h_align ).
    ENDIF.

    " Vertical Alignment: Bottom - Center - Distributed - Justify - Top
    IF im_v_align IS NOT INITIAL.
      lr_format->set_attribute_ns( name = 'Vertical' prefix = 'ss' value = im_v_align ).
    ENDIF.

    " Text Control: WrapText - ShrinkToFit - MergeCells
    " 0 - Inactive 1 - Active
    IF im_wraptext IS NOT INITIAL.
      lr_format->set_attribute_ns( name = 'WrapText' prefix = 'ss' value = im_wraptext ).
    ENDIF.
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->FORMAT_BORDER
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_POSITION                    TYPE        STRING(optional)
* | [--->] IM_LINESTYLE                   TYPE        STRING(optional)
* | [--->] IM_WEIGHT                      TYPE        STRING(optional)
* | [--->] IM_COLOR                       TYPE        STRING(optional)
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD format_border.
    DATA(lr_format) = document->create_simple_element( name = 'Border' parent = r_border ).

    " Position: Top - Left - Right - Bottom - DiagonalUp
    IF im_position IS NOT INITIAL.
      lr_format->set_attribute_ns( name = 'Position' prefix = 'ss' value = im_position ).
    ENDIF.

    " Line style: Continuos - Dot - DashDot - Dash- SlantDashDot - Double
    IF im_linestyle IS NOT INITIAL.
      lr_format->set_attribute_ns( name = 'LineStyle' prefix = 'ss' value = im_linestyle ).
    ENDIF.

    " Line weight: Thin - Medium - Thick
    IF im_weight IS NOT INITIAL.
      lr_format->set_attribute_ns( name = 'Weight' prefix = 'ss' value = im_weight ).
    ENDIF.

    " Color
    IF im_color IS NOT INITIAL.
      lr_format->set_attribute_ns( name = 'Color' prefix = 'ss' value = im_color ).
    ENDIF.
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->FORMAT_FONT
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_FONTNAME                    TYPE        STRING(optional)
* | [--->] IM_SIZE                        TYPE        STRING(optional)
* | [--->] IM_BOLD                        TYPE        STRING(optional)
* | [--->] IM_ITALIC                      TYPE        STRING(optional)
* | [--->] IM_UNDERLINE                   TYPE        STRING(optional)
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD format_font.
    DATA(lr_format) = document->create_simple_element( name = 'Font' parent = r_style ).

    " Name
    IF im_fontname IS NOT INITIAL.
      lr_format->set_attribute_ns( name = 'FontName' prefix = 'ss' value = im_fontname ).
    ENDIF.

    " Size
    IF im_size IS NOT INITIAL.
      lr_format->set_attribute_ns( name = 'Size' prefix = 'ss' value = im_size ).
    ENDIF.

    " Bold: 0 - Inactive 1 - Active
    IF im_bold IS NOT INITIAL.
      lr_format->set_attribute_ns( name = 'Bold' prefix = 'ss' value = im_bold ).
    ENDIF.

    " Italic: 0 - Inactive 1 - Active
    IF im_italic IS NOT INITIAL.
      lr_format->set_attribute_ns( name = 'Italic' prefix = 'ss' value = im_italic ).
    ENDIF.

    " Underline: Single - Singular
    IF im_underline IS NOT INITIAL.
      lr_format->set_attribute_ns( name = 'Underline' prefix = 'ss' value = im_underline ).
    ENDIF.
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->FORMAT_HEIGHT_ROW
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_AUTOFITHEIGHT               TYPE        STRING
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD format_height_row.
    IF im_autofitheight IS NOT INITIAL.
      r_row->set_attribute_ns( name = 'AutoFitHeight' prefix = 'ss' value = im_autofitheight ).
    ENDIF.
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->FORMAT_INTERIOR
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_COLOR                       TYPE        STRING(optional)
* | [--->] IM_PATTERN                     TYPE        STRING(optional)
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD format_interior.
    DATA(lr_format) = document->create_simple_element( name = 'Interior' parent = r_style ).

    IF im_color IS NOT INITIAL.
      lr_format->set_attribute_ns( name = 'Color' prefix = 'ss' value = im_color ).
    ENDIF.

    IF im_pattern IS NOT INITIAL.
      lr_format->set_attribute_ns( name = 'Pattern' prefix = 'ss' value = im_pattern ).
    ENDIF.
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->FORMAT_NUMBER
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_NUMBERFORMAT                TYPE        STRING
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD format_number.
    DATA(lr_format) = document->create_simple_element( name = 'NumberFormat' parent = r_style ).

    " Number format: General - Number - Currency - Date - Time - Percentage - ...
    IF im_numberformat IS NOT INITIAL.
      lr_format->set_attribute_ns( name = 'Format' prefix = 'ss' value = im_numberformat ).
    ENDIF.
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->FORMAT_WIDTH_COLUMN
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD format_width_column.
    LOOP AT t_dfies_item INTO DATA(ls_dfies_item).
      DATA(lr_column) = document->create_simple_element( name = 'Column' parent = r_table ).
      lr_column->set_attribute_ns( name = 'AutoFitWidth' prefix = 'ss' value = '1' ).
      ls_dfies_item-outputlen = COND #( WHEN ls_dfies_item-outputlen GE 5
                                        THEN ls_dfies_item-outputlen * 8
                                        WHEN ls_dfies_item-datatype = 'STRG' " String
                                        THEN 255
                                        ELSE 40 ).

      " Maximum limit - 255 characters
      ls_dfies_item-outputlen = COND #( WHEN ls_dfies_item-outputlen GT 255
                                        THEN 255
                                        ELSE ls_dfies_item-outputlen ).

      lr_column->set_attribute_ns( name = 'Width' prefix = 'ss' value = CONV #( ls_dfies_item-outputlen ) ).
    ENDLOOP.
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->GET_DATA_REFERENCE
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_HEADER                      TYPE        ANY
* | [--->] IM_ITEM                        TYPE        ANY
* | [--->] IM_ITEM_TAB                    TYPE        ANY TABLE
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD get_data_reference.
    IF im_header IS NOT INITIAL.
       GET REFERENCE OF im_header   INTO s_xls_header.
    ENDIF.
    
    IF im_item IS NOT INITIAL.
       GET REFERENCE OF im_item     INTO s_xls_item.
    ENDIF.

    IF im_item_tab IS NOT INITIAL.
       GET REFERENCE OF im_item_tab INTO t_xls_item.
    ENDIF.

    " Get data dictionary information
    ddif_fieldinfo_get( ).
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->RENDER_XML_DOCUMENT
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD render_xml_document.
    " Creating a Stream Factory
    DATA(lr_streamfactory) = ixml->create_stream_factory( ).

    " Connect Internal XML Table to Stream Factory
    DATA(lr_ostream) = lr_streamfactory->create_ostream_itable( table = xml_table ).

    " Rendering the Document
    DATA(lr_renderer) = ixml->create_renderer( ostream = lr_ostream document = document ).
    lr_renderer->set_no_escaping( '&' ).

    " Renders the attached XML document into the output stream
    DATA(lr_rc) = lr_renderer->render( ).

    " Saving the XML Document
    xml_size = lr_ostream->get_num_written_raw( ).
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method zcl_local_create_xls->SAVE_XLS_FILE
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_XLS_FILENAME                TYPE        STRING
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD save_xls_file.
    DATA: lv_filename    TYPE string,
          lv_path        TYPE string,
          lv_fullpath    TYPE string,
          lv_user_action TYPE i.

    " Display save dialog window
    CALL METHOD cl_gui_frontend_services=>file_save_dialog
      EXPORTING
        file_filter               = c_file_filter
        default_extension         = c_file_extension
        default_file_name         = im_xls_filename
        initial_directory         = c_path
      CHANGING
        filename                  = lv_filename
        path                      = lv_path
        fullpath                  = lv_fullpath
        user_action               = lv_user_action
      EXCEPTIONS
        cntl_error                = 1
        error_no_gui              = 2
        invalid_default_file_name = 3
        not_supported_by_gui      = 4
        OTHERS                    = 5.

    IF sy-subrc = 0.
      IF lv_user_action = 0.
        cl_gui_frontend_services=>gui_download( EXPORTING filename = lv_fullpath
                                                          filetype = 'BIN'
                                                 CHANGING data_tab = t_xls_bin
                                               EXCEPTIONS OTHERS   = 1 ).
      ENDIF.
    ELSE.
      MESSAGE e001(00) WITH 'Error saving file.' INTO DATA(lv_message).
      APPEND VALUE bapiret2(
                             id = sy-msgid
                             type = sy-msgty
                             number = sy-msgno
                             message    = lv_message
                             message_v1 = sy-msgv1
                             message_v2 = sy-msgv2
                             message_v3 = sy-msgv3
                             message_v4 = sy-msgv4
                           ) TO t_return.
    ENDIF.
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method zcl_local_create_xls->SET_GRIDLINES
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD set_gridlines.
    DATA(r_worksheetoptions) = document->create_simple_element( name = 'WorksheetOptions' parent = r_worksheet ).
    r_worksheetoptions->set_attribute( name = 'xmlns' value = 'urn:schemas-microsoft-com:office:excel' ).
    document->create_simple_element( name = 'DoNotDisplayGridlines' parent = r_worksheetoptions ).
  ENDMETHOD.
ENDCLASS.

 

Sample Report code

*&---------------------------------------------------------------------*
*& Report Z_TESTE_CREATE_XLS
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT z_teste_create_xls.

" Local Class ****************************
INCLUDE z_local_class_create_xls.

START-OF-SELECTION.
  " Set ramdom offset
  DATA: lv_offset TYPE integer2.

  CALL FUNCTION 'RANDOM_I2'
    EXPORTING
      rnd_min   = 1
      rnd_max   = 100
    IMPORTING
      rnd_value = lv_offset.

  " User Roles
  DATA: ls_usr_name  TYPE v_usr_name,
        ls_agr_users TYPE agr_users,
        lt_agr_users TYPE TABLE OF agr_users.

  " Selects the user name
  SELECT SINGLE *
    INTO ls_usr_name
    FROM v_usr_name
   WHERE bname = sy-uname.

  " Assignment of roles to users
  SELECT *
    INTO TABLE lt_agr_users
    FROM agr_users
   WHERE uname = sy-uname.

  DATA(lo_xls_file) = NEW zcl_local_create_xls( im_title     = 'User Roles Title'
                                                im_author    = CONV string( sy-uname )
                                                im_worksheet = 'User Roles' ).

  lo_xls_file->execute( im_header   = ls_usr_name
                        im_item     = ls_agr_users
                        im_item_tab = lt_agr_users
                        im_do_sum   = abap_false ).

  lo_xls_file->save_xls_file( 'User_roles' ).

  FREE lo_xls_file.

  " Purchase Order
  DATA: ls_ekko TYPE ekko,
        ls_ekpo TYPE ekpo,
        lt_ekpo TYPE TABLE OF ekpo.

  " Purchasing Document Header
  SELECT *
    FROM ekko
   ORDER BY ebeln
    INTO @ls_ekko UP TO 1 ROWS
  OFFSET @lv_offset.
  ENDSELECT.

  " Purchasing Document Item
  SELECT *
    INTO TABLE lt_ekpo
    FROM ekpo
   WHERE ebeln = ls_ekko-ebeln.

  lo_xls_file = NEW zcl_local_create_xls( im_author    = CONV string( sy-uname )
                                          im_worksheet = 'Purchase Order' ).

  lo_xls_file->execute( im_header   = ls_ekko
                        im_item     = ls_ekpo
                        im_item_tab = lt_ekpo
                        im_do_sum   = abap_true ).

  lo_xls_file->save_xls_file( 'PurchaseOrder_xls_file' ).

  FREE lo_xls_file.

  " Delivery
  DATA: ls_likp TYPE likp,
        ls_lips TYPE lips,
        lt_lips TYPE TABLE OF lips.

  " Delivery Header Data
  SELECT *
    FROM likp
   ORDER BY vbeln
    INTO @ls_likp UP TO 1 ROWS
  OFFSET @lv_offset.
  ENDSELECT.

  " Delivery Item data
  SELECT *
    INTO TABLE lt_lips
    FROM lips
   WHERE vbeln = ls_likp-vbeln.

  lo_xls_file = NEW zcl_local_create_xls( im_author           = CONV string( sy-uname )
                                          im_worksheet        = 'Delivery' ).

  lo_xls_file->execute( im_header   = ls_likp
                        im_item     = ls_lips
                        im_item_tab = lt_lips
                        im_do_sum   = abap_true ).

  lo_xls_file->save_xls_file( 'Delivery_xls_file' ).

  FREE lo_xls_file.

  " Doc material
  DATA: ls_mkpf TYPE mkpf,
        ls_mseg TYPE mseg,
        lt_mseg TYPE TABLE OF mseg.

  " Header Material Document
  SELECT *
    FROM mkpf
   ORDER BY mblnr
    INTO @ls_mkpf UP TO 1 ROWS
  OFFSET @lv_offset.
  ENDSELECT.

  " Document Segment Material
  SELECT *
    INTO TABLE lt_mseg
    FROM mseg
   WHERE mblnr = ls_mkpf-mblnr.

  lo_xls_file = NEW zcl_local_create_xls( "im_title            = 'Título'
                                             im_author           = CONV string( sy-uname )
                                             im_worksheet        = 'Doc. Material' ).

  lo_xls_file->execute( im_header   = ls_mkpf
                        im_item     = ls_mseg
                        im_item_tab = lt_mseg
                        im_do_sum   = abap_true ).

  lo_xls_file->save_xls_file( 'DocMaterial_xls_file' ).
11 Comments
You must be Logged on to comment or reply to a post.
  • That was my first thought … that ABAP2XLSX has been re-invented yet again…..

    Evere since ABAP2XLSX came out (2012?) approximately once every three months I see a new blog on SCN about how to programatically create a spreadsheet from ABAP.

     

      • I agree that this is far better than average.

        I also agree that some organisations have a real problem with using open source (in special snowflake ABAP only, they gleefully use it elsewhere) and so refuse to import ABAP2XLSX, abapGit etc..

        What they are really saying in this case is that as the product is not supported by SAP they will not be able to troubleshoot it because their developers are not good enough to understand ABAP code. They will not phrase it like that, but thats what they mean.

        If a company refuses to accept ABAP2XLSX then they either have to spend a load of money developing something similar, or have their users go without some truly useful functionality(at my place the users cannot live without it now) neither of which are good for the shareholders/stakeholders.

         

        • ” their developers are not good enough to understand ABAP code.”

          Just saw a recent development on a 7.50 system – literally less than 3 months old. A 4000 line perform is just the first of the issues…

          It’s in the transport release BADI. Which I’ve already got a nice easy to use OO framework for adding new checks – just implement an interface that does the specific check and done. If you don’t know where to start, try looking at existing implementation. Or read the documentation. But the sad fact is that the developer team we’ve got (offshore) simply don’t have the skills.

    • Hi Mauro,

      with this solution you set the attributes for each cell separately.

      So, if you want to fill a cell with a value or any other attribute like the color, you need to identify what cells you want to use instead of an entire row.

      You can determine the color through the style that you will use on method FILL_CELL, that is called on methods FILL_HEADER_ROWS and FILL_ITEM_ROWS, where you can put your conditions to change the cell color accordingly with your requirements.

      You can see the styles on method CREATE_STYLES. For each style you can use the method FORMAT_INTERIOR, passing the RGB color codes, to change the cell background color.