场景:在报表展示页面增加一个导入的按钮,点击导入按钮触发文件查看系统。
话不多说,上代码:
这是主要流程:选择文件->读取文件数据->插入数据。
save_code = ok_code.
CLEAR ok_code.
CASE save_code. "屏幕200的按钮事件调用
WHEN 'EXIT'.
LEAVE PROGRAM.
WHEN 'RETRUN'.
LEAVE TO SCREEN 100.
WHEN 'IMPORTING'."导入
PERFORM frm_f4_filename. "打开文件系统,选择文件
PERFORM frm_upload_data. "读取文件数据
PERFORM tips_window. "提示窗口
PERFORM add_data USING lv_yn gt_alv. "批量插入数据
WHEN OTHERS.
ENDCASE.
选择文件系统代码:
*定义p_path类型。
DATA:p_path LIKE rlgrap-filename.
*&---------------------------------------------------------------------*
*& Form FRM_F4_FILENAME
*&---------------------------------------------------------------------*
*& 文件搜索帮助
*&---------------------------------------------------------------------*
FORM frm_f4_filename .
CALL FUNCTION 'F4_FILENAME'
* EXPORTING
* PROGRAM_NAME = SYST-CPROG
* DYNPRO_NUMBER = SYST-DYNNR
* FIELD_NAME = ' '
IMPORTING
file_name = p_path. "p_path为文件路径+文件名
ENDFORM.
读取文件数据代码:
*&---------------------------------------------------------------------*
*& Form FRM_UPLOAD_DATA
*&---------------------------------------------------------------------*
*& 上传Excel数据到内表
*&---------------------------------------------------------------------*
FORM frm_upload_data .
DATA: lt_exc_data TYPE zalsmex_tabline OCCURS 0 WITH HEADER LINE,
lv_index TYPE sy-tabix.
DATA: BEGIN OF lt_split OCCURS 0,
field TYPE c LENGTH 50,
END OF lt_split.
FIELD-SYMBOLS: <fs_value>.
CLEAR: lt_exc_data[],
gt_alv,
gs_alv.
* 判断文件是否合法
IF p_path IS INITIAL.
CLEAR message_error.
message_error = '未选择任何文件!'.
PERFORM error_window.
ELSE.
FIND '.xls' IN p_path.
IF sy-subrc <> 0.
CLEAR message_error.
message_error = '请选择“.xls”或“.xlsx”文件进行导入!'.
PERFORM error_window.
ENDIF.
FIND '.xlsx' IN p_path.
IF sy-subrc <> 0.
CLEAR message_error.
message_error = '请选择“.xls”或“.xlsx”文件进行导入!'.
PERFORM error_window.
ENDIF.
ENDIF.
CALL FUNCTION 'ZALSM_EXCEL_TO_INTERNAL_TABLE' "调用函数读取excel表格数据到lt_exc_data
EXPORTING
filename = p_path "文件路径
i_begin_col = 1 "第一列开始
i_begin_row = 2 "第二行开始
i_end_col = 200 "第200列结束
i_end_row = 50000 "第50000行结束
TABLES
intern = lt_exc_data
EXCEPTIONS
inconsistent_parameters = 1
upload_ole = 2
OTHERS = 3.
IF sy-subrc <> 0.
EXIT.
ENDIF.
LOOP AT lt_exc_data. "将lt_exc_data的数据遍历到gt_alv内表中
lv_index = lt_exc_data-col.
ASSIGN COMPONENT lv_index OF STRUCTURE gs_alv TO <fs_value>.
MOVE lt_exc_data-value TO <fs_value>.
CLEAR lt_exc_data.
AT END OF row.
APPEND gs_alv TO gt_alv.
CLEAR gs_alv.
ENDAT.
ENDLOOP.
ENDFORM.
这里是提示确认弹窗的代码:
*&---------------------------------------------------------------------*
*& Form tips_window
*&---------------------------------------------------------------------*
*& "弹出确认框
*&---------------------------------------------------------------------*
FORM tips_window.
CALL FUNCTION 'POPUP_TO_CONFIRM_STEP'
EXPORTING
titel = '重要提示'
textline1 = '你确定要批量导入这些数据吗?'
cancel_display = 'X' "space 不显示cancel按钮,'X'是显示取消按钮
IMPORTING
answer = lv_yn. "确定= J 否=N 取消 = A
ENDFORM.
*&---------------------------------------------------------------------*
*& Form tips_window
*&---------------------------------------------------------------------*
*& "错误弹出确认框
*&---------------------------------------------------------------------*
FORM error_window.
CALL FUNCTION 'POPUP_TO_CONFIRM_STEP'
EXPORTING
titel = '重要提示'
textline1 = message_error
cancel_display = space "space 不显示cancel按钮,'X'是显示取消按钮
IMPORTING
answer = lv_yn. "确定= J 否=N 取消 = A
CALL SCREEN 101."出错回调101屏幕,不执行任何操作。
ENDFORM.
最后就是将内表的数据批量插入到数据库里面去就可以啦,代码如下:
*&---------------------------------------------------------------------*
*& Form ADD_DATA
*&---------------------------------------------------------------------*
*& 执行批量导入数据操作
*&---------------------------------------------------------------------*
FORM add_data USING lv_yn gt_alv.
DATA:gt_mast TYPE TABLE OF mast,
gs_mast TYPE mast.
DATA:gt_makt TYPE TABLE OF makt,
gs_makt TYPE makt.
DATA:gt_marm TYPE TABLE OF marm,
gs_marm TYPE marm.
DATA:gt_marc TYPE TABLE OF marc,
gs_marc TYPE marc.
* 遍历内表
IF lv_yn EQ 'J'.
LOOP AT gt_alv INTO gs_alv.
gs_mast-mandt = gs_alv-mandt.
gs_mast-werks = gs_alv-werks.
gs_mast-matnr = gs_alv-matnr.
gs_mast-stlan = gs_alv-stlan.
APPEND gs_mast TO gt_mast.
gs_makt-mandt = gs_alv-mandt.
gs_makt-maktx = gs_alv-maktx.
APPEND gs_makt TO gt_makt.
gs_marm-mandt = gs_alv-mandt.
gs_marm-meinh = gs_alv-meinh.
APPEND gs_marm TO gt_marm.
gs_marc-mandt = gs_alv-mandt.
gs_marc-beskz = gs_alv-beskz.
gs_marc-sobsl = gs_alv-sobsl.
APPEND gs_marc TO gt_marc.
ENDLOOP.
* 批量插入数据
* INSERT mast FROM TABLE gt_mast ACCEPTING DUPLICATE KEYS.
* INSERT makt FROM TABLE gt_makt ACCEPTING DUPLICATE KEYS.
* INSERT marm FROM TABLE gt_marm ACCEPTING DUPLICATE KEYS.
* INSERT marc FROM TABLE gt_marc ACCEPTING DUPLICATE KEYS.
ENDIF.
ENDFORM.
好了。
以上就是按照excel模板导入数据的案例了,新手代码写的不好望指正。