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

2021-10-14 수정

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

https://boy0.tistory.com/173

abap2xlsx helper

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

안녕하세요.

 

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

인터널 테이블을 넣으면 엑셀 파일 다운로드를 시작합니다.

옵션 파라미터 4개가 있는데,

IV_FOR_UPLOAD    : 나중에 업로드를 위한 다운로드 파일로 DATE와 TIME을 엑셀포맷이 아닌 일반문자로 바꿔줍니다.

IT_FIELD_CATALOG    : 필드 레이블을 변경할 수 있습니다. zcl_excel_common=>get_fieldcatalog( ) 호출하여 생성.

IV_FILENAME    : 파일명 (기본 export.xlsx)

IV_SHEET_TITLE    : 시트명 (기본 Export)

 

FUNCTION ZWD_EXCEL_DOWNLOAD.
*"--------------------------------------------------------------------
*"*"Local Interface:
*"  IMPORTING
*"     REFERENCE(IV_FOR_UPLOAD) TYPE  FLAG OPTIONAL
*"     REFERENCE(IT_FIELD_CATALOG) TYPE  ZEXCEL_T_FIELDCATALOG
*"         OPTIONAL
*"     REFERENCE(IV_FILENAME) TYPE  CSEQUENCE OPTIONAL
*"     REFERENCE(IV_SHEET_TITLE) TYPE  CSEQUENCE OPTIONAL
*"     REFERENCE(IV_AUTO_COLUMN_WIDTH) TYPE  FLAG DEFAULT ABAP_TRUE
*"  EXPORTING
*"     REFERENCE(EV_ERROR_TEXT) TYPE  STRING
*"  TABLES
*"      IT_TABLE TYPE  TABLE
*"--------------------------------------------------------------------
* 오픈소스 abap2xlsx 설치가 필요합니다. http://www.abap2xlsx.org
  DATA: lo_excel             TYPE REF TO zcl_excel,
        lo_writer            TYPE REF TO zif_excel_writer,
        lo_worksheet         TYPE REF TO zcl_excel_worksheet,
        ls_table_settings    TYPE zexcel_s_table_settings,
        lt_field_catalog     TYPE zexcel_t_fieldcatalog,
        ls_field_catalog     TYPE zexcel_s_fieldcatalog,
        lv_xstring           TYPE xstring,
        lo_zcx_excel         TYPE REF TO zcx_excel,
        lv_sheet_title       TYPE zexcel_sheet_title,
        lv_filename_string   TYPE string,
        lv_filename_path     TYPE string,
        lv_filename_fullpath TYPE string,
        lv_bin_filesize      TYPE i,
        lt_temptable         TYPE w3mimetabtype,
        lv_index             TYPE i.
  CLEAR ev_error_text.

  TRY.

      " Creates active sheet
      CREATE OBJECT lo_excel.

      " Get active sheet
      IF iv_sheet_title IS NOT INITIAL.
        lv_sheet_title = iv_sheet_title.
      ELSE.
        lv_sheet_title = 'Export'.
      ENDIF.
      lo_worksheet = lo_excel->get_active_worksheet( ).
      lo_worksheet->set_title( ip_title = lv_sheet_title ).

      ls_table_settings-table_style       = zcl_excel_table=>builtinstyle_medium2.
      ls_table_settings-show_row_stripes  = abap_true.
      ls_table_settings-nofilters         = abap_true.

      IF it_field_catalog IS NOT INITIAL.
        lt_field_catalog = it_field_catalog.
        SORT lt_field_catalog BY position fieldname.
        LOOP AT lt_field_catalog INTO ls_field_catalog.
          lv_index = sy-tabix.
          IF ls_field_catalog-position <> lv_index.
            ls_field_catalog-position = lv_index.
            MODIFY lt_field_catalog FROM ls_field_catalog INDEX lv_index TRANSPORTING position.
          ENDIF.
        ENDLOOP.
      ELSE.
        lt_field_catalog = zcl_excel_common=>get_fieldcatalog( ip_table = it_table[] ).
      ENDIF.

      IF iv_for_upload EQ abap_true.
        ls_field_catalog-dynpfld = abap_true.
        MODIFY lt_field_catalog FROM ls_field_catalog TRANSPORTING dynpfld WHERE dynpfld EQ abap_false.
        ls_field_catalog-abap_type = 'C'.
        MODIFY lt_field_catalog FROM ls_field_catalog TRANSPORTING abap_type WHERE abap_type EQ 'D'.  "Date -> Char
        MODIFY lt_field_catalog FROM ls_field_catalog TRANSPORTING abap_type WHERE abap_type EQ 'T'.  "Time -> Char
      ENDIF.

      lo_worksheet->bind_table( ip_table          = it_table[]
                                it_field_catalog = lt_field_catalog
                                is_table_settings = ls_table_settings ).
