SAP ABAP Excel模板上传及Excel数据批导
事务代码:al11 用来显示SAP目录(显示Excel目录位置)
事物代码:cg3z用来上载文件(上传Excel)
程序例子:
*&---------------------------------------------------------------------*
*& Report ZRXD05P
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
REPORT zrxd05p.
TABLES: kna1,zrmm116,sscrfields,zpos_id_cust.
TYPE-POOLS:slis, truxs.
DATA: BEGIN OF it_display OCCURS 0.
INCLUDE STRUCTURE zrxd05p.
DATA: END OF it_display.
DATA:zmsg(50) TYPE c,
ztype(1) TYPE c.
DATA: BEGIN OF wa_input ,
kunnr LIKE kna1-kunnr,
message(50) TYPE c,
END OF wa_input.
DATA: it_input LIKE TABLE OF wa_input WITH HEADER LINE.
DATA: it_zrxd05p LIKE TABLE OF zrxd05p WITH HEADER LINE.
DATA: it_kna1 LIKE TABLE OF kna1 WITH HEADER LINE.
DATA: i_input LIKE TABLE OF zrxd05p WITH HEADER LINE.
DATA: i_zship1 LIKE TABLE OF zship1 WITH HEADER LINE.
DATA: xlstab TYPE truxs_t_text_data,
filename TYPE string ,
lf_rc TYPE c,
f_size TYPE i.
DATA: n TYPE i.
*ALV
DATA: gwa_field TYPE slis_fieldcat_alv.
DATA: git_field LIKE TABLE OF gwa_field.
DATA: gwa_layout TYPE slis_layout_alv.
DATA: gd_repid TYPE sy-repid.
DATA: lit_sort TYPE slis_t_sortinfo_alv.
DATA: lwa_sort TYPE slis_sortinfo_alv,
gc_grid_title(50) TYPE c .
SELECTION-SCREEN: FUNCTION KEY 1.
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE t1.
PARAMETERS: p_file TYPE char128 .
SELECTION-SCREEN END OF BLOCK b1.
SELECTION-SCREEN BEGIN OF BLOCK rdg1 WITH FRAME TITLE s2.
PARAMETERS znew RADIOBUTTON GROUP rg1.
PARAMETERS zdel RADIOBUTTON GROUP rg1.
SELECTION-SCREEN END OF BLOCK rdg1.
INITIALIZATION.
t1 = '选择模板文件'.
s2 = '选择操作类型:'.
%_p_file_%_app_%-text = 'Excel文件(含表头)'.
sscrfields-functxt_01 = '下载模板'.
AT SELECTION-SCREEN.
IF sy-ucomm = 'FC01'.
PERFORM frm_down.
ENDIF.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
PERFORM get_pc_filename.
START-OF-SELECTION.
PERFORM upload_file.
IF LINES( it_input ) = 0.
MESSAGE s000(oo) WITH '文件为空,请重新上传。' DISPLAY LIKE 'E'.
STOP.
ELSE.
PERFORM input_data.
ENDIF.
*&---------------------------------------------------------------------*
*& Form GET_PC_FILENAME
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM get_pc_filename .
CALL FUNCTION 'WS_FILENAME_GET'
EXPORTING
mask = ',Excel文件(*.XLS;*.XLSX),*.XLS;*.XLSX,所有文件(*.*),*.*.'
mode = 'O'
IMPORTING
filename = p_file
rc = lf_rc
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 'SAPGUI_PROGRESS_INDICATOR'
EXPORTING
percentage = 0
text = '加载文件。。'.
CALL FUNCTION 'WS_UPLOAD'
EXPORTING
filename = p_file
filetype = 'BIN'
IMPORTING
filelength = f_size
TABLES
data_tab = xlstab
EXCEPTIONS
conversion_error = 1
file_open_error = 2
file_read_error = 3
invalid_type = 4
no_batch = 5
unknown_error = 6
invalid_table_width = 7
gui_refuse_filetransfer = 8
customer_error = 9
no_authority = 10
OTHERS = 11.
IF sy-subrc <> 0.
MESSAGE s000(oo) WITH '没有找到文件' DISPLAY LIKE 'E'.
STOP.
ENDIF.
IF p_file IS INITIAL.
STOP.
ENDIF.
CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
EXPORTING
percentage = 0
text = '转换Ing..'.
CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
EXPORTING
i_tab_raw_data = xlstab
i_filename = p_file
i_line_header = 'X'
TABLES
i_tab_converted_data = it_input
EXCEPTIONS
conversion_failed = 1
OTHERS = 2.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
* MESSAGE '转换Excel表有错误发生!' TYPE 'W' .
ENDIF.
ENDFORM. " UPLOAD_FILE
*&---------------------------------------------------------------------*
*& Form FRM_DOWN
*&---------------------------------------------------------------------*
* 下载模板文件
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM frm_down .
DATA: openfile LIKE ibipparms-path ,
result TYPE c .
CALL FUNCTION 'WS_FILENAME_GET'
EXPORTING
def_filename = '客户批量冻结及解冻批导模板.xlsx'
mask = ',Excel文件(*.XLSX),*.XLSX,所有文件(*.*),*.*.'
mode = 'S'
IMPORTING
filename = openfile
rc = result
EXCEPTIONS
inv_winsys = 1
no_batch = 2
selection_cancel = 3
selection_error = 4
OTHERS = 5.
CHECK result IS INITIAL AND sy-subrc = 0.
CALL FUNCTION 'C13Z_FILE_DOWNLOAD_BINARY'
EXPORTING
i_file_front_end = openfile
i_file_appl = 'ZRXD05P.xlsx'
i_file_overwrite = 'X'
IMPORTING
e_flg_open_error = result
EXCEPTIONS
fe_file_open_error = 1
fe_file_exists = 2
fe_file_write_error = 3
ap_no_authority = 4
ap_file_open_error = 5
ap_file_empty = 6
OTHERS = 7.
IF result = 'X' OR sy-subrc <> 0.
MESSAGE e000(oo) WITH '下载错误'.
ELSE.
MESSAGE s000(oo) WITH '下载到:' openfile.
ENDIF.
ENDFORM. " FRM_DOWN
*&---------------------------------------------------------------------*
*& Form INPUT_DATA
*&---------------------------------------------------------------------*
* 导入数据
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM input_data .
IF znew = 'X'.
ztype = 'N'.
ELSEIF zdel = 'X'.
ztype = 'D'.
ENDIF.
IF ztype = 'N'.
LOOP AT it_input.
CLEAR zmsg.
SELECT SINGLE * FROM kna1 WHERE kunnr = it_input-kunnr.
IF sy-subrc <> 0.
CONCATENATE '经销商编码' it_input-kunnr '不存在!' INTO zmsg.
MESSAGE zmsg TYPE 'I'.
RETURN .
ENDIF.
IF it_input-message IS INITIAL.
CONCATENATE '经销商编码' it_input-kunnr '操作原因为不能空!' INTO zmsg.
MESSAGE zmsg TYPE 'I'.
RETURN .
ENDIF.
ENDLOOP.
LOOP AT it_input.
MOVE-CORRESPONDING it_input TO i_input.
i_input-ernam = sy-uname.
i_input-erdat = sy-datum.
i_input-erzet = sy-uzeit.
i_input-zflg = ztype .
APPEND i_input.
SELECT SINGLE * INTO it_kna1 FROM kna1 WHERE kunnr = it_input-kunnr.
it_kna1-aufsd = '01'.
it_kna1-faksd = '01'.
it_kna1-lifsd = '02'.
it_kna1-sperr = 'X'.
it_kna1-cassd = 'X'.
APPEND it_kna1.
ENDLOOP.
MODIFY kna1 FROM TABLE it_kna1.
IF sy-subrc = 0.
CALL FUNCTION 'BAPI_TRANSACTION_COMMIT'
EXPORTING
wait = 'X'.
INSERT zrxd05p FROM TABLE i_input.
CALL FUNCTION 'BAPI_TRANSACTION_COMMIT'
EXPORTING
wait = 'X'.
LOOP AT it_input.
MOVE-CORRESPONDING it_input TO it_display.
it_display-ernam = sy-uname.
it_display-erdat = sy-datum.
it_display-erzet = sy-uzeit.
it_display-zflg = ztype .
it_display-message = '导入成功!'.
APPEND it_display.
ENDLOOP.
ELSE.
MESSAGE '导入失败!' TYPE 'I'.
ENDIF.
ELSEIF ztype = 'D'.
LOOP AT it_input.
CLEAR zmsg.
SELECT SINGLE * FROM kna1 WHERE kunnr = it_input-kunnr.
IF sy-subrc <> 0.
CONCATENATE '经销商编码' it_input-kunnr '不存在!' INTO zmsg.
MESSAGE zmsg TYPE 'I'.
RETURN .
ENDIF.
IF it_input-message IS INITIAL.
CONCATENATE '经销商编码' it_input-kunnr '操作原因为不能空!' INTO zmsg.
MESSAGE zmsg TYPE 'I'.
RETURN .
ENDIF.
ENDLOOP.
LOOP AT it_input.
MOVE-CORRESPONDING it_input TO i_input.
i_input-ernam = sy-uname.
i_input-erdat = sy-datum.
i_input-erzet = sy-uzeit.
i_input-zflg = ztype .
APPEND i_input.
SELECT SINGLE * INTO it_kna1 FROM kna1 WHERE kunnr = it_input-kunnr.
it_kna1-aufsd = ''.
it_kna1-faksd = ''.
it_kna1-lifsd = ''.
it_kna1-sperr = ''.
it_kna1-cassd = ''.
APPEND it_kna1.
ENDLOOP.
MODIFY kna1 FROM TABLE it_kna1.
IF sy-subrc = 0.
CALL FUNCTION 'BAPI_TRANSACTION_COMMIT'
EXPORTING
wait = 'X'.
INSERT zrxd05p FROM TABLE i_input.
CALL FUNCTION 'BAPI_TRANSACTION_COMMIT'
EXPORTING
wait = 'X'.
LOOP AT it_input.
MOVE-CORRESPONDING it_input TO it_display.
it_display-ernam = sy-uname.
it_display-erdat = sy-datum.
it_display-erzet = sy-uzeit.
it_display-zflg = ztype .
it_display-message = '导入成功!'.
APPEND it_display.
ENDLOOP.
ELSE.
MESSAGE '导入失败!' TYPE 'I'.
ENDIF.
ENDIF.
PERFORM frm_setalv.
ENDFORM. " INPUT_DATA
*&---------------------------------------------------------------------*
*& Form FRM_SETALV
*&---------------------------------------------------------------------*
* 创建ALV
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM frm_setalv .
gd_repid = sy-repid.
gwa_layout-info_fieldname = 'COLOR'.
gwa_layout-colwidth_optimize = 'X'.
PERFORM frm_fieldcat_init USING git_field[].
SET TITLEBAR '100' WITH gc_grid_title .
CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
EXPORTING
i_callback_program = gd_repid
is_layout = gwa_layout
it_fieldcat = git_field[]
TABLES
t_outtab = it_display.
ENDFORM. " FRM_SETALV
*&---------------------------------------------------------------------*
*& Form ALV_STATUS_SET
*&---------------------------------------------------------------------*
* ALV工具栏
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM alv_status_set .
SET TITLEBAR 'ZRDT004I_ALV_TITLE'.
SET PF-STATUS 'ZRDT004I_ALV_LIST'.
ENDFORM. " ALV_STATUS_SET
*&---------------------------------------------------------------------*
*& Form ALV_USER_COMMAND
*&---------------------------------------------------------------------*
* 单击事件
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM alv_user_command USING c_ucomm LIKE sy-ucomm
c_selfield TYPE slis_selfield .
CASE c_ucomm.
WHEN '&F03' OR '&F12' OR '&F15'.
LEAVE TO SCREEN 0.
ENDCASE.
ENDFORM. " ALV_USER_COMMAND
*&---------------------------------------------------------------------*
*& Form FRM_FIELDCAT_INIT
*&---------------------------------------------------------------------*
* ALV列
*----------------------------------------------------------------------*
* -->P_GIT_FIELD[] text
*----------------------------------------------------------------------*
FORM frm_fieldcat_init USING p_git_field TYPE slis_t_fieldcat_alv.
CLEAR gwa_field.
gwa_field-fieldname = 'KUNNR'.
gwa_field-seltext_l = '客户编号'.
gwa_field-outputlen = 9.
APPEND gwa_field TO git_field .
CLEAR gwa_field.
gwa_field-fieldname = 'ERNAM'.
gwa_field-seltext_l = '操作人'.
gwa_field-outputlen = 9.
APPEND gwa_field TO git_field .
CLEAR gwa_field.
gwa_field-fieldname = 'ERDAT'.
gwa_field-seltext_l = '导入日期'.
gwa_field-outputlen = 9.
APPEND gwa_field TO git_field .
CLEAR gwa_field.
gwa_field-fieldname = 'ERZET'.
gwa_field-seltext_l = '导入时间'.
gwa_field-outputlen = 9.
APPEND gwa_field TO git_field .
CLEAR gwa_field.
gwa_field-fieldname = 'MESSAGE'.
gwa_field-seltext_l = '导入状态'.
gwa_field-outputlen = 9.
APPEND gwa_field TO git_field .
CLEAR gwa_field.
gwa_field-fieldname = 'ZFLG'.
gwa_field-seltext_l = '导入类型(N冻结D解冻)'.
gwa_field-outputlen = 9.
APPEND gwa_field TO git_field .
ENDFORM. " FRM_FIELDCAT_INIT
文章来自于网络,如果侵犯了您的权益,请联系站长删除!