ABAP Development, SAP S/4HANA

Excel Upload with Custom Validations

This blog post is going to demo how to upload excel files from server(AL11) and local pc. This code is meant to be reused and modified to fit your own requirement in every project.

Purpose:

  1. To create a code that can have different validations for different programs, without changing the core logic of the upload function.
  2. To create a code that is reusable for every project.
  3. To minimize the errors happen in data uploading.
  4. To make it easy to use excel upload function.

Introduction:

This part will explain the entire concept of the upload part and validation part. In short, this code take advantage of the object oriented overriding(redefinition) concept to do custom validation.

Flow chart:

Figure 1: Flow chart

The above flow chart shows the concept of the entire upload process.

Step 1: Determine source of data.

Step 2: Read the excel data and convert to xString.

Step 3: Extract the data and stored in internal table for further processing.

Step 4: Validate data in internal table. Validation is very important in this process. There are two types of validations:

  1. Data type/format validation: This is to ensure the data uploaded from excel file is compatible with the file structure in the program. Methods such as CHECK_TYPE_P, CHECK_TYPE_I and so on are the methods to validate the data is in correct format. For example, if user uploads a non numeric value to type P variable, then method CHECK_TYPE_P will handle the exception and conversion dump, then return error.
  2. Data accuracy validation: This validation needs to be implemented in the caller program. This is to ensure the data uploaded from excel file is correct in term of business requirement. For instance, if there is no company code 1100 in the system, but user uploads this value to the program, then it will return error.

How to use:

Step 1 : This is a class program. Create a local class to inherit from parent class, then create an instance to call the methods.

Figure 2: Sample code to use the upload method

Step 2 : Redefine method to implement custom logic in validation. Otherwise, it will follow the standard validation.

Figure 3: Redefined method and custom validation method

Step 3 : Handle the output of the upload method. There are three object returned from the method: Successful upload internal table, Error object, Message. Handle the data returned and do custom logic after it.

Summary:

This code is meant to be reused at any other project, and thanks to the concept of object oriented redefinition concept, it brings a flexibility to add any custom logic to the validation and at the same time it does not change the core logic of the upload function. Error handling is another key point here, minimize the error by handling exception.

This is the end of the blog post. Please refer to the source code below. Let me know if you have any question in the code. Enjoy coding!

Source Code:

Class source code: ZCL_UPLOAD

class zcl_upload definition
  public
  create public .

  public section.

    types:
      begin of ty_message,
        row       type i,
        fieldname type lvc_fname,
        message   type string,
      end of ty_message .
    types:
      tt_message type standard table of ty_message with empty key .

    methods constructor
      importing
        value(im_filepath) type dxlpath optional
        value(im_skip_row) type i optional .
    methods set_constructor
      importing
        value(im_filepath) type dxlpath
        value(im_skip_row) type i .
    methods upload_excel_server
      changing
        !ch_success type table
        !ch_error   type ref to data
        !ch_message type tt_message optional
      exceptions
        empty_filepath
        file_not_found
        excel_content_error
        no_active_worksheet
        no_excel_content .
    methods upload_excel_pc
      changing
        !ch_success type table
        !ch_error   type ref to data
        !ch_message type tt_message optional
      exceptions
        empty_filepath
        file_open_error
        file_read_error
        no_batch
        gui_refuse_filetransfer
        invalid_type
        no_authority
        unknown_error
        bad_data_format
        header_not_allowed
        separator_not_allowed
        header_too_long
        unknown_dp_error
        access_denied
        dp_out_of_memory
        disk_full
        dp_timeout
        not_supported_by_gui
        error_no_gui
        excel_content_error
        no_active_worksheet
        no_excel_content .
    methods get_file_pc
      returning
        value(re_filepath) type dxlpath .
    methods get_file_serv
      importing
        !im_directory      type dxlpath default '.'
      returning
        value(re_filepath) type dxlpath .
  protected section.

    methods validate_row
      importing
        value(im_row_content) type any
        value(im_row)         type i
      exporting
        !ex_row_content       type any
        !ex_error             type char1
      changing
        !ch_message           type tt_message optional .
    methods check_type
      importing
        value(im_value) type any
      exporting
        !ex_error       type char1
      changing
        !ch_value       type any .
    methods check_type_p
      importing
        value(im_value) type any
      exporting
        !ex_error       type char1
      changing
        !ch_value       type any .
    methods check_type_i
      importing
        value(im_value) type any
      exporting
        !ex_error       type char1
      changing
        !ch_value       type any .
    methods check_type_c
      importing
        value(im_value) type any
      exporting
        !ex_error       type char1
      changing
        !ch_value       type any .
    methods check_type_f
      importing
        value(im_value) type any
      exporting
        !ex_error       type char1
      changing
        !ch_value       type any .
    methods check_type_n
      importing
        value(im_value) type any
      exporting
        !ex_error       type char1
      changing
        !ch_value       type any .
    methods check_type_d
      importing
        value(im_value) type any
      exporting
        !ex_error       type char1
      changing
        !ch_value       type any .
    methods check_type_t
      importing
        value(im_value) type any
      exporting
        !ex_error       type char1
      changing
        !ch_value       type any .
    methods check_type_other
      importing
        value(im_value) type any
      exporting
        !ex_error       type char1
      changing
        !ch_value       type any .
  private section.

    data filepath type dxlpath .
    data skip_row type i .

    methods validate
      importing
        !im_data    type ref to data
      changing
        !ch_success type table
        !ch_error   type ref to data
        !ch_message type tt_message optional .
    methods read_excel_server
      exporting
        !ex_solix type solix_tab
      exceptions
        file_not_found .
    methods read_excel_pc
      exporting
        !ex_solix type solix_tab
        !ex_subrc type syst_subrc .
    methods to_xstring
      importing
        !im_solix   type solix_tab
      exporting
        !ex_xstring type xstring
      exceptions
        empty_xstring
        empty_solix .
    methods extract_excel
      importing
        !im_xdocument type xstring
      changing
        !ch_data      type ref to data
      exceptions
        excel_content_error
        no_active_worksheet
        no_excel_content .
    methods create_structure
      importing
        !im_file_struc type ref to data
      changing
        !ch_str_table  type ref to data
        !ch_str_struc  type ref to data .
