Introduction to SpreadsheetML in ABAP

SpreadsheetML is an XML dialect developed by Microsoft to represent information in an Excel workbook. This column, explores the concept, schema definition, and some scenarios in which it makes sense to programmatically generate Excel workbooks using Office 2003 and SpreadsheetML.

Excel workbooks using Office 2003 and SpreadsheetML

REPORT ZADN_SAMPLE.

*----------------------------------------------------------------------*
* -> CLASS cl_xls_generator DEFINITION
*----------------------------------------------------------------------*
CLASS cl_xls_generator DEFINITION.

  PUBLIC SECTION.

    METHODS : crete_xls_data,
              save_xls_data.

  PRIVATE SECTION.

    TYPES : BEGIN OF line_data,
            fname TYPE c LENGTH 50,
            lname TYPE c LENGTH 50,
            phone TYPE c LENGTH 20,
          END OF line_data.

    DATA :  v_filename  TYPE string,
            v_path      TYPE string,
            v_fullpath  TYPE string ,
            it_data     TYPE STANDARD TABLE OF line_data,
            it_xls      TYPE STANDARD TABLE OF string,
            wa_data     LIKE LINE OF it_data,
            wa_xls      LIKE LINE OF it_xls.

ENDCLASS.                    "cl_xls_generator DEFINITION

*----------------------------------------------------------------------*
* -> CLASS cl_xls_generator IMPLEMENTATION
*----------------------------------------------------------------------*
CLASS cl_xls_generator IMPLEMENTATION.

  METHOD crete_xls_data.

    DO 30 TIMES.

      CLEAR me->wa_data.

      me->wa_data-fname = `Rafael`.
      me->wa_data-lname = `Riso`.
      me->wa_data-phone = `(99) 9999 - 9999`.

      APPEND me->wa_data TO me->it_data.

    ENDDO.

  ENDMETHOD.                    "crete_xls_data

  METHOD save_xls_data.

    CLEAR me->wa_data.

    APPEND ''                                                  TO me->it_xls.
    APPEND ''  TO me->it_xls.
    APPEND ''                                                            TO me->it_xls.
    APPEND ''                                                   TO me->it_xls.
    APPEND ''                                                 TO me->it_xls.
    APPEND ''                                                            TO me->it_xls.
    APPEND ''                                                           TO me->it_xls.
    APPEND ''                                       TO me->it_xls.
    APPEND ''                                                             TO me->it_xls.
    APPEND ''                                             TO me->it_xls.
    APPEND ''                                             TO me->it_xls.
    APPEND ''                                             TO me->it_xls.
    APPEND ''                                                TO me->it_xls.
    APPEND ''                                                              TO me->it_xls.
    APPEND 'First Name'                         TO me->it_xls.
    APPEND ''                                                             TO me->it_xls.
    APPEND ''                                                              TO me->it_xls.
    APPEND 'Last Name'                          TO me->it_xls.
    APPEND ''                                                             TO me->it_xls.
    APPEND ''                                                              TO me->it_xls.
    APPEND 'Phone Number'                       TO me->it_xls.
    APPEND ''                                                             TO me->it_xls.
    APPEND ''                                                              TO me->it_xls.

    LOOP AT me->it_data INTO me->wa_data.

      CLEAR me->wa_xls.

      APPEND '' TO it_xls.

      APPEND '' TO it_xls.

      CONCATENATE '' me->wa_data-fname '' INTO me->wa_xls.

      APPEND me->wa_xls TO me->it_xls.

      CLEAR me->wa_xls.

      APPEND '' TO me->it_xls.

      APPEND '' TO me->it_xls.

      CONCATENATE '' me->wa_data-lname '' INTO me->wa_xls.

      APPEND me->wa_xls TO me->it_xls.

      CLEAR me->wa_xls.

      APPEND '' TO me->it_xls.

      APPEND '' TO me->it_xls.

      CONCATENATE '' me->wa_data-phone '' INTO me->wa_xls.

      APPEND me->wa_xls TO me->it_xls.

      CLEAR me->wa_xls.

      APPEND '' TO me->it_xls.

      APPEND '' TO me->it_xls.

    ENDLOOP.

    APPEND ''     TO me->it_xls.
    APPEND '' TO me->it_xls.
    APPEND ''  TO me->it_xls.

    CALL METHOD cl_gui_frontend_services=>file_save_dialog
      EXPORTING
        window_title         = 'Enter File Name'
        default_extension    = 'xls'
        file_filter          = '*.xls'
        initial_directory    = 'c:'
        prompt_on_overwrite  = 'X'
      CHANGING
        filename             = me->v_filename
        path                 = me->v_path
        fullpath             = me->v_fullpath
      EXCEPTIONS
        cntl_error           = 1
        error_no_gui         = 2
        not_supported_by_gui = 3
        OTHERS               = 4.

    IF sy-subrc <> 0.

      MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

    ENDIF.

    CALL METHOD cl_gui_frontend_services=>gui_download
      EXPORTING
        filename                = me->v_fullpath
        filetype                = 'ASC'
        show_transfer_status    = 'X'
      CHANGING
        data_tab                = me->it_xls[]
      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.                    "save_xls_data

ENDCLASS.                    "cl_xls_generator IMPLEMENTATION

************************************************************************
************************************************************************
*                                                                      *
*                           PROGRAM FLOW LOGIC                         *
*                                                                      *
************************************************************************

START-OF-SELECTION.

  DATA : o_cl_xls_generator TYPE REF TO cl_xls_generator.

  CREATE OBJECT o_cl_xls_generator.

  o_cl_xls_generator->crete_xls_data(  ).
  o_cl_xls_generator->save_xls_data(  ).