ZFIR_001 ole下载

*&---------------------------------------------------------------------*
* Report ZFIR_001
* Application : FI 　 *
* Subject : 供应商付款建议清单导出 *
* Requested by : Luo tang *
* Execution : Online when required *
* Ref no: : *
* Author : CX *
* Req Date : 20170410 *
***********************************************************************
* 　　　　 *
***********************************************************************
* 　　　　设计主要逻辑与原理说明 *
***********************************************************************
*1.取数: 根据输入的附件建议清单程序 Q1？
* 查询条件：日期REGUH- LAUFD 和附加标识REGUH- LAUFI，
* 在表REGUH内导出对应供应商的相关付款数据 ， *
* Q2.在第三部基础上，
*按照对应公司代码REGUH- ZBUKR= ZVendor_ZBJ-BUKRS，
*供应商编码REGUH- LIFNR= LFB1- LIFNR ，
*找到供应商类别LFB1-QSREC，
*根据公司代码和供应商类别在自定义记录表ZVendor_ZBJ表
*找到对应质量保证金金额相减计算出的金额【？是什么东西，显示在哪一列？】
*2.输出: 公司代码 REGUH- ZBUKR
* 付款日期 REGUH- LAUFD
* 建议清单ID REGUH- LAUFI
* 供应商代码 REGUH- LIFNR
* 供应商名称 REGUH- NAME1
* 供应商等级 LFB1-MINDK
* 联系人 KNVK-NAME1
* 联系人身份证
* 客户联系方式 KNVK-TELF1
* 货币 REGUH- WAERS
* 计划付款金额 （REGUH- RBETR）-（ZTYJ_FK_001-WTGES）
* 现金金额 现金金额=计划付款金额-承兑金额
* 承兑金额 计划付款金额按照承兑和现金基础表比例计算计划付款金额的承兑金额
* ZVendor_ZFBL- CDHP*（（REGUH- RBETR）减去（ZTYJ_FK_001-WTGES））承兑以万为单位，向下取整
* 现汇% 计划付款金额按照承兑和现金基础表比例ZVendor_ZFBL- YHZZ
* 票据% 计划付款金额按照承兑和现金基础表比例ZVendor_ZFBL- CDHP
* 供应商账号 REGUH-KOINH
* 开户行代码 REGUH-ZBNKY
* 供应商开户行 根据REGUH-ZBNKY银行代码到BNKA表的BANKA字段
* 开户行所在省 根据REGUH-ZBNKY银行代码到BNKA表的PROVZ字段
* 开户行所在市 根据REGUH-ZBNKY银行代码到BNKA表的ORT01字段
*5. 按照以上计算出来最后金额按照既定格式导出到excle格式清单

REPORT zfir_001 NO STANDARD PAGE HEADING
MESSAGE-ID zyuejin_fico
LINE-SIZE 132
LINE-COUNT 65.

*----------------------------------------------------------------------*
* Type-Pools
*----------------------------------------------------------------------*
TYPE-POOLS: slis,icon,abap,ole2.
*----------------------------------------------------------------------
* TABLES/Structure
*----------------------------------------------------------------------
TABLES: reguh,"支付程序结算数据清单
f110v.
*----------------------------------------------------------------------
* Internal table
*----------------------------------------------------------------------
TYPES:BEGIN OF ty_data,
laufd TYPE reguh-laufd,"付款日期
laufi TYPE reguh-laufi,"建议清单ID
zbukr TYPE reguh-zbukr,"公司代码
butxt TYPE t001-butxt,
lifnr TYPE reguh-lifnr,"供应商代码
name1 TYPE reguh-name1,"供应商名称
mindk TYPE lfb1-mindk, "供应商等级
mtext TYPE t059t-mtext,"等级描述
namel TYPE knvk-name1, "联系人
telf1 TYPE knvk-telf1, "客户联系方式
waers TYPE reguh-waers,"货币
rbetr TYPE reguh-rbetr,"
xj_etr TYPE reguh-rbetr, "现金金额 现金金额 =计划付款金额-承兑金额
cd_etr TYPE reguh-rbetr, "承兑金额 按承兑和现金基础表比例计算计划付款金额的承兑金额
zyhzz TYPE ztyj_fk_002-zcdhp,"现汇%
zcdhp TYPE ztyj_fk_002-zcdhp,"票据%
koinh TYPE reguh-koinh, "供应商银行账号
zbnky TYPE reguh-zbnky, "开户行代码
banka TYPE bnka-banka, "供应商开户行
provz TYPE bnka-provz, "开户行所在省
sheng TYPE string,
ort01 TYPE bnka-ort01, "开户行所在市
cybtr TYPE reguh-rbetr, "计划付款金额
sel TYPE c,
*id_l TYPE string, "联系人身份证