endclass.



class zcl_upload implementation.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Protected Method ZCL_UPLOAD->CHECK_TYPE
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_VALUE                       TYPE        ANY
* | [<---] EX_ERROR                       TYPE        CHAR1
* | [<-->] CH_VALUE                       TYPE        ANY
* +--------------------------------------------------------------------------------------</SIGNATURE>
  method check_type.
*&---------------------------------------------------------------------*
*  Method Name       : CHECK_TYPE
*  Method Visibility : Protected
*  Method Desc       : Check type of the data. Then check if the value
*                      matches with the type. This will cover all the
*                      data types. The following check_type_* can be
*                      redefined with own logic.
*&---------------------------------------------------------------------*
    data: lv_type  type char1,
          lv_error type char1.
    describe field ch_value type lv_type.
*Different checking for each type
*Each check method can be redefined in caller program
    case lv_type.
      when 'P'.
        check_type_p( exporting im_value = im_value
                      importing ex_error = ex_error
                      changing  ch_value = ch_value ).
      when 'I'.
        check_type_i( exporting im_value = im_value
                      importing ex_error = ex_error
                      changing  ch_value = ch_value ).
      when 'C'.
        check_type_c( exporting im_value = im_value
                      importing ex_error = ex_error
                      changing  ch_value = ch_value ).
      when 'F'.
        check_type_f( exporting im_value = im_value
                      importing ex_error = ex_error
                      changing  ch_value = ch_value ).
      when 'N'.
        check_type_n( exporting im_value = im_value
                      importing ex_error = ex_error
                      changing  ch_value = ch_value ).
      when 'D'.
        check_type_d( exporting im_value = im_value
                      importing ex_error = ex_error
                      changing  ch_value = ch_value ).
      when 'T'.
        check_type_t( exporting im_value = im_value
                      importing ex_error = ex_error
                      changing  ch_value = ch_value ).
      when others.
        check_type_other( exporting im_value = im_value
                          importing ex_error = ex_error
                          changing  ch_value = ch_value ).
    endcase.
  endmethod.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Protected Method ZCL_UPLOAD->CHECK_TYPE_C
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_VALUE                       TYPE        ANY
* | [<---] EX_ERROR                       TYPE        CHAR1
* | [<-->] CH_VALUE                       TYPE        ANY
* +--------------------------------------------------------------------------------------</SIGNATURE>
  method check_type_c.
*&---------------------------------------------------------------------*
*  Method Name       : CHECK_TYPE_C
*  Method Visibility : Protected
*  Method Desc       : Check type C.
*&---------------------------------------------------------------------*
    ch_value = im_value.
  endmethod.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Protected Method ZCL_UPLOAD->CHECK_TYPE_D
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_VALUE                       TYPE        ANY
* | [<---] EX_ERROR                       TYPE        CHAR1
* | [<-->] CH_VALUE                       TYPE        ANY
* +--------------------------------------------------------------------------------------</SIGNATURE>
  method check_type_d.
*&---------------------------------------------------------------------*
*  Method Name       : CHECK_TYPE_D
*  Method Visibility : Protected
*  Method Desc       : Check type D.
*&---------------------------------------------------------------------*
    data: lv_external type string,
          lv_internal type datum.
    if im_value is not initial.
      lv_external = im_value.
      call function 'CONVERT_DATE_TO_INTERNAL'
        exporting
          date_external            = lv_external
        importing
          date_internal            = lv_internal
        exceptions
          date_external_is_invalid = 1
          others                   = 2.
      if sy-subrc <> 0.
        ex_error = abap_true.
      else.
        ch_value = lv_internal.
      endif.
    endif.
  endmethod.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Protected Method ZCL_UPLOAD->CHECK_TYPE_F
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_VALUE                       TYPE        ANY
* | [<---] EX_ERROR                       TYPE        CHAR1
* | [<-->] CH_VALUE                       TYPE        ANY
* +--------------------------------------------------------------------------------------</SIGNATURE>
  method check_type_f.
*&---------------------------------------------------------------------*
*  Method Name       : CHECK_TYPE_F
*  Method Visibility : Protected
*  Method Desc       : Check type F.
*&---------------------------------------------------------------------*
    try.
        ch_value = im_value.
      catch cx_sy_conversion_overflow "If value is not in range
            cx_sy_conversion_no_number. "If value is not numeric
        ex_error = abap_true.
    endtry.
  endmethod.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Protected Method ZCL_UPLOAD->CHECK_TYPE_I
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_VALUE                       TYPE        ANY
* | [<---] EX_ERROR                       TYPE        CHAR1
* | [<-->] CH_VALUE                       TYPE        ANY
* +--------------------------------------------------------------------------------------</SIGNATURE>
  method check_type_i.
*&---------------------------------------------------------------------*
*  Method Name       : CHECK_TYPE_I
*  Method Visibility : Protected
*  Method Desc       : Check type I.
*                      Type I accept number ranged from
*                      -2,147,483,648 to +2,147,483,647
*&---------------------------------------------------------------------*
    data:lv_value type string.
    lv_value = im_value.
    replace all occurrences of ',' in lv_value with ''.
    try.
        ch_value = lv_value.
      catch cx_sy_conversion_overflow "If value is not in range
            cx_sy_conversion_no_number. "If value is not numeric
        ex_error = abap_true.
    endtry.
  endmethod.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Protected Method ZCL_UPLOAD->CHECK_TYPE_N
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_VALUE                       TYPE        ANY
* | [<---] EX_ERROR                       TYPE        CHAR1
* | [<-->] CH_VALUE                       TYPE        ANY
* +--------------------------------------------------------------------------------------</SIGNATURE>
  method check_type_n.
