Excel Upload and Download Program with multiple tabs from Fiori Launch Pad(Web GUI)

Introduction 

There could be multiple scenarios where an excel upload and download using ABAP Report Program or a Odata service with different sheets or tabs in a Excel needs to developed as shown in the below screenshot.

Excel Upload with multiple tabs :

There has been multiple instances where most of the developers tend to use the function module ALSM_EXCEL_TO_INTERNAL_TABLE,TEXT_CONVERT_XLS_TO_SAP .This will work fine when we test the program or Odata from SAP GUI .

Disadvantages of using the ALSM_EXCEL_TO_INTERNAL_TABLE,TEXT_CONVERT_XLS_TO_SAP FM :

• This function module which uses OLE object for Office Integration solution internally  will produce a dump and is not supported when used in the SAP ABAP Program Odata when called from Fiori launch pad since this is web based HTTP application with WEBGUI and no OLE is supported similar to the SAP GUI .

Proposed Solution :

The below code can be leveraged to upload and read the excel with multiple excel tabs or worksheets .

• Use the Class CL_FDT_XL_SPREADSHEET in order to over come the above issue by instantiating and passing the XSTRING data in the constructor
• In Odata use the CREATE_STREAM method to write the below code
• IF_FDT_DOC_SPREADSHEET~GET_WORKSHEET_NAMES returns both the tabs as internal tables .
• Use the method IF_FDT_DOC_SPREADSHEET~GET_ITAB_FROM_WORKSHEET to get the corresponding tab into internal table by passing the sheet or tab number.

DATA : lv_filename      TYPE string,
         lt_records       TYPE solix_tab,
         lv_headerxstring TYPE xstring,
         lv_filelength    TYPE i.

  lv_filename = p_file.
*-- Read the Input File
  CALL FUNCTION 'GUI_UPLOAD'
    EXPORTING
      filename                = lv_filename
      filetype                = 'BIN'
    IMPORTING
      filelength              = lv_filelength
      header                  = lv_headerxstring
    TABLES
      data_tab                = lt_records
    EXCEPTIONS
      file_open_error         = 1
      file_read_error         = 2
      no_batch                = 3
      gui_refuse_filetransfer = 4
      invalid_type            = 5
      no_authority            = 6
      unknown_error           = 7
      bad_data_format         = 8
      header_not_allowed      = 9
      separator_not_allowed   = 10
      header_too_long         = 11
      unknown_dp_error        = 12
      access_denied           = 13
      dp_out_of_memory        = 14
      disk_full               = 15
      dp_timeout              = 16
      OTHERS                  = 17.
  IF sy-subrc IS NOT INITIAL.

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

  ENDIF.
*--convert binary data to xstring

  CALL FUNCTION 'SCMS_BINARY_TO_XSTRING'
    EXPORTING
      input_length = lv_filelength
    IMPORTING
      buffer       = lv_headerxstring
    TABLES
      binary_tab   = lt_records
    EXCEPTIONS
      failed       = 1
      OTHERS       = 2.

  IF sy-subrc IS NOT INITIAL.

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

  ENDIF.

  DATA : lo_excel_ref TYPE REF TO cl_fdt_xl_spreadsheet .

  TRY .
      lo_excel_ref = NEW cl_fdt_xl_spreadsheet(
                              document_name = lv_filename
                              xdocument     = lv_headerxstring ) .
    CATCH cx_fdt_excel_core.
  ENDTRY .
  IF lo_excel_ref IS BOUND.
*--Get List of Worksheets
    lo_excel_ref->if_fdt_doc_spreadsheet~get_worksheet_names(
      IMPORTING
        worksheet_names = DATA(lt_worksheets) ).

    IF NOT lt_worksheets IS INITIAL.
*-- Header Tab
      READ TABLE lt_worksheets INTO DATA(lv_woksheetname) INDEX 1.

      DATA(lo_data_ref) = lo_excel_ref->if_fdt_doc_spreadsheet~get_itab_from_worksheet(
                                               lv_woksheetname ).
*-- Excel work sheet data in dyanmic internal table
      ASSIGN lo_data_ref->* TO <gt_data_h>.
*-- Item Tab Details
      READ TABLE lt_worksheets INTO lv_woksheetname INDEX 2.

      lo_data_ref = lo_excel_ref->if_fdt_doc_spreadsheet~get_itab_from_worksheet(
                                               lv_woksheetname ).
*-- Excel work sheet data in dyanmic internal table
      ASSIGN lo_data_ref->* TO <gt_data_l>.

    ENDIF.
  ENDIF.

Once the Data has been read into two different internal tables use the below methods to the read the data and process it .

In the below example the dynamic internal tables are converted into header and items strucutres.

Similarly dynamic structure names can also be provided for the same.

