Bir abap programcısıysanız muhakkak excel yükleyen program yazmışsınızdır, yazmamış olsanız bile karşılaşmışsınızdır. Karşılaşmamış olsanız bile bir gün yolunuz kesişebilir. Ben de bu konuda kullanılabilir birşey tasarlamaya ve bunu paylaşmaya karar verdim.

Genellikle programlarımızda kullandığımız “ALSM_EXCEL_TO_INTERNAL_TABLE ” fonksiyonu yerine ” cl_openxml_helper” classından faydalandım. Daha sonra Excel işlemleri için ” cl_fdt_xl_spreadsheet ” classını kullandım. Dikkat edilmesi gereken noktalardan biri, excel dosyasındaki sütun sayısı ve exceli karşılayacak yapımızın alan sayısının eşit olması. Diğeri ise, excelden veriyi okurken kaçıncı satırdan başlayacağını belirtmek.

Çalışma mantığı olarak ise, SE11 işlem kodundan excel dosyanızdaki sütunları karşılayabilecek bir yapı(structure) oluşturmak.

Z_STLOG_NET

REPORT  Z_STLOG_NET.

DATA gt_tab        TYPE filetable.
DATA gs_tab        LIKE LINE OF gt_tab .
DATA gv_returncode TYPE i.
DATA gt_ret        TYPE bapiret2_t .
DATA gv_filepath   TYPE string .
DATA gv_strname    TYPE string .
DATA dref_out      TYPE REF TO data .

FIELD-SYMBOLS      <fs_out> TYPE ANY TABLE  .

SELECTION-SCREEN BEGIN OF BLOCK b01 WITH FRAME TITLE text-b01.
PARAMETERS p_strn TYPE dd02l-tabname .
PARAMETERS p_file TYPE string .
PARAMETERS p_frow TYPE int4 DEFAULT 2.
SELECTION-SCREEN END OF BLOCK b01.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
  CALL METHOD cl_gui_frontend_services=>file_open_dialog
    EXPORTING
      window_title            = |Select Excel File, e.g. *.xlsx|
      default_extension       = |.xlsx|
    CHANGING
      file_table              = gt_tab
      rc                      = gv_returncode
    EXCEPTIONS
      file_open_dialog_failed = 1
      cntl_error              = 2
      error_no_gui            = 3
      not_supported_by_gui    = 4
      OTHERS                  = 5.
  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
               WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.
  READ TABLE gt_tab INDEX 1 INTO gs_tab .
  p_file = gs_tab-filename .


START-OF-SELECTION .

  gv_strname = p_strn .
  gv_filepath = p_file .

  zexcel_class=>get_instance( EXPORTING iv_filepath = gv_filepath iv_strname = gv_strname iv_fromindex = p_frow )->upload_file( ) .
  gt_ret = zexcel_class=>get_instance( )->treturn .
  IF gt_ret IS NOT INITIAL .
    cl_demo_output=>display_data( value = gt_ret ).
    RETURN .
  ENDIF.

  CREATE DATA dref_out TYPE TABLE OF (gv_strname) .
  dref_out = zexcel_class=>get_instance( )->tabledata .
  ASSIGN dref_out->* TO <fs_out> .
  cl_demo_output=>display_data( value = <fs_out>  ).

ZEXCEL_CLASS

class ZEXCEL_CLASS definition
  public
  final
  create public .

public section.
*"* public components of class ZEXCEL_CLASS
*"* do not include other source files here!!!

  class-data TRETURN type BAPIRET2_T .
  data TABLEDATA type ref to DATA .

  class-methods GET_INSTANCE
    importing
      !IV_FILEPATH type STRING optional
      !IV_STRNAME type STRING optional
      !IV_FROMINDEX type INT4 optional
    returning
      value(RO_OBJ) type ref to ZEXCEL_CLASS .
  methods UPLOAD_FILE .
  PROTECTED SECTION.
*"* protected components of class ZEXCEL_CLASS
*"* do not include other source files here!!!
private section.
*"* private components of class ZEXCEL_CLASS
*"* do not include other source files here!!!

  types:
    tt_string   TYPE STANDARD TABLE OF string WITH DEFAULT KEY .
  types:
    tt_dd03p    TYPE STANDARD TABLE OF dd03p WITH DEFAULT KEY .

  class-data GO_SINGLETON type ref to ZEXCEL_CLASS .
  class-data FILEPATH type STRING .
  class-data STRNAME type STRING .
  class-data TABLESTRUCTURE type ref to CL_ABAP_STRUCTDESCR .
  class-data TABLETYPE type ref to CL_ABAP_TABLEDESCR .
  class-data FROMINDEX type INT4 value 2. "#EC NOTEXT .

  methods GET_DATA_FROM_EXCEL .