*&---------------------------------------------------------------------*
*  Method Name       : CHECK_TYPE_N
*  Method Visibility : Protected
*  Method Desc       : Check type N. It does not accept decimal places
*&---------------------------------------------------------------------*
    data: lv_value type string.
    lv_value = im_value.
    replace all occurrences of ',' in lv_value with ''.
    try.
        ch_value = lv_value.
      catch cx_sy_conversion_overflow "If value is not in range
            cx_sy_conversion_no_number. "If value is not numeric
        ex_error = abap_true.
    endtry.
  endmethod.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Protected Method ZCL_UPLOAD->CHECK_TYPE_OTHER
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_VALUE                       TYPE        ANY
* | [<---] EX_ERROR                       TYPE        CHAR1
* | [<-->] CH_VALUE                       TYPE        ANY
* +--------------------------------------------------------------------------------------</SIGNATURE>
  method check_type_other.
*&---------------------------------------------------------------------*
*  Method Name       : CHECK_TYPE_OTHER
*  Method Visibility : Protected
*  Method Desc       : Check other type
*&---------------------------------------------------------------------*
    ch_value = im_value.
  endmethod.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Protected Method ZCL_UPLOAD->CHECK_TYPE_P
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_VALUE                       TYPE        ANY
* | [<---] EX_ERROR                       TYPE        CHAR1
* | [<-->] CH_VALUE                       TYPE        ANY
* +--------------------------------------------------------------------------------------</SIGNATURE>
  method check_type_p.
*&---------------------------------------------------------------------*
*  Method Name       : CHECK_TYPE_P
*  Method Visibility : Protected
*  Method Desc       : Check type P. Convert to float then to PACK
*&---------------------------------------------------------------------*
    data: lv_float type float,
          lv_value type string.
    lv_value = im_value.
    replace all occurrences of ',' in lv_value with ''.
    try.
        lv_float = lv_value.
        ch_value = lv_float.
      catch cx_sy_conversion_no_number "If value is not numeric
            cx_sy_conversion_overflow. "If value is not in range
        ex_error = abap_true.
    endtry.
  endmethod.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Protected Method ZCL_UPLOAD->CHECK_TYPE_T
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_VALUE                       TYPE        ANY
* | [<---] EX_ERROR                       TYPE        CHAR1
* | [<-->] CH_VALUE                       TYPE        ANY
* +--------------------------------------------------------------------------------------</SIGNATURE>
  method check_type_t.
*&---------------------------------------------------------------------*
*  Method Name       : CHECK_TYPE_T
*  Method Visibility : Protected
*  Method Desc       : Check type T.
*&---------------------------------------------------------------------*
    data: lv_value type string.
    lv_value = im_value.
    replace all occurrences of ':' in lv_value with ''.
    if lv_value co '0123456789'.
      ch_value = lv_value.
    else.
      ex_error = abap_true.
    endif.
  endmethod.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCL_UPLOAD->CONSTRUCTOR
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_FILEPATH                    TYPE        DXLPATH(optional)
* | [--->] IM_SKIP_ROW                    TYPE        I(optional)
* +--------------------------------------------------------------------------------------</SIGNATURE>
  method constructor.
*&---------------------------------------------------------------------*
*  Method Name       : CONSTRUCTOR
*  Method Visibility : Public
*  Method Desc       : This is the constructor of the class. When the
*                      instance is created, the parameters has to be set
*                      with value
*                      filepath - the directory of the file
*                      worksheet_index - which sheet to read in excel
*                      file
*                      skip_row - skip header row
*&---------------------------------------------------------------------*
    filepath        = im_filepath.
    skip_row        = im_skip_row.
  endmethod.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZCL_UPLOAD->CREATE_STRUCTURE
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_FILE_STRUC                  TYPE REF TO DATA
* | [<-->] CH_STR_TABLE                   TYPE REF TO DATA
* | [<-->] CH_STR_STRUC                   TYPE REF TO DATA
* +--------------------------------------------------------------------------------------</SIGNATURE>
  method create_structure.
*&---------------------------------------------------------------------*
*  Method Name       : CREATE_STRUCTURE
*  Method Visibility : Private
*  Method Desc       : Create string type structure
*&---------------------------------------------------------------------*
    data:lt_comp         type standard table of abap_componentdescr with key name,
         lo_stru         type ref to cl_abap_structdescr,
         ls_comp         type abap_componentdescr,
         ls_fieldcatalog type lvc_s_fcat,
         lt_fieldcatalog type lvc_t_fcat,
         lt_data         type ref to data.

    field-symbols: <lfs_struc>       type any,
                   <lfs_data>        type ref to data,
                   <lfs_table>       type table,
                   <lfs_file_struc>  type any,
                   <lfs_str_table>   type any,
                   <lfs_t_str_table> type table.


    if im_file_struc is bound.
      assign im_file_struc->* to <lfs_file_struc>.
      if <lfs_file_struc> is assigned.
        lo_stru ?= cl_abap_typedescr=>describe_by_data( <lfs_file_struc> ).
        lt_comp = lo_stru->get_components( ).
        loop at lt_comp into ls_comp.
          ls_fieldcatalog-fieldname = ls_comp-name.
          ls_fieldcatalog-inttype = 'g'. "String type
          append ls_fieldcatalog to lt_fieldcatalog.
          clear: ls_fieldcatalog.
        endloop.
        if lt_fieldcatalog is not initial.
          assign ch_str_table to <lfs_str_table>.
          call method cl_alv_table_create=>create_dynamic_table
            exporting
              it_fieldcatalog           = lt_fieldcatalog
            importing
              ep_table                  = <lfs_str_table>
            exceptions
              generate_subpool_dir_full = 1
              others                    = 2.
          if sy-subrc = 0.
