使用excel动态上传数据至库表

开发技术：

1.文本文件上载技术

2.动态程序代码生成技术

3.ABAP动态程序执行技术

4.TXT文本文件对应用Table字段编辑技术

 

注意事项：

文件文件编辑过程中时间日期格式为 20060201 120000 表示 2006.02.01 12：00：00

文本文件中不能包含除字符，数字之外的其他特殊符号。假如Table中对应的字段数据为空时，在编辑数据时也对应该列为空。

EXCEL 文件必须带表头。（不带表头的可以修改下程序）

 

代码如下：

TABLES : dd03l,dd02t.

*------------------------------------------------------------*

*    内表

*-----------------------------------------------------------------*

DATA: BEGIN OF tab OCCURS 10,

        fcode(4),

      END OF tab.

DATA : BEGIN OF itab OCCURS 0,

        chk(1) TYPE c,                     " check box

        tabname    LIKE dd03l-tabname,     " Table name

        fieldname  LIKE dd03l-fieldname,   " Feld Name

        position   LIKE dd03l-position,    " Table

        keyflag    LIKE dd03l-keyflag,     " Primary Key

        datatype   LIKE dd03l-datatype,    " Data Type

        intlen     LIKE dd03l-leng,

       END OF itab.

DATA itab1 LIKE itab OCCURS 0 WITH HEADER LINE.

DATA : BEGIN OF uptab OCCURS 0,

*        text(72) TYPE c,

        text(100) TYPE c,

       END OF uptab.

DATA: BEGIN OF error_message,

        line1(72),

        line2(72),

        line3(72),

      END OF error_message.

DATA : cnt1(8) TYPE c,

       fcode LIKE sy-ucomm,

       changed LIKE s38e-buf_varied,

       prog(8) TYPE c,

       msg(120) TYPE c,

       er_include LIKE sy-repid,

       er_line    LIKE sy-index,

       er_off     LIKE sy-tabix,

       er_subrc   LIKE sy-subrc.

*------------------------------------------------------------------*

*    选择画面

*------------------------------------------------------------------*

PARAMETERS : tabname LIKE dd03l-tabname.

*------------------------------------------------------------------*

*    START-OF-SELECTION                                                *

*-------------------------------------------------------------------*

START-OF-SELECTION.

  IF tabname IS INITIAL.

    MESSAGE '请输入数据表名称！' TYPE 'S' DISPLAY LIKE 'E'.

    EXIT.

  ENDIF.

*上载Table字段分析

  SELECT SINGLE * FROM dd02t WHERE tabname = tabname.

  IF sy-subrc <> 0.

    MESSAGE '请输入正确的数据表名称！' TYPE 'S' DISPLAY LIKE 'E'.

    EXIT.

  ENDIF.

  SET PF-STATUS 'CBO'.

  SELECT * INTO CORRESPONDING FIELDS OF TABLE itab FROM dd03l

              WHERE tabname = tabname

              AND as4local = 'A'

              ORDER BY position.

  itab-chk = 'X'.

  MODIFY itab INDEX 1 TRANSPORTING chk.

*动态上载程序代码生成函数

  PERFORM generate_upload_code.

*ABAP程序代码编辑器调用

  PERFORM edit_generator_code.

AT USER-COMMAND .

  IF sy-ucomm = 'EDIT'.

    PERFORM edit_generator_code.

  ELSEIF sy-ucomm = 'EXEC'.

    GENERATE SUBROUTINE POOL uptab NAME prog MESSAGE msg.

    IF msg IS NOT INITIAL.

      MESSAGE msg TYPE 'S' DISPLAY LIKE 'E'.

      EXIT.

    ENDIF.

*程序代码语法检测

    PERFORM chcek_syntax_error.

*动态程序代码执行

    PERFORM dyn1 IN PROGRAM (prog).

  ELSEIF ( sy-ucomm = 'EXIT' OR sy-ucomm = 'BACK' OR sy-ucomm = 'CANC' ).

    EXIT.

  ENDIF.

*&---------------------------------------------------------------*

*&      Form  generate_upload_code

*&---------------------------------------------------------------*

