Introduction

In 2007, Microsoft embraced an open standard for Excel with the xlsx format. Google Docs is also interchangeable with this format. Searching for Excel upload and download ABAP programs, which support the open xlsx format can sometimes be time consuming. Here is a program which provides all of the code to upload and download an xlsx file. Most ABAP developers are aware of the ultimate solution, which is the open source abap2xlsx project. However, sometimes that may be difficult to install at a large customer, where the security organization rules. If you simply want to generate an xlsx spreadsheet, and read in a spreadsheet, the abap2xlsx library may be overkill. Standard SAP libraries are used here.

This Blog demonstrates a program that allows you to maintain a Z Table in SAP by entering data in an Excel spreadsheet in the xlsx format. This Blog will be useful for both new ABAPers, as well as experienced ABAPers trying to get familiar with the new ABAP language features. Within the program, code snippets are included which will show old ABAP syntax versus the new ABAP syntax (7.4+).

If you want to simply get the code and run it, then jump to the code at the end of this Blog.

This program was built to support this Blog for a Kotlin Application.

This Blog is organized as follows:

• Program Overview
  • End User Walk through
  • Code Overview
    • Create a Custom Table
    • Download – Extract and auto-open in Excel
    • Upload – Refresh your Z Table from Excel
• Import a CDS View into Excel
• Complete Program
• References

Software Versions:

• Application Server ABAP 7.54 SP00, SAPGUI Version 760.
• Eclipse Version: Version: 2019-03 (4.11.0), ABAP Development Tools 3.4.4
• Microsoft Excel for Office 365 – latest version
• Google Sheets – latest version

 

Program Overview

Here is an overview of the program flow, with method names in italics:

If we display our program in the Object List, we see the following methods, referenced in the above diagram:

End User Walk through

Let’s walk through an example of how the program will work.

We’ve created a custom Table named ZTEST_KOTLIN, consisting of the following fields, to simulate maintenance of a memo to a customer:

When you execute the program Z_EXCEL_MAINTENANCE, you will get the following selection screen:

Here is a description of these options:

• Open SAP Table in Excel
  • This option will fetch all records from the ZTEST_KOTLIN table, write an Excel file with extension xlsx to the user’s PC, then open the file in Excel.
• Refresh SAP Table from Excel
  • This option will read an Excel file from the user’s PC, wipe all records in the ZTEST_KOTLIN table and insert all records from the Excel file into the table. So, this will do a complete refresh.
• Z Table Name
  • Here, you can specify any Z Table of your choice to maintain.
• Excel File
  • This is the directory and file name specified on the user’s PC to either upload or download to or from. There is a drop-down for this field, to browse the user’s PC for a file.

Let’s assume we’ve just created our new Z Table, and it’s empty. We can start off with a template Excel file, by choosing the “Open SAP Table in Excel” option, which gives us an empty table with a header row. Choose a file name and directory on your PC. If we execute the program, with the above options, it will download the file “ExcelFile.xlsx” to our “c:\1\” directory on our PC, then launch Excel and show the file:

Next, let’s enter some test data. Click “Enable Editing”, and enter some test data:

Leave columns A thru E blank, as we’ll explain those later.

Save the file, then Alt-Tab back to our selection screen. Now click the option, “Refresh SAP Table from Excel”:

Execute the program, and you will get a pop-up with a record count of the refresh (number of records deleted) and the insert count:

Next, we can go look at the records in our ZTEST_KOTLIN table. Execute the SAP Transaction Code SE16, and enter table name ZTEST_KOTLIN and execute:

One nice feature, you can keep your Excel file open, edit the data, run the program and see the results in SAP all together. For example, tile your 3 sessions, as follows:

(1) Update your Excel File – Add a new row, and Save:

(2) Re-Execute the program:

(3) Refresh your SE16 screen , and see the newly added record in SAP:

Refresh Button:

You now have your own Excel SM30-like data maintenance tool!

Note, to upload the Excel data, you may have your Excel file open, but to download the data, you must have the file closed because it will be locked.

To see our Excel download again, wipe the records from your Excel file, and Save:

Close the Excel file, then go back to the program selection screen, and click “Open SAP Table in Excel”:

This will re-populate our Excel File from the table, download to the user’s computer, then open it:

Since we have our key field named “GUID” populated, we can also update the records by changing any non-key fields in our file, and uploading it again:

(1) Update the Excel Record, and Save:

(2) Re-run the program:

(3) See the update in our table:

Our custom method my_custom_table_logic was written for the table ZTEST_KOTLIN, where we evaluate this record key named “GUID”. You can add logic specific to your own custom Z table in this method.

Next, let’s walk through the code, to see how the above may be accomplished.

 

Code Overview

Create a Custom Table

For our example, we will create the custom table ZTEST_KOTLIN, but you may create any custom Z table you’d like and the program will still work. Simply enter your table name on the Selection Screen for “Z Table Name”:

Create the following Z Table in SAP:

Our data element for ZZ_NOTE is a string:

If you prefer Eclipse, we can see the following DDL for our Table:

@EndUserText.label : 'Kotlin Tester'
@AbapCatalog.enhancementCategory : #EXTENSIBLE_ANY
@AbapCatalog.tableCategory : #TRANSPARENT
@AbapCatalog.deliveryClass : #A
@AbapCatalog.dataMaintenance : #ALLOWED
define table ztest_kotlin {
  key mandt        : mandt not null;
  key guid         : /aif/exp_guid not null;
  date_updated     : sydatum;
  time_updated     : syuzeit;
  last_updated_by  : syuname;
  order_number     : vbeln;
  customer_number  : kunag;
  customer_message : zz_note;

}

We have a special method in our program called “my_custom_table_logic”. This method was written specifically for our ZTEST_KOTLIN  table, however, the program will still run if you define your own table, and this method will simply ignore the fields. More on this later.

As our diagram showed earlier, there are 2 main processes of the program, upload or download:

The radio buttons on the selection screen determine which option will run:

Download: Open SAP Table in Excel – Selection variable = r_down

Upload: Refresh SAP Table from Excel – Selection variable = r_up

The “execute” method is called first, and will perform one of the options to upload or download:

START-OF-SELECTION.
  TRY.
      NEW lcl_excel_maint( )->execute( VALUE #( upload     = r_up
                                                download   = r_down
                                                tabname    = p_table
                                                excel_file = p_file ) ).
    CATCH lcl_my_exception INTO DATA(lo_exc).
      MESSAGE lo_exc->get_message( ) TYPE 'I'.
  ENDTRY.