*Return a table & structure with string
            if <lfs_str_table> is bound.
              ch_str_table = <lfs_str_table>.
              assign <lfs_str_table>->* to <lfs_t_str_table>.
              create data ch_str_struc like line of <lfs_t_str_table>.
            endif.
          endif.
        endif.
      endif.
    endif.
  endmethod.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZCL_UPLOAD->EXTRACT_EXCEL
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_XDOCUMENT                   TYPE        XSTRING
* | [<-->] CH_DATA                        TYPE REF TO DATA
* | [EXC!] EXCEL_CONTENT_ERROR
* | [EXC!] NO_ACTIVE_WORKSHEET
* | [EXC!] NO_EXCEL_CONTENT
* +--------------------------------------------------------------------------------------</SIGNATURE>
  method extract_excel.
*&---------------------------------------------------------------------*
*  Method Name       : EXTRACT_EXCEL
*  Method Visibility : Private
*  Method Desc       : Extract data from excel xstring
*&---------------------------------------------------------------------*
    constants : lc_filename type string value 'DEFAULT'.

    data : lt_worksheets type table of string,
           l_excel       type ref to cl_fdt_xl_spreadsheet,
           l_excel_core  type ref to cx_fdt_excel_core,
           l_data        type ref to data,
           l_dref        type ref to data,
           lv_msg        type string,
           lv_ws_name    type string,
           ls_bapiret2   type bapiret2.
    try.
* Get excel content
        create object l_excel
          exporting
            document_name = lc_filename
            xdocument     = im_xdocument
            mime_type     = 'xls'.
      catch cx_fdt_excel_core into l_excel_core.
        raise excel_content_error.
    endtry.

* Call method to get list of worksheets in the .xlsx file
    l_excel->if_fdt_doc_spreadsheet~get_worksheet_names( importing worksheet_names = lt_worksheets ).

* Condition to check whether .xlsx file has any active worksheets
* Always default to read first worksheet in excel
* This worksheet index can be passed from caller prog.
    read table lt_worksheets index 1 into lv_ws_name.
    if sy-subrc <> 0.
      "Error
      raise no_active_worksheet.
    endif.
* Get reference of .xlsx file contents in the active worksheet
    ch_data = l_excel->if_fdt_doc_spreadsheet~get_itab_from_worksheet( lv_ws_name ).
    if ch_data is not bound.
      raise no_excel_content.
    endif.
  endmethod.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCL_UPLOAD->GET_FILE_PC
* +-------------------------------------------------------------------------------------------------+
* | [<-()] RE_FILEPATH                    TYPE        DXLPATH
* +--------------------------------------------------------------------------------------</SIGNATURE>
  method get_file_pc.
*&---------------------------------------------------------------------*
*  Method Name       : GET_FILE_PC
*  Method Visibility : Public
*  Method Desc       : This method is for caller to choose a file on
*                      local PC.
*&---------------------------------------------------------------------*

    data: lv_path       type dxfields-longpath,
          lv_abend_flag type dxfields-abendflag.

* Value help for Filename
    call function 'F4_DXFILENAME_TOPRECURSION'
      exporting
        i_location_flag = 'P'  "c_win
        i_server        = '?'
        i_path          = '/'
        filemask        = '(Excel Files *.xls;*.xlsx)|Excel Files *.xls;*.xlsx'
        fileoperation   = 'R'
      importing
        o_path          = lv_path
        abend_flag      = lv_abend_flag
      exceptions
        rfc_error       = 1
        others          = 2.

    if sy-subrc <> 0.
      message id      sy-msgid
              type    'S'
              number  sy-msgno
              with    sy-msgv1 display like sy-msgty.
    endif.

    if not lv_abend_flag is initial.
      clear lv_path.
    endif.
    re_filepath = lv_path.
  endmethod.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCL_UPLOAD->GET_FILE_SERV
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_DIRECTORY                   TYPE        DXLPATH (default ='.')
* | [<-()] RE_FILEPATH                    TYPE        DXLPATH
* +--------------------------------------------------------------------------------------</SIGNATURE>
  method get_file_serv.
    call function '/SAPDMC/LSM_F4_SERVER_FILE'
      exporting
        directory        = im_directory
        filemask         = '*.xlsx'
      importing
        serverfile       = re_filepath
      exceptions
        canceled_by_user = 1
        others           = 2.
  endmethod.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZCL_UPLOAD->READ_EXCEL_PC
* +-------------------------------------------------------------------------------------------------+
* | [<---] EX_SOLIX                       TYPE        SOLIX_TAB
* | [<---] EX_SUBRC                       TYPE        SYST_SUBRC
* +--------------------------------------------------------------------------------------</SIGNATURE>
  method read_excel_pc.
*&---------------------------------------------------------------------*
*  Method Name       : READ_EXCEL_PC
*  Method Visibility : Private
*  Method Desc       : Read excel file from local pc.
*&---------------------------------------------------------------------*
    data: lv_filename type string,
          lt_solix    type solix_tab.
    lv_filename = filepath.
    call method cl_gui_frontend_services=>gui_upload
      exporting
        filename                = lv_filename
        filetype                = 'BIN'
      changing
        data_tab                = lt_solix
      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
        not_supported_by_gui    = 17
        error_no_gui            = 18
        others                  = 19.
    ex_subrc = sy-subrc.
    ex_solix = lt_solix.
  endmethod.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZCL_UPLOAD->READ_EXCEL_SERVER
* +-------------------------------------------------------------------------------------------------+
* | [<---] EX_SOLIX                       TYPE        SOLIX_TAB
* | [EXC!] FILE_NOT_FOUND
* +--------------------------------------------------------------------------------------</SIGNATURE>
  method read_excel_server.
