ABAP
OLE
clode
2023. 2. 21. 23:39
728x90
반응형
예를 들어 ME21N에서 구매오더를 넣고 그 구매오더를 다른 업체에 엑셀로 넘겨주기 위해 엑셀로 다운받는 기능이 필요하다고 해보자. 그럴때 이 OLE를 사용한다. SMW0에서 양식을 추가하고, 어떤 데이터가 몇번 시트의 어떤 셀에 들어갈건지 일일히 지정해준다.
OLE 메소드를 만들어주고, 호출한다.
lcl_main=>form_01_print( ).데이터 선언부
CLASS lcl_main DEFINITION.
PUBLIC SECTION.
TYPES: BEGIN OF gty_tot,
menge TYPE ekpo-menge,
meins TYPE ekpo-meins,
END OF gty_tot.
TYPES: BEGIN OF gty_mepoitem.
INCLUDE TYPE mepoitem.
TYPES: zzcasno TYPE mara-zzcasno.
TYPES: size TYPE string.
TYPES: END OF gty_mepoitem.
TYPES: BEGIN OF gty_main,
mepoheader TYPE mepoheader,
mepoitem TYPE TABLE OF gty_mepoitem WITH EMPTY KEY,
lfa1 TYPE lfa1,
min_eindt TYPE eindt,
t001w_stras TYPE t001w-stras,
zterm_tt TYPE t052u-text1,
usrinfo TYPE bapiaddr3,
tot TYPE gty_tot,
item_cnt TYPE i,
body_max_idx TYPE i,
body_top_idx TYPE i,
END OF gty_main.
CLASS-DATA: mv_sheet_nm TYPE c LENGTH 30.
CLASS-DATA: ms_main TYPE gty_main.
CLASS-DATA: mv_visible TYPE i VALUE 0.
CLASS-DATA: mo_excel TYPE REF TO lcl_excel_interface.
CONSTANTS: gc_sheet TYPE char30 VALUE '시트이름'.
CLASS-METHODS: init.
CLASS-METHODS: get_data.
CLASS-METHODS: form_01_print.
CLASS-METHODS: form_01_fill_header .
CLASS-METHODS: form_01_fill_body .
CLASS-METHODS: form_01_fill_tail .
CLASS-METHODS: form_01_fill_tail_empty. METHOD form_01_print.
TRY .
_set_progress TEXT-p04 0 100 abap_true.
CREATE OBJECT mo_excel.
mo_excel->start_document( ).
_set_progress TEXT-p02 10 100 abap_true.
mo_excel->load_template( 'SMW0에 등록한 파일이름' ).
*--------------------------------------------------------------------*
* 시트 추가
*--------------------------------------------------------------------*
_set_progress TEXT-p03 20 100 abap_true.
mv_sheet_nm = gc_sheet.
ms_main-body_max_idx = 24.
ms_main-body_top_idx = 19.
mo_excel->set_active_sheet( CONV #( mv_sheet_nm ) ).
*.. Header Data Fill
form_01_fill_header( ).
*.. Fill Tail
form_01_fill_tail( ).
form_01_fill_tail_empty( ).
*
*.. Fill Item
_set_progress TEXT-p03 30 100 abap_true.
form_01_fill_body( ).
mo_excel->set_range( 'A1:A1' ).
*--------------------------------------------------------------------*
* Display
*--------------------------------------------------------------------*
_set_progress TEXT-p05 80 100 abap_true.
mo_excel->display_document( |PO [ { ms_main-mepoheader-ebeln } ] Display| ).
_set_progress TEXT-p05 100 100 abap_true.
CATCH cx_root INTO DATA(lr_root).
DATA(ls_bapiret2) = zcl_mmz_common_util=>conv_cx_root_to_bapiret( lr_root ).
MESSAGE ID ls_bapiret2-id TYPE zgmmz_i NUMBER ls_bapiret2-number
WITH ls_bapiret2-message_v1
ls_bapiret2-message_v2
ls_bapiret2-message_v3
ls_bapiret2-message_v4
DISPLAY LIKE zgmmz_e.
IF mo_excel IS BOUND.
mo_excel->close_document( ).
ENDIF.
ENDTRY.
ENDMETHOD. DEFINE _set_progress.
zcl_mmz_common_util=>set_progress( iv_text = &1
iv_processed = &2 iv_total = &3
iv_immediately = &4 ).
END-OF-DEFINITION. METHOD form_01_fill_header.
*--------------------------------------------------------------------*
* ■ Header Fill
*--------------------------------------------------------------------*
mo_excel->write_value( iv_cells = 'B10'
iv_value = ms_main-lfa1-name1 ).
mo_excel->write_value( iv_cells = 'I12'
iv_value = ms_main-mepoheader-ebeln ).
DATA: lv_bedat TYPE char20,
lt_month_tt TYPE TABLE OF t247.
lv_bedat = ms_main-mepoheader-bedat.
"월(영문)/일/년도
CALL FUNCTION 'MONTH_NAMES_GET'
EXPORTING
language = sy-langu
TABLES
month_names = lt_month_tt[]
EXCEPTIONS
month_names_not_found = 1
OTHERS = 2.
READ TABLE lt_month_tt INTO DATA(ls_month_tt) WITH KEY mnr = ms_main-mepoheader-bedat+4(2).
lv_bedat = zcl_mmz_common_util=>set_string(
|{ ls_month_tt-ktx }.{ ms_main-mepoheader-bedat+6(2) }.{ ms_main-mepoheader-bedat+0(4) }| ).
mo_excel->write_value( iv_cells = 'I13'
iv_value = lv_bedat ).
ENDMETHOD. METHOD form_01_fill_tail.
DATA: lv_value TYPE c LENGTH 255.
DATA: lv_format TYPE c LENGTH 255.
DATA: lv_eindt TYPE c LENGTH 10.
DATA: lv_text type c length 255.
"Total 단위
CLEAR: lv_value.
zgmmz_conv_cunit_output ms_main-tot-meins lv_value.
mo_excel->write_value( iv_cells = 'E20'
iv_value = lv_value ).
"Total 수량
CLEAR: lv_value.
zgmmz_conv_qty_output ms_main-tot-meins ms_main-tot-menge lv_value.
zgmmz_conv_qty_output ms_main-tot-meins 0 lv_format.
mo_excel->write_value( iv_cells = 'D20'
iv_value = lv_value
iv_format = mo_excel->conv_num_format( lv_format ) ).
"Total 통화
mo_excel->write_value( iv_cells = 'H20'
iv_value = ms_main-mepoheader-waers ).
"Total 금액
CLEAR: lv_value.
zgmmz_conv_amt_output ms_main-mepoheader-waers ms_main-mepoheader-zz_netwr lv_value.
zgmmz_conv_amt_output ms_main-mepoheader-waers 0 lv_format.
mo_excel->write_value( iv_cells = 'J20'
iv_value = lv_value
iv_format = mo_excel->conv_num_format( lv_format ) ).
" 운송조건
mo_excel->write_value( iv_cells = 'C23'
iv_value = TEXT-v11 ).
" 도착지
mo_excel->write_value( iv_cells = 'C24'
iv_value = ms_main-t001w_stras ).
" Packing ( User Input )
" 결제금액
CLEAR: lv_value.
zgmmz_conv_amt_output ms_main-mepoheader-waers ms_main-mepoheader-zz_netwr lv_value.
zgmmz_conv_amt_output ms_main-mepoheader-waers 0 lv_format.
mo_excel->write_value( iv_cells = 'C26'
iv_value = lv_value
iv_format = mo_excel->conv_num_format( lv_format ) ).
" 결제금액 ko
CLEAR: lv_text.
zgmmz_amount_to_text lv_value lv_text.
mo_excel->write_value( iv_cells = 'C27'
iv_value = lv_text ).
" 결제조건
mo_excel->write_value( iv_cells = 'C28'
iv_value = ms_main-zterm_tt ).
" 납기일
WRITE: ms_main-min_eindt TO lv_eindt USING EDIT MASK '____.__.__'.
mo_excel->write_value( iv_cells = 'C29'
iv_value = lv_eindt ).
ENDMETHOD.
METHOD form_01_fill_tail_empty.
DATA: lv_row TYPE i,
lv_insert_row TYPE i.
"131 Line copy
"1 Page ( max body line : 24 )
lv_row = ms_main-item_cnt.
IF lv_row <= 24.
lv_insert_row = 20 - lv_row.
mo_excel->copy_rows( iv_scr_row = 35 iv_ins_rows = lv_insert_row ).
RETURN.
ENDIF.
"2 Page ( max body line: 44 )
lv_row = ms_main-item_cnt.
IF lv_row <= 44.
lv_insert_row = 40 - lv_row.
mo_excel->copy_rows( iv_scr_row = 35 iv_ins_rows = lv_insert_row ).
RETURN.
ENDIF.
ENDMETHOD. METHOD copy_rows.
*--------------------------------------------
* Rows("9:9").Select
* Selection.Copy
* Rows("11:20").Select
* Selection.Insert Shift:=xlDown
* Range("A21").Select
*--------------------------------------------
CHECK iv_ins_rows >= 1.
DATA: lo_target_rows TYPE ole2_object,
lv_tar_fr TYPE i,
lv_tar_to TYPE i,
lv_scr_rows TYPE string,
lv_tar_rows TYPE string.
lv_tar_fr = iv_scr_row + iv_copy_rows.
lv_tar_to = lv_tar_fr + iv_ins_rows * iv_copy_rows - 1.
lv_scr_rows = zcl_mmz_common_util=>set_string( |{ iv_scr_row }:{ iv_scr_row + iv_copy_rows - 1 }| ).
lv_tar_rows = zcl_mmz_common_util=>set_string( |{ lv_tar_fr }:{ lv_tar_to }| ).
CALL METHOD OF ms_excel-sheet 'Rows' = ms_excel-rows EXPORTING #1 = lv_scr_rows .
CALL METHOD OF ms_excel-rows 'Select' QUEUE-ONLY.
CALL METHOD OF ms_excel-rows 'Copy' NO FLUSH QUEUE-ONLY.
check_error( ).
CALL METHOD OF ms_excel-sheet 'Rows' = lo_target_rows EXPORTING #1 = lv_tar_rows.
CALL METHOD OF lo_target_rows 'Select' QUEUE-ONLY.
CALL METHOD OF lo_target_rows 'Insert' NO FLUSH QUEUE-ONLY
EXPORTING
#1 = -4121. "Xldown
SET PROPERTY OF ms_excel-appl 'CutCopyMode' = 0.
check_error( ).
FREE: ms_excel-rows, lo_target_rows.
ENDMETHOD. METHOD form_01_fill_body_equip.
*--------------------------------------------------------------------*
* ■ Fill Body Equipment "Insert 21.03.26
*--------------------------------------------------------------------*
DATA: lt_contents TYPE lcl_excel_interface=>gty_t_contents,
lt_qty_format TYPE lcl_excel_interface=>gty_t_row_format,
lv_insert_line TYPE i,
lv_body_end_row TYPE i,
lv_row TYPE i,
lv_value TYPE c LENGTH 255,
lv_format TYPE c LENGTH 255.
*.. Line 추가
lv_insert_line = ( ms_main-item_cnt - 1 ).
mo_excel->copy_rows( EXPORTING iv_scr_row = ms_main-body_top_idx
iv_copy_rows = 1
iv_ins_rows = lv_insert_line ).
lv_body_end_row = ms_main-body_top_idx + lv_insert_line .
*-- Ranve Value 셋팅
lv_row = ms_main-body_top_idx.
LOOP AT ms_main-mepoitem INTO DATA(ls_mepoitem). "Insert 21.03.26
"-- 자재내역 A19
lt_contents = VALUE #( BASE lt_contents (
row = lv_row
col = mo_excel->conv_col_in( 'A' )
val = ls_mepoitem-txz01 ) ).
lt_contents = VALUE #( BASE lt_contents (
row = lv_row
col = mo_excel->conv_col_in( 'B' )
val = space ) ).
lt_contents = VALUE #( BASE lt_contents (
row = lv_row
col = mo_excel->conv_col_in( 'C' )
val = space ) ).
"-- 수량 C19
zgmmz_conv_qty_output ls_mepoitem-meins ls_mepoitem-menge lv_value.
lt_contents = VALUE #( BASE lt_contents (
row = lv_row
col = mo_excel->conv_col_in( 'D' )
val = lv_value ) ).
zgmmz_conv_qty_output ls_mepoitem-meins 0 lv_format.
lt_qty_format = VALUE #( BASE lt_qty_format (
val = ls_mepoitem-meins
row = lv_row
column = 'D'
format = mo_excel->conv_num_format( lv_format ) ) ).
"-- 단위 D19
zgmmz_conv_cunit_output ls_mepoitem-meins lv_value.
lt_contents = VALUE #( BASE lt_contents (
row = lv_row
col = mo_excel->conv_col_in( 'E' )
val = lv_value ) ).
"-- 단가 E19
zgmmz_conv_amt_output ms_main-mepoheader-waers ls_mepoitem-netpr lv_value.
lt_contents = VALUE #( BASE lt_contents (
row = lv_row
col = mo_excel->conv_col_in( 'F' )
val = lv_value ) ).
"-- 통화 / 단위
zgmmz_conv_cunit_output ls_mepoitem-meins lv_value.
lt_contents = VALUE #( BASE lt_contents (
row = lv_row
col = mo_excel->conv_col_in( 'G' )
val = |{ ms_main-mepoheader-waers } / { lv_value }| ) ).
"-- 통화 G19
lt_contents = VALUE #( BASE lt_contents (
row = lv_row
col = mo_excel->conv_col_in( 'H' )
val = ms_main-mepoheader-waers ) ).
"-- 금액 H19
lt_contents = VALUE #( BASE lt_contents (
row = lv_row
col = mo_excel->conv_col_in( 'I' )
val = space ) ).
zgmmz_conv_amt_output ms_main-mepoheader-waers ls_mepoitem-netwr lv_value.
lt_contents = VALUE #( BASE lt_contents (
row = lv_row
col = mo_excel->conv_col_in( 'J' )
val = lv_value ) ).
ADD 1 TO lv_row.
ENDLOOP.
*- Set Body Data
mo_excel->set_range_data(
iv_range = |A19:J{ lv_body_end_row }|
it_contents = lt_contents ).
*- Qty Format
mo_excel->set_range_format_row_mass( lt_qty_format ).
*- 금액필드 Format
zgmmz_conv_amt_output ms_main-mepoheader-waers 0 lv_format.
"--단가
mo_excel->set_range_format(
iv_range = |F19:F{ lv_body_end_row }|
iv_format = mo_excel->conv_num_format( lv_format ) ).
"--금액
mo_excel->set_range_format(
iv_range = |J19:J{ lv_body_end_row }|
iv_format = mo_excel->conv_num_format( lv_format ) ).
ENDMETHOD. METHOD set_range.
SPLIT iv_range AT ':' INTO DATA(lv_low) DATA(lv_high).
CALL METHOD OF ms_excel-appl 'Range' = ms_excel-range
EXPORTING #1 = lv_low
#2 = lv_high.
CALL METHOD OF ms_excel-range 'Select'.
check_error( ).
ENDMETHOD. 728x90
반응형