内表数据下载到已有EXCEL

以前把内表数据下载到已有EXCEL,均是采用的一个一个格子的写数据(系统有标准例程:RSDEMO01),数据量少还可以,数据量多了就等着吧.

近日仔细看了一下系统提供的FM:ALSM_EXCEL_TO_INTERNAL_TABLE,它其实是把EXCEL的数据复制到剪切板,然后处理了一下到内表。我觉得反过来肯定也是可行的,就试验了一下.

 

试验代码如下:

REPORT INTERNAL_TABLE_TO_EXCEL.
TABLES:SFLIGHT.

TYPE-POOLS OLE2.

TYPES: BEGIN OF TY_S_SENDERLINE,
         LINE(4096)               TYPE C,
       END OF TY_S_SENDERLINE,

       TY_T_SENDER                TYPE TY_S_SENDERLINE  OCCURS 0.

DATA: EXCEL_TAB     TYPE  TY_T_SENDER.
DATA: STR TYPE TY_S_SENDERLINE.
DATA: ITAB LIKE SFLIGHT OCCURS 0 WITH HEADER LINE.
DATA: LD_SEPARATOR  TYPE  C.
DATA: APPLICATION   TYPE  OLE2_OBJECT,
      WORKBOOK      TYPE  OLE2_OBJECT,
      WORKSHEET     TYPE  OLE2_OBJECT,
      H_CELL        TYPE  OLE2_OBJECT.

DATA: LD_RC             TYPE I.
***已存在的EXCEL文件
DATA:FILENAME(200) TYPE C VALUE 'c:\test.xls'.

SELECT CARRID CONNID FLDATE INTO CORRESPONDING FIELDS OF TABLE ITAB FROM SFLIGHT UP TO 3 ROWS.
***定义分隔符，就是TAB键啦
CLASS CL_ABAP_CHAR_UTILITIES DEFINITION LOAD.
LD_SEPARATOR = CL_ABAP_CHAR_UTILITIES=>HORIZONTAL_TAB.
***字段之间用分割符连接，这个很重要，否则就写到一个格子了
LOOP AT ITAB.
  CONCATENATE ITAB-CARRID ITAB-CONNID ITAB-FLDATE INTO STR-LINE SEPARATED BY LD_SEPARATOR.
  APPEND STR TO EXCEL_TAB.
ENDLOOP.
***内表数据复制到剪切板
CALL METHOD CL_GUI_FRONTEND_SERVICES=>CLIPBOARD_EXPORT
  IMPORTING
    DATA                 = EXCEL_TAB
  CHANGING
    RC                   = LD_RC
  EXCEPTIONS
    CNTL_ERROR           = 1
    ERROR_NO_GUI         = 2
    NOT_SUPPORTED_BY_GUI = 3
    OTHERS               = 4.

CREATE OBJECT APPLICATION 'Excel.Application'.
SET PROPERTY OF APPLICATION 'VISIBLE' = 1 .
CALL METHOD OF APPLICATION 'Workbooks' = WORKBOOK.
CALL METHOD OF WORKBOOK 'Open' EXPORTING #1 = FILENAME.
GET PROPERTY OF  APPLICATION 'ACTIVESHEET' = WORKSHEET.

CALL METHOD OF WORKSHEET 'Cells' = H_CELL
  EXPORTING #1 = 3 #2 = 1.

CALL METHOD OF H_CELL 'SELECT'.

CALL METHOD OF WORKSHEET 'PASTE'.

CALL METHOD OF WORKBOOK 'SAVE'.