The above statement, in older ABAP, would have been the following:

  "Old ABAP:
  DATA: lo_obj    TYPE REF TO lcl_excel_maint,
        lo_exc    TYPE REF TO lcl_my_exception,
        lw_params TYPE lcl_excel_maint=>lty_param.
  lw_params-upload     = r_up.
  lw_params-download   = r_down.
  lw_params-tabname    = p_table.
  lw_params-excel_file = p_file.
  CREATE OBJECT lo_obj.
  TRY.
      call method lo_obj->execute( lw_params ).
    CATCH lcl_my_exception INTO lo_exc.
      MESSAGE lo_exc->get_message( ) TYPE 'I'.
  ENDTRY.

The select-option parameters are passed to the execute method denoting upload, download, table name and the Excel File name and path.

It’s worth mentioning, throughout the program, we use our custom Class-Based Exception called LCL_MY_EXCEPTION, which will enable our exception messages to easily flow up the stack. Here is the definition and implementation of our Exception class:

CLASS lcl_my_exception DEFINITION INHERITING FROM cx_dynamic_check.
  PUBLIC SECTION.
    METHODS:
      constructor
        IMPORTING i_textid   LIKE textid OPTIONAL
                  i_previous LIKE previous OPTIONAL
                  i_message  TYPE string OPTIONAL,
      get_message RETURNING VALUE(r_message) TYPE bapi_msg.
  PRIVATE SECTION.
    DATA: gv_message TYPE string.
ENDCLASS. "lcl_my_exception DEFINITION
CLASS lcl_my_exception IMPLEMENTATION.
  METHOD constructor.
    CALL METHOD super->constructor
      EXPORTING
        textid   = i_textid
        previous = i_previous.
    CLEAR gv_message.
    gv_message = i_message.
  ENDMETHOD.
  METHOD get_message.
    r_message = gv_message.
  ENDMETHOD.
ENDCLASS. "lcl_my_exception IMPLEMENTATION

Also, we restrict this program to only custom tables starting with ‘Z’ or ‘Y’. Never update data directly in standard SAP tables:

IF i_parameters-tabname(1) <> 'Z' AND i_parameters-tabname(1) <> 'Y'.
  RAISE EXCEPTION TYPE lcl_my_exception
    EXPORTING i_message = |Only Custom Tables are supported (must begin with 'Y' or 'Z').|.
ENDIF.

As is, this program should only be used for tables that do not have too many records. For large datasets, the program would timeout. For large data loads, you would need to load your data directly from the SAP operating system, as a background job. For tables with large datasets, you could adjust this program to update subsets of records, rather than perform a complete refresh.

Since both options (upload or download) will require dynamic creation of an internal table, based on the table name entered on the selection screen, we will do the following for both options:

    CREATE DATA lo_table TYPE TABLE OF (i_parameters-tabname).
    ASSIGN lo_table->* TO <lt_table>.

The above dynamically creates an internal table <lt_table>, in our case the table we entered on the selection screen was ZTEST_KOTLIN, and is passed in the parameter “i_parameters-tabname”.

Because we will need all of the data in the table for both the upload and download methods, we then select all records into the internal table. If we are doing an upload, we will need this in order to completely refresh the table. If we are downloading, of course we need all of the records.

    "Get all records currently in our Z Table...
    SELECT * FROM (i_parameters-tabname) INTO TABLE <lt_table>.

Now, we will perform the following, based on the user request to upload or download…

 

Download – Extract and auto-open in Excel

The following 2 methods are called, when running in download mode, or the select option titled “Open SAP Table in Excel”:

"Write the Excel file, and open Excel and display on the user's PC...
export_excel_data( im_xstring = generate_xlsx_string( CHANGING ct_table = <lt_table> )
                   im_file    = CONV string( i_parameters-excel_file ) ).
open_excel_file_on_pc( i_parameters-excel_file ).

Written in older ABAP, the above call to the method “export_excel_data” would need to be the following:

      "Old ABAP:
      DATA: lv_xstring     TYPE xstring,
            lv_temp_string TYPE string.
      lv_xstring = generate_xlsx_string( CHANGING ct_table = <lt_table> ).
      lv_temp_string = i_parameters-excel_file.
      CALL METHOD export_excel_data(
        EXPORTING
          im_xstring = lv_xstring
          im_file    = lv_temp_string ).

With the new ABAP language features in 7.4+, we can now utilize temporary variables and inline method calls. So, we can turn the above into a single line of code:

export_excel_data( im_xstring = generate_xlsx_string( CHANGING ct_table = <lt_table> )
                   im_file    = CONV string( i_parameters-excel_file ) ).

Because the method “generate_xlsx_string” has a returning parameter, we can use this as an import parameter, and embed it inline to pass to the parameter im_xstring. Using the “CONV” keyword, we can dynamically convert i_parameters-excel_file into a string at runtime. We are converting it from data type “localfile” into a string, which is required for the im_file parameter for the “export_excel_data” method.

 

Method generate_xlsx_string:

This method utilizes the cl_salv_table class to generate our table, which may then be generated into an xstring of an xml version which Excel understands:

  METHOD generate_xlsx_string.
    DATA: lo_data TYPE REF TO data.
    DATA(l_version) = cl_salv_bs_a_xml_base=>get_version( ).
    IF l_version <> if_salv_bs_xml=>version_25 AND
       l_version <> if_salv_bs_xml=>version_26. RETURN.
    ENDIF.
    TRY.
        cl_salv_table=>factory( EXPORTING list_display = abap_false
                                IMPORTING r_salv_table = DATA(lo_salv_table)
                                CHANGING  t_table      = ct_table ).
      CATCH cx_salv_msg.
        RAISE EXCEPTION TYPE lcl_my_exception EXPORTING i_message = 'ALV Table create failed.'.
    ENDTRY.
    GET REFERENCE OF ct_table INTO lo_data.
    DATA(lo_result_data) = cl_salv_ex_util=>factory_result_data_table(
                            r_data         = lo_data
                            t_fieldcatalog = cl_salv_controller_metadata=>get_lvc_fieldcatalog(
                                               r_columns      = lo_salv_table->get_columns( )
                                               r_aggregations = lo_salv_table->get_aggregations( ) ) ).
    cl_salv_bs_tt_util=>if_salv_bs_tt_util~transform( EXPORTING xml_type      = if_salv_bs_xml=>c_type_xlsx
                                                                xml_version   = l_version
                                                                r_result_data = lo_result_data
                                                                xml_flavour   = if_salv_bs_c_tt=>c_tt_xml_flavour_export
                                                                gui_type      = if_salv_bs_xml=>c_gui_type_gui
                                                      IMPORTING xml           = r_xstring ).
  ENDMETHOD. "generate_xlsx_string

Once our xstring is generated, we call the method export_excel_data…

 

