1. SMW0에 템플릿 UPLOAD
- EXCEL버전 확인필요( EXCEL 97-2003)
2. 소스
*&---------------------------------------------------------------------*
*& Report ZEXCELDOWN
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
REPORT ZEXCELDOWN.
*======================================================================*
* 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.
*&---------------------------------------------------------------------*
*& Include ZTLAYTOP
*&---------------------------------------------------------------------*
*======================================================================*
* TABLES
*======================================================================*
TABLES : dd03l, " 테이블필드
dd03t, " DD: 필드에 대한 텍스트 (언어종속)
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 출력 관련
*======================================================================*
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 헤더
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 아이템
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
*======================================================================*
*// 조회조건
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
PARAMETERS : p_tabnm LIKE dd03l-tabname OBLIGATORY.
SELECTION-SCREEN END OF BLOCK b1.
*& Include ZTLAYTOP
*&---------------------------------------------------------------------*
*======================================================================*
* TABLES
*======================================================================*
TABLES : dd03l, " 테이블필드
dd03t, " DD: 필드에 대한 텍스트 (언어종속)
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 출력 관련
*======================================================================*
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 헤더
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 아이템
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
*======================================================================*
*// 조회조건
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
PARAMETERS : p_tabnm LIKE dd03l-tabname OBLIGATORY.
SELECTION-SCREEN END OF BLOCK b1.
*&---------------------------------------------------------------------*
*& 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."엑셀에 뿌려질 데이터를 모음.
PERFORM exec_print. "엑셀 오브젝트 생성 메서드실행.
ENDFORM. " GET_EXCEL
*&---------------------------------------------------------------------*
*& Form GET_PRINT_DATA
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM get_print_data .
* HEADER DATE 조합
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
*& 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."엑셀에 뿌려질 데이터를 모음.
PERFORM exec_print. "엑셀 오브젝트 생성 메서드실행.
ENDFORM. " GET_EXCEL
*&---------------------------------------------------------------------*
*& Form GET_PRINT_DATA
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM get_print_data .
* HEADER DATE 조합
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
3. 매크로
Option Explicit
Dim R3Table As Object
Dim R3Table11 As Object
Sub Macro1()
Dim r_count As Long
Dim i, i_row As Long
Application.WindowState = xlMaximized
ActiveWindow.WindowState = xlMaximized
'----------------------------
'--------헤더 데이터 입력S1
'----------------------------
ActiveWorkbook.Sheets(1).Activate
Set R3Table = ThisWorkbook.Container.LinkServer.Items("HEADER").Table
Range("D3:J3").Select
Cells(3, 4) = CVar(R3Table.Value(1, 1)) '테이블ID
Range("D4:J4").Select
Cells(4, 4) = CVar(R3Table.Value(1, 2)) '테이블명
'----------------------------
'--------아이템 데이터 입력S1
'----------------------------
Set R3Table11 = ThisWorkbook.Container.LinkServer.Items("ITEM11").Table
r_count = R3Table11.RowCount
i_row = 6
For i = 1 To r_count
Cells(i_row, 2) = CVar(R3Table11.Value(i, 1)) '1
Cells(i_row, 3) = CVar(R3Table11.Value(i, 2)) '2
Cells(i_row, 4) = CVar(R3Table11.Value(i, 3)) '3
Cells(i_row, 5) = CVar(R3Table11.Value(i, 4)) '4
Cells(i_row, 6) = CVar(R3Table11.Value(i, 5)) '5
Cells(i_row, 7) = CVar(R3Table11.Value(i, 6)) '6
Cells(i_row, 8) = CVar(R3Table11.Value(i, 7)) '7
Cells(i_row, 9) = CVar(R3Table11.Value(i, 8)) '8
Cells(i_row, 10) = CVar(R3Table11.Value(i, 9)) '9
i_row = i_row + 1
Next i
With Range("B2").CurrentRegion
.Borders.LineStyle = xlNone
.Borders.LineStyle = 1
End With
Range("A1").Select
End Sub
4. 엑셀양식
댓글 없음:
댓글 쓰기