*&---------------------------------------------------------------------*
*  Method Name       : READ_EXCEL_SERVER
*  Method Visibility : Private
*  Method Desc       : Read excel file from server
*&---------------------------------------------------------------------*
    data: lt_bapiret2 type bapiret2_t,
          lv_solix    type solix.
    open dataset filepath for input in binary mode.
    if sy-subrc <> 0.
      raise file_not_found.
      return.
    endif.
    do.
      read dataset filepath into lv_solix.
      if sy-subrc <> 0.
        exit.
      else.
        append lv_solix to ex_solix.
      endif.
    enddo.
    close dataset filepath.
  endmethod.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCL_UPLOAD->SET_CONSTRUCTOR
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_FILEPATH                    TYPE        DXLPATH
* | [--->] IM_SKIP_ROW                    TYPE        I
* +--------------------------------------------------------------------------------------</SIGNATURE>
  method set_constructor.
*&---------------------------------------------------------------------*
*  Method Name       : SET_CONSTRUCTOR
*  Method Visibility : Public
*  Method Desc       : This is to set the value if the constructor did
*                      not set
*                      filepath - the directory of the file
*                      skip_row - skip header row
*&---------------------------------------------------------------------*
    filepath        = im_filepath.
    skip_row        = im_skip_row.
  endmethod.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZCL_UPLOAD->TO_XSTRING
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_SOLIX                       TYPE        SOLIX_TAB
* | [<---] EX_XSTRING                     TYPE        XSTRING
* | [EXC!] EMPTY_XSTRING
* | [EXC!] EMPTY_SOLIX
* +--------------------------------------------------------------------------------------</SIGNATURE>
  method to_xstring.
*&---------------------------------------------------------------------*
*  Method Name       : TO_XSTRING
*  Method Visibility : Private
*  Method Desc       : Convert to XString format
*&---------------------------------------------------------------------*
    if im_solix is initial.
      "Should not pass an empty itab
      raise empty_solix.
    endif.
    call method cl_bcs_convert=>solix_to_xstring
      exporting
        it_solix   = im_solix
      receiving
        ev_xstring = ex_xstring.
    if ex_xstring is initial.
      raise empty_xstring.
    endif.
  endmethod.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCL_UPLOAD->UPLOAD_EXCEL_PC
* +-------------------------------------------------------------------------------------------------+
* | [<-->] CH_SUCCESS                     TYPE        TABLE
* | [<-->] CH_ERROR                       TYPE REF TO DATA
* | [<-->] CH_MESSAGE                     TYPE        TT_MESSAGE(optional)
* | [EXC!] EMPTY_FILEPATH
* | [EXC!] FILE_OPEN_ERROR
* | [EXC!] FILE_READ_ERROR
* | [EXC!] NO_BATCH
* | [EXC!] GUI_REFUSE_FILETRANSFER
* | [EXC!] INVALID_TYPE
* | [EXC!] NO_AUTHORITY
* | [EXC!] UNKNOWN_ERROR
* | [EXC!] BAD_DATA_FORMAT
* | [EXC!] HEADER_NOT_ALLOWED
* | [EXC!] SEPARATOR_NOT_ALLOWED
* | [EXC!] HEADER_TOO_LONG
* | [EXC!] UNKNOWN_DP_ERROR
* | [EXC!] ACCESS_DENIED
* | [EXC!] DP_OUT_OF_MEMORY
* | [EXC!] DISK_FULL
* | [EXC!] DP_TIMEOUT
* | [EXC!] NOT_SUPPORTED_BY_GUI
* | [EXC!] ERROR_NO_GUI
* | [EXC!] EXCEL_CONTENT_ERROR
* | [EXC!] NO_ACTIVE_WORKSHEET
* | [EXC!] NO_EXCEL_CONTENT
* +--------------------------------------------------------------------------------------</SIGNATURE>
  method upload_excel_pc.
*&---------------------------------------------------------------------*
*  Method Name       : UPLOAD_EXCEL_PC
*  Method Visibility : Public
*  Method Desc       : This method is for caller to upload excel file
*                      from local PC
*&---------------------------------------------------------------------*
    data : lt_solix    type solix_tab,
           lv_xstring  type xstring,
           lv_filename type string,
           lt_data     type ref to data,
           lv_subrc    type syst_subrc.

*Check filepath
    if filepath is initial.
      raise empty_filepath.
    endif.
*Read excel
    read_excel_pc(
      importing
        ex_solix = lt_solix                 " GBT: SOLIX as Table Type
        ex_subrc = lv_subrc                 " ABAP System Field: Return Code of ABAP Statements
    ).
    if lv_subrc <> 0.
      case lv_subrc.
        when 1.
          raise file_open_error.
        when 2.
          raise file_read_error.
        when 3.
          raise no_batch.
        when 4.
          raise gui_refuse_filetransfer.
        when 5.
          raise invalid_type.
        when 6.
          raise no_authority.
        when 7.
          raise unknown_error.
        when 8.
          raise bad_data_format.
        when 9.
          raise header_not_allowed.
        when 10.
          raise separator_not_allowed.
        when 11.
          raise header_too_long.
        when 12.
          raise unknown_dp_error.
        when 13.
          raise access_denied.
        when 14.
          raise dp_out_of_memory.
        when 15.
          raise disk_full.
        when 16.
          raise dp_timeout.
        when 17.
          raise not_supported_by_gui.
        when 18.
          raise error_no_gui.
        when others.
      endcase.
    endif.

*Convert to XString
    to_xstring( exporting im_solix   = lt_solix
                    importing ex_xstring = lv_xstring ).
*Extract Excel
    extract_excel( exporting  im_xdocument        = lv_xstring
                      changing   ch_data             = lt_data
                      exceptions excel_content_error = 1
                                 no_active_worksheet = 2
                                 no_excel_content    = 3 ).
    if sy-subrc = 1.
      raise excel_content_error.
    elseif sy-subrc = 2.
      raise no_active_worksheet.
    elseif sy-subrc = 3.
      raise no_excel_content.
    endif.