END OF ty_data.

DATA:gt_data TYPE TABLE OF ty_data,
gs_data LIKE LINE OF gt_data.

DATA: BEGIN OF tlaufk OCCURS 1.
INCLUDE STRUCTURE ilaufk.
DATA: END OF tlaufk.
DATA: xf4_c1(1) TYPE c. " Abbruch F4_Zahllauf
DATA:g_laufi LIKE f110v-laufi.
*----------------------------------------------------------------------*
* Ole Define
*----------------------------------------------------------------------*
DATA: g_excel TYPE ole2_object,
g_workbook TYPE ole2_object,
g_sheet TYPE ole2_object,
g_cell TYPE ole2_object,
g_cell1 TYPE ole2_object,
g_column TYPE ole2_object,
g_range TYPE ole2_object,
g_borders TYPE ole2_object,
g_button TYPE ole2_object,
g_int TYPE ole2_object,
g_font TYPE ole2_object,
g_row TYPE ole2_object.
DATA:gc_scnt TYPE i.
DATA:g_vendor(10) VALUE '银行转账格式',
g_data(10) VALUE '自动付款生成汇票格式'.
*----------------------------------------------------------------------
* Data parameters for alv report use
*----------------------------------------------------------------------
DATA: g_program TYPE sy-repid,
gw_layout TYPE slis_layout_alv,
gt_fieldcat TYPE slis_t_fieldcat_alv,
wa_fieldcat TYPE slis_fieldcat_alv,
gt_event TYPE slis_t_event,
wa_event TYPE slis_alv_event.
DATA: g_grid TYPE REF TO cl_gui_alv_grid.
DATA: g_len TYPE i.

*----------------------------------------------------------------------
* FIELD-SYMBOLS
*----------------------------------------------------------------------
FIELD-SYMBOLS:<fs_field> LIKE LINE OF gt_fieldcat,
<fs_data> LIKE LINE OF gt_data.

*======================================================================
* SELECTION-SCREEN
*======================================================================
SELECTION-SCREEN BEGIN OF BLOCK blk1 WITH FRAME TITLE text-001.
*&
PARAMETERS: zw_laufd LIKE f110v-laufd,"reguh-laufd,
zw_laufi LIKE f110v-laufi."reguh-laufi.

SELECTION-SCREEN END OF BLOCK blk1.

*----------------------------------------------------------------------
* INITIALIZATION
*----------------------------------------------------------------------
INITIALIZATION.
* 初始化数据
PERFORM frm_initial.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR: zw_laufd .
PERFORM frm_f4_laufd USING zw_laufd .

AT SELECTION-SCREEN ON VALUE-REQUEST FOR: zw_laufi.
PERFORM frm_f4_laufi USING zw_laufi.

*----------------------------------------------------------------------
* AT SELECTION-SCREEN
*----------------------------------------------------------------------
AT SELECTION-SCREEN.
* 权限检查部分
PERFORM frm_check_author.

 

*======================================================================
* MAIN PROGRAM
*======================================================================
* START-OF-SELECTION
*----------------------------------------------------------------------
START-OF-SELECTION.
PERFORM frm_get_data.
PERFORM frm_alv_output.

 

*----------------------------------------------------------------------
* END-OF-SELECTION
*----------------------------------------------------------------------

*&---------------------------------------------------------------------*
*& Form FRM_INITIAL
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM frm_initial.
* IF s_datum[] IS INITIAL.
* s_datum-low = sy-datum .
* s_datum-high = sy-datum .
* APPEND s_datum.
* ENDIF.
ENDFORM. " FRM_INITIAL

*&---------------------------------------------------------------------*
*& Form FRM_CHECK_AUTHOR
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM frm_check_author.

* LOOP AT lt_t001w.
* AUTHORITY-CHECK OBJECT 'M_MATE_WRK'
* FOR USER lv_yyloginid
* ID 'WERKS' FIELD lt_t001w-werks "S_WERKS-LOW
* ID 'ACTVT' FIELD '03'.
* IF sy-subrc NE 0.
* MESSAGE i004 WITH '没有地点' lt_t001w-werks '权限！'.
* STOP.
* ENDIF.
* ENDLOOP.