DATA : lv_numberofcolumns   TYPE i,
         lv_date_string       TYPE string,
         lv_target_date_field TYPE datum,
         lv_flag              TYPE boolean,
         l_param1             TYPE syst-msgv1,
         l_param2             TYPE syst-msgv1,
         lv_convert_date(10)  TYPE c,
         lv_skip              TYPE i,
         lv_empty             TYPE boole_d,
         lv_empty_tmp         TYPE boole_d.

  DATA: lo_struct TYPE REF TO cl_abap_structdescr,
        lo_table  TYPE REF TO cl_abap_tabledescr,
        lr_type   TYPE REF TO cl_abap_typedescr.
  FIELD-SYMBOLS: <struc_hdr>     TYPE any,
                 <struc_itm>     TYPE any,
                 <ls_data>       TYPE any,
                 <lv_field>      TYPE any,
                 <fs_excel_data> TYPE STANDARD TABLE,
                 <fs_data>       TYPE any,
                 <fs_comp>       TYPE any.

  DATA : lt_field TYPE cl_abap_structdescr=>component_table,
         lt_comp  TYPE cl_abap_structdescr=>component_table.


*--  find out number of columns dynamically from table
  lo_table ?= cl_abap_structdescr=>describe_by_data( gt_tab1 ).
  lo_struct ?= lo_table->get_table_line_type( ).
  lt_field = lo_struct->get_components( ).
  IF <gt_data_h> IS ASSIGNED.
*-- Build the Header details
    LOOP AT <gt_data_h> ASSIGNING  <ls_data> FROM 3.
*-- First, verify if field exists in dictionary
      lv_empty = abap_true.
      APPEND INITIAL LINE TO  gt_tab1 ASSIGNING FIELD-SYMBOL(<ls_context>).

*-- Initialize flag
      lv_flag = abap_true.
      l_param1 = sy-tabix.
      CONDENSE l_param1 NO-GAPS.

      WHILE lv_flag = abap_true.
        lv_empty_tmp = abap_false.

*-- Read columnwise entries
        ASSIGN COMPONENT sy-index OF STRUCTURE <ls_data> TO <fs_comp> .
        IF <fs_comp> IS NOT ASSIGNED.
          lv_flag = abap_false.
*-- Exit the loop when a row ends
          EXIT.
        ELSE.
          IF <fs_comp> IS INITIAL.
            lv_empty_tmp = abap_true.
          ENDIF.

          READ TABLE lt_field ASSIGNING FIELD-SYMBOL(<ls_field>) INDEX sy-index .
          IF sy-subrc = 0.
*-- Get the Datatype by finding the field name
            ASSIGN COMPONENT <ls_field>-name OF STRUCTURE <ls_context> TO FIELD-SYMBOL(<ls_value>).
            l_param2 = <ls_field>-name.
            CONDENSE l_param2 NO-GAPS.
            lr_type ?= <ls_field>-type.
*            CASE lr_type->type_kind.
**-- Convert Date to SAP readable Format
*              WHEN cl_abap_typedescr=>typekind_date.
*                IF <fs_comp> IS NOT INITIAL.
                  lv_convert_date = <fs_comp>  .
                  if <ls_field>-name = 'BLDAT' or <ls_field>-name = 'BUDAT'.
                  FIND REGEX '^\d{4}[/|-]\d{1,2}[/|-]\d{1,2}$' IN lv_convert_date.
                  IF sy-subrc = 0.
                    CALL FUNCTION '/SAPDMC/LSM_DATE_CONVERT'
                      EXPORTING
                        date_in             = lv_convert_date
                        date_format_in      = 'DYMD'
                        to_output_format    = ' '
                        to_internal_format  = 'X'
                      IMPORTING
                        date_out            = lv_convert_date
                      EXCEPTIONS
                        illegal_date        = 1
                        illegal_date_format = 2
                        no_user_date_format = 3
                        OTHERS              = 4.
                    IF sy-subrc = 0.
                      <fs_comp>  = lv_convert_date .
                      <ls_value> = <fs_comp>.
                    ELSE.

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

                    " date format DD/MM/YYYY
                    FIND REGEX '^\d{1,2}[/|-]\d{1,2}[/|-]\d{4}$' IN lv_convert_date.
                    IF sy-subrc = 0.
                      CALL FUNCTION '/SAPDMC/LSM_DATE_CONVERT'
                        EXPORTING
                          date_in             = lv_convert_date
                          date_format_in      = 'DDMY'
                          to_output_format    = ' '
                          to_internal_format  = 'X'
                        IMPORTING
                          date_out            = lv_convert_date
                        EXCEPTIONS
                          illegal_date        = 1
                          illegal_date_format = 2
                          no_user_date_format = 3
                          OTHERS              = 4.
                      IF sy-subrc = 0.
                        <fs_comp>  = lv_convert_date .
                        <ls_value> = <fs_comp>.
