sap abap 最简单的excle 导入程序

程序源代码：

REPORT  yfir0042  NO STANDARD PAGE HEADING
MESSAGE-ID 1000 LINE-SIZE 120 LINE-COUNT 65.

INCLUDE y_alv_func.
INCLUDE zabap_public.
INCLUDE <icon>.
TABLES : yfilob02,csks,ska1.
DATA:BEGIN OF itab OCCURS 0.
        INCLUDE STRUCTURE yfilob02.
DATA END OF itab.
DATA:BEGIN OF it_show OCCURS 0.
        INCLUDE STRUCTURE yfilob02.
DATA END OF it_show.

DATA:exceltab LIKE alsmex_tabline OCCURS 0 WITH HEADER LINE.
**********input field begin ******************
SELECTION-SCREEN BEGIN OF BLOCK b1  WITH FRAME  TITLE text-t01.
SELECTION-SCREEN BEGIN OF LINE.
*查询
PARAMETERS:p_cx TYPE c RADIOBUTTON GROUP g1 USER-COMMAND g2 .
SELECTION-SCREEN COMMENT 3(15) text-b01 FOR FIELD p_cx.
SELECTION-SCREEN POSITION 20.
*导入
PARAMETERS:p_dr TYPE c RADIOBUTTON GROUP g1     .
SELECTION-SCREEN COMMENT 23(15) text-b02 FOR FIELD p_dr.
SELECTION-SCREEN POSITION 40.
SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN END OF BLOCK b1.

SELECTION-SCREEN BEGIN OF BLOCK b2 WITH FRAME TITLE text-t02  .
PARAMETERS:p_period LIKE  yfilob02-period  DEFAULT sy-datum(6)   .
PARAMETERS:p_fname LIKE rlgrap-filename MODIF ID g2   .
SELECT-OPTIONS:p_ktopl FOR  yfilob02-ktopl MODIF ID g3.
SELECT-OPTIONS:p_saknr FOR  ska1-saknr MODIF ID g3.
SELECT-OPTIONS:p_kostl FOR csks-kostl MODIF ID g3.
SELECT-OPTIONS:p_prctr FOR  yfilob02-prctr MODIF ID g3.
SELECTION-SCREEN END OF BLOCK b2.

AT SELECTION-SCREEN OUTPUT.

  LOOP AT SCREEN.
    CASE screen-group1.
      WHEN 'G2'.
        IF p_dr = 'X'  .
          screen-active = '1'.
        ELSE.
          screen-active = '0'.
        ENDIF.
      WHEN 'G3'.
        IF p_cx = 'X'  .
          screen-active = '1'.
        ELSE.
          screen-active = '0'.
        ENDIF.

    ENDCASE.
    MODIFY SCREEN.
  ENDLOOP.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR  p_fname.
  CALL FUNCTION 'WS_FILENAME_GET'
    EXPORTING
      def_filename     = ''
      def_path         = 'D:/'
      mask             = ',*.xls,*.xls;,*.xlsx,*.xlsx.'
      mode             = 'O'
      title            = 'Upload file'
    IMPORTING
      filename         = p_fname
    EXCEPTIONS
      inv_winsys       = 1
      no_batch         = 2
      selection_cancel = 3
      selection_error  = 4
      OTHERS           = 5.

 

INITIALIZATION.
  p_cx  = 'X'.

START-OF-SELECTION.

  IF p_period  IS INITIAL.
    my_exit '请输入期间' ''.
  ENDIF.
***read data
  PERFORM select_data.
  IF p_dr = 'X'.
***manage excledata
    PERFORM manage_data.
    IF exitflag > 0 .
      EXIT.
    ENDIF.
***SAVE data
    PERFORM save_data.
  ENDIF.

*&---------------------------------------------------------------------*
*&      Form  select_data
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM select_data .
  CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
    EXPORTING
      percentage = 1
      text       = '开始读取数据'.
  IF p_dr = 'X'.
    CALL FUNCTION 'Y_READ_XLS'
      EXPORTING
        fname    = p_fname
      TABLES
        exceltab = exceltab.
  ELSE.

    CLEAR it_show[].
    SELECT * FROM yfilob02
    INTO CORRESPONDING FIELDS OF TABLE it_show
    WHERE  period = p_period
    AND  kostl IN p_kostl
    AND prctr IN p_prctr
   AND ktopl IN  p_ktopl
   AND saknr IN p_saknr.

    LOOP AT it_show.
      SHIFT it_show-saknr LEFT DELETING   LEADING  '0'.
      SHIFT it_show-kostl LEFT DELETING   LEADING  '0'.
      SHIFT it_show-prctr LEFT DELETING   LEADING  '0'.
      MODIFY it_show.
    ENDLOOP.
    PERFORM show_data.
  ENDIF.