ENDFORM. " FRM_CHECK_AUTHOR

*&---------------------------------------------------------------------*
*& Form frm_get_data
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM frm_get_data .

DATA:lt_lfa1 TYPE TABLE OF lfa1,
lw_lfa1 LIKE LINE OF lt_lfa1.
DATA:lt_t001 TYPE TABLE OF t001,
lw_t001 LIKE LINE OF lt_t001.
DATA:lt_t059t TYPE TABLE OF t059t,
lw_t059t LIKE LINE OF lt_t059t.
DATA:lt_lfb1 LIKE TABLE OF lfb1 WITH HEADER LINE.
DATA:lt_ztyj_fk_001 LIKE TABLE OF ztyj_fk_001,
ls_ztyj_fk_001 LIKE LINE OF lt_ztyj_fk_001.
DATA:lt_ztyj_fk_002 LIKE TABLE OF ztyj_fk_002,
ls_ztyj_fk_002 LIKE LINE OF lt_ztyj_fk_002.

DATA:lt_reguh TYPE TABLE OF ty_data.
DATA:ls_knvk TYPE knvk.
DATA:l_tabix TYPE sy-tabix.

*BREAK C_xiangc.
*&s1.Get Main table data
SELECT *
INTO CORRESPONDING FIELDS OF TABLE gt_data
FROM reguh
WHERE laufd EQ zw_laufd
AND laufi EQ zw_laufi
AND xvorl = 'X'
AND vblnr NE space. "收付凭证的凭证编号

IF gt_data IS INITIAL.
MESSAGE i001. "WITH ''.
STOP.
ENDIF.
* SORT gt_data BY zbukr lifnr.

SELECT *
INTO TABLE lt_t001
FROM t001
WHERE spras = sy-langu.

SELECT *
INTO TABLE lt_t059t
FROM t059t.

lt_reguh[] = gt_data[].
SORT lt_reguh BY lifnr zbukr.
DELETE ADJACENT DUPLICATES FROM lt_reguh COMPARING lifnr zbukr.
IF lt_reguh[] IS NOT INITIAL.
SELECT * INTO TABLE lt_lfb1
FROM lfb1
FOR ALL ENTRIES IN lt_reguh
WHERE lifnr = lt_reguh-lifnr
AND bukrs = lt_reguh-zbukr.
SORT lt_lfb1 BY lifnr bukrs.

SELECT * INTO TABLE lt_ztyj_fk_001
FROM ztyj_fk_001
FOR ALL ENTRIES IN lt_reguh
WHERE bukrs = lt_reguh-zbukr.

SELECT * INTO TABLE lt_ztyj_fk_002
FROM ztyj_fk_002
FOR ALL ENTRIES IN lt_reguh
WHERE bukrs = lt_reguh-zbukr.
ENDIF.

*&s2.get others
LOOP AT gt_data INTO gs_data.
l_tabix = sy-tabix.

*&供应商等级
READ TABLE lt_lfb1 WITH KEY lifnr = gs_data-lifnr
bukrs = gs_data-zbukr BINARY SEARCH.
IF sy-subrc EQ 0.
gs_data-mindk = lt_lfb1-mindk.
ENDIF.

*&1.检查供应商保证金
* gs_data-cybtr = F110金额 - lifnr 保证金 ( > 0 保存 / <= 0 删除)
gs_data-rbetr = gs_data-rbetr * -1.

CLEAR:ls_ztyj_fk_001.
READ TABLE lt_ztyj_fk_001 INTO ls_ztyj_fk_001 WITH KEY bukrs = gs_data-zbukr
lifnr = gs_data-lifnr.
*&计算最终需付款
IF sy-subrc EQ 0.
gs_data-cybtr = gs_data-rbetr - ls_ztyj_fk_001-wtges.
ELSE.
gs_data-cybtr = gs_data-rbetr.
ENDIF.

IF gs_data-cybtr <= 0.
DELETE gt_data INDEX l_tabix.
CLEAR gs_data.
CONTINUE.
ENDIF.

*& 公司描述
READ TABLE lt_t001 INTO lw_t001 WITH KEY bukrs = gs_data-zbukr.
IF sy-subrc EQ 0.
gs_data-butxt = lw_t001-butxt.
ENDIF.
*& 供应商类别
READ TABLE lt_t059t INTO lw_t059t WITH KEY mindk = gs_data-mindk.
IF sy-subrc EQ 0.
gs_data-mtext = lw_t059t-mtext.
ENDIF.