*Validation
    validate( exporting im_data    = lt_data
              changing  ch_success = ch_success
                        ch_error   = ch_error
                        ch_message = ch_message
    ).
  endmethod.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCL_UPLOAD->UPLOAD_EXCEL_SERVER
* +-------------------------------------------------------------------------------------------------+
* | [<-->] CH_SUCCESS                     TYPE        TABLE
* | [<-->] CH_ERROR                       TYPE REF TO DATA
* | [<-->] CH_MESSAGE                     TYPE        TT_MESSAGE(optional)
* | [EXC!] EMPTY_FILEPATH
* | [EXC!] FILE_NOT_FOUND
* | [EXC!] EXCEL_CONTENT_ERROR
* | [EXC!] NO_ACTIVE_WORKSHEET
* | [EXC!] NO_EXCEL_CONTENT
* +--------------------------------------------------------------------------------------</SIGNATURE>
  method upload_excel_server.
*&---------------------------------------------------------------------*
*  Method Name       : UPLOAD_EXCEL_SERVER
*  Method Visibility : Public
*  Method Desc       : This method is for caller to upload excel file
*                      from AL11.
*&---------------------------------------------------------------------*
    data: lt_bapiret2 type bapiret2_t,
          lt_solix    type solix_tab,
          lv_solix    type solix,
          lv_xstring  type xstring,
          lt_data     type ref to data.
*Validation
*Check filepath
    if filepath is initial.
      raise empty_filepath.
    endif.
*Read excel
    read_excel_server( importing ex_solix = lt_solix
                       exceptions file_not_found = 1 ).
    if sy-subrc = 1.
      raise file_not_found.
    endif.
*Convert to XString
    to_xstring( exporting im_solix   = lt_solix
                importing ex_xstring = lv_xstring ).
*Extract excel
    extract_excel( exporting  im_xdocument        = lv_xstring
                   changing   ch_data             = lt_data
                   exceptions excel_content_error = 1
                              no_active_worksheet = 2
                              no_excel_content    = 3 ).
    if sy-subrc = 1.
      raise excel_content_error.
    elseif sy-subrc = 2.
      raise no_active_worksheet.
    elseif sy-subrc = 3.
      raise no_excel_content.
    endif.

*Validation
    validate( exporting im_data    = lt_data
              changing  ch_success = ch_success
                        ch_error   = ch_error
                        ch_message = ch_message ).

  endmethod.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZCL_UPLOAD->VALIDATE
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_DATA                        TYPE REF TO DATA
* | [<-->] CH_SUCCESS                     TYPE        TABLE
* | [<-->] CH_ERROR                       TYPE REF TO DATA
* | [<-->] CH_MESSAGE                     TYPE        TT_MESSAGE(optional)
* +--------------------------------------------------------------------------------------</SIGNATURE>
  method validate.
*&---------------------------------------------------------------------*
*  Method Name       : VALIDATE
*  Method Visibility : Private
*  Method Desc       : Validate all the row in the excel file. In this
*                      method, it receives the excel content in
*                      IM_DATA, then it gets successful itab(CH_SUCCESS)
*                      in custom structure, and CH_MESSAGE is optional.
*                      The idea is, use the structure in CH_SUCCESS,
*                      to create another itab with string type and
*                      store excel content which has error.
*                      If the excel content passes validation, then
*                      it will append to CH_SUCCESS, else CH_ERROR.
*&---------------------------------------------------------------------*
    data: dref_file_struc  type ref to data,
          lt_comp          type standard table of abap_componentdescr with key name,
          lo_stru          type ref to cl_abap_structdescr,
          ls_comp          type abap_componentdescr,
          lv_error         type char1,
          lv_row_no        type syst_tabix,  "Row number
          dref_t_str       type ref to data, "Table with string
          dref_s_str       type ref to data, "Structure with string
          dref_s_err       type ref to data, "Error structure with string,
          dref_s_succ      type ref to data, "Success structure with string
          dref_succ        type ref to data, "Success structue
          lt_message       type tt_message,  "Message
          lt_final_message type tt_message.  "Final message

    field-symbols: <lfs_t_file_data> type table,
                   <lfs_s_file_data> type any,
                   <lfs_s_err>       type any,
                   <lfs_s_succ>      type any,
                   <lfs_t_err>       type table,
                   <lfs_s_str>       type any.
*Get the structure & table of the input itab
    create data dref_file_struc like line of ch_success.
    create_structure(
      exporting
        im_file_struc = dref_file_struc " File Structure.
      changing
        ch_str_table  = dref_t_str      " Table with string.
        ch_str_struc  = dref_s_str      " Data structure with string.
    ).
    assign im_data->* to <lfs_t_file_data>.
    if <lfs_t_file_data> is assigned.
*Create data object and reference to field symbol
      create data dref_s_err  like dref_s_str.
      create data dref_s_succ like line of ch_success.
      assign dref_s_err->*    to <lfs_s_err>.
      assign dref_s_succ->*   to <lfs_s_succ>.
      assign dref_s_str->*    to <lfs_s_str>.
      create data ch_error    like table of <lfs_s_str>.
      assign ch_error->*      to <lfs_t_err>.
*Loop the data and do validation
      loop at <lfs_t_file_data> assigning <lfs_s_file_data>.
        lv_row_no = sy-tabix.
        if lv_row_no <= skip_row.
          continue.
        endif.
        do.
          assign component sy-index of structure <lfs_s_file_data> to field-symbol(<a>).
          if sy-subrc = 0.
            assign component sy-index of structure <lfs_s_str> to field-symbol(<b>).
            if sy-subrc = 0.
              <b> = <a>.
            else.
              exit.
            endif.
          else.
            exit.
          endif.
        enddo.
        unassign: <a>, <b>.
