SAP EXCEL数据上传,ALV显示模板程序

来源：互联网发布：edg淘宝编辑：程序博客网时间：2024/05/03 20:49

先将EXCEL数据上传到SAP的内表中，然后对数据进行检查，将数据存储到SAP的后台表，然后用ALV对数据进行查询。

REPORT ztest002.
TYPE-POOLS truxs.
DATA xltab TYPE truxs_t_text_data.

DATA: BEGIN OF gt_upload OCCURS 0,
        matnr(18),
        lifnr(10),
        jyfs(10),   "检验方式
        maktx(40),
        name1(35).
DATA: END OF gt_upload.

DATA: BEGIN OF gt_ztest002 OCCURS 0.
INCLUDE STRUCTURE zsrm_ztest002.
DATA: END OF gt_ztest002.

*************ALV 报表定义
TYPE-POOLS slis.
*DATA: myrepid LIKE sy-repid.  "REPORT NAME
DATA: fieldcat TYPE slis_t_fieldcat_alv.
DATA: afield   TYPE LINE OF slis_t_fieldcat_alv.
DATA: layout   TYPE slis_layout_alv .

DEFINE fill.
  clear afield.
  afield-col_pos = &1.
  afield-fieldname = &2.
  afield-seltext_l = &3.
  afield-no_zero = &4.
  append afield to fieldcat.
END-OF-DEFINITION.

DATA: BEGIN OF gt_error OCCURS 0,
        matnr  TYPE zsrm_ztest002-matnr,
        maktx  TYPE zsrm_ztest002-maktx,
        lifnr  TYPE zsrm_ztest002-lifnr,
        name1  TYPE zsrm_ztest002-name1,
        jyfs   TYPE zsrm_ztest002-jyfs,
        message(40) TYPE c.
DATA: END OF gt_error.
DATA: BEGIN OF gx_error_title,
        matnr(18),
        maktx(40),
        lifnr(10),
        name1(35),
        jyfs(10),
        message(40) TYPE c.
DATA: END OF gx_error_title.

SELECTION-SCREEN BEGIN OF BLOCK blk2
                          WITH FRAME TITLE title2.
SELECTION-SCREEN BEGIN OF LINE.
PARAMETER: r1 RADIOBUTTON GROUP gr1 .
SELECTION-SCREEN COMMENT 3(10)  text1.
SELECTION-SCREEN COMMENT 16(5)  text2.
PARAMETER: file LIKE rlgrap-filename .

SELECTION-SCREEN COMMENT 70(10) text4.
PARAMETER: del_box AS CHECKBOX.
SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN BEGIN OF LINE.
PARAMETER: r2 RADIOBUTTON GROUP gr1 DEFAULT 'X'.
SELECTION-SCREEN COMMENT 3(20) text3.
SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN END OF BLOCK blk2.

*&---------------------------------------------------------------------*
*&      INITIALIZATION.
*&---------------------------------------------------------------------*
INITIALIZATION.
  title2 = '待检数据上传\查看'.
  text1  = '待检数据上传'.
  text2  = '文件名'.
  text3  = '待检数据显示'.
  text4  = '全删全建'.
*&---------------------------------------------------------------------*
*&      AT SELECTION-SCREEN ON VALUE-REQUEST
*&---------------------------------------------------------------------*
AT SELECTION-SCREEN ON VALUE-REQUEST FOR file.
  PERFORM get_pc_filename.
*&---------------------------------------------------------------------*
*&      START-OF-SELECTION
*&---------------------------------------------------------------------*
START-OF-SELECTION.
  IF r1 = 'X' AND file IS NOT INITIAL.
    PERFORM upload_file.
    PERFORM data_check.
    IF gt_error[] IS INITIAL.
      PERFORM insert_data.
    ELSE.
      PERFORM popup_error.
    ENDIF.
  ELSEIF r2 = 'X'.
    PERFORM get_ztest002.
    PERFORM show_ztest002.
  ENDIF.
*&---------------------------------------------------------------------*
*&      Form  GET_PC_FILENAME
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM get_pc_filename .
  CALL FUNCTION 'WS_FILENAME_GET'
    EXPORTING
*      mask             = ',*.XLS ,*.XLS.'
      mode             = '0'
      title            = 'Get the file name'
    IMPORTING
      filename         = file
    EXCEPTIONS
      inv_winsys       = 1
      no_batch         = 2
      selection_cancel = 3
      selection_error  = 4
      OTHERS           = 5.
ENDFORM.                    " GET_PC_FILENAME
*&---------------------------------------------------------------------*
*&      Form  UPLOAD_FILE
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM upload_file .
  CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
    EXPORTING
      i_line_header        = 'X'
      i_tab_raw_data       = xltab
      i_filename           = file
    TABLES
      i_tab_converted_data = gt_upload
    EXCEPTIONS
      conversion_failed    = 1
      OTHERS               = 2.
  IF gt_upload[] IS INITIAL.
    MESSAGE '上传表格没有数据' TYPE 'I'.
    STOP.
  ENDIF.
ENDFORM.                    " UPLOAD_FILE
*&---------------------------------------------------------------------*
*&      Form  DATA_CHECK
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM data_check .
  DATA: lt_tmp LIKE STANDARD TABLE OF gt_upload WITH HEADER LINE.

