Upload XLSX with GUI_UPLOAD in a system 7.00

Question

Is it possible to load in an itab an excel XLSX using the cl_gui_frontend_services=>gui_upload?
The system is ECC6 7.00 an old one.
Thanks Elias

Answer 1

The method cl_gui_frontend_services=>gui_upload can be used to obtain the binary representation of an excel file (or any type of file on the computer running SAPGui). However, interpreting that blob of binary data and turning it into an internal table is a far more complicated issue. The XLSX format is very complex, and explaining how to parse it would exceed the scope of a stackoverflow answer.

Fortunately there is a 3rd party library available under a permissive open source license (Apache) that can do it: ABAP2XLSX. There is also a bunch of example programs that demonstrate how to use it with files on the frontend. Here is an example of my own that will open an arbitrary excel file from the frontend and write the content of the cell A1 of the first worksheet.

PARAMETERS p_upfile TYPE string. " <- Filename with path on client

DATA:
  reader       TYPE REF TO zcl_excel_reader_2007,
  lo_excel     TYPE REF TO zcl_excel,
  lo_worksheet TYPE REF TO zcl_excel_worksheet.

CREATE OBJECT reader.
excel = reader->zif_excel_reader~load_file( 
   i_filename = p_upfile 
   i_from_applserver = abap_false ).
worksheet = excel->get_worksheet_by_index( 1 ).
worksheet->get_cell( 
  EXPORTING ip_column = 'A' 
            ip_row    = '1' 
  IMPORTING ep_value = DATA(value) ).
WRITE value.

I believe that it should work on an old 7.00 system, but I don't have one available to check. If there is a syntax error, please tell me in the comments and I will try to fix it.

Answer 2

For this task a solution which uses ABAP OLE (Object linking and embedding) could be possible. I will share my code which you can adapt to your needs and which would work on a system 7.00. In the code below I intentionally do not use new ABAP syntax / feautres. Note that this approach requires an Excel installation on the frontend (which also means that it will not work in a background process).

For the case described below I will use the following simple excel sheet with 2 columns:

First let's define some technical types needed for parsing:

TYPES: BEGIN OF ty_zexcel_tabline,
         row   TYPE kcd_ex_row_n,
         col   TYPE kcd_ex_col_n,
         value TYPE string,
       END OF ty_zexcel_tabline,
       BEGIN OF ty_senderline,
         line(4096) TYPE c,
       END OF ty_senderline,
       ty_t_zexcel_tabline TYPE STANDARD TABLE OF ty_zexcel_tabline
                           WITH DEFAULT KEY,
       ty_t_senderline     TYPE STANDARD TABLE OF ty_senderline
                           WITH DEFAULT KEY.
CONSTANTS: gc_esc VALUE '"'.

Next we need to define a type ty_outtab which would reflect the layout of excel sheet we need to import:

TYPES: BEGIN OF ty_outtab,
         text   TYPE c LENGTH 20,
         amount TYPE i,
       END OF ty_outtab.
TYPES ty_t_outtab TYPE STANDARD TABLE OF ty_outtab.

In our case there are 2 fields: the first is character like and the second is integer.

For this example I would create a simple report, so we need a parameter to select an excel file:

PARAMETERS: pa_excel TYPE string OBLIGATORY LOWER CASE.

and an event on VALUE-REQUEST for pa_excel parameter to allow the file selection; pa_excel would contain the file name:

AT SELECTION-SCREEN ON VALUE-REQUEST FOR pa_excel.
  DATA lt_filename  TYPE filetable.
  DATA ls_filename  TYPE file_table.
  DATA l_rc         TYPE i.

  CALL METHOD cl_gui_frontend_services=>file_open_dialog
    EXPORTING
      multiselection          = ''
    CHANGING
      file_table              = lt_filename
      rc                      = l_rc
    EXCEPTIONS
      file_open_dialog_failed = 1
      cntl_error              = 2
      error_no_gui            = 3
      not_supported_by_gui    = 4
      OTHERS                  = 5.
  IF sy-subrc = 0.
    READ TABLE lt_filename INDEX 1 INTO ls_filename.
    pa_excel = ls_filename-filename.
  ENDIF.

