首页 > SAP > FIORI > 下载模板文件到本地及上传EXECL文件得到内表
2019
11-25

下载模板文件到本地及上传EXECL文件得到内表

1.一、下载SMW0中的模板

使用SMW0上传模板后,可以使用如下函数下载模板到为本地文件;

此函数中附近选择本地目录、文件名功能。使用时只OBJID就可以。

FUNCTION ZFC_EXCEL_DOWNLOAD_FROM_SMW0.
*"----------------------------------------------------------------------
*"*"Local interface:
*"  IMPORTING
*"     VALUE(I_OBJID) TYPE  WWWDATATAB-OBJID
*"     VALUE(I_FIELDNAME) TYPE  STRING
*"  EXPORTING
*"     REFERENCE(E_RETURN) TYPE  BAPIRET2
*"----------------------------------------------------------------------
*--此函数用于,在SWM0上传后,通过此函数下载到本地;

* 相关变量定义
  DATA:LO_OBJDATA     LIKE WWWDATATAB,
       LO_MIME        LIKE W3MIME,
       LC_FULLPATH    TYPE STRING VALUE 'c:\',
       LC_PATH        TYPE STRING VALUE 'c:\',
       LS_DESTINATION LIKE RLGRAP-FILENAME,
       LS_OBJNAM      TYPE STRING,
       L_RC           LIKE SY-SUBRC,
       LS_ERRTXT      TYPE STRING,
       L_DEST         LIKE SAPB-SAPPFAD.

* 模板下载路径名
  CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_SAVE_DIALOG
    EXPORTING
      WINDOW_TITLE              = '模板下载'
      DEFAULT_EXTENSION         = 'xlsx'
      DEFAULT_FILE_NAME         = I_FIELDNAME
    CHANGING
      FILENAME                  = I_FIELDNAME
      PATH                      = LC_PATH
      FULLPATH                  = LC_FULLPATH
    EXCEPTIONS
      CNTL_ERROR                = 1
      ERROR_NO_GUI              = 2
      NOT_SUPPORTED_BY_GUI      = 3
      OTHERS                    = 5.
* 没有选择路径
  IF LC_FULLPATH = ''.
    E_RETURN-TYPE = 'E'.
    E_RETURN-MESSAGE =  '请选择正确的路径!'.
    RETURN.
  ELSE.
*-  路径名
    L_DEST = LC_FULLPATH.

    SELECT SINGLE
           RELID                   "IMPORT/EXPORT 记录中的区域
           OBJID                   "SAP WWW 网关对象名
      FROM WWWDATA
      INTO CORRESPONDING FIELDS OF LO_OBJDATA
     WHERE SRTF2 = 0
      AND RELID = 'MI'
      AND OBJID = I_OBJID.

    IF SY-SUBRC <> 0 OR LO_OBJDATA-OBJID IS INITIAL.
      E_RETURN-TYPE = 'E'.
      E_RETURN-MESSAGE =  '没有获得模板数据'.
      RETURN.
    ENDIF.

    LS_DESTINATION = L_DEST.
    CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
      EXPORTING
        KEY         = LO_OBJDATA
        DESTINATION = LS_DESTINATION
      IMPORTING
        RC          = L_RC.
    IF L_RC <> 0.
      E_RETURN-TYPE = 'E'.
      E_RETURN-MESSAGE =  '模板下载失败'.
      RETURN.
    ENDIF.
  ENDIF.




ENDFUNCTION.

使用方法

FORM FRM_EXCEL_DOWN .



  DATA: LV_OBJID  TYPE W3OBJID,

        LS_RETURN TYPE BAPIRET2.
  LV_OBJID = 'ZFIR_718'.
  CLEAR LS_RETURN

  CALL FUNCTION 'ZFC_EXCEL_DOWNLOAD_FROM_SMW0'
    EXPORTING
      I_OBJID     = LV_OBJID
      I_FIELDNAME = '代收款费用入账_货款清账批导模板'
    IMPORTING
      E_RETURN    = LS_RETURN.
  IF LS_RETURN-TYPE EQ 'E'.
    MESSAGE LS_RETURN-MESSAGE TYPE 'S' DISPLAY LIKE 'E'.
    STOP.
  ENDIF.