Method export_excel_data:

This method will turn our xstring into binary data, for the standard GUI_DOWNLOAD, which downloads the file to the user’s computer:

  METHOD export_excel_data.
    TYPES: BEGIN OF lty_line,
             data(1024) TYPE x,
           END OF lty_line.
    DATA: lv_size   TYPE i,
          lt_bintab TYPE TABLE OF lty_line.
    IF im_xstring IS INITIAL. RETURN. ENDIF.
    CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
      EXPORTING
        buffer        = im_xstring
      IMPORTING
        output_length = lv_size
      TABLES
        binary_tab    = lt_bintab.

    cl_gui_frontend_services=>gui_download( EXPORTING bin_filesize = lv_size
                                                          filename = im_file
                                                          filetype = 'BIN'
                                            CHANGING      data_tab = lt_bintab
                                            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
                                                       OTHERS                  = 22 ).
    IF sy-subrc <> 0.
      MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
        WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4
        INTO DATA(lv_error_text).
      IF sy-subrc = 15.
        lv_error_text = |{ lv_error_text }. Do you have the file currently open? | &&
                        |If so, close the Excel file, and re-run.|.
      ENDIF.
      RAISE EXCEPTION TYPE lcl_my_exception EXPORTING i_message = lv_error_text.
    ENDIF.
  ENDMETHOD. "export_excel_data

Finally, SAP provides a method to take the file directory and name, and open the Excel program on the user’s computer…

 

Method open_excel_file_on_pc:

The following method parses out the directory and file name, then calls the standard SAP class to open a program on the user’s computer (PC):

  METHOD open_excel_file_on_pc.
    DATA: lv_pathname TYPE string,
          lv_filename TYPE string.
    CALL FUNCTION 'RPM_DX_PATH_FILE_SPLIT'
      EXPORTING
        iv_pathfile = im_file
      IMPORTING
        ev_pathname = lv_pathname
        ev_filename = lv_filename.
    cl_gui_frontend_services=>execute( EXPORTING application       = 'EXCEL'
                                                 parameter         = lv_filename
                                                 default_directory = lv_pathname
                                                 operation         = 'OPEN'
                                                 maximized         = 'X'
                                       EXCEPTIONS cntl_error             = 1
                                                  error_no_gui           = 2
                                                  bad_parameter          = 3
                                                  file_not_found         = 4
                                                  path_not_found         = 5
                                                  file_extension_unknown = 6
                                                  error_execute_failed   = 7
                                                  synchronous_failed     = 8
                                                  not_supported_by_gui   = 9 ).
    IF sy-subrc <> 0.
      MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4
        INTO DATA(lv_error_text).
      RAISE EXCEPTION TYPE lcl_my_exception EXPORTING i_message = |Excel Could not open. { lv_error_text }|.
    ENDIF.
  ENDMETHOD. "open_excel_file_on_pc

That’s it for the download, just 3 simple method calls. Next, let’s see how the upload works…

 

Upload – Refresh your Z Table from Excel

Because we do a complete refresh of the table, and overwrite with the records in the Excel file, first we will delete all records in our table:

"Upload the Excel file from the user's PC and populate our SAP table...
SELECT COUNT( * ) FROM (i_parameters-tabname) INTO lv_count_before.
IF <lt_table> IS NOT INITIAL.
  "Refresh/Delete all existing entries from the SAP table...
  DELETE (i_parameters-tabname) FROM TABLE <lt_table>.
  CLEAR <lt_table>[].
ENDIF.

We make note of a “before” record count, so we may show the end-user a message with the number of records that were deleted.

To perform the upload, we have the following method call:

convert_excel_to_table( EXPORTING io_table   = import_excel_data( CONV string( i_parameters-excel_file ) )
                                  im_tabname = i_parameters-tabname
                        CHANGING  ct_table   = <lt_table> ).

Just like our download method earlier, we have an inline method call, which has a returning parameter. The method “import_excel_data” returns a data reference type for the io_table import parameter. We must also convert our excel_file parameter to a string with the CONV keyword. The result will be that our internal table <lt_table> now has all of the records from the Excel file. Let’s see how these methods work…

 

Method import_excel_data:

Here is the code for our import method:

  METHOD import_excel_data.
    DATA: lt_xtab TYPE cpt_x255,
          lv_size TYPE i.
    cl_gui_frontend_services=>gui_upload( EXPORTING filename   = im_file
                                                    filetype   = 'BIN'
                                          IMPORTING filelength = lv_size
                                           CHANGING data_tab   = lt_xtab ).
    IF sy-subrc NE 0.
      RAISE EXCEPTION TYPE lcl_my_exception EXPORTING i_message = |Invalid File { im_file }|.
    ENDIF.
    cl_scp_change_db=>xtab_to_xstr( EXPORTING im_xtab    = lt_xtab
                                              im_size    = lv_size
                                    IMPORTING ex_xstring = DATA(lv_xstring) ).
    DATA(lo_excel) = NEW cl_fdt_xl_spreadsheet( document_name = im_file
                                                xdocument     = lv_xstring ).
    lo_excel->if_fdt_doc_spreadsheet~get_worksheet_names(
      IMPORTING worksheet_names = DATA(lt_worksheets) ).
    rt_table = lo_excel->if_fdt_doc_spreadsheet~get_itab_from_worksheet( lt_worksheets[ 1 ] ).
    IF rt_table IS INITIAL.
      RAISE EXCEPTION TYPE lcl_my_exception EXPORTING i_message = 'No Data found in Excel File'.
    ENDIF.
  ENDMETHOD. "import_excel_data

This method will use the SAP standard gui_upload method to read the Excel File from the end-user’s computer. It then converts the results into an xstring with the method “cl_scp_change_db=>xtab_to_xstr”. Once we have our xstring, we can call some standard SAP classes to extract the data into an internal table. Finally, we get our table with the following method call:

rt_table = lo_excel->if_fdt_doc_spreadsheet~get_itab_from_worksheet( lt_worksheets[ 1 ] ).

Our assumption is that we only have a single worksheet within our Excel file. If you have many worksheets, you can loop at lt_worksheets and write code as needed. We will simply get the first worksheet by referenceing “lt_worksheets[ 1 ]”. This is new ABAP syntax for getting record number 1 in the internal table “lt_worksheets”. In old abap, we would do something like the following:

    "Old ABAP:
    DATA: lt_temp_table TYPE REF TO data,
          lt_worksheets TYPE STANDARD TABLE OF string,
          lv_worksheet  TYPE string.
    CALL METHOD lo_excel->if_fdt_doc_spreadsheet~get_worksheet_names
      IMPORTING
        worksheet_names = lt_worksheets.
    READ TABLE lt_worksheets INTO lv_worksheet INDEX 1.
    CALL METHOD lo_excel->if_fdt_doc_spreadsheet~get_itab_from_worksheet
      EXPORTING
        worksheet_name = lv_worksheet
      RECEIVING
        itab           = rt_table.

