Skip to Content
Technical Articles
Author's profile photo Vijay Chintarlapalli

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 .

Assigned Tags

      16 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Syambabu Allu
      Syambabu Allu

      Hi Vijay,

      Thank you for sharing in detail.

      We have recently got this kind of requirement and achieved the same you mentioned in the blog

      Thank you,

      Syam

      Author's profile photo Vijay Chintarlapalli
      Vijay Chintarlapalli
      Blog Post Author

      Thank you

      Author's profile photo Sandra Rossi
      Sandra Rossi

      abap2xlsx allows both reading and writing.

      Author's profile photo Vijay Chintarlapalli
      Vijay Chintarlapalli
      Blog Post Author

      Sandra Rossi  : yes, abap2xlsx allows both .However we need install abapGit for it. The idea behind this blog is provide all the available possibilities .

      Author's profile photo Jelena Perfiljeva
      Jelena Perfiljeva

      Vijaya Chintarlapalli Sorry I'm a bit late to the party but I believe this statement is incorrect. Back in 2019 (and I doubt this changed), I installed ABAP2XLSX in a customer system without abapGit. Having abapGit would definitely make the process easier, but the source code can also be just copy-pasted into the system, like any ZCL... class. So if someone using this as an excuse to avoid ABAP2XLSX, they're probably misinformed.

      Author's profile photo PRABHU DAS POLE
      PRABHU DAS POLE

      Hi Vijaya,

       

      Thanks a lot  for sharing the blog in details.

      I  have got recently  this kind of requirement and achieved it with the help of : https://www.samplecodeabap.com/how-to-upload-excel-to-sapusing-abap/

       

      Thank you,

      Prabhu. Pole

      Author's profile photo Vijay Chintarlapalli
      Vijay Chintarlapalli
      Blog Post Author

      Thanks for sharing !

      Author's profile photo Sandra Rossi
      Sandra Rossi

      Big warning about (not) using CL_FDT_XL_SPREADSHEET, here is what says the note 2468709 - Usage of standard class CL_FDT_XL_SPREADSHEET:

      • You are using class CL_FDT_XL_SPREADSHEET to upload/download Excel in custom program. Unexpected behavior happens, such as, blank data is uploaded, uploaded data gets garbled, value in bold font are ignored etc.
      • FDT (BRFplus) Excel objects/methods are specific to be used internally by BRFplus and cannot be used for a generic Excel export or import or for any other purpose. The objects/methods are used inside decision table in BRFplus which expects the Excel to be in specific format.
      • Class CL_FDT_XL_SPREADSHEET is designed for decision table in BRFplus workbench only. The usage in any other scenario is not supported.
      • An example of downloaded decision table is attached in "Example Downloaded DT". If you still want to use class CL_FDT_XL_SPREADSHEET to upload/download Excel, but not for BRFplus decision table, you can try to use the format like in attachment. However, this does not mean it will work. We still suggest you not to use  CL_FDT_XL_SPREADSHEET without scenario of BRFplus decision table. Any problem when using class CL_FDT_XL_SPREADSHEET without BRFplus decision table is not supported.
      • If you are using BRFplus applications and have issues in uploading or downloading excel for decision table, please create an incident to component BC-SRV-BR.

       

      Author's profile photo Vijay Chintarlapalli
      Vijay Chintarlapalli
      Blog Post Author

      Thanks for your valuable  suggestion!

      Author's profile photo Adam Trickett
      Adam Trickett

      It works for simple cases, but thanks for making these limitations known.

      Loading Excel is a common business requirement and as companies move away from SAP GUI towards Fiori, and Windows PC with Office installed to lighter solutions with cloud tools, being able to read an Excel file without Excel has become a standard requirement and a standard solution in the box is pretty important.

      Author's profile photo Jonathan Blavatsky
      Jonathan Blavatsky

      how do you solve the problem with bold fonts?

      Author's profile photo Gregor Wolf
      Gregor Wolf

      abap2xlsx.

      Author's profile photo Jonathan Blavatsky
      Jonathan Blavatsky

      any simple example on how to upload the xlsx to internal table?

      Author's profile photo Gregor Wolf
      Gregor Wolf

      src/zdemo_excel37.prog.abap

      Author's profile photo Charu Borkar
      Charu Borkar

      Hi Vijay

      I have implemented the ABAP program in the backend and execute it on a gateway call. it gives be a shortdump with CONTROL_FLUSH_ERROR which looks like it's virus scan interface - profile not active exception being raised.

      Please advice.

       

      Thanks!

      Charu

      Author's profile photo Saurabh Joshi
      Saurabh Joshi

      What is the type of lcl_output?