&---------------------------------------------------------------------
*& Report ZTEST002
&---------------------------------------------------------------------
*&Description: 用粘贴板导出EXCEL数据
&---------------------------------------------------------------------
REPORT ZTEST002.
TABLES BKPF.
TYPE-POOLS:SLIS,OLE2.
TYPES:
BEGIN OF TYP_BKPF,
BUKRS TYPE BUKRS,
BELNR TYPE BELNR_D,
GJAHR TYPE GJAHR,
BLART TYPE BLART,
BUDAT TYPE BUDAT,
MONAT TYPE MONAT,
TCODE TYPE TCODE,
END OF TYP_BKPF.
DATA: GT_BKPF TYPE TABLE OF TYP_BKPF,
GS_BKPF TYPE TYP_BKPF.
DATA: V_EXCEL TYPE OLE2_OBJECT,
V_BOOK TYPE OLE2_OBJECT,
V_SHEET TYPE OLE2_OBJECT,
V_CELL TYPE OLE2_OBJECT.
*屏幕
SELECT-OPTIONS:
S_BUKRS FOR BKPF-BUKRS,
S_BELNR FOR BKPF-BELNR,
S_GJAHR FOR BKPF-GJAHR,
S_MONAT FOR BKPF-MONAT.
START-OF-SELECTION.
PERFORM FRM_GET_BKPF_DATA.
PERFORM FRM_DOWN_DATA.
&---------------------------------------------------------------------
*& Form FRM_GET_BKPF_DATA
&---------------------------------------------------------------------
FORM FRM_GET_BKPF_DATA .
SELECT BUKRS
BELNR
GJAHR
BLART
BUDAT
MONAT
TCODE
INTO TABLE GT_BKPF
FROM BKPF
WHERE BUKRS IN S_BUKRS
AND BELNR IN S_BELNR
AND GJAHR IN S_GJAHR
AND MONAT IN S_MONAT.
ENDFORM.
&---------------------------------------------------------------------
*& Form FRM_DOWN_DATA
&---------------------------------------------------------------------
FORM FRM_DOWN_DATA .
DATA:
L_NAME LIKE WWWDATATAB,
L_MINE LIKE W3MIME OCCURS 10,
L_TITLE TYPE STRING,
L_FILENAME TYPE STRING,
L_PATH TYPE STRING,
L_FULLPATH TYPE STRING.
L_NAME-RELID = ‘MI’.
L_NAME-OBJID = ‘ZTEST002’.
L_NAME-TEXT = ‘粘贴板测试’.
CALL FUNCTION ‘WWWDATA_IMPORT’
EXPORTING
KEY = L_NAME
TABLES
MIME = L_MINE
EXCEPTIONS
WRONG_OBJECT_TYPE = 1
IMPORT_ERROR = 2
OTHERS = 3.
L_TITLE = ‘粘贴板测试’.
CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_SAVE_DIALOG
EXPORTING
WINDOW_TITLE = L_TITLE
DEFAULT_EXTENSION = ‘xls’
DEFAULT_FILE_NAME = L_TITLE
FILE_FILTER = ‘(电子表格EXCEL)’
CHANGING
FILENAME = L_FILENAME
PATH = L_PATH
FULLPATH = L_FULLPATH
EXCEPTIONS
CNTL_ERROR = 1
ERROR_NO_GUI = 2
NOT_SUPPORTED_BY_GUI = 3
OTHERS = 4.
IF SY-SUBRC <> 0.
STOP.
ENDIF.
IF L_FULLPATH IS INITIAL.
EXIT.
ENDIF.
CALL FUNCTION ‘SAPGUI_PROGRESS_INDICATOR’
EXPORTING
PERCENTAGE = ‘0’
TEXT = ‘正在导出EXCEL’.
CALL FUNCTION ‘GUI_DOWNLOAD’
EXPORTING
FILENAME = L_FULLPATH
FILETYPE = ‘BIN’
TABLES
DATA_TAB = L_MINE.
*–打开excel模板
PERFORM FRM_OPEN_EXCEL USING L_FULLPATH.
*–向excel写数据
PERFORM FRM_WRITE_EXCEL_SHEET TABLES GT_BKPF[].
*–设置excel可见
CALL METHOD OF V_EXCEL ‘Worksheets’ = V_SHEET
EXPORTING
#1 = 1.
CALL METHOD OF V_SHEET ‘Activate’.
SET PROPERTY OF V_EXCEL ‘Visible’ = 1.
*关闭EXCEL并保存
PERFORM FRM_SAVE_EXCEL USING L_FULLPATH.
ENDFORM.
&---------------------------------------------------------------------
*& Form FRM_OPEN_EXCEL
&---------------------------------------------------------------------
FORM FRM_OPEN_EXCEL USING PA_PATH.
CREATE OBJECT V_EXCEL ‘Excel.Application’.
CALL METHOD OF V_EXCEL ‘Workbooks’ = V_BOOK.
CALL METHOD OF V_BOOK ‘Open’ = V_BOOK
EXPORTING
#1 = PA_PATH.
CALL METHOD OF V_BOOK ‘Sheets’ = V_SHEET
EXPORTING
#1 = 1.
ENDFORM.
&---------------------------------------------------------------------
*& Form FRM_WRITE_EXCEL_SHEET
&---------------------------------------------------------------------
FORM FRM_WRITE_EXCEL_SHEET TABLES PT_TAB LIKE GT_BKPF.
DATA: L_STR TYPE STRING,
CH TYPE CHAR20,
L_RC TYPE I.
DATA: LW_XFC TYPE LVC_S_FCAT,
LI_IFC TYPE LVC_T_FCAT.
DATA: BEGIN OF LI_DATA OCCURS 0,
LINE(4096),
END OF LI_DATA.
DATA: L_FGF TYPE C.
DATA: DY_TABLE TYPE REF TO DATA,
DY_LINE TYPE REF TO DATA.
FIELD-SYMBOLS:<DYN_TABLE> TYPE STANDARD TABLE, "动态内表
<DYN_WA>, "动态工作区
<DYN_FIELD>. "动态字段名
L_FGF = CL_ABAP_CHAR_UTILITIES=>HORIZONTAL_TAB.
LW_XFC-FIELDNAME = ‘BUKRS’.
LW_XFC-INTTYPE = ‘C’.
LW_XFC-DATATYPE = ‘C’.
LW_XFC-INTLEN = 4.
APPEND LW_XFC TO LI_IFC.
LW_XFC-FIELDNAME = ‘BELNR’.
LW_XFC-INTTYPE = ‘C’.
LW_XFC-DATATYPE = ‘C’.
LW_XFC-INTLEN = 10.
APPEND LW_XFC TO LI_IFC.
LW_XFC-FIELDNAME = ‘GJAHR’.
LW_XFC-INTTYPE = ‘C’.
LW_XFC-DATATYPE = ‘C’.
LW_XFC-INTLEN = 4.
APPEND LW_XFC TO LI_IFC.
LW_XFC-FIELDNAME = ‘BLART’.
LW_XFC-INTTYPE = ‘C’.
LW_XFC-DATATYPE = ‘C’.
LW_XFC-INTLEN = 4.
APPEND LW_XFC TO LI_IFC.
LW_XFC-FIELDNAME = ‘BUDAT’.
LW_XFC-INTTYPE = ‘C’.
LW_XFC-DATATYPE = ‘C’.
LW_XFC-INTLEN = 10.
APPEND LW_XFC TO LI_IFC.
LW_XFC-FIELDNAME = ‘MONAT’.
LW_XFC-INTTYPE = ‘C’.
LW_XFC-DATATYPE = ‘C’.
LW_XFC-INTLEN = 2.
APPEND LW_XFC TO LI_IFC.
LW_XFC-FIELDNAME = ‘TCODE’.
LW_XFC-INTTYPE = ‘C’.
LW_XFC-DATATYPE = ‘C’.
LW_XFC-INTLEN = 20.
APPEND LW_XFC TO LI_IFC.
*创建动态内表和工作区
CALL METHOD CL_ALV_TABLE_CREATE=>CREATE_DYNAMIC_TABLE
EXPORTING
IT_FIELDCATALOG = LI_IFC
IMPORTING
EP_TABLE = DY_TABLE.
ASSIGN DY_TABLE->* TO <DYN_TABLE>.
CREATE DATA DY_LINE LIKE LINE OF <DYN_TABLE>.
ASSIGN DY_LINE->* TO <DYN_WA>.
LOOP AT PT_TAB.
ASSIGN COMPONENT ‘BUKRS’ OF STRUCTURE <DYN_WA> TO <DYN_FIELD>.
<DYN_FIELD> = PT_TAB-BUKRS.
ASSIGN COMPONENT ‘BELNR’ OF STRUCTURE <DYN_WA> TO <DYN_FIELD>.
<DYN_FIELD> = PT_TAB-BELNR.
ASSIGN COMPONENT ‘GJAHR’ OF STRUCTURE <DYN_WA> TO <DYN_FIELD>.
<DYN_FIELD> = PT_TAB-GJAHR.
ASSIGN COMPONENT ‘BLART’ OF STRUCTURE <DYN_WA> TO <DYN_FIELD>.
<DYN_FIELD> = PT_TAB-BLART.
ASSIGN COMPONENT ‘BUDAT’ OF STRUCTURE <DYN_WA> TO <DYN_FIELD>.
<DYN_FIELD> = PT_TAB-BUDAT.
ASSIGN COMPONENT ‘MONAT’ OF STRUCTURE <DYN_WA> TO <DYN_FIELD>.
<DYN_FIELD> = PT_TAB-MONAT.
ASSIGN COMPONENT ‘TCODE’ OF STRUCTURE <DYN_WA> TO <DYN_FIELD>.
<DYN_FIELD> = PT_TAB-TCODE.
APPEND <DYN_WA> TO <DYN_TABLE>.
ENDLOOP.
LOOP AT <DYN_TABLE> INTO <DYN_WA>.
LOOP AT LI_IFC INTO LW_XFC.
ASSIGN COMPONENT SY-TABIX OF STRUCTURE <DYN_WA> TO <DYN_FIELD>.
CH = <DYN_FIELD>.
CONDENSE CH.
IF LI_DATA-LINE IS INITIAL.
CONCATENATE LI_DATA-LINE CH L_FGF INTO LI_DATA-LINE.
CLEAR CH.
ELSE.
CONCATENATE LI_DATA-LINE CH L_FGF INTO LI_DATA-LINE.
CLEAR CH.
ENDIF.
ENDLOOP.
APPEND LI_DATA.
CLEAR:LI_DATA.
ENDLOOP.
CALL METHOD CL_GUI_FRONTEND_SERVICES=>CLIPBOARD_EXPORT
IMPORTING
DATA = LI_DATA[]
CHANGING
RC = L_RC
EXCEPTIONS
CNTL_ERROR = 1
ERROR_NO_GUI = 2
NOT_SUPPORTED_BY_GUI = 3
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.
CALL METHOD OF
V_SHEET
‘CELLS’ = V_CELL
EXPORTING
#1 = 2
#2 = 1.
CALL METHOD OF
V_CELL
‘SELECT’.
CALL METHOD OF
V_SHEET
‘PASTE’.
ENDFORM.
FORM FRM_SAVE_EXCEL USING P_FULLPATH.
SET PROPERTY OF V_EXCEL ‘DisplayAlerts’ = 0.
CALL METHOD OF V_BOOK ‘SAVEAS’
EXPORTING
#1 = P_FULLPATH.
CALL METHOD OF V_BOOK ‘Exit’ = V_EXCEL.
- CALL METHOD OF V_BOOK ‘QUIT’.
- CALL METHOD OF V_EXCEL ‘QUIT’.
FREE OBJECT V_CELL.
FREE OBJECT V_SHEET.
FREE OBJECT V_BOOK.
FREE OBJECT V_EXCEL.
ENDFORM. "FRM_SAVE_EXCEL