SAP ABAP 数据批量导入
*&---------------------------------------------------------------------*
*& Report YTEST_LJM_01
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT YTEST_LJM_01.
*&---------------------------------------------------------------------*
*& DEFINE INCLUDE
*&---------------------------------------------------------------------*
INCLUDE ytest_ljm_cmxlsup.
INCLUDE ytest_ljm_01_top."Top
INCLUDE ytest_ljm_01_f01."Form
*&---------------------------------------------------------------------*
* AT SELECTION-SCREEN
*&---------------------------------------------------------------------*
AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FILE.
* 得到文件路径
PERFORM FRM_GET_FILE_PATH USING P_FILE.
AT SELECTION-SCREEN OUTPUT.
* 设置屏幕字段必输标识
PERFORM FRM_CFM_OUTPUT.
*&---------------------------------------------------------------------*
* START-OF-SELECTION
*&---------------------------------------------------------------------*
START-OF-SELECTION.
* 检查必输字段
PERFORM FRM_CHECK_REQUIRED USING P_FILE.
* 处理导入数据
IF P_FILE <> ''.
PERFORM FRM_PROCESS_DATA.
* 使用ALV显示数据
PERFORM FRM_DISPLAY_DATA.
ENDIF.
*&---------------------------------------------------------------------*
*& 包含 YTEST_LJM_CMXLSUP
*&---------------------------------------------------------------------*
*&---------------------------------------------------------------------*
*& Form FRM_GET_FILE_PATH
*&---------------------------------------------------------------------*
* text：取得文件路径
*----------------------------------------------------------------------*
* u_file:选择界面输入的文件路径
*----------------------------------------------------------------------*
FORM frm_get_file_path USING u_file.
DATA: lt_filetab TYPE filetable.
DATA: lv_rc TYPE i.
* 取得文件路径
CALL METHOD cl_gui_frontend_services=>file_open_dialog
EXPORTING
window_title = '导入文件'
default_extension = '*.xls'
file_filter = cl_gui_frontend_services=>filetype_excel
CHANGING
file_table = lt_filetab
rc = lv_rc
EXCEPTIONS
file_open_dialog_failed = 1
cntl_error = 2
error_no_gui = 3
not_supported_by_gui = 4
OTHERS = 5.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
IF lt_filetab IS NOT INITIAL.
READ TABLE lt_filetab INTO u_file INDEX 1.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& 包含 YTEST_LJM_01_TOP
*&---------------------------------------------------------------------*
TABLES:sflight.
*结构
TYPES:BEGIN OF ty_sflight,
carrid TYPE char3,
connid TYPE char4,
price TYPE char15,
icon(4),
msg(255),
END OF ty_sflight.
* 内表及工作区间
DATA:gt_sflight TYPE STANDARD TABLE OF ty_sflight.
* ALV布局结构
DATA:gs_layout TYPE lvc_s_layo,
gt_fieldcat TYPE lvc_t_fcat,
gs_fieldcat LIKE LINE OF gt_fieldcat,
gv_repid TYPE syrepid.
* 选择屏幕
SELECTION-SCREEN BEGIN OF BLOCK blk_1 WITH FRAME TITLE TEXT-t01.
PARAMETERS:p_file TYPE string MODIF ID m1.
SELECTION-SCREEN END OF BLOCK blk_1.
*&---------------------------------------------------------------------*
*& 包含 YTEST_LJM_01_F01
*&---------------------------------------------------------------------*
*&---------------------------------------------------------------------*
*& Form FRM_CFM_OUTPUT
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM frm_cfm_output .
LOOP AT SCREEN.
* 必输项
IF screen-group1 = 'M1'.
screen-required = '2'.
MODIFY SCREEN.
ENDIF.
ENDLOOP.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_CHECK_REQUIRED
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> P_FILE
*&---------------------------------------------------------------------*
FORM frm_check_required USING p_p_file.
* 上传路径为空，则报错，并退出
IF p_file IS INITIAL.
MESSAGE '上传文件路径不能为空' TYPE 'E'.
STOP.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_PROCESS_DATA
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM frm_process_data .
* 上传文件
PERFORM frm_process_file.
* 录入数据库
PERFORM frm_handing_data.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_PROCESS_FILE
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM frm_process_file .
DATA:lv_file TYPE rlgrap-filename.
DATA:lv_ln TYPE i.
DATA:lv_filename TYPE rlgrap-filename,
lt_data_tab TYPE truxs_t_text_data.
lv_file = p_file.
* 上载模板数据
CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
EXPORTING
text = '数据加载中'.
* 调用函数上载excel数据
CALL FUNCTION 'GUI_UPLOAD'
EXPORTING
filename = p_file
filetype = 'ASC'
has_field_separator = cl_abap_char_utilities=>horizontal_tab
TABLES
data_tab = lt_data_tab
EXCEPTIONS
file_open_error = 1
file_read_error = 2
no_batch = 3
gui_refuse_filetransfer = 4
invalid_type = 5
no_authority = 6
unknown_error = 7
bad_data_format = 8
header_not_allowed = 9
separator_not_allowed = 10
header_too_long = 11
unknown_dp_error = 12
access_denied = 13
dp_out_of_memory = 14
disk_full = 15
dp_timeout = 16
OTHERS = 17.
IF sy-subrc EQ 0.
lv_filename = p_file.
CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
EXPORTING
i_tab_raw_data = lt_data_tab
i_filename = lv_filename
TABLES
"读取到的数据存入此处
i_tab_converted_data = gt_sflight
EXCEPTIONS
conversion_failed = 1
OTHERS = 2.
IF sy-subrc NE 0.
MESSAGE ID sy-msgid TYPE 'S' NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4
DISPLAY LIKE 'E'.
LEAVE LIST-PROCESSING.
ENDIF.
ELSE.
MESSAGE ID sy-msgid TYPE 'S' NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4
DISPLAY LIKE 'E'.
LEAVE LIST-PROCESSING.
ENDIF.
* 得到file contents 行数
DESCRIBE TABLE gt_sflight LINES lv_ln.
* 如果只有Header行，则报错
IF lv_ln LT 1.
MESSAGE '数据为空！' TYPE 'S' DISPLAY LIKE 'E'.
LEAVE LIST-PROCESSING.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_HANDING_DATA
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM frm_handing_data .
DATA:lt_sflight TYPE STANDARD TABLE OF sflight,
lwa_list LIKE LINE OF lt_sflight.
DATA:lt_comp TYPE cl_abap_structdescr=>component_table,
lwa_comp LIKE LINE OF lt_comp.
DATA:lwa_sflight LIKE LINE OF gt_sflight.
DATA:lv_str TYPE string.
FIELD-SYMBOLS:<lfs_ex> TYPE any,
<lfs_fd> TYPE any.
LOOP AT gt_sflight ASSIGNING FIELD-SYMBOL(<fs_sflight>) FROM 2.
<fs_sflight>-icon = icon_green_light.
lwa_sflight = <fs_sflight>.
LOOP AT lt_comp INTO lwa_comp.
TRY.
IF lwa_comp-name EQ 'ICON' OR lwa_comp-name EQ 'MSG'.
CONTINUE.
ELSE.
ASSIGN COMPONENT lwa_comp-name OF STRUCTURE lwa_list TO <lfs_fd>.
ASSIGN COMPONENT lwa_comp-name OF STRUCTURE lwa_sflight TO <lfs_ex>.
IF <lfs_ex> IS INITIAL.
CONTINUE.
ELSE.
<lfs_fd> = <lfs_ex>.
IF LWA_COMP-NAME EQ 'CONNID'.
CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
EXPORTING
INPUT = <LFS_FD>
IMPORTING
OUTPUT = <LFS_FD>.
ENDIF.
ENDIF.
ENDIF.
CATCH cx_sy_conversion_error.
lwa_sflight-icon = icon_red_light.
READ TABLE gt_fieldcat INTO gs_fieldcat
WITH KEY fieldname = lwa_comp-name.
MESSAGE s093(zsd) INTO lv_str
WITH gs_fieldcat-scrtext_l <lfs_ex>.
lwa_sflight-msg = lwa_sflight-msg && '|' && lv_str.
ENDTRY.
CLEAR:lwa_comp.
ENDLOOP.
APPEND lwa_list TO lt_sflight.
CLEAR:lwa_sflight,lwa_list.
ENDLOOP.
* MODIFY sflight FROM TABLE lt_sflight.
MESSAGE '导入成功' TYPE 'S'.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_DISPLAY_DATA
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM frm_display_data .
DATA:lt_event TYPE slis_t_event,
ls_event LIKE LINE OF lt_event.
DATA(lt_fieldcat) = VALUE lvc_t_fcat( ).
* 载入ALV需要显示的列
PERFORM frm_append_col CHANGING lt_fieldcat.
* 给layout赋值
gs_layout-cwidth_opt = abap_true.
gs_layout-zebra = abap_true.
gs_layout-no_rowmark = abap_true.
gv_repid = sy-repid.
* 调用function module显示ALV
CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY_LVC'
EXPORTING
* i_callback_pf_status_set = 'FRM_PF_STATUS_PRT'
i_callback_program = sy-repid
is_layout_lvc = gs_layout
it_fieldcat_lvc = lt_fieldcat
i_save = 'A'
TABLES
t_outtab = gt_sflight
EXCEPTIONS
program_error = 1
OTHERS = 2.
IF sy-subrc <> 0.
* Implement suitable error handling here
MESSAGE ID sy-msgid
TYPE sy-msgty
NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_APPEND_COL
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM frm_append_col CHANGING c_fieldcat TYPE lvc_t_fcat.
DEFINE mcr_def_field.
APPEND VALUE lvc_s_fcat( fieldname = &1
reptext = &2
edit = &3
do_sum = &4
no_zero = &5
col_opt = 'X'
) TO c_fieldcat.
END-OF-DEFINITION.
mcr_def_field:
'CARRID' '航线ID' '' '' '',
'CONNID' '航班ID' '' '' '',
'PRICE' '航空运费' '' '' '',
'ICON' '状态' '' '' '',
'MSG' '消息' '' '' ''.
ENDFORM.
EXCEL模板：
导入成功后ALV展示导入的数据：
