웹딘프로아밥 XLSX 엑셀파일 업로드 펑션

2021-10-14 수정

abap2xlsx helper 를 만들었습니다. 더 쉽고 편리합니다.

https://boy0.tistory.com/173

abap2xlsx helper

**********************************************************************

안녕하세요.

 

ABAP2XLSX 를 활용한 엑셀 업로드 입니다. (설치방법: http://boy0.tistory.com/95)

WDA용 엑셀 업로드 예제 프로그램은 http://boy0.tistory.com/106 에서 확인하시고,

이를 더 발전시켜서 모듈화 시켜서 사용하기 쉽게 펑션을 제작해 보았습니다.

엑셀 파일을 넣으면 인터널 테이블로 변환시켜 줍니다. 웹딘프로 아닌 일반 ABAP에도 사용가능합니다.

 

FUNCTION ZWD_EXCEL_UPLOAD.
*"----------------------------------------------------------------------
*"*"Local Interface:
*"  IMPORTING
*"     REFERENCE(IV_EXCEL) TYPE  XSTRING
*"     REFERENCE(IV_BEGIN_ROW) TYPE  I
*"     REFERENCE(IV_SHEET_NO) TYPE  INT1 DEFAULT 1
*"  EXPORTING
*"     REFERENCE(EV_ERROR_TEXT) TYPE  STRING
*"  TABLES
*"      ET_TABLE TYPE  TABLE
*"----------------------------------------------------------------------
* 오픈소스 abap2xlsx 설치가 필요합니다. http://www.abap2xlsx.org
  DATA: lv_xstring        TYPE xstring,
        lo_excel          TYPE REF TO zcl_excel,
        lo_reader         TYPE REF TO zif_excel_reader,
        lo_worksheet      TYPE REF TO zcl_excel_worksheet,
        lv_highest_column TYPE zexcel_cell_column,
        lv_highest_row    TYPE int4,
        lv_column         TYPE zexcel_cell_column,
        lv_col_str        TYPE zexcel_cell_column_alpha,
        lv_row            TYPE int4,
        lv_value          TYPE zexcel_cell_value,
        lv_date           TYPE datum,
        lv_time           TYPE uzeit,
        lv_char_col       TYPE string,
        lv_char_row       TYPE string,
        lv_style_guid     TYPE zexcel_cell_style,
        ls_stylemapping   TYPE zexcel_s_stylemapping,
        lo_root           TYPE REF TO cx_root,
        lo_zcx_excel      TYPE REF TO zcx_excel.
  FIELD-SYMBOLS: <lv_data> TYPE data.

  CLEAR:          ev_error_text, et_table[].

  lv_xstring = iv_excel.

  TRY.
      CREATE OBJECT lo_reader TYPE zcl_excel_reader_2007.
      lo_excel = lo_reader->load( lv_xstring  ). "Load data into reader
      lo_excel->set_active_sheet_index( iv_sheet_no ).
      lo_worksheet = lo_excel->get_active_worksheet( ).

      lv_highest_column = lo_worksheet->get_highest_column( ).
      lv_highest_row    = lo_worksheet->get_highest_row( ).
      lv_row = iv_begin_row.
      lv_column = 1.

      WHILE lv_row <= lv_highest_row.
        WHILE lv_column <= lv_highest_column.
          lv_col_str = zcl_excel_common=>convert_column2alpha( lv_column ).
          lo_worksheet->get_cell(
            EXPORTING
              ip_column = lv_col_str
              ip_row    = lv_row
            IMPORTING
              ep_value  = lv_value
              ep_guid   = lv_style_guid ).
*&---------------------------------------------------------------------*
*&■ Add : Excel Date style Conv
*&---------------------------------------------------------------------*
          IF lv_style_guid IS NOT INITIAL AND lv_value IS NOT INITIAL.
            " Read style attributes
            ls_stylemapping = lo_excel->get_style_to_guid( lv_style_guid ).
            CASE ls_stylemapping-complete_style-number_format-format_code.
              WHEN zcl_excel_style_number_format=>c_format_date_ddmmyyyy
                OR zcl_excel_style_number_format=>c_format_date_ddmmyyyydot
                OR zcl_excel_style_number_format=>c_format_date_dmminus
                OR zcl_excel_style_number_format=>c_format_date_dmyminus
                OR zcl_excel_style_number_format=>c_format_date_dmyslash
                OR zcl_excel_style_number_format=>c_format_date_myminus
                OR zcl_excel_style_number_format=>c_format_date_std
                OR zcl_excel_style_number_format=>c_format_date_xlsx14
                OR zcl_excel_style_number_format=>c_format_date_xlsx15
                OR zcl_excel_style_number_format=>c_format_date_xlsx16
                OR zcl_excel_style_number_format=>c_format_date_xlsx17
                OR zcl_excel_style_number_format=>c_format_date_xlsx22
                OR zcl_excel_style_number_format=>c_format_date_xlsx45
                OR zcl_excel_style_number_format=>c_format_date_xlsx46
                OR zcl_excel_style_number_format=>c_format_date_xlsx47
                OR zcl_excel_style_number_format=>c_format_date_yymmdd
                OR zcl_excel_style_number_format=>c_format_date_yymmddminus
                OR zcl_excel_style_number_format=>c_format_date_yymmddslash
                OR zcl_excel_style_number_format=>c_format_date_yyyymmdd
                OR zcl_excel_style_number_format=>c_format_date_yyyymmddminus
                OR zcl_excel_style_number_format=>c_format_date_yyyymmddslash.
                " Convert excel date to ABAP date
                lv_date = zcl_excel_common=>excel_string_to_date( lv_value ).
                lv_value = lv_date.
              WHEN zcl_excel_style_number_format=>c_format_date_time1
                OR zcl_excel_style_number_format=>c_format_date_time2
                OR zcl_excel_style_number_format=>c_format_date_time3
                OR zcl_excel_style_number_format=>c_format_date_time4
                OR zcl_excel_style_number_format=>c_format_date_time5
                OR zcl_excel_style_number_format=>c_format_date_time6
                OR zcl_excel_style_number_format=>c_format_date_time7
                OR zcl_excel_style_number_format=>c_format_date_time8
                OR 'h:mm:ss;@'.
                " Convert excel time to ABAP time
                lv_time = zcl_excel_common=>excel_string_to_time( lv_value ).
                lv_value = lv_time.
            ENDCASE.
          ENDIF.
*&---------------------------------------------------------------------*
          CONDENSE lv_value.
          IF lv_value IS NOT INITIAL.
            ASSIGN COMPONENT lv_column OF STRUCTURE et_table TO <lv_data>.
            IF sy-subrc <> 0.
              EXIT.
            ENDIF.
            <lv_data> = lv_value.
          ENDIF.
          lv_column = lv_column + 1.
        ENDWHILE.
        IF NOT et_table IS INITIAL .
          APPEND et_table.
        ENDIF.
        CLEAR et_table .
        lv_column = 1.
        lv_row    = lv_row + 1.
      ENDWHILE.
    CATCH cx_root INTO lo_root.    " Exceptions for ABAP2XLSX
      ev_error_text = lo_root->get_text( ).
      lv_char_col = lv_column. CONDENSE lv_char_col NO-GAPS.
      lv_char_row = lv_row.    CONDENSE lv_char_row NO-GAPS.
      ev_error_text = ev_error_text && '(Col:' && lv_char_col && ',Row:' && lv_char_row && ')'.
*      IF NOT MSG IS INITIAL.
**Raise exception message
*      ENDIF.
  ENDTRY.





ENDFUNCTION.

 

 

참고로 다운로드 펑션은 여기 있습니다.

http://boy0.tistory.com/108