ENDCLASS.



CLASS ZEXCEL_CLASS IMPLEMENTATION.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZEXCEL_CLASS->GET_DATA_FROM_EXCEL
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD get_data_from_excel.
  TYPES: BEGIN OF lty_mapping,
           sap_field TYPE string,
           exc_field TYPE string,
         END OF lty_mapping.
  TYPES ltt_maping TYPE STANDARD TABLE OF lty_mapping. " WITH EMPTY KEY.
  DATA : lt_mapping TYPE ltt_maping ,
         ls_mapping LIKE LINE OF lt_mapping .

  DATA ls_treturn        LIKE LINE OF treturn .

  DATA lv_lines1      TYPE i .
  DATA lv_lines2      TYPE i .
  DATA lo_exceltablestructure TYPE REF TO cl_abap_typedescr.
  DATA xstring_excel TYPE xstring .

  DATA lt_compdescr_tab TYPE abap_compdescr_tab .
  DATA ls_compdescr_tab LIKE LINE OF lt_compdescr_tab .

  FIELD-SYMBOLS <fs_exceldata_t>   TYPE STANDARD TABLE.
  FIELD-SYMBOLS <fs_excelfields_t> TYPE STANDARD TABLE.
  FIELD-SYMBOLS <fs_tabledata_t>   TYPE STANDARD TABLE.
  FIELD-SYMBOLS <fs_tabledata_s>   TYPE any.
  FIELD-SYMBOLS <fs_excelfields_s> TYPE any.
  FIELD-SYMBOLS <fs_exceldata_s>   TYPE any.
  FIELD-SYMBOLS <fs_val_sap>       TYPE any.
  FIELD-SYMBOLS <fs_val_exc>       TYPE any.


  TRY .
      xstring_excel = cl_openxml_helper=>load_local_file( filepath ).
    CATCH cx_openxml_not_found.  " Part not found
      RETURN .
  ENDTRY.


  DATA lref_excel  TYPE REF TO cl_fdt_xl_spreadsheet .
  TRY .
      CREATE OBJECT lref_excel
        EXPORTING
          document_name = filepath
          xdocument     = xstring_excel.
    CATCH cx_fdt_excel_core .
      RETURN .
  ENDTRY.


  DATA lt_worksheets TYPE STANDARD TABLE OF string .
  DATA ls_worksheets LIKE LINE OF lt_worksheets .
  lref_excel->if_fdt_doc_spreadsheet~get_worksheet_names( IMPORTING worksheet_names = lt_worksheets ).

  READ TABLE lt_worksheets INDEX 1 INTO ls_worksheets.

  DATA lref_data TYPE REF TO data .
  lref_data =
  lref_excel->if_fdt_doc_spreadsheet~get_itab_from_worksheet( ls_worksheets ).



  ASSIGN lref_data->* TO <fs_exceldata_t> .
  IF <fs_exceldata_t> IS NOT ASSIGNED.
    RETURN .
  ENDIF.

  "excel alan yapısı
  lo_exceltablestructure = cl_abap_typedescr=>describe_by_data_ref( lref_data ).
  ASSIGN ('LO_EXCELTABLESTRUCTURE->KEY') TO <fs_excelfields_t> .
  IF <fs_excelfields_t> IS INITIAL .
    RETURN .
  ENDIF.

  TYPE-POOLS : abap .
  "excel karşılayacak yapının alan sayısı
  lt_compdescr_tab = tablestructure->components .
  DELETE lt_compdescr_tab WHERE name EQ 'MANDT' .
  DESCRIBE TABLE tablestructure->components LINES lv_lines1 .
  "excel sütun sayısı
  DESCRIBE TABLE <fs_excelfields_t> LINES lv_lines2 .

  "alan sayıları eşit mi?
  IF lv_lines1 NE lv_lines2 .
    ls_treturn-type = 'E' .
    ls_treturn-id = '00'. ls_treturn-number = '001'.
    ls_treturn-message_v1 = | { strname } yapısındaki alanlar ile excel sütunları arasında tutarsızlık! | .
    APPEND ls_treturn TO treturn .
    RETURN .
  ENDIF.



  "alan mappingi hazırlanıyor..
  LOOP AT lt_compdescr_tab INTO ls_compdescr_tab  .
    CLEAR ls_mapping .
    ls_mapping-sap_field = ls_compdescr_tab-name .
    READ TABLE <fs_excelfields_t> INDEX sy-tabix ASSIGNING <fs_excelfields_s> .
    IF <fs_excelfields_s> IS ASSIGNED .
      ls_mapping-exc_field = <fs_excelfields_s> .
    ENDIF.
    APPEND ls_mapping TO lt_mapping .
  ENDLOOP.

