Excel ABAP Helper
Criei essa classe para tarefas de leitura de arquivos Excel (xlsx). O diferencial é a tratativa dos campos fica no programa que chama essa classe ou seja, eu posso ter tratativas diferentes para arquivos diferentes. Isso é feito usando o evento excel_loaded que dispara o perform do programa chamador.
CLASS zcl_migration_cockpit_helper DEFINITION
PUBLIC
FINAL
CREATE PUBLIC .
PUBLIC SECTION.
INTERFACES zif_migration_cockpit_helper .
CLASS-DATA _instance TYPE REF TO zcl_migration_cockpit_helper .
EVENTS excel_loaded
EXPORTING
VALUE(ir_data) TYPE REF TO data .
CLASS-METHODS class_constructor .
METHODS constructor .
METHODS bupa_change_bpkind
IMPORTING
!iv_partner TYPE bp_partner
!iv_partnertype TYPE bu_bpkind
RETURNING
VALUE(rt_return) TYPE bapiret2_tab .
METHODS bupa_change_vip
IMPORTING
!iv_partner TYPE bp_partner
!iv_vip TYPE bp_vip_partner
RETURNING
VALUE(rt_return) TYPE bapiret2_tab .
CLASS-METHODS get_instance
RETURNING
VALUE(ro_instance) TYPE REF TO zcl_migration_cockpit_helper .
PROTECTED SECTION.
PRIVATE SECTION.
METHODS load_excel
FOR EVENT load_excel OF zcl_copia_proj_audit
IMPORTING
!ir_data .
ENDCLASS.
CLASS zcl_migration_cockpit_helper IMPLEMENTATION.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCL_MIGRATION_COCKPIT_HELPER->BUPA_CHANGE_BPKIND
* +-------------------------------------------------------------------------------------------------+
* | [--->] IV_PARTNER TYPE BP_PARTNER
* | [--->] IV_PARTNERTYPE TYPE BU_BPKIND
* | [<-()] RT_RETURN TYPE BAPIRET2_TAB
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD bupa_change_bpkind.
* DATA(ls_centraldata) = VALUE bapibus1006_central( partnertype = iv_partnertype ).
* DATA(ls_centraldata_x) = VALUE bapibus1006_central_x( partnertype = abap_true ).
*
* CALL FUNCTION 'BAPI_BUPA_CENTRAL_CHANGE'
* EXPORTING
* businesspartner = iv_partner
* centraldata = ls_centraldata
* centraldata_x = ls_centraldata_x
* TABLES
* return = rt_return.
*
* LOOP AT rt_return INTO DATA(ls_return).
* MESSAGE ID ls_return-id
* TYPE ls_return-type
* NUMBER ls_return-number
* WITH ls_return-message_v1 ls_return-message_v2 ls_return-message_v3 ls_return-message_v4 INTO DATA(lv_dummy).
* zcl_ptool_helper=>bal_log_msg_add( iv_handle = _handle ).
* ENDLOOP.
* zcl_ptool_helper=>bal_db_save( ).
*
* IF NOT line_exists( rt_return[ type = |E| ] ).
*
* CALL FUNCTION 'BAPI_TRANSACTION_COMMIT'
* EXPORTING
* wait = abap_true
* IMPORTING
* return = ls_return.
*
* IF ls_return IS NOT INITIAL.
* MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 INTO lv_dummy.
* zcl_ptool_helper=>bal_log_msg_add( iv_handle = _handle ).
* zcl_ptool_helper=>bal_db_save( ).
* ELSE.
* MESSAGE ID 'ZBUPA' TYPE 'S' NUMBER '000' WITH iv_partner iv_partnertype DISPLAY LIKE 'S'.
* zcl_ptool_helper=>bal_log_msg_add( iv_handle = _handle ).
* zcl_ptool_helper=>bal_db_save( ).
* ENDIF.
*
* ENDIF.
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCL_MIGRATION_COCKPIT_HELPER->BUPA_CHANGE_VIP
* +-------------------------------------------------------------------------------------------------+
* | [--->] IV_PARTNER TYPE BP_PARTNER
* | [--->] IV_VIP TYPE BP_VIP_PARTNER
* | [<-()] RT_RETURN TYPE BAPIRET2_TAB
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD bupa_change_vip.
* DATA(ls_bp001) = VALUE bp001( vip = abap_true ).
* DATA(ls_bp001_x) = VALUE bp001_x( vip = abap_true ).
*
* CALL FUNCTION 'FS_API_BP001_SET'
* EXPORTING
* iv_partner = iv_partner
* is_bp001 = ls_bp001
* is_bp001_x = ls_bp001_x
* TABLES
* et_return = rt_return.
*
* LOOP AT rt_return INTO DATA(ls_return).
* MESSAGE ID ls_return-id
* TYPE ls_return-type
* NUMBER ls_return-number
* WITH ls_return-message_v1 ls_return-message_v2 ls_return-message_v3 ls_return-message_v4 INTO DATA(lv_dummy).
* zcl_ptool_helper=>bal_log_msg_add( iv_handle = _handle ).
* ENDLOOP.
* zcl_ptool_helper=>bal_db_save( ).
*
* IF NOT line_exists( rt_return[ type = |E| ] ).
*
* CALL FUNCTION 'BAPI_TRANSACTION_COMMIT'
* EXPORTING
* wait = abap_true
* IMPORTING
* return = ls_return.
*
* IF ls_return IS NOT INITIAL.
* MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 INTO lv_dummy.
* ELSE.
* MESSAGE ID 'ZBUPA' TYPE 'S' NUMBER '002' WITH iv_partner INTO lv_dummy.
* zcl_ptool_helper=>bal_log_msg_add( iv_handle = _handle ).
* zcl_ptool_helper=>bal_db_save( ).
* ENDIF.
*
* ENDIF.
*
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Static Public Method ZCL_MIGRATION_COCKPIT_HELPER=>CLASS_CONSTRUCTOR
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD class_constructor.
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCL_MIGRATION_COCKPIT_HELPER->CONSTRUCTOR
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD constructor.
zif_migration_cockpit_helper~_handle = zcl_ptool_helper=>bal_log_create( iv_object = |ZBUPA| ).
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Static Public Method ZCL_MIGRATION_COCKPIT_HELPER=>GET_INSTANCE
* +-------------------------------------------------------------------------------------------------+
* | [<-()] RO_INSTANCE TYPE REF TO ZCL_MIGRATION_COCKPIT_HELPER
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD get_instance.
IF _instance IS NOT BOUND.
_instance = NEW #( ).
ENDIF.
ro_instance = _instance.
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZCL_MIGRATION_COCKPIT_HELPER->LOAD_EXCEL
* +-------------------------------------------------------------------------------------------------+
* | [--->] IR_DATA LIKE
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD load_excel.
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCL_MIGRATION_COCKPIT_HELPER->ZIF_MIGRATION_COCKPIT_HELPER~CONVERT_FILE
* +-------------------------------------------------------------------------------------------------+
* | [--->] IV_FILELENGTH TYPE I
* | [--->] IT_RECORDS TYPE SOLIX_TAB
* | [<-()] RV_HEADERXSTRING TYPE XSTRING
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD zif_migration_cockpit_helper~convert_file.
CHECK it_records[] IS NOT INITIAL.
MESSAGE ID 'ZBUPA' TYPE 'S' NUMBER '004' INTO DATA(lv_dummy).
zcl_ptool_helper=>bal_log_msg_add( iv_handle = zif_migration_cockpit_helper~_handle ).
zcl_ptool_helper=>bal_db_save( ).
"convert binary data to xstring
"if you are using cl_fdt_xl_spreadsheet in odata then skips this step
"as excel file will already be in xstring
CALL FUNCTION 'SCMS_BINARY_TO_XSTRING'
EXPORTING
input_length = iv_filelength
IMPORTING
buffer = rv_headerxstring
TABLES
binary_tab = it_records
EXCEPTIONS
failed = 1
OTHERS = 2.
IF sy-subrc = 0.
* DATA(lv_message) = |Convertendo arquivo...|.
ELSE.
* lv_message = |N�o foi poss�vel converter o arquivo!|.
ENDIF.
* WRITE: / lv_message.
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCL_MIGRATION_COCKPIT_HELPER->ZIF_MIGRATION_COCKPIT_HELPER~OPEN_EXCEL
* +-------------------------------------------------------------------------------------------------+
* | [--->] IV_FILE TYPE STRING
* | [--->] IV_HEADERXSTRING TYPE XSTRING
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD zif_migration_cockpit_helper~open_excel.
FIELD-SYMBOLS : <fs_table> TYPE STANDARD TABLE.
DATA: lo_excel_ref TYPE REF TO cl_fdt_xl_spreadsheet .
CHECK iv_headerxstring IS NOT INITIAL.
TRY .
lo_excel_ref = NEW cl_fdt_xl_spreadsheet(
document_name = iv_file
xdocument = iv_headerxstring ).
CATCH cx_fdt_excel_core INTO DATA(lx_excel).
WRITE: |Erro convertendo Excel...|.
RETURN.
"Implement suitable error handling here
ENDTRY .
CHECK lo_excel_ref IS NOT INITIAL.
"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.
READ TABLE lt_worksheets INTO DATA(lv_woksheetname) INDEX 1.
zif_migration_cockpit_helper~_data = lo_excel_ref->if_fdt_doc_spreadsheet~get_itab_from_worksheet( lv_woksheetname ).
"now you have excel work sheet data in dyanmic internal table
* ASSIGN lo_data_ref->* TO <fs_table>.
* WRITE: / |Processando Excel...|.
MESSAGE ID 'ZBUPA' TYPE 'S' NUMBER '003' INTO DATA(lv_dummy).
zcl_ptool_helper=>bal_log_msg_add( iv_handle = zif_migration_cockpit_helper~_handle ).
zcl_ptool_helper=>bal_db_save( ).
RAISE EVENT excel_loaded EXPORTING ir_data = zif_migration_cockpit_helper~_data.
* PERFORM f_process IN PROGRAM (sy-cprog) IF FOUND.
ENDIF.
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCL_MIGRATION_COCKPIT_HELPER->ZIF_MIGRATION_COCKPIT_HELPER~READ_FILE
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD zif_migration_cockpit_helper~read_file.
CALL METHOD cl_gui_frontend_services=>gui_upload
EXPORTING
filename = zif_migration_cockpit_helper~_filename
filetype = CONV char10( `BIN` )
IMPORTING
filelength = zif_migration_cockpit_helper~_filelength
CHANGING
data_tab = zif_migration_cockpit_helper~_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
not_supported_by_gui = 17
error_no_gui = 18
OTHERS = 19.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCL_MIGRATION_COCKPIT_HELPER->ZIF_MIGRATION_COCKPIT_HELPER~SET_FILENAME
* +-------------------------------------------------------------------------------------------------+
* | [--->] IV_FILENAME TYPE STRING
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD zif_migration_cockpit_helper~set_filename.
zif_migration_cockpit_helper~_filename = iv_filename.
ENDMETHOD.
ENDCLASS.