Next inside START-OF-SELECTION event call the following code which would do the job:

  DATA: l_excel_inputfilename TYPE rlgrap-filename,
        lt_zexcel             TYPE ty_t_zexcel_tabline,
        ls_zexcel             LIKE LINE OF lt_zexcel,
        lt_outtab             TYPE ty_t_outtab,
        ls_outtab             LIKE LINE OF lt_outtab.

  l_excel_inputfilename = pa_excel.
  PERFORM z_excel_to_internal_table TABLES lt_zexcel
                                    USING  l_excel_inputfilename
                                           1 2
                                           3 100.

  LOOP AT lt_zexcel INTO ls_zexcel.
    AT NEW row.
      CLEAR ls_outtab.
    ENDAT.

    CASE ls_zexcel-col.
      WHEN 1.
        ls_outtab-text   = ls_zexcel-value.
      WHEN 2.
        ls_outtab-amount = ls_zexcel-value.
    ENDCASE.

    AT END OF row.
      APPEND ls_outtab TO lt_outtab.
    ENDAT.
  ENDLOOP.

  cl_demo_output=>display_data( EXPORTING value = lt_outtab ).

Data from excel sheet was imported into lt_outtab:

Note that you pass additional parameters to the form z_excel_to_internal_table: begin and end column numbers (in our example from 1 to 2) and also begin and end row numbers (in our example from 2 - so we are ignoring the first row with column titles - and up to 100 rows).

Afterwards in a loop you should fill the output structure appropriately.

Next there is a form z_excel_to_internal_table which does the importing work with some auxilary forms - just copy them:

FORM z_excel_to_internal_table TABLES intern     TYPE ty_t_zexcel_tabline
                               USING filename      LIKE rlgrap-filename
                                     i_begin_col TYPE   i
                                     i_begin_row TYPE   i
                                     i_end_col   TYPE i
                                     i_end_row   TYPE i.

  DATA: excel_tab    TYPE ty_t_senderline,
        ld_separator TYPE c,
        application  TYPE ole2_object,
        workbook     TYPE ole2_object,
        range        TYPE ole2_object,
        worksheet    TYPE ole2_object,
        h_cell       TYPE ole2_object,
        h_cell1      TYPE ole2_object,
        ld_rc        TYPE i.

  DEFINE m_message.
    CASE sy-subrc.
      WHEN 0.
      WHEN 1.
        MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
                WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
      WHEN OTHERS. RAISE upload_ole.
    ENDCASE.
  END-OF-DEFINITION.

  IF i_begin_row > i_end_row. RAISE inconsistent_parameters. ENDIF.
  IF i_begin_col > i_end_col. RAISE inconsistent_parameters. ENDIF.

  CLASS cl_abap_char_utilities DEFINITION LOAD.
  ld_separator = cl_abap_char_utilities=>horizontal_tab.

  IF application-header = space OR application-handle = -1.
    CREATE OBJECT application 'Excel.Application'.
    m_message.
  ENDIF.
  CALL METHOD  OF application    'Workbooks' = workbook.
  m_message.
  CALL METHOD  OF workbook 'Open'    EXPORTING #1 = filename.
  m_message.
  GET PROPERTY OF  application 'ACTIVESHEET' = worksheet.
  m_message.

  CALL METHOD OF worksheet 'Cells' = h_cell EXPORTING #1 = i_begin_row #2 = i_begin_col.
  m_message.
  CALL METHOD OF worksheet 'Cells' = h_cell1 EXPORTING #1 = i_end_row #2 = i_end_col.
  m_message.
  CALL METHOD  OF worksheet 'RANGE' = range EXPORTING #1 = h_cell #2 = h_cell1.
  m_message.
  CALL METHOD OF range 'SELECT'.
  m_message.
  CALL METHOD OF range 'COPY'.
  m_message.

  CALL METHOD cl_gui_frontend_services=>clipboard_import
    IMPORTING
      data       = excel_tab
    EXCEPTIONS
      cntl_error = 1
      OTHERS     = 4.
  IF sy-subrc <> 0.
    MESSAGE a037(alsmex).
  ENDIF.

  PERFORM separated_to_intern_convert TABLES excel_tab intern USING  ld_separator.

  REFRESH excel_tab.
  CALL METHOD cl_gui_frontend_services=>clipboard_export
    IMPORTING
      data       = excel_tab
    CHANGING
      rc         = ld_rc
    EXCEPTIONS
      cntl_error = 1
      OTHERS     = 4.

  CALL METHOD OF application 'QUIT'.
  m_message.

  FREE OBJECT h_cell.       m_message.
  FREE OBJECT h_cell1.      m_message.
  FREE OBJECT range.        m_message.
  FREE OBJECT worksheet.    m_message.
  FREE OBJECT workbook.     m_message.
  FREE OBJECT application.  m_message.