*
  ASSIGN tabledata->* TO <fs_tabledata_t> .
  LOOP AT <fs_exceldata_t> FROM fromindex ASSIGNING <fs_exceldata_s> .

    APPEND INITIAL LINE TO <fs_tabledata_t> ASSIGNING <fs_tabledata_s> .
    LOOP AT lt_mapping INTO ls_mapping .

      UNASSIGN : <fs_val_sap>, <fs_val_exc> .
      ASSIGN COMPONENT ls_mapping-sap_field OF STRUCTURE <fs_tabledata_s> TO <fs_val_sap> .
      ASSIGN COMPONENT ls_mapping-exc_field OF STRUCTURE <fs_exceldata_s> TO <fs_val_exc> .

      "dönüşümler..
      READ TABLE lt_compdescr_tab INTO ls_compdescr_tab WITH KEY name = ls_mapping-sap_field .
      CASE ls_compdescr_tab-type_kind .
        WHEN 'D' . "Date
          REPLACE ALL OCCURRENCES OF '-' IN <fs_val_exc> WITH '' .
          REPLACE ALL OCCURRENCES OF '/' IN <fs_val_exc> WITH '' .
          REPLACE ALL OCCURRENCES OF '.' IN <fs_val_exc> WITH '' .
          <fs_val_sap> = <fs_val_exc>  .
        WHEN 'P' . "CURR
          TRANSLATE <fs_val_exc> USING ',.. ' .
          CONDENSE <fs_val_exc> NO-GAPS  .
          <fs_val_sap> = <fs_val_exc> .
        WHEN OTHERS.
            <fs_val_sap> = <fs_val_exc> .
      ENDCASE.


    ENDLOOP.

  ENDLOOP .

ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Static Public Method ZEXCEL_CLASS=>GET_INSTANCE
* +-------------------------------------------------------------------------------------------------+
* | [--->] IV_FILEPATH                    TYPE        STRING(optional)
* | [--->] IV_STRNAME                     TYPE        STRING(optional)
* | [--->] IV_FROMINDEX                   TYPE        INT4(optional)
* | [<-()] RO_OBJ                         TYPE REF TO ZEXCEL_CLASS
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD get_instance.

    IF go_singleton IS INITIAL.
      CREATE OBJECT go_singleton .
    ENDIF.
    IF iv_filepath IS NOT INITIAL.
      filepath = iv_filepath .
    ENDIF.
    IF iv_strname IS NOT INITIAL.
      strname = iv_strname .
    ENDIF.
    IF iv_fromindex IS NOT INITIAL.
      fromindex = iv_fromindex .
    ENDIF.

    ro_obj = go_singleton.

  ENDMETHOD.                    "GET_INSTANCE


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZEXCEL_CLASS->UPLOAD_FILE
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD upload_file.
  DATA ls_treturn LIKE LINE OF treturn .

  "yapı mevcut mu??
  SELECT SINGLE COUNT( * ) FROM tadir WHERE obj_name EQ strname .
  IF sy-subrc NE 0 .
    ls_treturn-type = 'E' .
    ls_treturn-id = '00'. ls_treturn-number = '001'.
    ls_treturn-message_v1 = | { strname }, yapısı mevcut değil! | .
    APPEND ls_treturn TO treturn .
    RETURN .
  ENDIF.

  "yapıyı al
  tablestructure ?= cl_abap_typedescr=>describe_by_name( strname ) .
  IF tablestructure IS NOT BOUND.
    ls_treturn-type = 'E' .
    ls_treturn-id = '00'. ls_treturn-number = '001'.
    ls_treturn-message_v1 = | { strname }, yapısını ayrıştırmada hata! | .
    APPEND ls_treturn TO treturn .
    RETURN .
  ENDIF.

  "yapının tipini belirle
  tabletype = cl_abap_tabledescr=>create( p_line_type =  tablestructure ).
  IF tabletype IS NOT BOUND.
    ls_treturn-type = 'E' .
    ls_treturn-id = '00'. ls_treturn-number = '001'.
    ls_treturn-message_v1 = | { strname }, yapısını ayrıştırmada hata! | .
    APPEND ls_treturn TO treturn .
    RETURN .
  ENDIF.

  CREATE DATA tabledata TYPE HANDLE tabletype.

  get_data_from_excel( ) .

ENDMETHOD.
ENDCLASS.
1+