ENDFORM.                    "FRM_EXCEL_DOWN

2.二、上传EXECL得到内表

FUNCTION ZFC_PUB_EXCEL_UPLOAD.
*"----------------------------------------------------------------------
*"*"Local interface:
*"  IMPORTING
*"     VALUE(I_BECOL) TYPE  I DEFAULT 1
*"     VALUE(I_ENCOL) TYPE  I DEFAULT 20
*"     VALUE(I_BEROW) TYPE  I DEFAULT 2
*"     VALUE(I_ENROW) TYPE  I DEFAULT 50000
*"     VALUE(I_MOD) TYPE  CHAR10 DEFAULT 'FIELD'
*"     VALUE(I_TITLE_LINE) TYPE  I DEFAULT 1
*"     VALUE(I_EXCEL_WAY) TYPE  LOCALFILE OPTIONAL
*"  EXPORTING
*"     VALUE(E_RETURN) TYPE  BAPIRET2
*"  TABLES
*"      T_TAB TYPE  STANDARD TABLE
*"----------------------------------------------------------------------

*局部变量的定义
  DATA: LT_EXCEL TYPE TABLE OF ALSMEX_TABLINE.
  DATA: LS_EXCEL TYPE ALSMEX_TABLINE,
        LV_COL   TYPE I,
        CL_CX    TYPE REF TO CX_ROOT,
        LT_HEAD  TYPE TABLE OF ALSMEX_TABLINE WITH HEADER LINE.

  CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
    EXPORTING
      TEXT = '正在导入数据'.

  CHECK I_EXCEL_WAY IS NOT INITIAL.

  IF I_MOD EQ 'FIELD' AND I_TITLE_LINE IS INITIAL.
    E_RETURN-TYPE  = 'E'.
    E_RETURN-MESSAGE  = '请输入TITLE行'.
    RETURN.
  ENDIF.

* 把文件的数据变成内表
  CLEAR LT_EXCEL.
  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      FILENAME                = I_EXCEL_WAY
      I_BEGIN_COL             = I_BECOL
      I_BEGIN_ROW             = I_BEROW
      I_END_COL               = I_ENCOL
      I_END_ROW               = I_ENROW
    TABLES
      INTERN                  = LT_EXCEL
    EXCEPTIONS
      INCONSISTENT_PARAMETERS = 1
      UPLOAD_OLE              = 2
      ERROR_MESSAGE           = 3
      OTHERS                  = 4.

  IF SY-SUBRC NE 0.
    E_RETURN-TYPE = 'E'.
    IF SY-MSGID IS NOT INITIAL.
      MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO WITH  SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4 INTO E_RETURN-MESSAGE.
    ELSE.
      CASE SY-SUBRC.
        WHEN '1'.
          E_RETURN-MESSAGE = 'inconsistent_parameters'.
        WHEN '2'.
          E_RETURN-MESSAGE = 'upload_ole'.
        WHEN OTHERS.
          E_RETURN-MESSAGE = '上传EXCEL未知异常'.
      ENDCASE.
    ENDIF.
    RETURN.
  ENDIF.

  DATA: DY_WA   TYPE REF TO DATA,
        DYN_TAB TYPE REF TO DATA.

  FIELD-SYMBOLS: <ITABLE>  TYPE STANDARD TABLE,
                 <F_LINE>  TYPE ANY,
                 <F_FIELD> TYPE ANY.

  CREATE DATA DYN_TAB LIKE TABLE OF T_TAB.
  ASSIGN DYN_TAB->* TO <ITABLE>.

  CREATE DATA DY_WA LIKE LINE OF <ITABLE>.
  ASSIGN DY_WA->* TO <F_LINE>.

