반응형
첨부파일을 SMW0에 등록
Add attached file in SMW0
sap excel macro example
1. ZTLAY
*&---------------------------------------------------------------------*
*& Report ZTLAY
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
REPORT ZTLAY.
*======================================================================*
* INCLUDE
*======================================================================*
INCLUDE ztlaytop.
INCLUDE ztlayf01.
*======================================================================*
* INITIALIZATION
*======================================================================*
INITIALIZATION.
*======================================================================*
* AT SELECTION-SCREEN OUTPUT
*======================================================================*
AT SELECTION-SCREEN OUTPUT.
*======================================================================*
* START-OF-SELECTION
*======================================================================*
START-OF-SELECTION.
PERFORM get_data.
*======================================================================*
* END-OF-SELECTION
*======================================================================*
END-OF-SELECTION.
IF gt_list[] IS NOT INITIAL.
PERFORM GET_EXCEL.
ENDIF.2. ZTLAYTOP
*&---------------------------------------------------------------------*
*& Include ZTLAYTOP
*&---------------------------------------------------------------------*
*======================================================================*
* TABLES
*======================================================================*
TABLES : dd03l, " 테이블필드 (Table field)
dd03t, " DD: 필드에 대한 텍스트 (언어종속) (Text of fields)
dd04t. " R/3 DD: Data element 텍스트
*======================================================================*
* INTERNAL TABLES
*======================================================================*
*// DISPLAY
DATA : BEGIN OF gs_list OCCURS 0,
position LIKE dd03l-position,
fieldname LIKE dd03l-fieldname,
rollname LIKE dd03l-rollname,
notnull LIKE dd03l-notnull,
keyflag LIKE dd03l-keyflag,
datatype LIKE dd03l-datatype,
intlen LIKE dd03l-intlen,
decimals LIKE dd03l-decimals,
ddtext LIKE dd03t-ddtext,
END OF gs_list.
DATA : gt_list LIKE gs_list OCCURS 0 WITH HEADER LINE.
*======================================================================*
* VARIANTS
*======================================================================*
DATA : ok_code TYPE sy-ucomm.
DATA : g_tabtx LIKE dd02t-ddtext.
*======================================================================*
* EXCEL 출력 관련 (Excel output)
*======================================================================*
INCLUDE officeintegrationinclude.
DATA: gv_retcode TYPE t_oi_ret_string,
gc_factory TYPE REF TO i_oi_document_factory,
gc_link_server TYPE REF TO i_oi_link_server,
gv_doc_size TYPE i,
gv_doc_format TYPE c LENGTH 80,
gv_doc_type TYPE c LENGTH 80 VALUE soi_doctype_word97_document,
gt_doc_table TYPE TABLE OF w3mime,
gc_document TYPE REF TO i_oi_document_proxy.
* Excel 헤더 (Header)
DATA: BEGIN OF gs_xls_header,
d01(60),
d02(60),
END OF gs_xls_header,
gt_xls_header LIKE TABLE OF gs_xls_header.
* Excel 아이템 (Item)
DATA: BEGIN OF gs_xls_item11,
d01(60) ,
d02(60) ,
d03(60) ,
d04(60) ,
d05(60) ,
d06(60) ,
d07(60) ,
d08(60) ,
d09(60) ,
END OF gs_xls_item11,
gt_xls_item11 LIKE TABLE OF gs_xls_item11.
*======================================================================*
* SELECTION-SCREEN
*======================================================================*
*// 조회조건 (Search options)
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
PARAMETERS : p_tabnm LIKE dd03l-tabname OBLIGATORY.
SELECTION-SCREEN END OF BLOCK b1.
3. ZTLAYF01
*&---------------------------------------------------------------------*
*& Include ZTLAYF01
*&---------------------------------------------------------------------*
FORM get_data .
clear : gt_list, gt_list[].
SELECT position
fieldname
rollname
notnull
keyflag
datatype
intlen
decimals
INTO CORRESPONDING FIELDS OF TABLE gt_list
FROM dd03l
WHERE tabname EQ p_tabnm.
SORT gt_list BY position.
CHECK gt_list[] IS NOT INITIAL.
CLEAR : g_tabtx.
SELECT SINGLE ddtext
INTO g_tabtx
FROM dd02t
WHERE tabname EQ p_tabnm
AND ddlanguage EQ sy-langu.
LOOP AT gt_list.
IF gt_list-rollname IS INITIAL.
SELECT SINGLE ddtext
INTO gt_list-ddtext
FROM dd03t
WHERE tabname EQ p_tabnm
AND ddlanguage EQ sy-langu
AND fieldname EQ gt_list-fieldname.
ELSE.
SELECT SINGLE ddtext
INTO gt_list-ddtext
FROM dd04t
WHERE rollname EQ gt_list-rollname
AND ddlanguage EQ sy-langu.
ENDIF.
MODIFY gt_list INDEX sy-tabix.
ENDLOOP.
ENDFORM. " GET_DATA
*&---------------------------------------------------------------------*
*& Form GET_EXCEL
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM get_excel.
PERFORM get_print_data."엑셀에 뿌려질 데이터를 모음. (Gather data)
PERFORM exec_print. "엑셀 오프잭트 생성 메서드실행. (Excel object creation)
ENDFORM. " GET_EXCEL
*&---------------------------------------------------------------------*
*& Form GET_PRINT_DATA
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM get_print_data .
* HEADER DATA
PERFORM make_header_data.
* ITEM DATA
PERFORM make_item_data.
ENDFORM. " GET_PRINT_DATA
*&---------------------------------------------------------------------*
*& Form MAKE_HEADER_DATA
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM make_header_data .
CLEAR: gs_xls_header, gt_xls_header.
* HEADER DATA
gs_xls_header-d01 = p_tabnm.
gs_xls_header-d02 = g_tabtx.
APPEND gs_xls_header TO gt_xls_header.
ENDFORM. " MAKE_HEADER_DATA
*&---------------------------------------------------------------------*
*& Form MAKE_ITEM_DATA
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM make_item_data .
CLEAR: gs_xls_item11, gt_xls_item11.
LOOP AT gt_list.
gs_xls_item11-d01 = gt_list-position.
gs_xls_item11-d02 = gt_list-fieldname.
gs_xls_item11-d03 = gt_list-rollname.
gs_xls_item11-d04 = gt_list-notnull.
gs_xls_item11-d05 = gt_list-keyflag.
gs_xls_item11-d06 = gt_list-datatype.
gs_xls_item11-d07 = gt_list-intlen.
gs_xls_item11-d08 = gt_list-decimals.
gs_xls_item11-d09 = gt_list-ddtext.
APPEND gs_xls_item11 TO gt_xls_item11.
CLEAR gs_xls_item11.
ENDLOOP.
ENDFORM. " MAKE_ITEM_DATA
*&---------------------------------------------------------------------*
*& Form EXEC_PRINT
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM exec_print .
PERFORM init_factory.
PERFORM open_doc.
PERFORM close_factory.
ENDFORM. " EXEC_PRINT
*&---------------------------------------------------------------------*
*& Form INIT_FACTORY
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM init_factory .
CHECK gc_factory IS INITIAL.
CALL METHOD c_oi_factory_creator=>get_document_factory
IMPORTING
factory = gc_factory
retcode = gv_retcode.
IF gv_retcode NE c_oi_errors=>ret_ok.
EXIT.
ENDIF.
CALL METHOD gc_factory->start_factory
EXPORTING
r3_application_name = 'WORK_ORDER'
IMPORTING
retcode = gv_retcode.
CALL METHOD c_oi_errors=>show_message
EXPORTING
type = 'E'.
CALL METHOD gc_factory->get_link_server
IMPORTING
link_server = gc_link_server
retcode = gv_retcode.
CALL METHOD c_oi_errors=>show_message
EXPORTING
type = 'E'.
CALL METHOD gc_link_server->start_link_server
IMPORTING
retcode = gv_retcode.
CALL METHOD c_oi_errors=>show_message
EXPORTING
type = 'E'.
ENDFORM. " INIT_FACTORY
*&---------------------------------------------------------------------*
*& Form OPEN_DOC
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM open_doc .
CALL METHOD gc_link_server->add_table_item2
EXPORTING
item_name = 'HEADER'
IMPORTING
retcode = gv_retcode
CHANGING
data_table = gt_xls_header.
CALL METHOD gc_link_server->add_table_item2
EXPORTING
item_name = 'ITEM11'
IMPORTING
retcode = gv_retcode
CHANGING
data_table = gt_xls_item11.
CALL FUNCTION 'SAP_OI_LOAD_MIME_DATA'
EXPORTING
object_id = 'YTAB'
IMPORTING
data_size = gv_doc_size
document_format = gv_doc_format
document_type = gv_doc_type
TABLES
data_table = gt_doc_table
EXCEPTIONS
object_not_found = 1
internal_error = 2
OTHERS = 3.
IF sy-subrc NE 0.
MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
IF gv_doc_size NE 0.
CALL METHOD gc_factory->get_document_proxy
EXPORTING
document_type = gv_doc_type
IMPORTING
document_proxy = gc_document
retcode = gv_retcode.
CALL METHOD c_oi_errors=>show_message
EXPORTING
type = 'E'.
CALL METHOD gc_document->open_document_from_table
EXPORTING
document_table = gt_doc_table
document_size = gv_doc_size
protect_document = space
startup_macro = 'MACRO1'
IMPORTING
retcode = gv_retcode.
CALL METHOD c_oi_errors=>show_message
EXPORTING
type = 'E'.
ELSE.
ENDIF.
ENDFORM. " OPEN_DOC
*&---------------------------------------------------------------------*
*& Form CLOSE_FACTORY
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM close_factory .
IF NOT gc_link_server IS INITIAL.
CALL METHOD gc_link_server->stop_link_server
IMPORTING
retcode = gv_retcode.
FREE gc_link_server.
ENDIF.
IF NOT gc_factory IS INITIAL.
CALL METHOD gc_factory->stop_factory
IMPORTING
retcode = gv_retcode.
FREE gc_factory.
ENDIF.
ENDFORM. " CLOSE_FACTORY
반응형
반응형