*&票据% + 现汇%
CLEAR ls_ztyj_fk_002.
READ TABLE lt_ztyj_fk_002 INTO ls_ztyj_fk_002 WITH KEY bukrs = gs_data-zbukr
mindk = gs_data-mindk.
IF sy-subrc EQ 0.
gs_data-zcdhp = ls_ztyj_fk_002-zcdhp.
gs_data-zyhzz = ls_ztyj_fk_002-zyhzz.
ENDIF.

*&承兑金额 （= 计划付款金额* zcdhp-% DIV 10000 ）
* gs_data-cd_etr = gs_data-cybtr * gs_data-zcdhp / 1000000.
gs_data-cd_etr = gs_data-cybtr * gs_data-zcdhp DIV 1000000.
gs_data-cd_etr = gs_data-cd_etr * 10000.

*&现金金额
* gs_data-xj_etr = gs_data-cybtr / 10000 - gs_data-cd_etr.
gs_data-xj_etr = gs_data-cybtr - gs_data-cd_etr.

*&联系人 *&客户联系方式
CLEAR:ls_knvk.
SELECT SINGLE * INTO ls_knvk
FROM knvk
WHERE lifnr = gs_data-lifnr.
IF sy-subrc EQ 0.
CONCATENATE ls_knvk-name1 ls_knvk-namev INTO gs_data-namel.
gs_data-telf1 = ls_knvk-telf1.
ENDIF.
*&联系人身份证

DATA:ls_lfbk TYPE lfbk.
CLEAR:ls_lfbk.
*&银行账号，开户行代码
SELECT SINGLE * INTO ls_lfbk
FROM lfbk
WHERE lifnr = gs_data-lifnr.
gs_data-zbnky = ls_lfbk-bankl.
gs_data-koinh = ls_lfbk-koinh.

DATA:ls_bnka TYPE bnka.
CLEAR:ls_bnka.
*&供应商开户行bnka-banka,
SELECT SINGLE * INTO ls_bnka
FROM bnka
WHERE banks = 'CN'
AND bankl = ls_lfbk-bankl.

gs_data-banka = ls_bnka-banka."供应商开户行
gs_data-provz = ls_bnka-provz."开户行所在省
*&所在省描述
SELECT SINGLE bezei
INTO gs_data-sheng
FROM t005u
WHERE land1 = 'CN'
AND bland = ls_bnka-provz
and SPRAS = sy-langu.

gs_data-ort01 = ls_bnka-ort01."开户行所在市
*&开户行所在省bnka-provz,
*&开户行所在市bnka-ort01,

SHIFT gs_data-lifnr LEFT DELETING LEADING '0'.
MODIFY gt_data FROM gs_data INDEX l_tabix.
CLEAR gs_data.

ENDLOOP.

*&s3.get lines
l_tabix = LINES( gt_data ).
IF l_tabix > 0.
MESSAGE s002 WITH l_tabix.
ELSE.
MESSAGE i001.
STOP.
ENDIF.

ENDFORM. " frm_get_data

*&---------------------------------------------------------------------*
*& Form frm_alv_output
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM frm_alv_output .
DATA: l_nn TYPE i.

DEFINE add_fieldcat.
clear wa_fieldcat.
wa_fieldcat-fieldname = &1.
wa_fieldcat-seltext_l = &2.
wa_fieldcat-key = &3.
wa_fieldcat-col_pos = l_nn + 1.
* wa_fieldcat-just = &4.
* wa_fieldcat-outputlen = &5.
* wa_fieldcat-fix_column = &6.
wa_fieldcat-no_zero = &4.
wa_fieldcat-edit = &5.
append wa_fieldcat to gt_fieldcat.
END-OF-DEFINITION.

CLEAR gt_fieldcat.
REFRESH gt_fieldcat.