lt_tmp[] = gt_upload[].

  SORT lt_tmp BY matnr lifnr.
  DELETE ADJACENT DUPLICATES FROM lt_tmp COMPARING matnr lifnr.
  IF sy-subrc = 0.
    MESSAGE '存在相同物料号和供应商数据' TYPE 'E'.
  ENDIF.
  REFRESH lt_tmp.

  LOOP AT gt_upload.
    CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
      EXPORTING
        input  = gt_upload-lifnr
      IMPORTING
        output = gt_upload-lifnr.
    CALL FUNCTION 'CONVERSION_EXIT_MATN1_INPUT'
      EXPORTING
        input  = gt_upload-matnr
      IMPORTING
        output = gt_upload-matnr.

SELECT SINGLE maktx INTO gt_upload-maktx FROM makt WHERE matnr = gt_upload-matnr AND spras = sy-langu.
SELECT SINGLE name1 INTO gt_upload-name1 FROM lfa1 WHERE lifnr = gt_upload-lifnr .

MODIFY gt_upload.
ENDLOOP.

LOOP AT gt_upload WHERE maktx = space.

    MOVE-CORRESPONDING gt_upload TO gt_error.
    SHIFT gt_error-matnr LEFT DELETING LEADING '0'.
    SHIFT gt_error-lifnr LEFT DELETING LEADING '0'.
    gt_error-message = '物料号不存在!'.
    APPEND gt_error.
  ENDLOOP.
  LOOP AT gt_upload WHERE name1 = space.
    MOVE-CORRESPONDING gt_upload TO gt_error.
    SHIFT gt_error-matnr LEFT DELETING LEADING '0'.
    SHIFT gt_error-lifnr LEFT DELETING LEADING '0'.
    gt_error-message = '供应商不存在!'.
    APPEND gt_error.
  ENDLOOP.
ENDFORM.                    " DATA_CHECK
*&---------------------------------------------------------------------*
*&      Form  INSERT_DATA
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM insert_data .
  DATA: BEGIN OF lt_ztest002 OCCURS 0.
          INCLUDE STRUCTURE zsrm_ztest002.
  DATA: END  OF lt_ztest002.

  LOOP AT gt_upload.
    MOVE-CORRESPONDING gt_upload TO lt_ztest002.
    APPEND lt_ztest002.
  ENDLOOP.

  lt_ztest002-sydat = sy-datum.
  lt_ztest002-sytim = sy-uzeit.
  lt_ztest002-syusr = sy-uname.
  MODIFY lt_ztest002 TRANSPORTING sydat sytim syusr WHERE sydat IS INITIAL.

  IF del_box = 'X'.
    DELETE FROM zsrm_ztest002 .
    INSERT zsrm_ztest002 FROM TABLE lt_ztest002.
  ELSE.
    MODIFY zsrm_ztest002 FROM TABLE lt_ztest002.
  ENDIF.
  IF sy-subrc = 0.
    MESSAGE '更新成功！' TYPE 'S'.
  ENDIF.

ENDFORM.                    " INSERT_DATA
*&---------------------------------------------------------------------*
*&      Form  GET_ztest002
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM get_ztest002 .

SELECT * INTO TABLE gt_ztest002 FROM zsrm_ztest002.

ENDFORM.                    " GET_ztest002
*&---------------------------------------------------------------------*
*&      Form  SHOW_ztest002
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM show_ztest002 .
  layout-zebra        = 'X'.
  layout-colwidth_optimize = 'X'.

  fill 1   'MATNR'  '物料'        'X'.
  fill 2   'MAKTX'  '描述'        ''.
  fill 3   'LIFNR'  '供应商'        'X'.
  fill 4   'NAME1'  '名称'        ''.
  fill 5   'JYFS'   '检验方式'    ''.
  fill 6   'SYDAT'  '日期'        ''.
  fill 7   'SYTIM'  '时间'        ''.
  fill 8   'SYUSR'  '用户名'      ''.

  CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
    EXPORTING
      is_layout     = layout
      i_save        = 'X'
      it_fieldcat   = fieldcat
*      it_events     = gt_events
    TABLES
      t_outtab      = gt_ztest002  "输出内表
    EXCEPTIONS
      program_error = 1
      OTHERS        = 2.
ENDFORM.                    " SHOW_ztest002
*&---------------------------------------------------------------------*
*&      Form  POPUP_ERROR
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM popup_error .
  DATA: title LIKE  rgsel-dynp_title.
  DATA: l_line TYPE sy-tabix.

  title = '错误信息'.
  gx_error_title-matnr  = '物料编码'.
  gx_error_title-maktx  = '物料描述'.
  gx_error_title-lifnr  = '供应商'.
  gx_error_title-name1  = '供应商名称'.
  gx_error_title-jyfs   = '检验方式'.
  gx_error_title-message = '错误信息'.

  CALL FUNCTION 'G_DISPLAY_SELECTION_DYNPRO'
    EXPORTING
      dynp_title     = title
      show_also_1    = 'X'
      allow_sort     = 'X'
      start_column   = 24
      sel_title1     = gx_error_title      "EXTENDED_DISPLAY = 'X'
    IMPORTING
      sel_index      = l_line "返回双击选中行的index
    TABLES
      sel_table      = gt_error
    EXCEPTIONS
      no_lines       = 1
      no_line_picked = 2
      OTHERS         = 3.
ENDFORM.                    " POPUP_ERROR

0 0