ZMMR103-数据批量导入

***********************************************************************
* Title : ZMMR103 *
* Application : MM 　 *
* Subject : PO vendor conform data upload *
* Requested by : 采购部 *
* Execution : any time *
* Ref no: : B179 *
* Author : rainy *
* Req Date : 20150605 *
***********************************************************************
* Production Order Variance Report *
***********************************************************************
***********************************************************************
*1.取数:
*2.输出: *
***********************************************************************
* MODIFICATIONS (latest entry at the top) *
* ------------------------------------------------------------------- *
* REL DATE NAME (COMPANY) DESCRIPTION TASK-NO *
* --- ---- ---- --------- ----------- ------- *
* *
***********************************************************************
REPORT zmmr103 MESSAGE-ID zmm_001.

TABLES:zmmt001,
sscrfields.

DATA:BEGIN OF it_text.
*INCLUDE STRUCTURE zmmt001.
DATA: "mandt LIKE ekpo-mandt,
ebeln LIKE ekpo-ebeln,
ebelp LIKE ekpo-ebelp,
zdate1 TYPE string, "zmmt001-zdate1,
zqty1 LIKE zmmt001-zqty1 ,
zdate2 TYPE string,"LIKE zmmt001-zdate2,
zqty2 LIKE zmmt001-zqty2 ,
ztext LIKE zmmt001-ztext ,
usnam LIKE zmmt001-usnam ,
datum LIKE zmmt001-datum ,
time LIKE zmmt001-time .
DATA:END OF it_text.
DATA:gt_text LIKE TABLE OF it_text WITH HEADER LINE,
wa_text LIKE gt_text.
DATA:gt_zmmt001 LIKE TABLE OF zmmt001 WITH HEADER LINE.

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE t1.

PARAMETERS: so_file(300).

SELECTION-SCREEN END OF BLOCK b1.
SELECTION-SCREEN FUNCTION KEY 1.

*----------------------------------------------------------------------
* INITIALIZATION
*----------------------------------------------------------------------
INITIALIZATION.
t1 = '导入数据'.
sscrfields-functxt_01 = '下载导入模板'.

*----------------------------------------------------------------------
* AT SELECTION-SCREEN.
*----------------------------------------------------------------------
AT SELECTION-SCREEN.
*&下载模板
PERFORM frm_download_template.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR so_file.
PERFORM getdata USING so_file.

 

*----------------------------------------------------------------------
* START-OF-SELECTION.
*----------------------------------------------------------------------
START-OF-SELECTION.

*&S1.upload data.
PERFORM frm_upload.
* CHECK gt_text[] IS NOT INITIAL.
*&write text
PERFORM frm_exeupdate.

*&---------------------------------------------------------------------*
*& Form getdata
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->SO_FILE text
*----------------------------------------------------------------------*
FORM getdata USING so_file.
* Data for open dialog
DATA: l_filetab TYPE filetable,
l_rc TYPE i.
CLEAR l_filetab.
REFRESH l_filetab.

* Open dialog
CALL METHOD cl_gui_frontend_services=>file_open_dialog
EXPORTING
* WINDOW_TITLE = 'SAP Custom - Open File'
* DEFAULT_EXTENSION =
default_filename = '*.xls'
* FILE_FILTER = '*.xls'
initial_directory = 'd:\'
multiselection = ''
CHANGING
file_table = l_filetab
rc = l_rc
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
OTHERS = 4.
* Get file path
CHECK l_rc EQ 1.
READ TABLE l_filetab INDEX 1 INTO so_file.

ENDFORM. "getdata
*&---------------------------------------------------------------------*
*& Form FRM_UPLOAD
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM frm_upload .
DATA:BEGIN OF i_excel OCCURS 0.
INCLUDE STRUCTURE alsmex_tabline.
DATA:END OF i_excel.
DATA:l_answer(1) TYPE c,
l_culumn TYPE i,
l_pathname LIKE rlgrap-filename.
FIELD-SYMBOLS:<fs>.
DATA:it_raw TYPE truxs_t_text_data.