add_fieldcat 'ZBUKR' '公司代码'(101) '' '' ''.
add_fieldcat 'BUTXT' '公司名称'(102) '' '' ''.
add_fieldcat 'LAUFD' '付款日期'(103) '' '' ''.
add_fieldcat 'LAUFI' '建议清单ID'(104) '' '' ''.
add_fieldcat 'LIFNR' '供应商代码'(105) '' '' ''.
add_fieldcat 'NAME1' '供应商名称'(106) '' '' ''.
add_fieldcat 'MINDK' '供应商等级'(107) '' '' ''.
add_fieldcat 'MTEXT' '等级描述'(108) '' '' ''.
add_fieldcat 'NAMEL' '联系人'(109) '' '' ''.
*add_fieldcat 'ID_L' '联系人身份证'(110) '' '' ''.
add_fieldcat 'TELF1' '客户联系方式'(111) '' '' ''.
add_fieldcat 'WAERS' '货币'(112) '' '' ''.
add_fieldcat 'CYBTR' '计划付款金额'(113) '' '' ''.
add_fieldcat 'XJ_ETR' '现金金额'(114) '' '' ''.
add_fieldcat 'CD_ETR' '承兑金额'(115) '' '' ''.
add_fieldcat 'ZYHZZ' '现汇%'(116) '' '' ''.
add_fieldcat 'ZCDHP' '票据%'(117) '' '' ''.
add_fieldcat 'KOINH' '供应商银行账号'(118) '' '' ''.
add_fieldcat 'ZBNKY' '开户行代码'(119) '' '' ''.
add_fieldcat 'BANKA' '供应商开户行'(120) '' '' ''.
add_fieldcat 'SHENG' '开户行所在省'(121) '' '' ''.
add_fieldcat 'ORT01' '开户行所在市'(122) '' '' ''.
* add_fieldcat 'CYBTR' '计划付款金额' '' '' ''.

gw_layout-colwidth_optimize = 'X'.
gw_layout-zebra = 'X'.
* gw_layout-box_fieldname = 'SEL'.

*& 调用函数显示ALV列表
CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
EXPORTING
i_callback_program = sy-repid
i_save = 'A'
is_layout = gw_layout
it_fieldcat = gt_fieldcat
it_events = gt_event
i_callback_user_command = 'SET_USER_COMMAND'
i_callback_pf_status_set = 'SET_PF_STATUS'
TABLES
t_outtab = gt_data
EXCEPTIONS
program_error = 1
OTHERS = 2.

ENDFORM. " frm_alv_output
*@--------------------------------------------------------------------*
*@ Form SET_USER_COMMAND
*@--------------------------------------------------------------------*
* -->R_UCOMM 事务功能码
* -->RS_SELFIELD ALV相关的数据
*---------------------------------------------------------------------*
FORM set_user_command USING r_ucomm LIKE sy-ucomm
rs_selfield TYPE slis_selfield.

CASE r_ucomm.
WHEN '&IC1'. "双击事件的功能码
READ TABLE gt_data INTO gs_data INDEX rs_selfield-tabindex.
**F110 没有parameter ID.
SET PARAMETER ID 'RID' FIELD gs_data-laufd.
CALL TRANSACTION 'F110' AND SKIP FIRST SCREEN.

WHEN 'POST'. "下载代码
PERFORM frm_down_data.
ENDCASE.
* 刷新ALV屏幕报表
rs_selfield-refresh = 'X'.
ENDFORM. "SET_USER_COMMAND
*@---------------------------------------------------------------------*
*@ FORM SET_PF_STATUS *
*@---------------------------------------------------------------------*
* 设置ALV菜单
* 通过SE41，拷贝程序 SAPLSLVC_FULLSCREEN 的状态STANDARD_FULLSCREEN过来
*@---------------------------------------------------------------------*
FORM set_pf_status USING rt_extab TYPE slis_t_extab.
SET PF-STATUS 'PF_STATU'.
ENDFORM. "SET_PF_STATUS

*&---------------------------------------------------------------------*
*& Form FRM_F4_LAUFD
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_ZW_LAUFD text
*----------------------------------------------------------------------*
FORM frm_f4_laufd USING zw_laufd.
DATA: BEGIN OF tlaufk OCCURS 1.
INCLUDE STRUCTURE ilaufk.
DATA: END OF tlaufk.

REFRESH tlaufk.
tlaufk-laufk = space.
tlaufk-sign = 'I'.
APPEND tlaufk.

CALL FUNCTION 'F4_ZAHLLAUF'
EXPORTING
f1typ = 'D'
f2nme = 'F110V-LAUFI'
IMPORTING
laufd = f110v-laufd
laufi = f110v-laufi
nothing_selected = xf4_c1
TABLES
laufk = tlaufk.
zw_laufd = f110v-laufd.
IF xf4_c1 IS INITIAL.

ENDIF.

DATA:lt_dynpread TYPE dynpread OCCURS 0 WITH HEADER LINE.

lt_dynpread-fieldname = 'ZW_LAUFI'.
lt_dynpread-fieldvalue = f110v-laufi.
APPEND lt_dynpread.

