Excle 上传到SAP处理的简单程序
*&---------------------------------------------------------------------*
*& Report ZONG_CAL1
*&
*&---------------------------------------------------------------------*
*&上传一个excle 文件到sap,再进行一些简单的逻辑
*&
*&---------------------------------------------------------------------*
REPORT zong_cal1.
*&---------------------------------------------------------------------*
*& POOLS DEFINITION
*&---------------------------------------------------------------------*
TYPE-POOLS truxs.
*&---------------------------------------------------------------------*
*& DATA DEFINITION
*&---------------------------------------------------------------------*
TYPES:BEGIN OF ty_data,
pob_id TYPE char25,
reported_qty TYPE char25,
nominator_qty TYPE char25,
denominator_qty TYPE char25,
END OF ty_data,
it_ty_data TYPE STANDARD TABLE OF ty_data.
TYPES:BEGIN OF ty_text,
message TYPE string,
END OF ty_text,
it_ty_text TYPE STANDARD TABLE OF ty_text.
DATA:lt_text TYPE it_ty_text, " MESSAGE DISPLAY
lt_data TYPE it_ty_data,
it_raw TYPE truxs_t_text_data,
ls_data TYPE ty_data,
ls_text TYPE ty_text, " WORK AREA (MESSAGE DISPLAY)
ls_data_num TYPE ty_data,
* LV_SUM_TEMP TYPE CHAR9,
* LV_SUM3 TYPE CHAR9, " SUM OF REPORTED_QTY / DENOMINATOR_QTY
lv_ok TYPE char15 VALUE 'HAVE QTY ISSUE',
lv_no TYPE char15 VALUE 'HAVE NO QTY ISSUE',
lv_sum TYPE farr_rpt_quantity, " SUM OF REPORTED_QTY
lv_qty_nu TYPE farr_quantity_nominator,
lv_qty_de TYPE farr_quantity_denominator,
lv_qty_nm_tmp TYPE farr_quantity_nominator,
lv_qty_dm_tmp TYPE farr_quantity_denominator.
*&---------------------------------------------------------------------*
*& PARAMETERS DEFINITION
*&---------------------------------------------------------------------*
PARAMETERS:p_file TYPE rlgrap-filename.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
PERFORM data_initial.
*&---------------------------------------------------------------------*
*& INITIALIZATION
*&---------------------------------------------------------------------*
INITIALIZATION.
* DATA CLEAR && FILE UPLOAD
CLEAR: lt_data,
lt_text,
ls_text,
ls_data,
lv_sum,
* LV_SUM_TEMP,
* LV_SUM3, " SUM OF REPORTED_QTY / DENOMINATOR_QTY
lv_qty_nu,
lv_qty_de.
*&---------------------------------------------------------------------*
*& START-OF-SELECTION
*&---------------------------------------------------------------------*
START-OF-SELECTION.
* READ THE TEXT OF EXCEL
PERFORM read_excel.
*&---------------------------------------------------------------------*
*& END-OF-SELECTION
*&---------------------------------------------------------------------*
END-OF-SELECTION.
* SHOW RESULTS
PERFORM data_display.
*&---------------------------------------------------------------------*
*& Form READ_EXCEL
*&---------------------------------------------------------------------*
* READ THE TEXT OF EXCEL
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM read_excel .
CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP' '
EXPORTING
* I_FIELD_SEPERATOR =
* I_LINE_HEADER =
i_tab_raw_data = it_raw " WORK TABLE
i_filename = p_file " FILE NAME
TABLES
i_tab_converted_data = lt_data "ACTUAL DATA
EXCEPTIONS
conversion_failed = 1
OTHERS = 2.
IF sy-subrc <> 0.
MESSAGE: 'CANNOT READ FILE ' TYPE 'E'.
EXIT.
ENDIF.
IF lt_data IS NOT INITIAL.
* SORT TABLE
SORT lt_data
BY pob_id DESCENDING.
LOOP AT lt_data INTO ls_data.
CASE sy-tabix.
WHEN 1.
CONTINUE.
WHEN OTHERS.
lv_sum = ls_data-reported_qty + lv_sum.
* LV_SUM_TEMP = LS_DATA-NOMINATOR_QTY / LS_DATA-DENOMINATOR_QTY.
* LV_SUM3 = LV_SUM_TEMP + LV_SUM3.
lv_qty_nm_tmp = ls_data-nominator_qty.
lv_qty_dm_tmp = ls_data-denominator_qty.
CALL METHOD cl_farr_contract_utility=>cal_nominator_denominator
EXPORTING
iv_factor_1_nominator = lv_qty_nu
iv_factor_1_denominator = lv_qty_de
iv_factor_2_nominator = lv_qty_nm_tmp
iv_factor_2_denominator = lv_qty_dm_tmp
iv_operation = '+'
IMPORTING
ev_nominator = lv_qty_nu
ev_denominator = lv_qty_de.
* CLEAR:
* LV_SUM_TEMP.
* BY POB_ID
AT END OF pob_id.
* SUM OF REPORTED_QTY = SUM OF ( REPORTED_QTY / DENOMINATOR_QTY )
IF lv_sum = lv_qty_nu / lv_qty_de.
* POB_ID + HAVE NO QTY ISSUE
CONCATENATE ls_data-pob_id 'DOES NOT HAVE QTY ISSUE' INTO ls_text-message SEPARATED BY '-'.
APPEND ls_text TO lt_text.
ELSE.
* POB_ID + HAVE QTY ISSUE
CONCATENATE ls_data-pob_id 'HAVE QTY ISSUE' INTO ls_text-message SEPARATED BY '-'.
APPEND ls_text TO lt_text.
ENDIF.
* DATA CLEAR
CLEAR:
ls_text,
lv_qty_nu,
lv_qty_de,
lv_sum.
* LV_SUM3.
ENDAT.
ENDCASE.
ENDLOOP.
ELSE.
MESSAGE: 'THIS FILE IS EMPTY' TYPE 'E'.
EXIT.
ENDIF.
ENDFORM. " READ_EXCEL
*&---------------------------------------------------------------------*
*& Form DATA_INITIAL
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM data_initial .
* FILE UPLOAD
CALL FUNCTION 'F4_FILENAME'
EXPORTING
* PROGRAM_NAME = SYST-CPROG
* DYNPRO_NUMBER = SYST-DYNNR
field_name = ' '
IMPORTING
file_name = p_file. " RETURN BACK FILE NAME
.
ENDFORM. " DATA_INITIAL
*&---------------------------------------------------------------------*
*& Form DATA_DISPLAY
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM data_display .
IF lt_text IS NOT INITIAL.
CLEAR: ls_text.
LOOP AT lt_text INTO ls_text.
WRITE: 'POB:',ls_text-message,/.
ENDLOOP.
ENDIF.
ENDFORM. " DATA_DISPLAY