*&文件名赋值l_pathname.
MOVE so_file TO l_pathname.

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
EXPORTING
* I_FIELD_SEPERATOR =
i_line_header = 'X'
i_tab_raw_data = it_raw
i_filename = l_pathname
TABLES
i_tab_converted_data = gt_text
* EXCEPTIONS
* CONVERSION_FAILED = 1
* OTHERS = 2
.
* CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
* EXPORTING
** I_FIELD_SEPERATOR =
** I_LINE_HEADER =
* i_tab_raw_data =
* i_filename =
* TABLES
* i_tab_converted_data =
** 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.
ELSE.
IF gt_text[] IS INITIAL.
CALL FUNCTION 'POPUP_TO_CONFIRM_WITH_MESSAGE'
EXPORTING
* DEFAULTOPTION = 'Y'
diagnosetext1 = 'No data in excel'
* DIAGNOSETEXT2 = ' '
* DIAGNOSETEXT3 = ' '
textline1 = 'No data in excel'
* TEXTLINE2 = ' '
titel = 'Confirm'
* START_COLUMN = 25
* START_ROW = 6
* CANCEL_DISPLAY = 'X'
IMPORTING
answer = l_answer.
*&OUT
EXIT.
ENDIF.
ENDIF.

*&删除标题行
*DELETE gt_text FROM 1 TO 1.

ENDFORM. " FRM_UPLOAD
*&---------------------------------------------------------------------*
*& Form FRM_DOWNLOAD_TEMPLATE
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM frm_download_template .
DATA: ls_key TYPE wwwdatatab,
lv_temp TYPE c,
lv_file TYPE string VALUE '\ZM103.xlsx',
lv_filepath TYPE rlgrap-filename,
lv_result TYPE abap_bool,
lv_path TYPE string,
lv_msg TYPE string.
DATA:l_rc TYPE sy-subrc.

CASE sscrfields-ucomm.
WHEN 'FC01'.

CALL METHOD cl_gui_frontend_services=>directory_browse
EXPORTING
window_title = '选择文件目录'
* initial_folder = 'C:\TEMP'
CHANGING
selected_folder = lv_path
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
OTHERS = 4.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ELSE.
CONCATENATE lv_path lv_file INTO lv_file.

CALL METHOD cl_gui_frontend_services=>file_exist
EXPORTING
file = lv_file
RECEIVING
result = lv_result
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
wrong_parameter = 3
not_supported_by_gui = 4
OTHERS = 5.
IF lv_result = 'X'.
CONCATENATE '模板' lv_file '已存在！' INTO lv_msg.
MESSAGE lv_msg TYPE 'S'.
STOP.
ELSE.
ls_key-relid = 'MI'.
ls_key-objid = 'ZM103'. "SMW0定义的对象名称
lv_filepath = lv_file.
CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
EXPORTING
key = ls_key
destination = lv_filepath
IMPORTING
rc = l_rc.
IF l_rc <> 0.
MESSAGE '下载模板失败！' TYPE 'E'.
ELSE.
MESSAGE s000 WITH '成功下载模板至目录:' lv_filepath.
ENDIF.
ENDIF.
ENDIF.

ENDCASE.
ENDFORM. " FRM_DOWNLOAD_TEMPLATE
*&---------------------------------------------------------------------*
*& Form FRM_EXEUPDATE
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM frm_exeupdate .
FIELD-SYMBOLS:<fs_text> LIKE LINE OF gt_text.
DATA: lv_upd(1).
DATA:lv_date LIKE sy-datum,
lv_day(2) TYPE n,
lv_month(2) TYPE n,
lv_year(4) TYPE n,
lv_calc_date TYPE d.
DATA:l_line TYPE string.

SORT gt_text BY ebeln ebelp.

*&S1.处理数据
LOOP AT gt_text ASSIGNING <fs_text>.