ENDFORM.

FORM separated_to_intern_convert TABLES i_tab       TYPE ty_t_senderline
                                        i_intern    TYPE ty_t_zexcel_tabline
                                 USING  i_separator TYPE c.
  DATA: l_sic_tabix LIKE sy-tabix,
        l_sic_col   TYPE kcd_ex_col,
        l_fdpos     LIKE sy-fdpos.

  REFRESH i_intern.

  LOOP AT i_tab.
    l_sic_tabix = sy-tabix.
    l_sic_col = 0.
    WHILE i_tab CA i_separator.
      l_fdpos = sy-fdpos.
      l_sic_col = l_sic_col + 1.
      PERFORM line_to_cell_separat TABLES i_intern
                                   USING  i_tab l_sic_tabix l_sic_col
                                          i_separator l_fdpos.
    ENDWHILE.
    IF i_tab <> space.
      CLEAR i_intern.
      i_intern-row = l_sic_tabix.
      i_intern-col = l_sic_col + 1.
      i_intern-value = i_tab.
      APPEND i_intern.
    ENDIF.
  ENDLOOP.
ENDFORM.                    " SEPARATED_TO_INTERN_CONVERT

FORM line_to_cell_separat TABLES i_intern    TYPE ty_t_zexcel_tabline
                          USING  i_line
                                 i_row       LIKE sy-tabix
                                 ch_cell_col TYPE kcd_ex_col
                                 i_separator TYPE c
                                 i_fdpos     LIKE sy-fdpos.
  DATA: l_string  TYPE ty_senderline,
        l_sic_int TYPE i.

  CLEAR i_intern.
  l_sic_int = i_fdpos.
  i_intern-row = i_row.
  l_string = i_line.
  i_intern-col = ch_cell_col.
  IF ( i_separator = ';' OR  i_separator = ',' ) AND
       l_string(1) = gc_esc.
    PERFORM line_to_cell_esc_sep USING l_string
                                       l_sic_int
                                       i_separator
                                       i_intern-value.
  ELSE.
    IF l_sic_int > 0.
      i_intern-value = i_line(l_sic_int).
    ENDIF.
  ENDIF.
  IF l_sic_int > 0.
    APPEND i_intern.
  ENDIF.
  l_sic_int = l_sic_int + 1.
  i_line = i_line+l_sic_int.
ENDFORM.

FORM line_to_cell_esc_sep USING i_string
                                i_sic_int      TYPE i
                                i_separator    TYPE c
                                i_intern_value TYPE ty_zexcel_tabline-value.
  DATA: l_int         TYPE i,
        l_cell_end(2).
  FIELD-SYMBOLS: <l_cell>.

  l_cell_end = gc_esc.
  l_cell_end+1 = i_separator .

  IF i_string CS gc_esc.
    i_string = i_string+1.
    IF i_string CS l_cell_end.
      l_int = sy-fdpos.
      ASSIGN i_string(l_int) TO <l_cell>.
      i_intern_value = <l_cell>.
      l_int = l_int + 2.
      i_sic_int = l_int.
      i_string = i_string+l_int.
    ELSEIF i_string CS gc_esc.
      l_int = sy-fdpos.
      ASSIGN i_string(l_int) TO <l_cell>.
      i_intern_value = <l_cell>.
      l_int = l_int + 1.
      i_sic_int = l_int.
      i_string = i_string+l_int.
      l_int = strlen( i_string ).
      IF l_int > 0 . MESSAGE x001(kx) . ENDIF.
    ELSE.
      MESSAGE x001(kx).
    ENDIF.
  ENDIF.
ENDFORM.