With our new ABAP language features, the above is greatly simplified:

lo_excel->if_fdt_doc_spreadsheet~get_worksheet_names(
  IMPORTING worksheet_names = DATA(lt_worksheets) ).
rt_table = lo_excel->if_fdt_doc_spreadsheet~get_itab_from_worksheet( lt_worksheets[ 1 ] ).

Notice that the standard SAP cl_fdt_xl_spreadsheet class returns a generic table, with alphabetic column names:

So, we’ll need to transfer this table into our SAP defined Z table schema. This is done in the following method, after we receive the above results into our io_table parameter…

 

Method convert_excel_to_table:

After the above method call, we receive an internal table with alphabetic column names. We need to translate this table into our custom ZTEST_KOTLIN table. That is, column “A”, translates to column “MANDT”, column “B” moves into the “GUID” column, etc. As a reminder, our ZTEST_KOTLIN schema has the following 8 columns:

Our code for this method is:

  METHOD convert_excel_to_table.
    DATA: lo_record TYPE REF TO data.
    FIELD-SYMBOLS: <lt_input_table> TYPE table.
    ASSIGN io_table->* TO <lt_input_table>.
    "Remove the Header row, if one exists (i.e. starts with Client - MANDT)...
    ASSIGN COMPONENT 1 OF STRUCTURE <lt_input_table>[ 1 ] TO FIELD-SYMBOL(<lfs_mandt>).
    IF <lfs_mandt> IS ASSIGNED.
      IF <lfs_mandt> = 'Client'. DELETE <lt_input_table> INDEX 1. ENDIF.
    ENDIF.
    "Need to move the generic Excel input table into our SAP defined table...
    "Get a list of fields in our SAP table...
    CREATE DATA lo_record LIKE LINE OF ct_table.
    ASSIGN lo_record->* TO FIELD-SYMBOL(<lw_record>).
    DATA(components) = CAST cl_abap_structdescr( cl_abap_typedescr=>describe_by_name( im_tabname ) )->components.
    "Migrate each field into our table with an SAP schema...
    LOOP AT <lt_input_table> ASSIGNING FIELD-SYMBOL(<lw_input>).
      CLEAR <lw_record>.
      LOOP AT components INTO DATA(lw_field).
        ASSIGN COMPONENT lw_field-name OF STRUCTURE <lw_record> TO FIELD-SYMBOL(<lfs_target>).
        ASSIGN COMPONENT sy-tabix OF STRUCTURE <lw_input> TO FIELD-SYMBOL(<lfs_source>).
        IF <lfs_target> IS ASSIGNED AND <lfs_source> IS ASSIGNED.
          <lfs_target> = <lfs_source>.
        ENDIF.
        UNASSIGN: <lfs_source>, <lfs_target>.
      ENDLOOP.
      IF <lw_record> IS NOT INITIAL.
        APPEND <lw_record> TO ct_table.
      ENDIF.
    ENDLOOP.
  ENDMETHOD. "convert_excel_to_table

Because the user typed in the table name, we can simply call the standard SAP structure class to get a list of fields for our table schema:

DATA(components) = CAST cl_abap_structdescr( cl_abap_typedescr=>describe_by_name( im_tabname ) )->components.

I must say, compressing the above code into a single line is very cool. In old ABAP, we may have had to do the following:

    "Old ABAP:
    data: type_ref    type ref to cl_abap_typedescr,
          struc_descr type ref to cl_abap_structdescr,
          components  type ABAP_COMPDESCR_TAB.
    type_ref = cl_abap_typedescr=>describe_by_name( im_tabname ).
    struc_descr ?= type_ref.
    components = struc_descr->components.

The internal table “components” now contains information on our table schema. There is a field called “NAME”, which holds the table field name. We loop at all components of the Z table schema, and assign one component at a time from our Excel table, into the target field of ZTEST_KOTLIN.

Source Table:

Source = Target Fields:

A = MANDT

B = GUID

C = DATE_UPDATED

D = TIME_UPDATED

…and so on…

Notice we have a header row in our Excel spreadsheet, so we would want to delete the first row:

    "Remove the Header row, if one exists (i.e. starts with Client - MANDT)...
    ASSIGN COMPONENT 1 OF STRUCTURE <lt_input_table>[ 1 ] TO FIELD-SYMBOL(<lfs_mandt>).
    IF <lfs_mandt> IS ASSIGNED.
      IF <lfs_mandt> = 'Client'. DELETE <lt_input_table> INDEX 1. ENDIF.
    ENDIF.

The above simply searches for the keyword “Client” in the first field to determine if there is a header row.

Method my_custom_table_logic:

After we perform our upload from Excel, you may want to execute some custom code, specific to your own Z Table. We have the following custom method, specific to our ZTEST_KOTLIN table:

  METHOD my_custom_table_logic.
    LOOP AT ct_table ASSIGNING FIELD-SYMBOL(<lfs_wa>).
      "Auto-generate a unique key...
      ASSIGN COMPONENT 'GUID' OF STRUCTURE <lfs_wa> TO FIELD-SYMBOL(<lfs_guid>).
      IF <lfs_guid> IS ASSIGNED.
        IF <lfs_guid> IS INITIAL.
          TRY.
              <lfs_guid> = cl_system_uuid=>if_system_uuid_static~create_uuid_x16( ).
            CATCH cx_uuid_error INTO DATA(lo_guid_error).
              MESSAGE 'GUID Generation error.' TYPE 'I'.
          ENDTRY.
        ENDIF.
      ENDIF.
      ASSIGN COMPONENT 'DATE_UPDATED' OF STRUCTURE <lfs_wa> TO FIELD-SYMBOL(<lfs_udate>).
      IF <lfs_udate> IS ASSIGNED.
        <lfs_udate> = sy-datum.
      ENDIF.
      ASSIGN COMPONENT 'TIME_UPDATED' OF STRUCTURE <lfs_wa> TO FIELD-SYMBOL(<lfs_utime>).
      IF <lfs_utime> IS ASSIGNED.
        <lfs_utime> = sy-uzeit.
      ENDIF.
      ASSIGN COMPONENT 'LAST_UPDATED_BY' OF STRUCTURE <lfs_wa> TO FIELD-SYMBOL(<lfs_user>).
      IF <lfs_user> IS ASSIGNED.
        <lfs_user> = sy-uname.
      ENDIF.
      UNASSIGN: <lfs_udate>, <lfs_guid>, <lfs_utime>, <lfs_user>.
    ENDLOOP.
  ENDMETHOD. "my_custom_table_logic