CLEAR:lv_day,
lv_month,
lv_year.
*&S1.1PO#加前导0
CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
EXPORTING
input = <fs_text>-ebeln
IMPORTING
output = <fs_text>-ebeln.

MOVE-CORRESPONDING <fs_text> TO gt_zmmt001.
IF <fs_text>-zdate1 CS '.'.
*&zdate1 中. 去除
REPLACE '.' WITH '' INTO <fs_text>-zdate1.
REPLACE '.' WITH '' INTO <fs_text>-zdate1.
CONDENSE <fs_text>-zdate1 NO-GAPS.
lv_day = <fs_text>-zdate1(2).
lv_month = <fs_text>-zdate1+2(2).
lv_year = <fs_text>-zdate1+4(4).
*&换成系统标准 年月日.
CONCATENATE lv_year lv_month lv_day INTO gt_zmmt001-zdate1.
ENDIF.

IF <fs_text>-zdate2 CS '.'.
*&zdate1 中. 去除
REPLACE '.' WITH '' INTO <fs_text>-zdate2.
REPLACE '.' WITH '' INTO <fs_text>-zdate2.
CONDENSE <fs_text>-zdate2 NO-GAPS.
lv_day = <fs_text>-zdate2(2).
lv_month = <fs_text>-zdate2+2(2).
lv_year = <fs_text>-zdate2+4(4).
CONCATENATE lv_year lv_month lv_day INTO gt_zmmt001-zdate2.
ENDIF.
gt_zmmt001-mandt = sy-mandt.
APPEND gt_zmmt001.
CLEAR gt_zmmt001.
ENDLOOP.

*&S2.创建人信息
gt_zmmt001-usnam = sy-uname.
gt_zmmt001-datum = sy-datum.
gt_zmmt001-time = sy-uzeit.
gt_zmmt001-mandt = sy-mandt.
MODIFY gt_zmmt001 TRANSPORTING usnam datum time mandt WHERE ebeln IS NOT INITIAL.
l_line = lines( gt_zmmt001[] ).

*&S3.更新数据表
CLEAR lv_upd.
TRY .
MODIFY zmmt001 FROM TABLE gt_zmmt001.
CATCH cx_sy_open_sql_db.
lv_upd = 'F'.
ENDTRY.

IF lv_upd EQ ' '.
COMMIT WORK.
MESSAGE s000 WITH '成功上传' l_line '条数据！'.
ELSE.
ROLLBACK WORK.
MESSAGE e006.
ENDIF.

ENDFORM. " FRM_EXEUPDATE

 

* CALL FUNCTION 'ZALSM_EXCEL_TO_INTERNAL_TABLE'
* EXPORTING
* filename = l_pathname
* i_begin_col = n_begin_col
* i_begin_row = n_begin_row
* i_end_col = n_end_col
* i_end_row = n_end_row
* TABLES
* intern = i_excel
* EXCEPTIONS
* inconsistent_parameters = 1
* upload_ole = 2
* OTHERS = 3.
* IF sy-subrc <> 0.
* MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
* WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
* ELSE.
* IF i_excel[] IS INITIAL.
* CALL FUNCTION 'POPUP_TO_CONFIRM_WITH_MESSAGE'
* EXPORTING
* diagnosetext1 = 'No data in excel'
* textline1 = 'No data in excel'
* titel = 'Confirm'
* IMPORTING
* answer = l_answer.
* ELSE.
* DELETE i_excel WHERE row = '0001'.
* SORT i_excel BY row col.
* LOOP AT i_excel.
* MOVE i_excel-col TO l_column.
* ASSIGN COMPONENT l_column OF STRUCTURE it_text TO <fs>.
* MOVE i_excel-value TO <fs>.
* AT END OF row.
* APPEND gt_text.
* CLEAR gt_text.
* ENDAT.
* ENDLOOP.
* ENDIF.
* ENDIF.