SAP EXCEL 模板使用示例

将SAP系统内容导入Excel表
*&---------------------------------------------------------------------*
*& Report  Z_XH_WRITE_XLS_TEMPLET
*&
*&---------------------------------------------------------------------*
*& 1. 用TCODE:SMW0把EXCEL文件上载到SAP系统
*&   1) 选择WebRFC 应用程序的二进制数据, 按'查找'按钮
*&   2) 把包名(开发类)输入, 按'运行' 按钮
*&   3) 按'新建'按钮, 在建立对象的时候要导入EXCEL文件, 对象最好要加入'.XLS'后缀
*&   注: 在menu 设置--&gt维护MIME类型 中看是否有EXCEL文件类型, 没有则要建立
*&---------------------------------------------------------------------*

REPORT  Z_XH_WRITE_XLS_TEMPLET MESSAGE-ID ZMESS_NWGC
        NO STANDARD PAGE HEADING.
*导出EXCEL所用到变量
TYPE-POOLS OLE2 .
DATA: V_EXCEL TYPE OLE2_OBJECT,
      V_SHEET TYPE OLE2_OBJECT,
      V_BOOK  TYPE OLE2_OBJECT,
      V_CELL  TYPE OLE2_OBJECT.
DATA: V_SHEET_NUMBER TYPE I.

DATA: BEGIN OF I_DATA OCCURS 0,
        FIELD1(10) TYPE N,
        FIELD2(20) TYPE C,
        FIELD3     LIKE EKPO-NETWR,
        FIELD4(4)  TYPE N,
        FIELD5     LIKE EKPO-NETWR,
        FIELD6(4)  TYPE N,
      END OF I_DATA.

START-OF-SELECTION.
  PERFORM. FRM_GET_DATA.
  PERFORM. FRM_CREATE_XSL.

END-OF-SELECTION.
*&---------------------------------------------------------------------*
*&      Form  FRM_CREATE_XSL
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM. FRM_CREATE_XSL .
  DATA: L_NAME LIKE WWWDATATAB,
        L_MIME LIKE W3MIME OCCURS 10,
        L_FILENAME TYPE STRING,
        L_PATH TYPE STRING,
        L_FULLPATH TYPE STRING,
        L_ROW TYPE I,
        L_COL TYPE I,
        L_TITLE TYPE STRING.
  L_NAME-RELID = 'MI'.
  L_NAME-OBJID = 'ZTEST0002.XLS'.
  L_NAME-TEXT = SY-TITLE.
  CALL FUNCTION 'WWWDATA_IMPORT'
    EXPORTING
      KEY               = L_NAME
    TABLES
      MIME              = L_MIME
    EXCEPTIONS
      WRONG_OBJECT_TYPE = 1
      IMPORT_ERROR      = 2
      OTHERS            = 3.
  L_TITLE = SY-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.
  CALL FUNCTION 'GUI_DOWNLOAD'
    EXPORTING
      FILENAME = L_FULLPATH
      FILETYPE = 'BIN'
    TABLES
      DATA_TAB = L_MIME.

*--打开excel模板
  PERFORM. FRM_OPEN_EXCEL USING L_FULLPATH.

*--打开excel第一个sheet
  V_SHEET_NUMBER = 1.
  PERFORM. FRM_OPEN_EXCEL_SHEET USING V_SHEET_NUMBER.

*--写excel file的内容
  L_ROW = 4.
  L_COL = 1.
  PERFORM. FRM_WRITE_CELL USING L_ROW L_COL SY-DATUM(4).
  LOOP AT I_DATA.
    L_ROW = SY-TABIX + 6.
    L_COL = 1.
    PERFORM. FRM_WRITE_CELL USING L_ROW L_COL I_DATA-FIELD1.
    L_COL = 2.
    PERFORM. FRM_WRITE_CELL USING L_ROW L_COL I_DATA-FIELD2.
    L_COL = 3.
    PERFORM. FRM_WRITE_CELL USING L_ROW L_COL I_DATA-FIELD3.
    L_COL = 4.
    PERFORM. FRM_WRITE_CELL USING L_ROW L_COL I_DATA-FIELD4.
    L_COL = 5.
    PERFORM. FRM_WRITE_CELL USING L_ROW L_COL I_DATA-FIELD5.
    L_COL = 6.
    PERFORM. FRM_WRITE_CELL USING L_ROW L_COL I_DATA-FIELD6.
  ENDLOOP.