Our key field is named GUID. If the key field is blank, we’ll generate a unique key with the custom class cl_system_uuid. We’ll also default the fields DATE_UPDATED, TIME_UPDATED, LAST_UPDATED_BY with the date, time and SAP user ID whom updated this record for logging purposes.

If you define your own Z table, this method will simply ignore any fields not named GUID, DATE_UPDATED, TIME_UPDATED and LAST_UPDATED_BY and the program could work as-is for your table. If you want special logic for your custom table, simply overwrite the code in this method.

 

Import a CDS View into Excel

This section sort of wanders off on a tangent, but very cool stuff and relevant for our Excel themed Blog. HANA CDS Views are insanely powerful, and you can even create an entire Fiori app from just a CDS View with some clever annotations. Google it, and read more about it. Show this section to an experienced SAP Functional Analyst, and see the delight on their face, as they realize they can become CDS View ninjas, and totally eliminate all future interaction with any ABAP report writers ?…If you are an ABAP programmer who would like to stay current, then yes, go learn CDS Views, Eclipse ABAP Development Tools, SAP Gateway, BOPF, BRF+, Fiori, etc.

Now that we have our custom Z Table, let’s write a simple CDS View, so that we can easily expose it as a service to the outside world:

For CDS View development, you must use Eclipse. Open Eclipse, and perform the following:

Create a new ABAP Project, and login to your SAP system:

Navigate to “Core Data Services” and create a new Data Definition:

Name your new CDS View:

Click Next, accept defaults until you get to the following screen. Choose the “Define View” template:

Click Finish

Overwrite the template strings with the following:

Replace @AbapCatalog.sqlViewName: ‘sql_view_name’ with @AbapCatalog.sqlViewName: ‘ZCDS_KOTLIN_SE11’.

Replace “as select from data_source_name” with “as select from ztest_kotlin”.

You will now have the following:

@AbapCatalog.sqlViewName: 'ZCDS_KOTLIN_SE11'
define view ZCDS_KOTLIN as select from ZTEST_KOTLIN {
You should have the following:
@AbapCatalog.sqlViewName: 'ZCDS_KOTLIN_SE11'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Kotlin CDS View'
define view ZCDS_KOTLIN as select from ZTEST_KOTLIN {
    
}

With your cursor on the empty row, between the curly brackets { }, enter the shortcut Ctrl + Space, to get the following Code Completion tips:

Double-click on “Insert all elements (template)”, and it will place all fields into the CDS View for you:

@AbapCatalog.sqlViewName: 'ZCDS_KOTLIN_SE11'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Kotlin CDS View'
define view ZCDS_KOTLIN as select from ztest_kotlin {
    //ZTEST_KOTLIN
    key guid,
    date_updated,
    time_updated,
    last_updated_by,
    order_number,
    customer_number,
    customer_message
}

Save and activate our new CDS View.

The annotation “AbapCatalog.sqlViewName”, gives you a way to display your CDS view in the SAPGUI in SE11. Go to SE11, and display ZCDS_KOTLIN_SE11:

You can Double-click on the DDL Source “ZCDS_KOTLIN”, to see the same DDL you see in Eclipse:

But of course, now that we have Eclipse, there is no need to use the SAPGUI anymore ?… In all seriousness, SAP is slowly adding features to Eclipse that will include the ability to do everything you can do in SAPGUI, but it’s a work in progress.

Go back to Eclipse, and display the data in the table, by accessing the CDS View. Right-click on your CDS View, then go to “Open With > Data Preview”:

Now, let’s create an SAP Gateway service to expose our CDS view to the world, as a RESTful web service…

Back in the SAPGUI, Execute the TCode SEGW. Click on the “Create Project” button:

Enter the following and continue:

This generates the following tree structure:

Go to the following menu path, which is the latest way to auto-generate a service from a CDS View:

Enter our CDS View:

Click “Next”, then “Finish” to accept all defaults.

We now have the following auto-generated for our project:

Save and click Generate, to create all of the code necessary for the Gateway to utilize our CDS View:

Accept all of the defaults and hit Enter to generate the below classes:

When prompted, save it as a Local Object.

We now have all of our runtime artifacts for the service to execute:

Under the “Service Maintenance” node, if a Gateway has already been configured, you will see something like the following:

Right-click on the “GW_HUB” node, and “Register” the service on the Gateway:

Enter System Alias “LOCAL” (most common), and hit Enter:

Select Local Object, and accept all defaults and hit Enter:

Click “Save” again, on the main screen. Now, let’s test our service. Right-click on our Gateway, and go to menu path “SAP Gateway Client”:

Let’s walk through some cool features of the SAP Gateway Client, to test our Service…

In the Gateway Client, click on the “EntitySets” button:

Double-click to select “ZCDS_KOTLIN”:

Click on the Execute Button:

In the lower-right, you will see the OData results for our Gateway Service:

Notice we can see our first record, above. You can scroll down to see the other records.

Click on the button “Add URI Option”:

Double-click on the entry “$format=xlsx”:

This simply adds a new parameter to the end of our URL:

Execute again, and we see the following message in our HTTP Response:

Click on the “Response in Browser” button, and it will auto-open our data in Excel:

Our service looks good, and is responding with data. Let’s approach it from another angle…

In our first test, you can see the following url in the HTTP Response:

We can simply copy this url, then enter it into a Web Browser, and see the following results:

You may be prompted for your SAP User ID and password:

After you enter your SAP ID and password, you can see your raw data in the browser:

Now we know we can access our service from our workstation (PC). Keep this URL handy, so we may use it, below…

To pull our data from Excel, perform the following steps…

Open Excel and create a new Blank workbook:

Go to the “Data” tab, and the path Get Data > From Other Sources > From OData Feed

Paste in the url, which we just tested in our browser:

Click the OK button to continue. You will either get an authorization error message, or it will show your data in the pop-up if you are already logged in. If you get an authorization error, click on the “Basic” option on the left, then enter your SAP User and password. Then choose the longest URL in the drop-down at the bottom of the list (our URL from above, which we pasted into the browser). Here is an example of the Authorization screen:

Once logged in, your data will appear:

The above steps took very little time, with zero programming. Find your CDS View, expose it via an SAP Gateway service, and hand it off to your Excel experts in the business. They now have a real-time reporting tool hooked into SAP on the backend.

 

Complete Program

Below is the complete program for this Blog. The old ABAP code reference from this tutorial is included and commented out. Simply delete the “Old ABAP” commented sections, if you’d like to remove it.

REPORT z_excel_maintenance.
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE fil_lbl.
  PARAMETERS: r_down RADIOBUTTON GROUP g1,
              r_up   RADIOBUTTON GROUP g1.
  PARAMETERS: p_table TYPE tabname16 DEFAULT 'ZTEST_KOTLIN',
              p_file  TYPE localfile DEFAULT 'c:\1\ExcelFile.xlsx'.
SELECTION-SCREEN END OF BLOCK b1.
CLASS lcl_my_exception DEFINITION INHERITING FROM cx_dynamic_check.
  PUBLIC SECTION.
    METHODS:
      constructor
        IMPORTING i_textid   LIKE textid OPTIONAL
                  i_previous LIKE previous OPTIONAL
                  i_message  TYPE string OPTIONAL,
      get_message RETURNING VALUE(r_message) TYPE bapi_msg.
  PRIVATE SECTION.
    DATA: gv_message TYPE string.
ENDCLASS. "lcl_my_exception DEFINITION
CLASS lcl_my_exception IMPLEMENTATION.
  METHOD constructor.
    CALL METHOD super->constructor
      EXPORTING
        textid   = i_textid
        previous = i_previous.
    CLEAR gv_message.
    gv_message = i_message.
  ENDMETHOD.
  METHOD get_message.
    r_message = gv_message.
  ENDMETHOD.
ENDCLASS. "lcl_my_exception IMPLEMENTATION
CLASS lcl_excel_maint DEFINITION CREATE PUBLIC FINAL.
  PUBLIC SECTION.
    CONSTANTS: c_excel TYPE string VALUE 'Excel files(*.xlsx)|*.xlsx'.
    TYPES: BEGIN OF lty_param,
             upload     TYPE char1,
             download   TYPE char1,
             tabname    TYPE tabname16,
             excel_file TYPE localfile,
           END OF lty_param.
    CLASS-METHODS:
      select_file
        IMPORTING i_filename     TYPE string
                  i_type         TYPE string
        RETURNING VALUE(re_file) TYPE localfile.
    METHODS:
      execute
        IMPORTING i_parameters TYPE lty_param
        RAISING   lcl_my_exception.
  PRIVATE SECTION.
    METHODS:
      import_excel_data
        IMPORTING im_file         TYPE string
        RETURNING VALUE(rt_table) TYPE REF TO data
        RAISING   lcl_my_exception,
      generate_xlsx_string
        CHANGING  ct_table         TYPE table
        RETURNING VALUE(r_xstring) TYPE xstring,
      export_excel_data
        IMPORTING im_xstring TYPE xstring
                  im_file    TYPE string
        RAISING   lcl_my_exception,
      my_custom_table_logic
        CHANGING ct_table TYPE table,
      open_excel_file_on_pc
        IMPORTING im_file TYPE localfile
        RAISING   lcl_my_exception,
      convert_excel_to_table
        IMPORTING io_table   TYPE REF TO data
                  im_tabname TYPE tabname16
        CHANGING  ct_table   TYPE table
        RAISING   lcl_my_exception.
ENDCLASS. "lcl_excel_maint
CLASS lcl_excel_maint IMPLEMENTATION.
  METHOD execute.
    DATA: lo_table        TYPE REF TO data,
          lv_count_before TYPE i,
          lv_count_after  TYPE i.
    FIELD-SYMBOLS: <lt_table>       TYPE table.
    IF i_parameters-tabname(1) <> 'Z' AND i_parameters-tabname(1) <> 'Y'.
      RAISE EXCEPTION TYPE lcl_my_exception
        EXPORTING
          i_message = |Only Custom Tables are supported (must begin with 'Y' or 'Z').|.
    ENDIF.
    SELECT SINGLE tabname INTO @DATA(lv_tabname) FROM dd03l
        WHERE tabname = @i_parameters-tabname.
    IF sy-subrc <> 0.
      RAISE EXCEPTION TYPE lcl_my_exception EXPORTING i_message = |Invalid Table Name '{ i_parameters-tabname }'.|.
    ENDIF.
    CREATE DATA lo_table TYPE TABLE OF (i_parameters-tabname).
    ASSIGN lo_table->* TO <lt_table>.
    "Get all records currently in our Z Table...
    SELECT * FROM (i_parameters-tabname) INTO TABLE <lt_table>.
    IF i_parameters-download = abap_true.
      "Old ABAP:
*      DATA: lv_xstring     TYPE xstring,
*            lv_temp_string TYPE string.
*      lv_xstring = generate_xlsx_string( CHANGING ct_table = <lt_table> ).
*      lv_temp_string = i_parameters-excel_file.
*      CALL METHOD export_excel_data(
*        EXPORTING
*          im_xstring = lv_xstring
*          im_file    = lv_temp_string ).
      "Write the Excel file, and open Excel and display on the user's PC...
      export_excel_data( im_xstring = generate_xlsx_string( CHANGING ct_table = <lt_table> )
                         im_file    = CONV string( i_parameters-excel_file ) ).
      open_excel_file_on_pc( i_parameters-excel_file ).
    ELSEIF i_parameters-upload = abap_true.
      "Upload the Excel file from the user's PC and populate our SAP table...
      SELECT COUNT( * ) FROM (i_parameters-tabname) INTO lv_count_before.
      IF <lt_table> IS NOT INITIAL.
        "Refresh/Delete all existing entries from the SAP table...
        DELETE (i_parameters-tabname) FROM TABLE <lt_table>.
        CLEAR <lt_table>[].
      ENDIF.
      convert_excel_to_table( EXPORTING io_table   = import_excel_data( CONV string( i_parameters-excel_file ) )
                                        im_tabname = i_parameters-tabname
                              CHANGING  ct_table   = <lt_table> ).
      "Adjust the table according to our requirements...
      my_custom_table_logic( CHANGING ct_table = <lt_table> ).
      MODIFY (i_parameters-tabname) FROM TABLE <lt_table>.
      COMMIT WORK AND WAIT.
      IF sy-subrc = 0.
        SELECT COUNT( * ) FROM (i_parameters-tabname) INTO lv_count_after.
        MESSAGE |Table { i_parameters-tabname } successfully refreshed. | &&
                |{ lv_count_before } records deleted and { lv_count_after } inserted.| TYPE 'I'.
      ELSE.
        RAISE EXCEPTION TYPE lcl_my_exception
          EXPORTING
            i_message = |Failed to update table { i_parameters-tabname }.|.
      ENDIF.
    ENDIF.
  ENDMETHOD. "execute
  METHOD convert_excel_to_table.
    DATA: lo_record TYPE REF TO data.
    FIELD-SYMBOLS: <lt_input_table> TYPE table.
    ASSIGN io_table->* TO <lt_input_table>.
    "Remove the Header row, if one exists (i.e. starts with Client - MANDT)...
    ASSIGN COMPONENT 1 OF STRUCTURE <lt_input_table>[ 1 ] TO FIELD-SYMBOL(<lfs_mandt>).
    IF <lfs_mandt> IS ASSIGNED.
      IF <lfs_mandt> = 'Client'. DELETE <lt_input_table> INDEX 1. ENDIF.
    ENDIF.
    "Need to move the generic Excel input table into our SAP defined table...
    "Get a list of fields in our SAP table...
    CREATE DATA lo_record LIKE LINE OF ct_table.
    ASSIGN lo_record->* TO FIELD-SYMBOL(<lw_record>).
    DATA(components) = CAST cl_abap_structdescr( cl_abap_typedescr=>describe_by_name( im_tabname ) )->components.
*    "Old ABAP for the above single line of code:
*    data: type_ref    type ref to cl_abap_typedescr,
*          struc_descr type ref to cl_abap_structdescr,
*          components  type ABAP_COMPDESCR_TAB.
*    type_ref = cl_abap_typedescr=>describe_by_name( im_tabname ).
*    struc_descr ?= type_ref.
*    components = struc_descr->components.
    "Migrate each field into our table with an SAP schema...
    LOOP AT <lt_input_table> ASSIGNING FIELD-SYMBOL(<lw_input>).
      CLEAR <lw_record>.
      LOOP AT components INTO DATA(lw_field).
        ASSIGN COMPONENT lw_field-name OF STRUCTURE <lw_record> TO FIELD-SYMBOL(<lfs_target>).
        ASSIGN COMPONENT sy-tabix OF STRUCTURE <lw_input> TO FIELD-SYMBOL(<lfs_source>).
        IF <lfs_target> IS ASSIGNED AND <lfs_source> IS ASSIGNED.
          <lfs_target> = <lfs_source>.
        ENDIF.
        UNASSIGN: <lfs_source>, <lfs_target>.
      ENDLOOP.
      IF <lw_record> IS NOT INITIAL.
        APPEND <lw_record> TO ct_table.
      ENDIF.
    ENDLOOP.
  ENDMETHOD. "convert_excel_to_table
  METHOD my_custom_table_logic.
    LOOP AT ct_table ASSIGNING FIELD-SYMBOL(<lfs_wa>).
      "Auto-generate a unique key...
      ASSIGN COMPONENT 'GUID' OF STRUCTURE <lfs_wa> TO FIELD-SYMBOL(<lfs_guid>).
      IF <lfs_guid> IS ASSIGNED.
        IF <lfs_guid> IS INITIAL.
          TRY.
              <lfs_guid> = cl_system_uuid=>if_system_uuid_static~create_uuid_x16( ).
            CATCH cx_uuid_error INTO DATA(lo_guid_error).
              MESSAGE 'GUID Generation error.' TYPE 'I'.
          ENDTRY.
        ENDIF.
      ENDIF.
      ASSIGN COMPONENT 'DATE_UPDATED' OF STRUCTURE <lfs_wa> TO FIELD-SYMBOL(<lfs_udate>).
      IF <lfs_udate> IS ASSIGNED.
        <lfs_udate> = sy-datum.
      ENDIF.
      ASSIGN COMPONENT 'TIME_UPDATED' OF STRUCTURE <lfs_wa> TO FIELD-SYMBOL(<lfs_utime>).
      IF <lfs_utime> IS ASSIGNED.
        <lfs_utime> = sy-uzeit.
      ENDIF.
      ASSIGN COMPONENT 'LAST_UPDATED_BY' OF STRUCTURE <lfs_wa> TO FIELD-SYMBOL(<lfs_user>).
      IF <lfs_user> IS ASSIGNED.
        <lfs_user> = sy-uname.
      ENDIF.
      UNASSIGN: <lfs_udate>, <lfs_guid>, <lfs_utime>, <lfs_user>.
    ENDLOOP.
  ENDMETHOD. "my_custom_table_logic
  METHOD open_excel_file_on_pc.
    DATA: lv_pathname TYPE string,
          lv_filename TYPE string.
    CALL FUNCTION 'RPM_DX_PATH_FILE_SPLIT'
      EXPORTING
        iv_pathfile = im_file
      IMPORTING
        ev_pathname = lv_pathname
        ev_filename = lv_filename.
    cl_gui_frontend_services=>execute( EXPORTING application       = 'EXCEL'
                                                 parameter         = lv_filename
                                                 default_directory = lv_pathname
                                                 operation         = 'OPEN'
                                                 maximized         = 'X'
                                       EXCEPTIONS cntl_error             = 1
                                                  error_no_gui           = 2
                                                  bad_parameter          = 3
                                                  file_not_found         = 4
                                                  path_not_found         = 5
                                                  file_extension_unknown = 6
                                                  error_execute_failed   = 7
                                                  synchronous_failed     = 8
                                                  not_supported_by_gui   = 9 ).
    IF sy-subrc <> 0.
      MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4
        INTO DATA(lv_error_text).
      RAISE EXCEPTION TYPE lcl_my_exception EXPORTING i_message = |Excel Could not open. { lv_error_text }|.
    ENDIF.
  ENDMETHOD. "open_excel_file_on_pc
  METHOD select_file.
    re_file = cl_openxml_helper=>browse_local_file_open(
      iv_title      = 'Select File'
      iv_filename   = i_filename
      iv_extpattern = i_type ).
  ENDMETHOD.                    "select_file
  METHOD generate_xlsx_string.
    DATA: lo_data TYPE REF TO data.
    DATA(l_version) = cl_salv_bs_a_xml_base=>get_version( ).
    IF l_version <> if_salv_bs_xml=>version_25 AND
       l_version <> if_salv_bs_xml=>version_26. RETURN.
    ENDIF.
    TRY.
        cl_salv_table=>factory( EXPORTING list_display = abap_false
                                IMPORTING r_salv_table = DATA(lo_salv_table)
                                CHANGING  t_table      = ct_table ).
      CATCH cx_salv_msg.
        RAISE EXCEPTION TYPE lcl_my_exception EXPORTING i_message = 'ALV Table create failed.'.
    ENDTRY.
    GET REFERENCE OF ct_table INTO lo_data.
    DATA(lo_result_data) = cl_salv_ex_util=>factory_result_data_table(
                            r_data         = lo_data
                            t_fieldcatalog = cl_salv_controller_metadata=>get_lvc_fieldcatalog(
                                               r_columns      = lo_salv_table->get_columns( )
                                               r_aggregations = lo_salv_table->get_aggregations( ) ) ).
    cl_salv_bs_tt_util=>if_salv_bs_tt_util~transform( EXPORTING xml_type      = if_salv_bs_xml=>c_type_xlsx
                                                                xml_version   = l_version
                                                                r_result_data = lo_result_data
                                                                xml_flavour   = if_salv_bs_c_tt=>c_tt_xml_flavour_export
                                                                gui_type      = if_salv_bs_xml=>c_gui_type_gui
                                                      IMPORTING xml           = r_xstring ).
  ENDMETHOD. "generate_xlsx_string
  METHOD export_excel_data.
    TYPES: BEGIN OF lty_line,
             data(1024) TYPE x,
           END OF lty_line.
    DATA: lv_size   TYPE i,
          lt_bintab TYPE TABLE OF lty_line.
    IF im_xstring IS INITIAL. RETURN. ENDIF.
    CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
      EXPORTING
        buffer        = im_xstring
      IMPORTING
        output_length = lv_size
      TABLES
        binary_tab    = lt_bintab.

    cl_gui_frontend_services=>gui_download( EXPORTING bin_filesize = lv_size
                                                          filename = im_file
                                                          filetype = 'BIN'
                                            CHANGING      data_tab = lt_bintab
                                            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
                                                       OTHERS                  = 22 ).
    IF sy-subrc <> 0.
      MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
        WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4
        INTO DATA(lv_error_text).
      IF sy-subrc = 15.
        lv_error_text = |{ lv_error_text }. Do you have the file currently open? | &&
                        |If so, close the Excel file, and re-run.|.
      ENDIF.
      RAISE EXCEPTION TYPE lcl_my_exception EXPORTING i_message = lv_error_text.
    ENDIF.
  ENDMETHOD. "export_excel_data

  METHOD import_excel_data.
    DATA: lt_xtab TYPE cpt_x255,
          lv_size TYPE i.
    cl_gui_frontend_services=>gui_upload( EXPORTING filename   = im_file
                                                    filetype   = 'BIN'
                                          IMPORTING filelength = lv_size
                                           CHANGING data_tab   = lt_xtab ).
    IF sy-subrc NE 0.
      RAISE EXCEPTION TYPE lcl_my_exception EXPORTING i_message = |Invalid File { im_file }|.
    ENDIF.
    cl_scp_change_db=>xtab_to_xstr( EXPORTING im_xtab    = lt_xtab
                                              im_size    = lv_size
                                    IMPORTING ex_xstring = DATA(lv_xstring) ).
    DATA(lo_excel) = NEW cl_fdt_xl_spreadsheet( document_name = im_file
                                                xdocument     = lv_xstring ).
    lo_excel->if_fdt_doc_spreadsheet~get_worksheet_names(
      IMPORTING worksheet_names = DATA(lt_worksheets) ).
    rt_table = lo_excel->if_fdt_doc_spreadsheet~get_itab_from_worksheet( lt_worksheets[ 1 ] ).
*    "Old ABAP for the above 2 lines of code:
*    DATA: lt_temp_table TYPE REF TO data,
*          lt_worksheets TYPE STANDARD TABLE OF string,
*          lv_worksheet  TYPE string.
*    CALL METHOD lo_excel->if_fdt_doc_spreadsheet~get_worksheet_names
*      IMPORTING
*        worksheet_names = lt_worksheets.
*    READ TABLE lt_worksheets INTO lv_worksheet INDEX 1.
*    CALL METHOD lo_excel->if_fdt_doc_spreadsheet~get_itab_from_worksheet
*      EXPORTING
*        worksheet_name = lv_worksheet
*      RECEIVING
*        itab           = rt_table.
    IF rt_table IS INITIAL.
      RAISE EXCEPTION TYPE lcl_my_exception EXPORTING i_message = 'No Data found in Excel File'.
    ENDIF.
  ENDMETHOD. "import_excel_data
ENDCLASS. "lcl_excel_maint

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
  p_file = lcl_excel_maint=>select_file( i_filename = CONV string( p_file )
                                         i_type     = lcl_excel_maint=>c_excel ).

INITIALIZATION.
  %_p_file_%_app_%-text  = 'Excel File'.
  %_p_table_%_app_%-text = 'Z Table Name'.
  %_r_up_%_app_%-text    = 'Refresh SAP Table from Excel'.
  %_r_down_%_app_%-text  = 'Open SAP Table in Excel'.
  fil_lbl = 'Excel Table Maintenance Options'.

START-OF-SELECTION.
  "Old ABAP for the below execute method:
*  DATA: lo_obj    TYPE REF TO lcl_excel_maint,
*        lo_exc    TYPE REF TO lcl_my_exception,
*        lw_params TYPE lcl_excel_maint=>lty_param.
*  lw_params-upload     = r_up.
*  lw_params-download   = r_down.
*  lw_params-tabname    = p_table.
*  lw_params-excel_file = p_file.
*  CREATE OBJECT lo_obj.
*  TRY.
*      call method lo_obj->execute( lw_params ).
*    CATCH lcl_my_exception INTO lo_exc.
*      MESSAGE lo_exc->get_message( ) TYPE 'I'.
*  ENDTRY.

  "New ABAP:
  TRY.
      NEW lcl_excel_maint( )->execute( VALUE #( upload     = r_up
                                                download   = r_down
                                                tabname    = p_table
                                                excel_file = p_file ) ).
    CATCH lcl_my_exception INTO DATA(lo_exc).
      MESSAGE lo_exc->get_message( ) TYPE 'I'.
  ENDTRY.

 

References

Related Blog for a Kotlin Appilcation:

https://blogs.sap.com/2020/02/17/kotlin-application-with-sap-gateway/

 

Information on the xlsx format:

https://en.wikipedia.org/wiki/Microsoft_Excel#File_formats

https://en.wikipedia.org/wiki/Office_Open_XML

 

Open Source project abap2xlsx:

https://wiki.scn.sap.com/wiki/display/ABAP/abap2xlsx

 

Blog to create an xlsx file:

https://abapblog.com/articles/tricks/33-create-xlsx-mhtml-file-from-internal-table-in-background

 

An Excellent Learning website:

https://sapyard.com/

 

Eclipse Shortcuts:

https://blogs.sap.com/2017/07/12/abap-in-eclipse-keyboard-shortcuts-you-cannot-miss-cheat-sheet/

 

Some excellent blogs for new ABAP Language features:

https://blogs.sap.com/2015/10/25/abap-740-quick-reference/

https://blogs.sap.com/2016/03/02/old-and-new-abap-syntax-overview-sheet/

 

Blog to dynamically generate a SAPGUI Report from a CDS View, using Excel for configuration:

https://blogs.sap.com/2019/03/01/report-creator/