*Pass the string structure to do custom validation
        validate_row(
          exporting
            im_row_content = <lfs_s_str>       " Row content to be validated.
            im_row         = lv_row_no         " Row number
          importing
            ex_row_content = <lfs_s_succ>      " Excel content which is in excel file format
            ex_error       = lv_error          " Error Indicator
          changing
            ch_message     = lt_message ).     " Error structure
        if lv_error = abap_true.
          append <lfs_s_file_data> to <lfs_t_err>.
        else.
          append <lfs_s_succ> to ch_success.
        endif.
        if lt_message is not initial.
          append lines of lt_message to ch_message.
        endif.
        clear: lv_error, lt_message.
        clear: <lfs_s_str>.
      endloop.
    endif.
  endmethod.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Protected Method ZCL_UPLOAD->VALIDATE_ROW
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_ROW_CONTENT                 TYPE        ANY
* | [--->] IM_ROW                         TYPE        I
* | [<---] EX_ROW_CONTENT                 TYPE        ANY
* | [<---] EX_ERROR                       TYPE        CHAR1
* | [<-->] CH_MESSAGE                     TYPE        TT_MESSAGE(optional)
* +--------------------------------------------------------------------------------------</SIGNATURE>
  method validate_row.
*&---------------------------------------------------------------------*
*  Method Name       : VALIDATE_ROW
*  Method Visibility : Protected
*  Method Desc       : Validate each content row. This method can be
*                      redefined to put custom logic.
*&---------------------------------------------------------------------*
    data: lv_type type char1.
    do.
      assign component sy-index of structure im_row_content to field-symbol(<a>).
      if sy-subrc = 0.
        assign component sy-index of structure ex_row_content to field-symbol(<b>).
        if sy-subrc = 0.
          check_type(
            exporting
              im_value = <a>
            changing
              ch_value = <b> ).
        else.
          exit.
        endif.
      else.
        exit.
      endif.
    enddo.
  endmethod.
endclass.

Sample program source code: Z_UPLOAD

report z_upload.
*&---------------------------------------------------------------------*
*&  Program Name       : Z_UPLOAD
*&  Program description: This program demo how to upload excel file from
*&                       server(AL11) and local pc.
*&---------------------------------------------------------------------*
class lcl_upload definition deferred.
*Data declaration
types: begin of ty_file,
         mandt   type mandt,
         vbeln   type vbeln,
         vkorg   type vkorg,
         erdat   type erdat,
         erzet   type erzet,
         netwr   type netwr_ak,
         awahr   type awahr_ak,
         mahza   type mahza,
         stwae   type stwae,
         pendays type pdays,
       end of ty_file.
types: begin of ty_message,
         row       type i,
         fieldname type lvc_fname,
         message   type string,
       end of ty_message,
       tt_message type standard table of ty_message with empty key.
data: lv_filepath type dxlpath,
      go_upload   type ref to lcl_upload,
      gt_file     type standard table of ty_file,
      dref_err    type ref to data,
      gt_message  type tt_message.
field-symbols: <gfs_t_error> type table.

*Class Defination
class lcl_upload definition inheriting from zcl_upload final.
  protected section.
    methods: validate_row redefinition.
  private section.
    methods: validate_mandt importing value(im_value) type mandt
                            exporting ex_error        type char1.
    methods: validate_vkorg importing value(im_value) type vkorg
                            exporting ex_error        type char1.
endclass.
class lcl_upload implementation.
  method validate_row.
    data: ls_row_content type ty_file,
          lv_error       type char1,
          ls_message     type ty_message,
          lt_message     type tt_message.
*****************************EXPLANATION*****************************
*This part will perform 2 kind of validation
*1. Format validation
*2. Data accuracy validation
*
*There are parameters passed from caller method 'VALIDATE'
*5 parameters here:
* - IM_ROW_CONTENT -> Excel content which is in string format
* - IM_ROW_NO      -> Row number
* - EX_ROW_CONTENT -> Excel content which is in excel file(custom) format
* - EX_ERROR       -> FLAG X if error, it will be copied to error structure
* - CH_MESSAGE     -> Add custom message
*****************************EXPLANATION*****************************

*There are two methods to do validation.
*First method -> Get the field one by one and compare,
*                it is tedious but there could be some
*                scenario that some fields depend on
*                each other in validation

*Second method -> Do validation in a do loop to read
*                 field one by one
*
*Comment out one of the method, to see the output
*
******************************************
* Start of first method
******************************************
    assign component 'MANDT' of structure im_row_content to field-symbol(<mandt>).
    if sy-subrc = 0.
*Type validation
      check_type( exporting im_value = <mandt>                 " Input Value
                  importing ex_error = lv_error                " Error Indicator.
                  changing  ch_value = ls_row_content-mandt ). " Output Value
      if lv_error <> abap_true.
*Data accuracy(Put your own logic here ).
        validate_mandt( exporting im_value = ls_row_content-mandt
                        importing ex_error = lv_error ).
      endif.
*Error message here(example)
      if lv_error = abap_true.
        ls_message-row = im_row.
        ls_message-fieldname = 'MANDT'.
        ls_message-message = 'Invalid Client Number' && | | && <mandt>.
        append ls_message to lt_message.
        clear: ls_message.
        ex_error = abap_true.
      endif.
      clear: lv_error.
    endif.
*****Repeat the same thing for other fields......

*Return value to method VALIDATE
    ex_row_content = ls_row_content.
    ch_message = lt_message.

******************************************
* End of first method
******************************************

*--------------------------------------------------------------------*