* 处理内表数据
  CLEAR: LS_EXCEL,LT_HEAD,LT_HEAD[].
  SORT LT_EXCEL BY ROW COL.
  LOOP AT LT_EXCEL INTO LS_EXCEL.
    IF LS_EXCEL-ROW EQ I_TITLE_LINE.
      APPEND LS_EXCEL TO LT_HEAD.
      IF I_MOD EQ 'FIELD'.
        AT END OF ROW.
          SORT LT_HEAD BY COL.
        ENDAT.
        CLEAR LS_EXCEL.
        CONTINUE.
      ENDIF.
    ENDIF.

    IF I_MOD EQ 'FIELD'.
      CLEAR LT_HEAD.
      READ TABLE LT_HEAD WITH KEY COL = LS_EXCEL-COL BINARY SEARCH.
      ASSIGN COMPONENT LT_HEAD-VALUE OF STRUCTURE <F_LINE> TO <F_FIELD>.
    ELSE.
      LV_COL = LS_EXCEL-COL.
      ASSIGN COMPONENT LV_COL OF STRUCTURE <F_LINE> TO <F_FIELD>.
    ENDIF.

    IF SY-SUBRC EQ 0.
      TRY.
          MOVE LS_EXCEL-VALUE TO <F_FIELD>.
        CATCH CX_ROOT INTO CL_CX.
          ASSIGN COMPONENT 'MESSAGE' OF STRUCTURE <F_LINE> TO <F_FIELD>.
          IF SY-SUBRC EQ 0.
            <F_FIELD> = CL_CX->GET_TEXT( ).
          ENDIF.

          ASSIGN COMPONENT 'MTYPE' OF STRUCTURE <F_LINE> TO <F_FIELD>.
          IF SY-SUBRC EQ 0.
            <F_FIELD> = 'E'.
          ENDIF.

          ASSIGN COMPONENT 'STATU' OF STRUCTURE <F_LINE> TO <F_FIELD>.
          IF SY-SUBRC EQ 0.
            <F_FIELD> = ICON_LED_RED.
          ENDIF.
      ENDTRY.
    ENDIF.

    IF LT_HEAD-VALUE = 'MATNR'.
      CALL FUNCTION 'CONVERSION_EXIT_MATN1_INPUT'
        EXPORTING
          INPUT  = <F_FIELD>
        IMPORTING
          OUTPUT = <F_FIELD>.
    ELSEIF LT_HEAD-VALUE(5) = 'VBELN'.
      CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
        EXPORTING
          INPUT  = <F_FIELD>
        IMPORTING
          OUTPUT = <F_FIELD>.
    ENDIF.

    AT END OF ROW.
      APPEND <F_LINE> TO <ITABLE>.
      CLEAR <F_LINE>.
    ENDAT.
    CLEAR LS_EXCEL.
  ENDLOOP.

  T_TAB[] = <ITABLE>[].

  IF T_TAB[] IS INITIAL.
    E_RETURN-TYPE = 'E'.
    E_RETURN-MESSAGE = '没有需要上传的数据'.
  ENDIF.
ENDFUNCTION.

使用时,指定本地EXECL文件名,直接调用行

FORM PF_UPLOAD_DATA .

  DATA: LS_RETURN TYPE BAPIRET2.



  CLEAR LS_RETURN.

  CALL FUNCTION 'ZFC_PUB_EXCEL_UPLOAD'
    EXPORTING
      I_BECOL     = 1
      I_ENCOL     = 27
      I_BEROW     = 2 " 含标题
*     I_ENROW     = 50000
*     I_MOD       = 'FIELD'
*     I_TITLE_LINE       = 1
      I_EXCEL_WAY = P_FILE
    IMPORTING
      E_RETURN    = LS_RETURN
    TABLES
      T_TAB       = GT_ALV[].

  IF LS_RETURN-TYPE EQ 'E'.
    MESSAGE LS_RETURN-MESSAGE TYPE 'S' DISPLAY LIKE 'E'.
    STOP.
  ENDIF.
ENDFORM.                    "UPLOAD_DATA
                       

留下一个回复

你的email不会被公开。