* 关闭EXCEL
  PERFORM. FRM_SAVE_EXCEL USING L_FULLPATH.

ENDFORM.                    " FRM_CREATE_XSL
*&---------------------------------------------------------------------*
*&      Form  FRM_OPEN_EXCEL
*&---------------------------------------------------------------------*
*      --&gtP_L_FULLPATH  Local excel file full path
*----------------------------------------------------------------------*
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.

  IF SY-SUBRC <> 0.
    MESSAGE E001 WITH 'Open file failed!'.
    STOP.
  ENDIF.

ENDFORM.                    " FRM_OPEN_EXCEL
*&---------------------------------------------------------------------*
*&      Form  FRM_OPEN_EXCEL_SHEET
*&---------------------------------------------------------------------*
*      --&gtP_V_SHEET_NUMBER  sheet number
*----------------------------------------------------------------------*
FORM. FRM_OPEN_EXCEL_SHEET  USING    PA_SHEET_NUMBER.
  SET PROPERTY OF V_EXCEL 'Visible' = 1.

  CALL METHOD OF V_BOOK 'Sheets' = V_SHEET
    EXPORTING #1 = PA_SHEET_NUMBER.

ENDFORM.                    " FRM_OPEN_EXCEL_SHEET
*&---------------------------------------------------------------------*
*&      Form  FRM_WRITE_CELL
*&---------------------------------------------------------------------*
*      --&gtP_L_ROW  number of row
*      --&gtP_L_COL  number of collum
*      --&gtP_L_VAL  cell value
*----------------------------------------------------------------------*
FORM. FRM_WRITE_CELL  USING    PA_ROW
                              PA_COL
                              PA_VAL.
  CALL METHOD OF V_SHEET 'Cells' = V_CELL
    EXPORTING #1 = PA_ROW
    #2 = PA_COL.
  SET PROPERTY OF V_CELL 'Value' = PA_VAL.

ENDFORM.                    " FRM_WRITE_CELL
*&---------------------------------------------------------------------*
*&      Form  FRM_SAVE_EXCEL
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      --&gtP_L_FULLPATH  text
*----------------------------------------------------------------------*
FORM. FRM_SAVE_EXCEL  USING    PA_FULLPATH.
  SET PROPERTY OF V_EXCEL 'DisplayAlerts' = 0.
  CALL METHOD OF V_BOOK 'SAVEAS'
    EXPORTING #1 = PA_FULLPATH.

  CALL METHOD OF V_BOOK 'Exit' = V_BOOK.
  FREE OBJECT V_EXCEL.

ENDFORM.                    " FRM_SAVE_EXCEL
*&---------------------------------------------------------------------*
*&      Form  FRM_GET_DATA
*&---------------------------------------------------------------------*
FORM. FRM_GET_DATA .
  REFRESH: I_DATA.

  I_DATA-FIELD1 = '0000000001'.
  I_DATA-FIELD2 = 'PROJECT1'.
  I_DATA-FIELD3 = '1000.001'.
  I_DATA-FIELD4 = '2006'.
  I_DATA-FIELD5 = '1001.000'.
  I_DATA-FIELD6 = '2006'.
  APPEND I_DATA.
  CLEAR I_DATA.

  I_DATA-FIELD1 = '0000000002'.
  I_DATA-FIELD2 = 'PROJECT2'.
  I_DATA-FIELD3 = '2000.002'.
  I_DATA-FIELD4 = '2007'.
  I_DATA-FIELD5 = '2002.000'.
  I_DATA-FIELD6 = '2007'.
  APPEND I_DATA.
  CLEAR I_DATA.
ENDFORM.                    " FRM_GET_DATA

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23495244/viewspace-696238/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23495244/viewspace-696238/