ENDFORM.                    " select_data

 

*&---------------------------------------------------------------------*
*&      Form  manage_data
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM manage_data .
  DATA:l_date(10).
  CLEAR exitflag.
  SORT exceltab BY row col.
  DATA:i TYPE i.
  DATA: stemp(4) TYPE n.
  LOOP AT exceltab.
*    TRANSLATE EXCELTAB-VALUE TO UPPER CASE.
*MATNR
*JHZQ
*WLFL
    CLEAR  stemp .
    stemp =  exceltab-col.

 

    CASE stemp .

      WHEN 1. itab-ktopl = exceltab-value.
      WHEN 2. itab-saknr = exceltab-value.
      WHEN 3. itab-kostl = exceltab-value.
      WHEN 4. itab-prctr = exceltab-value.
      WHEN 5. itab-txt20 = exceltab-value.
      WHEN 6. itab-cktext = exceltab-value.
      WHEN 7. itab-lktext = exceltab-value.
      WHEN 8. itab-base = exceltab-value.
      WHEN 9. itab-basetype = exceltab-value.
      WHEN 10. itab-del  = exceltab-value.

 

      WHEN OTHERS.
        my_exit '导入文件格式不正确！' 'I'.
    ENDCASE.

    AT END OF row.
      IF itab-kostl <> ''.
        i = i + 1.
        msg = i .
        CONCATENATE '处理第' msg '笔数据！' INTO msg.
        CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
          EXPORTING
            percentage = 1
            text       = msg.

        APPEND itab.
        CLEAR itab.
      ENDIF.
    ENDAT.
  ENDLOOP.

ENDFORM.                    " manage_data

*&---------------------------------------------------------------------*
*&      Form  output_data
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM save_data .
  DATA: l_datum LIKE sy-datum,
       l_modti LIKE sy-uzeit,
       l_uname LIKE sy-uname,
       l_flag,
       maxver LIKE ymara_sd_history-version.

  CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
    EXPORTING
      percentage = 1
      text       = '开始保存数据'.
**  处理本次导入数据

  LOOP AT itab.
    CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
      EXPORTING
        input  = itab-kostl
      IMPORTING
        output = itab-kostl.

    SELECT SINGLE kostl FROM csks
    INTO itab-kostl
    WHERE kostl =  itab-kostl.
    IF sy-subrc <> '0'.
      DELETE itab.
      CONCATENATE 'SAP系统中没有成本中心:' itab-kostl INTO msg.
      MESSAGE msg TYPE 'I'.
      CONTINUE.
    ENDIF.

    CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
      EXPORTING
        input  = itab-saknr
      IMPORTING
        output = itab-saknr.

    SELECT SINGLE saknr FROM ska1
    INTO itab-saknr
    WHERE saknr =  itab-saknr.
    IF sy-subrc <> '0'.
      DELETE itab.
      CONCATENATE 'SAP系统中没有总账科目:' itab-kostl INTO msg.
      MESSAGE msg TYPE 'I'.
      CONTINUE.
    ENDIF.
    IF   exitflag > 0.
      DELETE itab.
      CONTINUE.
    ENDIF.
    CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
      EXPORTING
        input  = itab-prctr
      IMPORTING
        output = itab-prctr.

    itab-period = p_period.
    MODIFY itab.
  ENDLOOP.
  IF itab[] IS NOT INITIAL.
    DELETE FROM yfilob02 WHERE period = p_period .
  ENDIF.
  MODIFY yfilob02 FROM TABLE itab.
  DATA: s TYPE string.
  IF sy-subrc = 0.
    COMMIT WORK.
    s = LINES( itab ).
    CONCATENATE '成功导入：' s '条数据！' INTO s.
    MESSAGE i899 WITH s.

  ELSE.
    my_exit '没有数据被更新！' 'I'.
  ENDIF.