*******************************************
** Start of second method
*******************************************
*    data: lv_type type char1,
*          lt_comp type standard table of abap_componentdescr with key name,
*          ls_comp type abap_componentdescr,
*          lo_stru type ref to cl_abap_structdescr.
*
*    lo_stru ?= cl_abap_typedescr=>describe_by_data( im_row_content ).
*    lt_comp = lo_stru->get_components( ).
*    do.
*      assign component sy-index of structure im_row_content to field-symbol(<a>).
*      if sy-subrc = 0.
*        assign component sy-index of structure ex_row_content to field-symbol(<b>).
*        read table lt_comp into ls_comp index sy-index.
*        if sy-subrc = 0.
**Type validation
*          check_type( exporting im_value = <a>      " Input Value
*                      importing ex_error = lv_error " Error Indicator.
*                      changing  ch_value = <b> ).   " Output Value
*          if lv_error <> abap_true.
**Data accuracy(Put your own logic here ).
**Pass the value to another validate method
*            if sy-subrc = 0.
*              case ls_comp-name.
*                when 'MANDT'.
*                  validate_mandt( exporting im_value = <b>
*                                  importing ex_error = lv_error ).
*                when 'VKORG'.
*                  validate_vkorg( exporting im_value = <b>
*                                  importing ex_error = lv_error ).
**Put other fields here...
*                  "when..
*                  "when..
*                when others.
*                  "Put your own logic
*              endcase.
*            endif.
*          endif.
*          if lv_error = abap_true.
**Error message here(example)
*            ls_message-row = im_row.
*            ls_message-fieldname = ls_comp-name.
*            case ls_comp-name.
*              when 'MANDT'.
*                ls_message-message = 'Invalid Client Number' && | | && <b>.
*              when 'VKORG'.
*                ls_message-message = 'Invalid sales org' && | | && <b>.
*            endcase.
*
*            append ls_message to lt_message.
*            clear: ls_message.
*            ex_error = abap_true.
*          endif.
*        else.
*          exit.
*        endif.
*      else.
*        exit.
*      endif.
*      clear: lv_error, ls_comp.
*    enddo.
**Return value to method VALIDATE
*    ex_row_content = ls_row_content.
*    ch_message = lt_message.
*******************************************
** End of second method
*******************************************
  endmethod.

  method validate_mandt.
*Own logic..
*Example
    if im_value <> 100."client 100
      ex_error = abap_true.
    endif.
  endmethod.
  method validate_vkorg.
*Own logic
*Example
    if im_value > 0.
      ex_error = abap_true.
    endif.
  endmethod.
endclass.

parameters: p_serv radiobutton group rb1 default 'X' user-command cm1,
            p_locl radiobutton group rb1,
            p_path type dxlpath OBLIGATORY.

at selection-screen on value-request for p_path.
  if p_serv = abap_true.
    p_path = go_upload->get_file_serv( ).
  elseif p_locl = abap_true.
    p_path = go_upload->get_file_pc( ).
  endif.

initialization.
  create object: go_upload.

start-of-selection.
  if p_serv = abap_true.
    perform read_from_server.
  elseif p_locl = abap_true.
    perform read_from_local.
  endif.

end-of-selection.

form read_from_server.
*Upload from AL11
  go_upload->set_constructor(
    exporting
      im_filepath = p_path      " Local file for upload/download
      im_skip_row = 1           " Skip row
  ).

  go_upload->upload_excel_server(
    changing
      ch_success          = gt_file          " To store successful loaded record.
      ch_error            = dref_err         " To store failed record.
      ch_message          = gt_message       " To store custom message.
    exceptions
      empty_filepath      = 1                " Empty Filepath.
      file_not_found      = 2                " File not found.
      excel_content_error = 3                " Excel Content Error
      no_active_worksheet = 4                " No active worksheet
      no_excel_content    = 5                " No content found in excel.
      others              = 6 ).
  if sy-subrc <> 0.
*Put own logic to cater exception
  endif.

**Display successful loaded data
  if gt_file is not initial.
    cl_demo_output=>display( gt_file ).
  endif.

*Display error data
  assign dref_err->* to <gfs_t_error>.
  if sy-subrc = 0.
    cl_demo_output=>display( <gfs_t_error> ).
  endif.

*Display message
  if gt_message is not initial.
    cl_demo_output=>display( gt_message ).
  endif.
endform.

form read_from_local.
*Upload from local pc
  go_upload->set_constructor(
    exporting
      im_filepath = p_path      " Local file for upload/download
      im_skip_row = 1           " Skip row
  ).
  go_upload->upload_excel_pc(
    changing
      ch_success              = gt_file                 " To store successful loaded record.
      ch_error                = dref_err                " To store failed record.
      ch_message              = gt_message              " To store custom message.
    exceptions
      empty_filepath          = 1
      file_open_error         = 2
      file_read_error         = 3
      no_batch                = 4
      gui_refuse_filetransfer = 5
      invalid_type            = 6
      no_authority            = 7
      unknown_error           = 8
      bad_data_format         = 9
      header_not_allowed      = 10
      separator_not_allowed   = 11
      header_too_long         = 12
      unknown_dp_error        = 13
      access_denied           = 14
      dp_out_of_memory        = 15
      disk_full               = 16
      dp_timeout              = 17
      not_supported_by_gui    = 18
      error_no_gui            = 19
      excel_content_error     = 20
      no_active_worksheet     = 21
      no_excel_content        = 22
      others                  = 23 ).
  if sy-subrc <> 0.
    message id sy-msgid type sy-msgty number sy-msgno
      with sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  endif.
**Display successful loaded data
  if gt_file is not initial.
    cl_demo_output=>display( gt_file ).
  endif.

**Display error data
  assign dref_err->* to <gfs_t_error>.
  if sy-subrc = 0.
    cl_demo_output=>display( <gfs_t_error> ).
  endif.

*Display message
  if gt_message is not initial.
    cl_demo_output=>display( gt_message ).
  endif.
endform.

Sample Excel File:

Leave a Reply

Your email address will not be published. Required fields are marked *