*                                iv_no_header = iv_no_header
*                                iv_no_table_setting = iv_no_table_setting ).

      IF iv_auto_column_width EQ abap_true.
        LOOP AT lt_field_catalog INTO ls_field_catalog WHERE dynpfld = abap_true.
          lo_worksheet->set_column_width(
            EXPORTING
              ip_column         = zcl_excel_common=>convert_column2alpha( ls_field_catalog-position )
              ip_width_autosize = abap_true
          ).
        ENDLOOP.
      ENDIF.

      lo_worksheet->freeze_panes( ip_num_rows = 1 ). "freeze column headers when scrolling

*** Create output
      CREATE OBJECT lo_writer TYPE zcl_excel_writer_2007.
      lv_xstring = lo_writer->write_file( lo_excel ).


    CATCH zcx_excel INTO lo_zcx_excel.    " Exceptions for ABAP2XLSX
      ev_error_text = lo_zcx_excel->error.
      RETURN.
*      IF NOT MSG IS INITIAL.
**Raise exception message
*      ENDIF.
  ENDTRY.


  lv_filename_string = iv_filename.
  IF iv_filename IS INITIAL.
    lv_filename_string = |export_{ sy-datum }_{ sy-uzeit }.xlsx|.
  ENDIF.
  IF wdr_task=>application_name IS NOT INITIAL.
    CALL METHOD cl_wd_runtime_services=>attach_file_to_response
      EXPORTING
        i_filename  = lv_filename_string
        i_content   = lv_xstring
        i_mime_type = 'xlsx'
*       i_in_new_window = ABAP_FALSE
*       i_inplace   = ABAP_FALSE
      .
  ELSE.
    CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
      EXPORTING
        buffer        = lv_xstring
      IMPORTING
        output_length = lv_bin_filesize
      TABLES
        binary_tab    = lt_temptable.
    cl_gui_frontend_services=>file_save_dialog(
      EXPORTING
*        window_title              = window_title      " Window Title
*        default_extension         = default_extension " Default Extension
        default_file_name         = lv_filename_string " Default File Name
*        with_encoding             = with_encoding
        file_filter               = '*.xlsx'       " File Type Filter Table
*        initial_directory         = initial_directory " Initial Directory
*        prompt_on_overwrite       = 'X'
      CHANGING
        filename                  = lv_filename_string          " File Name to Save
        path                      = lv_filename_path              " Path to File
        fullpath                  = lv_filename_fullpath          " Path + File Name
*        user_action               = user_action       " User Action (C Class Const ACTION_OK, ACTION_OVERWRITE etc)
*        file_encoding             = file_encoding
      EXCEPTIONS
        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.
    IF lv_filename_fullpath IS NOT INITIAL.
      cl_gui_frontend_services=>gui_download(
         EXPORTING
           bin_filesize = lv_bin_filesize
           filename     = lv_filename_fullpath
           filetype     = 'BIN'
         CHANGING
           data_tab     = lt_temptable
         EXCEPTIONS
           file_write_error          = 1
           no_batch                  = 2
           gui_refuse_filetransfer   = 3
           invalid_type              = 4
           no_authority              = 5
           unknown_error             = 6
           header_not_allowed        = 7
           separator_not_allowed     = 8
           filesize_not_allowed      = 9
           header_too_long           = 10
           dp_error_create           = 11
           dp_error_send             = 12
           dp_error_write            = 13
           unknown_dp_error          = 14
           access_denied             = 15
           dp_out_of_memory          = 16
           disk_full                 = 17
           dp_timeout                = 18
           file_not_found            = 19
           dataprovider_exception    = 20
           control_flush_error       = 21
           not_supported_by_gui      = 22
           error_no_gui              = 23
           OTHERS                    = 24
      ).
      IF sy-subrc <> 0.
        MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
                   WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
      ENDIF.
    ENDIF.
  ENDIF.

ENDFUNCTION.

 

 

 

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

http://boy0.tistory.com/107