*                      ELSE.
*
*                        MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
*                                WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
                      ENDIF.
                    ENDIF.
                  ENDIF.
                ENDIF.
*              WHEN OTHERS.
                <ls_value> = <fs_comp>.
*            ENDCASE.
          ENDIF.
        ENDIF.

        UNASSIGN <fs_comp>.
      ENDWHILE.
    ENDLOOP .
*-- Build the line items details
    lo_table ?= cl_abap_structdescr=>describe_by_data( gt_tab2 ).
    lo_struct ?= lo_table->get_table_line_type( ).
    lt_field = lo_struct->get_components( ).
    LOOP AT <gt_data_l> ASSIGNING  <ls_data> FROM 3 .
*--First, verify if field exists in dictionary
      lv_empty = abap_true.
      APPEND INITIAL LINE TO  gt_tab2 ASSIGNING FIELD-SYMBOL(<ls_context_1>).

*-- Initialize flag
      lv_flag = abap_true.
      l_param1 = sy-tabix.
      CONDENSE l_param1 NO-GAPS.

      WHILE lv_flag = abap_true.
        lv_empty_tmp = abap_false.

*-- Read columnwise entries
        ASSIGN COMPONENT sy-index OF STRUCTURE <ls_data> TO <fs_comp> .
        IF <fs_comp> IS NOT ASSIGNED.
          lv_flag = abap_false.
*-- Exit the loop when a row ends
          EXIT.
        ELSE.
          IF <fs_comp> IS INITIAL.
            lv_empty_tmp = abap_true.
          ENDIF.

          READ TABLE lt_field ASSIGNING FIELD-SYMBOL(<ls_field1>) INDEX sy-index .
          IF sy-subrc = 0.

            ASSIGN COMPONENT <ls_field1>-name OF STRUCTURE <ls_context_1> TO FIELD-SYMBOL(<ls_value_1>).
          ENDIF.
        ENDIF.
        <ls_value_1> = <fs_comp>.
*-- Unassign field symbol
        UNASSIGN <fs_comp>.
      ENDWHILE.

    ENDLOOP .
  ENDIF.

Additional check for the Date format and other data structure can be performed in the loop as shown above.

Generate and Download Excel with multiple tabs or sheets from WEBGUI or Fiori Lauch pad :

There are classes like CL_SALV_EXPORT_TOOL_ATS_XLS  which could be used to achieve the above functionality .However we could not use this since it will not work creating multiple tabs or sheets.

Suggested Solution  :

The only solution which could solve this problem :

• Install the abapGit using the below link :abapGit documentation – Installation
• Perform the SSL setup using the STRUST transaction
• Import the custom code required for the excel generation using the below link:  https://github.com/sapmentors/abap2xlsx
• This custom code should be captured in a transport and the same should be transported along with your report or Odata.

Below is the sample code using the abapGit classes to achieve the same  :

*&---------------------------------------------------------------------*
*& Report  ZDEMO_EXCEL4
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT zdemo_excel4.

DATA: lo_excel                TYPE REF TO zcl_excel,
      lo_worksheet            TYPE REF TO zcl_excel_worksheet,

      lo_hyperlink            TYPE REF TO zcl_excel_hyperlink,

      lv_tabcolor             TYPE zexcel_s_tabcolor,

      ls_header               TYPE zexcel_s_worksheet_head_foot,
      ls_footer               TYPE zexcel_s_worksheet_head_foot.

CONSTANTS: gc_save_file_name TYPE string VALUE '04_Sheets.xlsx'.
INCLUDE zdemo_excel_outputopt_incl.


START-OF-SELECTION.

  " Creates active sheet
  CREATE OBJECT lo_excel.

  " Get active sheet
  lo_worksheet = lo_excel->get_active_worksheet( ).
  lo_worksheet->set_title( ip_title = 'Sheet1' ).
  lo_worksheet->zif_excel_sheet_properties~selected = zif_excel_sheet_properties=>c_selected.
  lo_worksheet->set_cell( ip_column = 'B' ip_row = 2 ip_value = 'This is the first sheet' ).