CALL FUNCTION 'DYNP_VALUES_UPDATE'
EXPORTING
dyname = sy-cprog
dynumb = sy-dynnr
TABLES
dynpfields = lt_dynpread
EXCEPTIONS
invalid_abapworkarea = 1
invalid_dynprofield = 2
invalid_dynproname = 3
invalid_dynpronummer = 4
invalid_request = 5
no_fielddescription = 6
undefind_error = 7
OTHERS = 8.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.

ENDFORM. " FRM_F4_LAUFD

*&---------------------------------------------------------------------*
*& Form FRM_F4_LAUFI
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_ZW_LAUFI text
*----------------------------------------------------------------------*
FORM frm_f4_laufi USING zw_laufi.
DATA: BEGIN OF tlaufk OCCURS 1.
INCLUDE STRUCTURE ilaufk.
DATA: END OF tlaufk.

REFRESH tlaufk.
tlaufk-laufk = space.
tlaufk-sign = 'I'.
APPEND tlaufk.

CALL FUNCTION 'F4_ZAHLLAUF'
EXPORTING
f1typ = 'I'
f2nme = 'F110V-LAUFD'
IMPORTING
laufd = f110v-laufd
laufi = f110v-laufi
nothing_selected = xf4_c1
TABLES
laufk = tlaufk.
zw_laufi = f110v-laufi.
IF xf4_c1 IS INITIAL.

ENDIF.

DATA: lt_dynpread TYPE dynpread OCCURS 0 WITH HEADER LINE.

lt_dynpread-fieldname = 'ZW_LAUFD'.
lt_dynpread-fieldvalue = f110v-laufd.
APPEND lt_dynpread.

CALL FUNCTION 'DYNP_VALUES_UPDATE'
EXPORTING
dyname = sy-cprog
dynumb = sy-dynnr
TABLES
dynpfields = lt_dynpread
EXCEPTIONS
invalid_abapworkarea = 1
invalid_dynprofield = 2
invalid_dynproname = 3
invalid_dynpronummer = 4
invalid_request = 5
no_fielddescription = 6
undefind_error = 7
OTHERS = 8.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.

ENDFORM. " FRM_F4_LAUFI
*&---------------------------------------------------------------------*
*& Form FRM_DOWN_DATA
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM frm_down_data .
DATA:lv_xlsname TYPE string.
DATA:lv_line TYPE i VALUE 1, "行号
lv_cols TYPE i VALUE 0. "行号
DATA:ls_ztyj_fk_status LIKE ztyj_fk_status.
*&+1.确认要不要 检查已下载---检查是否在审批中？ZTYJ_FK_STATUS-ZCHEC = 'X'.

* SELECT SINGLE *ZCHEC
* FROM ztyj_fk_status
* WHERE laufd = p_laufd
* AND laufi = p_laufi
* AND zsftz = 'X'.
* IF sy-subrc NE 0.
* MESSAGE e018.
* ENDIF.

** 获取下载文件完整路径
PERFORM frm_save_path CHANGING lv_xlsname.
*** 获取列名
* PERFORM frm_get_fname.-GT_fieldcat-seltext_l

PERFORM frm_create_excel.

PERFORM frm_create_sheet.
*PERFORM frm_create_sheet2.

PERFORM frm_save_book USING lv_xlsname.
IF sy-subrc NE 0.
MESSAGE e000 WITH '导出失败！'(e01).
ELSE.
MESSAGE s000 WITH '导出成功！'(s01).

CLEAR:ls_ztyj_fk_status.
ls_ztyj_fk_status-laufd = zw_laufd.
ls_ztyj_fk_status-laufi = zw_laufi.
ls_ztyj_fk_status-zchec = 'X'.
ls_ztyj_fk_status-datum = sy-datum.
ls_ztyj_fk_status-uzeit = sy-uzeit.
ls_ztyj_fk_status-usnam = sy-uname.

MODIFY ztyj_fk_status FROM ls_ztyj_fk_status.
IF sy-subrc EQ 0.
COMMIT WORK AND WAIT.
ELSE.
ROLLBACK WORK.
ENDIF.
* UPDATE ztyj_fk_status SET zchec = 'X'
* datum = sy-datum
* uzeit = sy-uzeit
* usnam = sy-uname
* WHERE laufd = zw_laufd
* AND laufi = zw_laufi.

ENDIF.