ENDFORM.                    " output_data
*&---------------------------------------------------------------------*
*&      Form  show_data
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM show_data .
  DATA title TYPE string.
  title = LINES( it_show ).
  CONCATENATE  '记录共计：'   title '条.' INTO title.
  alv_set_title  title.
  alv_clear_data.
  alv_add_data 'IT_SHOW'  '' ' ' '' 'X' 'CHAR' 'KTOPL' '帐目表'.
  alv_add_data 'IT_SHOW'  '' ' ' '' 'X' 'CHAR' 'SAKNR' '总帐科目编号'.
  alv_add_data 'IT_SHOW'  '' ' ' '' 'X' 'CHAR' 'KOSTL' '成本中心'.
  alv_add_data 'IT_SHOW'  '' ' ' '' 'X' 'CHAR' 'PRCTR' '利润中心'.
  alv_add_data 'IT_SHOW'  '' ' ' '' 'X' 'CHAR' 'PERIOD' '期间'.
  alv_add_data 'IT_SHOW'  '' ' ' '' 'X' 'CHAR' 'TXT20' '标准的文本'.
  alv_add_data 'IT_SHOW'  '' ' ' '' 'X' 'CHAR' 'CKTEXT' '成本中心描述'.
  alv_add_data 'IT_SHOW'  '' ' ' '' 'X' 'CHAR' 'LKTEXT' '利润中心描述'.
  alv_add_data 'IT_SHOW'  '' ' ' '' 'X' 'CHAR' 'BASE' '基数'.
  alv_add_data 'IT_SHOW'  '' ' ' '' 'X' 'CHAR' 'BASETYPE' '基数类型'.
  alv_add_data 'IT_SHOW'  '' ' ' '' 'X' 'CHAR' 'DEL' '删除标识'.

  alv_show_data it_show.
ENDFORM.                    " SHOW_DATA

*&---------------------------------------------------------------------*
*&      Form  user_command
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->R_UCOMM      text
*      -->RS_SELFIELD  text
*----------------------------------------------------------------------*
FORM user_command  USING r_ucomm LIKE sy-ucomm
                           rs_selfield TYPE slis_selfield.

  CASE r_ucomm.
    WHEN '&IC1'.
*      MESSAGE    rs_selfield-value TYPE 'I'.
  ENDCASE.
ENDFORM.                    "user_command

 

 

 

函数源代码 

 

function y_read_xls .
*"----------------------------------------------------------------------
*"*"Local interface:
*"  IMPORTING
*"     REFERENCE(FNAME) LIKE  RLGRAP-FILENAME
*"     REFERENCE(BEGINCOL) TYPE  I DEFAULT 1
*"     REFERENCE(ENDCOL) TYPE  I DEFAULT 50
*"     REFERENCE(BEGINROW) TYPE  I DEFAULT 2
*"     REFERENCE(ENDROW) TYPE  I DEFAULT 6000
*"  TABLES
*"      EXCELTAB STRUCTURE  ALSMEX_TABLINE
*"----------------------------------------------------------------------
  tables:tbslt.
  data:fname1 like rlgrap-filename .
  data: h_mask(20) type  c .
  data: result   type i.
  data: index(2) type n value '01',
        view_name(20) type c value 'MSICHTAUSW-KZSEL(00)'.
  data: view_name1 type c.
  data: view(14) value 'KCVEDPAFLSQBGX'.

  data: ok_code like sy-ucomm.
  data: i type i.
  data: statmk,statmv,statme,statmc.
  data: flength type i.

  fname1 = fname.

*  call function 'WS_FILENAME_GET'
*    exporting
*      def_filename     = ''
*      def_path         = ''
*      mask             = h_mask
*      mode             = 'O'
*    importing
*      filename         = fname1
*    exceptions
*      inv_winsys       = 1
*      no_batch         = 2
*      selection_cancel = 3
*      selection_error  = 4
*      others           = 5.

  call function 'WS_QUERY'
    exporting
      filename       = fname1
      query          = 'FE'
    importing
      return         = result
    exceptions
      inv_query      = 1
      no_batch       = 2
      frontend_error = 3
      others         = 4.
  if result = 0.
*    message e999 with fname1 '上传文件不存在!'.
    stop.
  endif.

  call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    exporting
      filename                = fname1
      i_begin_col             = begincol
      i_begin_row             = beginrow
      i_end_col               = endcol
      i_end_row               = endrow
    tables
      intern                  = exceltab
    exceptions
      inconsistent_parameters = 1
      upload_ole              = 2
      others                  = 3.
  if sy-subrc <> 0.
*    message e000 with 'UPLOAD ERROR!'.
  endif.
  endfunction.

 

 

开发包源代码：

*&---------------------------------------------------------------------*
*&  包括                ZABAP_PUBLIC
*&---------------------------------------------------------------------*
DATA:exitflag TYPE i,ucomm LIKE sy-ucomm,msg TYPE string,mt TYPE c.
DEFINE my_exit.
  exitflag = exitflag + 1.
  mt = &2.
  translate mt to upper case.
  msg = &1.
  if mt is initial . mt = 'S'. endif.
  rollback work.
  CALL FUNCTION 'BUFFER_REFRESH_ALL'.
  message msg type mt.
  return.
END-OF-DEFINITION.