* Set color to tab with sheetname   - Red
  lv_tabcolor-rgb = zcl_excel_style_color=>create_new_argb( ip_red   = 'FF'
                                                            ip_green = '00'
                                                            ip_blu   = '00' ).
  lo_worksheet->set_tabcolor( lv_tabcolor ).

  lo_hyperlink = zcl_excel_hyperlink=>create_internal_link( iv_location = 'Sheet2!B2' ).
  lo_worksheet->set_cell( ip_column = 'B' ip_row = 3 ip_value = 'This is link to second sheet' ip_hyperlink = lo_hyperlink ).

  " Page printing settings
  lo_worksheet->sheet_setup->set_page_margins( ip_header = '1' ip_footer = '1' ip_unit = 'cm' ).
  lo_worksheet->sheet_setup->black_and_white   = 'X'.
  lo_worksheet->sheet_setup->fit_to_page       = 'X'.  " you should turn this on to activate fit_to_height and fit_to_width
  lo_worksheet->sheet_setup->fit_to_height     = 0.    " used only if ip_fit_to_page = 'X'
  lo_worksheet->sheet_setup->fit_to_width      = 2.    " used only if ip_fit_to_page = 'X'
  lo_worksheet->sheet_setup->orientation       = zcl_excel_sheet_setup=>c_orientation_landscape.
  lo_worksheet->sheet_setup->page_order        = zcl_excel_sheet_setup=>c_ord_downthenover.
  lo_worksheet->sheet_setup->paper_size        = zcl_excel_sheet_setup=>c_papersize_a4.
  lo_worksheet->sheet_setup->scale             = 80.   " used only if ip_fit_to_page = SPACE

  " Header and Footer
  ls_header-right_value = 'print date &D'.
  ls_header-right_font-size = 8.
  ls_header-right_font-name = zcl_excel_style_font=>c_name_arial.

  ls_footer-left_value = '&Z&F'. "Path / Filename
  ls_footer-left_font = ls_header-right_font.
  ls_footer-right_value = 'page &P of &N'. "page x of y
  ls_footer-right_font = ls_header-right_font.

  lo_worksheet->sheet_setup->set_header_footer( ip_odd_header  = ls_header
                                                ip_odd_footer  = ls_footer ).


  lo_worksheet = lo_excel->add_new_worksheet( ).
  lo_worksheet->set_title( ip_title = 'Sheet2' ).
* Set color to tab with sheetname   - Green
  lv_tabcolor-rgb = zcl_excel_style_color=>create_new_argb( ip_red   = '00'
                                                            ip_green = 'FF'
                                                            ip_blu   = '00' ).
  lo_worksheet->set_tabcolor( lv_tabcolor ).
  lo_worksheet->zif_excel_sheet_properties~selected = zif_excel_sheet_properties=>c_selected.
  lo_worksheet->set_cell( ip_column = 'B' ip_row = 2 ip_value = 'This is the second sheet' ).
  lo_hyperlink = zcl_excel_hyperlink=>create_internal_link( iv_location = 'Sheet1!B2' ).
  lo_worksheet->set_cell( ip_column = 'B' ip_row = 3 ip_value = 'This is link to first sheet' ip_hyperlink = lo_hyperlink ).
  lo_worksheet->set_cell( ip_column = 'B' ip_row = 4 ip_value = 'Sheet3 is hidden' ).

  lo_worksheet->sheet_setup->set_header_footer( ip_odd_header  = ls_header
                                                ip_odd_footer  = ls_footer ).

  lo_worksheet = lo_excel->add_new_worksheet( ).
  lo_worksheet->set_title( ip_title = 'Sheet3' ).
* Set color to tab with sheetname   - Blue
  lv_tabcolor-rgb = zcl_excel_style_color=>create_new_argb( ip_red   = '00'
                                                            ip_green = '00'
                                                            ip_blu   = 'FF' ).
  lo_worksheet->set_tabcolor( lv_tabcolor ).
  lo_worksheet->zif_excel_sheet_properties~hidden = zif_excel_sheet_properties=>c_hidden.

  lo_worksheet->sheet_setup->set_header_footer( ip_odd_header  = ls_header
                                                ip_odd_footer  = ls_footer ).

  lo_worksheet = lo_excel->add_new_worksheet( ).
  lo_worksheet->set_title( ip_title = 'Sheet4' ).
* Set color to tab with sheetname   - other color
  lv_tabcolor-rgb = zcl_excel_style_color=>create_new_argb( ip_red   = '00'
                                                            ip_green = 'FF'
                                                            ip_blu   = 'FF' ).
  lo_worksheet->set_tabcolor( lv_tabcolor ).
  lo_worksheet->set_cell( ip_column = 'B' ip_row = 2 ip_value = 'Cell B3 has value 0' ).
  lo_worksheet->set_cell( ip_column = 'B' ip_row = 3 ip_value = 0 ).
  lo_worksheet->zif_excel_sheet_properties~show_zeros = zif_excel_sheet_properties=>c_hidezero.

  lo_worksheet->sheet_setup->set_header_footer( ip_odd_header  = ls_header
                                                ip_odd_footer  = ls_footer ).

  lo_excel->set_active_sheet_index_by_name( 'Sheet1' ).


*** Create output
  lcl_output=>output( lo_excel ).

Additionally we can also add hyperlinks and also colors to the excel sheets using the abapGit.

But the only challenge would be to importing additional classes into SAP systems which needsexplicit approvals from system owner.

Please provide your valuable feedback .