ENDFORM. " FRM_DOWN_DATA
*&---------------------------------------------------------------------*
*& Form FRM_save_path
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* <--P_LV_XLSNAME text
*----------------------------------------------------------------------*
FORM frm_save_path CHANGING p_in_path TYPE string.
DATA: lv_filename TYPE string,
lv_path TYPE string.

CALL METHOD cl_gui_frontend_services=>file_save_dialog
EXPORTING
window_title = '下载文件'
file_filter = '*.xls'
CHANGING
filename = lv_filename
path = lv_path
fullpath = p_in_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.
ENDIF.
* IF lv_filename EQ space.
* MESSAGE e011 WITH '操作中止'.
* ENDIF.
ENDFORM. " FRM_save_path
*&---------------------------------------------------------------------*
*& Form FRM_CREATE_EXCEL
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM frm_create_excel .
* 启动Excel
CREATE OBJECT g_excel 'EXCEL.APPLICATION'.
IF sy-subrc NE 0.
WRITE: / '打开Excel失败'.
STOP.
ENDIF.
SET PROPERTY OF g_excel 'DisplayAlerts' = 0.
CALL METHOD OF g_excel 'WORKBOOKS' = g_workbook .
** Put g_excel in background
* IF p_exvis EQ 'X'.
SET PROPERTY OF g_excel 'VISIBLE' = 0.
* ELSE.
* SET PROPERTY OF g_excel 'VISIBLE' = 0.
* ENDIF.
** Create worksheet
SET PROPERTY OF g_excel 'SheetsInNewWorkbook' = 1.
CALL METHOD OF g_workbook 'ADD'.
ENDFORM. " FRM_CREATE_EXCEL
*&---------------------------------------------------------------------*
*& Form FRM_CREATE_SHEET
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM frm_create_sheet .
gc_scnt = 1.
CALL METHOD OF g_excel 'WORKSHEETS' = g_sheet
EXPORTING
#1 = gc_scnt.
CALL METHOD OF g_sheet 'ACTIVATE'.
SET PROPERTY OF g_sheet 'NAME' = g_vendor.
FREE OBJECT g_sheet."OK
**写入sheet2 gt_vendor
PERFORM frm_fill_sheet1.
**
CALL METHOD OF g_excel 'Columns' = g_column.
CALL METHOD OF g_column 'Autofit'.
**清空变量
FREE OBJECT g_column.
FREE OBJECT g_button.
FREE OBJECT g_font.
FREE OBJECT g_int.
FREE OBJECT g_cell.
FREE OBJECT g_cell1.
FREE OBJECT g_range.
FREE OBJECT g_borders.
FREE OBJECT: g_column, g_row.

ENDFORM. " FRM_CREATE_SHEET
*&---------------------------------------------------------------------*
*& Form FRM_SAVE_BOOK
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM frm_save_book USING lv_xlsname TYPE string.
** 保存
GET PROPERTY OF g_excel 'ActiveSheet' = g_sheet.
FREE OBJECT g_sheet.
FREE OBJECT g_workbook.
GET PROPERTY OF g_excel 'ActiveWorkbook' = g_workbook.
CALL METHOD OF g_workbook 'SAVEAS'
EXPORTING
#1 = lv_xlsname
#2 = 1.
CALL METHOD OF g_workbook 'CLOSE'.
CALL METHOD OF g_excel 'QUIT'.
FREE OBJECT g_sheet.
FREE OBJECT g_workbook.
FREE OBJECT g_excel.
ENDFORM. " FRM_SAVE_BOOK
*&---------------------------------------------------------------------*
*& Form FRM_FILL_SHEET1
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM frm_fill_sheet1 .

DATA:lv_line TYPE i VALUE 1, "行号
lv_cols TYPE i VALUE 0. "行号
DATA:lv_date(8).