FORM generate_upload_code.

  REFRESH uptab.

  uptab-text = 'REPORT ZLM_UPLOAD_TABLE.'.

  APPEND uptab.

  CONCATENATE 'TABLES :' tabname '.' INTO uptab-text

  SEPARATED BY space.

  APPEND uptab.

  uptab-text = 'TYPE-POOLS: truxs.'.

  APPEND uptab.

  uptab-text = ' DATA: wa_row TYPE truxs_t_text_data.'.

  APPEND uptab.

  uptab-text = 'DATA : BEGIN OF UPTAB OCCURS 0,'.

  APPEND uptab.

  LOOP AT itab WHERE chk = ''.

    CLEAR uptab.

    CONCATENATE '  ' itab-fieldname '(' itab-intlen  ')'  ' TYPE C ,'

                INTO uptab-text+10(80).

    APPEND uptab.

  ENDLOOP.

  uptab-text+6(82) = 'END OF UPTAB.'.

  APPEND uptab.

  uptab-text = 'DATA : BEGIN OF RESULT OCCURS 0.'.

  APPEND uptab.

  CLEAR uptab.

  CONCATENATE 'INCLUDE STRUCTURE' itab-tabname '.'

               INTO uptab-text+10(80) SEPARATED BY space.

  APPEND uptab.

  uptab-text = 'DATA : END OF RESULT.'.

  APPEND uptab.

  uptab-text = 'data:filename type rlgrap-filename.'.

  APPEND uptab.

  uptab-text = 'FORM DYN1.'.

  APPEND uptab.

  uptab-text = 'CALL FUNCTION ''WS_FILENAME_GET'''.

  APPEND uptab.

  uptab-text = ' EXPORTING'.

  APPEND uptab.

  uptab-text = '  def_filename     = '''''.

  APPEND uptab.

  uptab-text = '  def_path         = ''D:\'''.

  APPEND uptab.

  uptab-text = '  MASK             = '',*.xls,*.xls;,*.xlsx,*.xlsx.'''.

  APPEND uptab.

  uptab-text = '  MODE             = ''O'''.

  APPEND uptab.

  uptab-text = '  TITLE            = ''UPLOAD file'''.

  APPEND uptab.

  uptab-text = ' IMPORTING'.

  APPEND uptab.

  uptab-text = '   filename        = filename'.

  APPEND uptab.

  uptab-text = ' EXCEPTIONS'.

  APPEND uptab.

  uptab-text = '  inv_winsys       = 1'.

  APPEND uptab.

  uptab-text = '  no_batch         = 2'.

  APPEND uptab.

  uptab-text = '  selection_cancel = 3'.

  APPEND uptab.

  uptab-text = '  selection_error  = 4'.

  APPEND uptab.

  uptab-text = '  OTHERS           = 5.'.

  APPEND uptab.

  CLEAR uptab.

  uptab-text+2(88) = 'CALL FUNCTION ''TEXT_CONVERT_XLS_TO_SAP'''.

  APPEND uptab.

  CLEAR uptab.

  uptab-text+4(86) = 'EXPORTING'.

  APPEND uptab.

  CLEAR uptab.

  uptab-text+6(84) = 'i_filename  = filename'.

  APPEND uptab.

  CLEAR uptab.

  uptab-text+6(84) = 'i_line_header = ''X'' "excel中不带表头的，把此处X改为space'.

  APPEND uptab.

  uptab-text+6(84) = 'i_tab_raw_data  = wa_row'.

  APPEND uptab.

  uptab-text+4(86) = 'TABLES'.

  APPEND uptab.

  CLEAR uptab.

  uptab-text+6(84) = 'i_tab_converted_data  = UPTAB'.

  APPEND uptab.

  uptab-text = '   EXCEPTIONS    '.

  APPEND uptab.

  uptab-text = '     conversion_failed = 1     '.

  APPEND uptab.

  uptab-text = '     OTHERS = 2.'.

  APPEND uptab.

  uptab-text = '   IF sy-subrc <> 0.   '.

  APPEND uptab.

  uptab-text = '     MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno '.

  APPEND uptab.

  uptab-text = '     WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.   '.

  APPEND uptab.

  uptab-text = '    ENDIF. '.

  APPEND uptab.

  uptab-text+2(88) = 'LOOP AT UPTAB.'.

  APPEND uptab.

  CLEAR uptab.

  uptab-text+4(86) = 'CLEAR RESULT.'.

  APPEND uptab.

  uptab-text+4(86) = 'MOVE-CORRESPONDING UPTAB TO RESULT.'.

  APPEND uptab.

  uptab-text+4(86) = 'APPEND RESULT.'.

  APPEND uptab.

  uptab-text+2(88) = 'ENDLOOP.'.

  APPEND uptab.

  CLEAR uptab.

  CONCATENATE 'MODIFY'  itab-tabname 'FROM TABLE RESULT.'

              INTO uptab-text+2(88) SEPARATED BY space.

  APPEND uptab.

  uptab-text = '  DATA:LINE TYPE string.'.

  APPEND uptab.

  uptab-text = ' DATA:msg TYPE string.'.

  APPEND uptab.

  uptab-text = ' LINE = LINES( result ).'.

  APPEND uptab.

  uptab-text = ' CONCATENATE  ''成功导入''  LINE ''条数据！''  INTO msg.'.

  APPEND uptab.

  uptab-text = '  MESSAGE    msg TYPE ''I''.'.

  APPEND uptab.

  uptab-text = 'ENDFORM.' .

  APPEND uptab.

  CLEAR uptab.

ENDFORM.                    " generate_upload_code

*&---------------------------------------------------------------*

*&      Form  edit_generator_code

*&---------------------------------------------------------------*

FORM edit_generator_code.

  CALL FUNCTION 'EDITOR_APPLICATION'

    EXPORTING

      application = 'BF'

      display     = ' '

      name        = 'Source Code.....'

    IMPORTING

      fcode       = fcode

      changed     = changed

    TABLES

      content     = uptab.

  LOOP AT uptab.

    WRITE:/1 uptab-text.

  ENDLOOP.

ENDFORM.                               " PRINT_GENERATOR_CODE

*&---------------------------------------------------------------*

*&      Form  chcek_syntax_error

*&---------------------------------------------------------------*

FORM chcek_syntax_error.

  CALL FUNCTION 'EDITOR_SYNTAX_CHECK'

       EXPORTING

            i_global_check   = sy-calld

            i_global_program = sy-repid

            i_program        = 'ZUPLOAD_TABLE'

*            i_r2_check       = ' '

*            i_r2_destination = ' '

*            i_trdir          = ' '

       IMPORTING

            o_error_include  = er_include

            o_error_line     = er_line

            o_error_message  = error_message

            o_error_offset   = er_off

            o_error_subrc    = er_subrc

       TABLES

            i_source         = uptab.

  IF er_subrc <> 0.

    er_line = er_line - 2.

    WRITE:/1 'Error Line : ',er_line.

    WRITE:/1 error_message-line1,error_message-line2,

             error_message-line3.

    STOP.

  ENDIF.

ENDFORM.                    " chcek_syntax_error

代码中用到的status跟上篇文章中一样，

步骤也一样