**sheet 列名
lv_line = 1.
lv_cols = 1.
LOOP AT gt_fieldcat ASSIGNING <fs_field>.
PERFORM frm_setexcel_cell USING g_excel
lv_line
<fs_field>-seltext_l
lv_cols.
ENDLOOP.
LOOP AT gt_data ASSIGNING <fs_data>.
lv_line = lv_line + 1. "Excel 中行号从1开始
lv_cols = 1.
** 序号
PERFORM frm_setexcel_cell USING g_excel
lv_line
<fs_data>-zbukr
lv_cols.
** 收款人代码
PERFORM frm_setexcel_cell USING g_excel
lv_line
<fs_data>-butxt
lv_cols.
*BREAK-POINT.
* REPLACE
* REPLACE ALL OCCURRENCES OF REGEX '.' IN <fs_data>-laufd WITH ''.
** 付款日期
CLEAR:lv_date.
lv_date = <fs_data>-laufd.
PERFORM frm_setexcel_cell USING g_excel
lv_line
lv_date
lv_cols.
** !!!标识
* CLEAR:lv_laufi.
* lv_laufi = <fs_data>-laufI.
PERFORM frm_setexcel_cell USING g_excel
lv_line
<fs_data>-laufi
lv_cols.
** 付款金额
PERFORM frm_setexcel_cell USING g_excel
lv_line
<fs_data>-lifnr
lv_cols.
** 收款人开户行
PERFORM frm_setexcel_cell USING g_excel
lv_line
<fs_data>-name1
lv_cols.
** 收款人帐号
PERFORM frm_setexcel_cell USING g_excel
lv_line
<fs_data>-mindk
lv_cols.
** 区域
PERFORM frm_setexcel_cell USING g_excel
lv_line
<fs_data>-mtext
lv_cols.
** 汇划方式
PERFORM frm_setexcel_cell USING g_excel
lv_line
<fs_data>-namel
lv_cols.
** 收款人代码
PERFORM frm_setexcel_cell USING g_excel
lv_line
<fs_data>-telf1
lv_cols.
** 收款人名称
PERFORM frm_setexcel_cell USING g_excel
lv_line
<fs_data>-waers
lv_cols.
** 付款用途
PERFORM frm_setexcel_cell USING g_excel
lv_line
<fs_data>-cybtr
lv_cols.
** 付款金额
PERFORM frm_setexcel_cell USING g_excel
lv_line
<fs_data>-xj_etr
lv_cols.
** 收款人开户行
PERFORM frm_setexcel_cell USING g_excel
lv_line
<fs_data>-cd_etr
lv_cols.
** 收款人帐号
PERFORM frm_setexcel_cell USING g_excel
lv_line
<fs_data>-zyhzz
lv_cols.
** 区域
PERFORM frm_setexcel_cell USING g_excel
lv_line
<fs_data>-zcdhp
lv_cols.
** 汇划方式
PERFORM frm_setexcel_cell USING g_excel
lv_line
<fs_data>-koinh
lv_cols.

** 收款人开户行
PERFORM frm_setexcel_cell USING g_excel
lv_line
<fs_data>-zbnky
lv_cols.
** 收款人帐号
PERFORM frm_setexcel_cell USING g_excel
lv_line
<fs_data>-banka
lv_cols.
** 区域
PERFORM frm_setexcel_cell USING g_excel
lv_line
<fs_data>-sheng
lv_cols.
** 汇划方式
PERFORM frm_setexcel_cell USING g_excel
lv_line
<fs_data>-ort01
lv_cols.

ENDLOOP.

ENDFORM. " FRM_FILL_SHEET1
*&---------------------------------------------------------------------*
*& Form FRM_setexcel_cell
*&---------------------------------------------------------------------*
* 设定单元格-列
*----------------------------------------------------------------------*
* -->P_IN_EXCEL excel对象
* -->P_IN_LINE 行号
* -->P_IN_FIELD 项目
* <--P_OUT_COLS 列号
*----------------------------------------------------------------------*
FORM frm_setexcel_cell USING p_in_excel TYPE ole2_object
p_in_line TYPE i
p_in_field TYPE any
p_out_cols TYPE i.

DATA:lv_cell TYPE ole2_object.
DATA:columnobj TYPE ole2_object.
*！
CALL METHOD OF g_excel 'COLUMNS' = columnobj
EXPORTING
#1 = 4."the column number
SET PROPERTY OF columnobj 'ColumnWidth' = 10.
SET PROPERTY OF columnobj 'NumberFormat' = '@'.

CALL METHOD OF g_excel 'COLUMNS' = columnobj
EXPORTING
#1 = 18."the column number
SET PROPERTY OF columnobj 'ColumnWidth' = 10.
SET PROPERTY OF columnobj 'NumberFormat' = '@'.

* 指定单元格
CALL METHOD OF
p_in_excel
'CELLS' = lv_cell
EXPORTING
#1 = p_in_line
#2 = p_out_cols.
* 写入值
SET PROPERTY OF lv_cell
'VALUE' = p_in_field.
p_out_cols = p_out_cols + 1.

ENDFORM. " FRM_setexcel_cells

转载于:https://www.cnblogs.com/rainysblog/p/6866851.html