*&---------------------------------------------------------------------*
*& Report
*&
*&---------------------------------------------------------------------*
*&程序名称：仓库理货单导出
*&事物代码：
*&导入模板：理货单模板.xls（上传对象名为：ZMM_XLS_001)
*&作者：
*&时间：
*&更新时间：
*&----------------------------------
REPORT zmmr008.
TABLES: vttp,lips,likp,kna1 ,vttk, ztmm001,ztbjdyzt.
INCLUDE ole2incl.
DATA: gs_fcat TYPE lvc_s_fcat,
gt_fcat TYPE lvc_t_fcat,
gs_layo TYPE lvc_s_layo,
gs_grid TYPE lvc_s_glay.
DATA: gt_event TYPE slis_t_event WITH HEADER LINE,
gt_event_exit TYPE slis_t_event_exit WITH HEADER LINE.
DATA: ref_grid TYPE REF TO cl_gui_alv_grid .
DATA: excel TYPE ole2_object,
workbook TYPE ole2_object,
sheet TYPE ole2_object,
cell TYPE ole2_object,
column TYPE ole2_object,
row TYPE ole2_object.
TYPES: BEGIN OF ty_all,
tknum LIKE vttk-tknum, "运单号
vbeln LIKE lips-vbeln, "交货单号
posnr LIKE lips-posnr, "行项目
uecha LIKE lips-uecha, "上层项目批
tplst LIKE vttk-tplst , "装运点
tprfo LIKE vttp-tprfo, "理货顺序
kunnr LIKE kna1-kunnr, "客户代码
name1 LIKE kna1-name1 , "客户
erdat LIKE vttk-erdat , "创建日期
tdlnr LIKE vttk-tdlnr , "物流公司
route LIKE vttk-route , "线路
distz LIKE vttk-distz , "里程
add01 LIKE vttk-add01 , "车主
exti1 LIKE vttk-exti1 , "卸货点
add02 LIKE vttk-add02 , "司机
bfart LIKE vttk-bfart , "车厢类型
matnr LIKE lips-matnr, "物料编码
arktx LIKE lips-arktx, "物料描述
lgpla_c LIKE ztmm001-lgpla_c, "仓位
brgew LIKE lips-brgew, "货物单项目毛重
lfimg LIKE lips-lfimg, "单项目数量
vrkme LIKE lips-vrkme , "单位
gewei LIKE lips-gewei, "重量单位
lgort LIKE lips-lgort, "库存地点
beizhu(30) TYPE c, "备注
END OF ty_all.
TYPES:BEGIN OF ty_header,
tknum LIKE vttk-tknum, "运单号
vbeln LIKE lips-vbeln, "交货单号
kunnr LIKE kna1-kunnr, "客户代码
name1 LIKE kna1-name1 , "客户
tprfo LIKE vttp-tprfo, "理货顺序
tplst LIKE vttk-tplst , "装运点
tplst_txt LIKE ttdst-bezei, "装运点描述
erdat LIKE vttk-erdat , "装运日期
tdlnr LIKE vttk-tdlnr , "物流公司
tdlnr_txt LIKE lfa1-lifnr, "物流公司描述
route LIKE vttk-route , "线路
distz LIKE vttk-distz , "里程
add01 LIKE vttk-add01 , "车主
add01_txt LIKE vtadd01t-bezei, "车主描述
exti1 LIKE vttk-exti1 , "卸货点
add02 LIKE vttk-add02 , "司机
add02_txt LIKE vtadd02t-bezei, "车主描述
bfart LIKE vttk-bfart , "车厢类型
total_brgew LIKE lips-brgew, "一个交货单的总重量
total_lfimg LIKE lips-lfimg, "一个交货单总数量
lgort LIKE lips-lgort, "库存地点
END OF ty_header.
*单据抬头结束
*单据明细开始
TYPES:BEGIN OF ty_items,
tknum LIKE vttk-tknum, "运单号
vbeln LIKE lips-vbeln, "交货单号
posnr LIKE lips-posnr, "行项目
tprfo LIKE vttp-tprfo, "理货顺序
kunnr LIKE kna1-kunnr, "客户代码
name1 LIKE kna1-name1 , "客户
matnr LIKE lips-matnr, "物料编码
arktx LIKE lips-arktx, "物料描述
lgpla_c LIKE ztmm001-lgpla_c, "仓位
brgew LIKE lips-brgew, "单项目毛重
gewei LIKE lips-gewei, "重量单位
lfimg LIKE lips-lfimg, "单项目数量
vrkme LIKE lips-vrkme , "单位
lgort LIKE lips-lgort, "库存地点
beizhu(30) TYPE c, "备注
END OF ty_items.
*单据明细结束
DATA:wa_all TYPE ty_all,
gt_all TYPE ty_all OCCURS 0.
DATA : hs_all TYPE HASHED TABLE OF ty_all WITH UNIQUE KEY tknum name1 WITH HEADER LINE .
DATA:wa_header TYPE ty_header,
gt_header TYPE ty_header OCCURS 0.
DATA:wa_items TYPE ty_items,
gt_items TYPE ty_items OCCURS 0.
DATA: gt_header_show TYPE ty_header OCCURS 0.
DATA: gt_items_show TYPE ty_items OCCURS 0.
*定义传入到smartforms的变量开始
DATA:fm_name TYPE rs38l_fnam.
DATA:itemsname(32) TYPE c.
DATA:headername(32) TYPE c.
*定义传入到smartforms的变量结束
*定义存储ALV向smartforms传入数据的内表开始
DATA:gt_header_temp TYPE ty_header OCCURS 0.
DATA:gt_items_temp TYPE ty_items OCCURS 0.
DATA:gt_all_temp TYPE ty_all OCCURS 0.
*定义存储ALV向smartforms传入数据的内表结束
"控制smartforms参数声明开始
DATA :control_parameters TYPE ssfctrlop.
"控制smartforms参数声明结束
DATA:gt_dc TYPE STANDARD TABLE OF ztbjdyzt.
DATA:wa_dc TYPE ztbjdyzt.
TYPES: BEGIN OF ty_sum ,
tknum LIKE vttk-tknum, "运单号
tprfo LIKE vttp-tprfo, "理货顺序
matnr LIKE lips-matnr, "物料编码
arktx LIKE lips-arktx, "物料描述
lgpla_c LIKE ztmm001-lgpla_c, "仓位
lfimg LIKE lips-lfimg, "单项目数量
vrkme LIKE lips-vrkme , "单位
kunnr LIKE kna1-kunnr, "客户代码
name1 LIKE kna1-name1 , "客户
lgort LIKE lips-lgort, "库存地点
brgew LIKE lips-brgew, "单项目毛重
gewei LIKE lips-gewei, "重量单位
END OF ty_sum.
DATA : itab TYPE ty_sum OCCURS 0 ."WITH HEADER LINE ."从数据库中取出的数放入的内表
DATA : hs_sum TYPE HASHED TABLE OF ty_sum WITH UNIQUE KEY tprfo matnr arktx lgpla_c vrkme gewei lgort kunnr name1 tknum WITH HEADER LINE .
DATA : itab_sum TYPE STANDARD TABLE OF ty_sum WITH HEADER LINE .
TYPES: BEGIN OF itab_sum,"行列转换后的内表
lgpla_c
LIKE ztmm001-lgpla_c, "仓位
matnr
LIKE lips-matnr, "物料编码
arktx
LIKE lips-arktx, "物料描述
line_sum
TYPE i,
brgew_sum
LIKE lips-brgew, "吨位
"vrkme LIKE lips-vrkme , "单位
"lgort LIKE lips-lgort, "库存地点
01 TYPE i,
02 TYPE i,
03 TYPE i,
04 TYPE i,
05 TYPE i,
06 TYPE i,
07 TYPE i,
08 TYPE i,
09 TYPE i,
10 TYPE i,
11 TYPE i,
12 TYPE i,
13 TYPE i,
14 TYPE i,
15 TYPE i,
16 TYPE i,
17 TYPE i,
18 TYPE i,
19 TYPE i,
20 TYPE i,
21 TYPE i,
22 TYPE i,
23 TYPE i,
24 TYPE i,
25 TYPE i,
26 TYPE i,
tknum
LIKE vttk-tknum, "运单号
END OF itab_sum.
DATA:itab_out TYPE itab_sum OCCURS 0 WITH HEADER LINE ."声明要导出数据的内表结构
TYPES: BEGIN OF ty_col ,"存储动态显示列的内表结构
kunnr
LIKE kna1-kunnr, "客户代码
name1
LIKE kna1-name1 , "客户
END OF ty_col.
DATA : hs_col TYPE HASHED TABLE OF ty_col WITH UNIQUE KEY name1 kunnr WITH HEADER LINE .
DATA : itab_col TYPE STANDARD TABLE OF ty_col WITH HEADER LINE .
FIELD-SYMBOLS: <f_fs1> , <f_fs2>.
SELECTION-SCREEN BEGIN OF BLOCK blk1 WITH FRAME TITLE text-001.
SELECT-OPTIONS:
p_tknum
FOR vttk-tknum ," no-EXTENSION NO INTERVALS,
p_matnr
FOR lips-matnr,
p_erdat
FOR vttk-erdat .
SELECTION-SCREEN END OF BLOCK blk1.
SELECTION-SCREEN BEGIN OF BLOCK blk2 WITH FRAME TITLE text-002.
PARAMETERS: r1 RADIOBUTTON GROUP gr1 DEFAULT 'X' USER-COMMAND radio.
PARAMETERS: r2 RADIOBUTTON GROUP gr1.
SELECTION-SCREEN END OF BLOCK blk2.
INITIALIZATION.
START-OF-SELECTION.
IF r1 EQ 'X'.
PERFORM frm_no_transfered_getdata.
DATA:title TYPE lvc_s_layo-grid_title.
CLEAR gt_fcat.
CLEAR gs_layo.
PERFORM outdata USING title .
ELSEIF r2 EQ 'X'.
PERFORM frm_transfered_getdata.
CLEAR gt_fcat.
CLEAR gs_layo.
PERFORM outdata USING title .
ENDIF.
FORM frm_transfered_getdata.
CLEAR gt_all[].
DATA:lwa_all TYPE ty_all,
lgt_all
TYPE ty_all OCCURS 0.
CLEAR wa_all.
SELECT *
FROM vttk AS vk INNER
JOIN vttp AS vp
ON vk~tknum = vp~tknum INNER
JOIN lips AS l
ON l~vbeln = vp~vbeln INNER
JOIN likp AS lp
ON lp~vbeln = l~vbeln INNER
JOIN kna1 AS k
ON ( k~kunnr = lp~kunag OR k~kunnr = lp~kunnr )
LEFT
JOIN ztmm001 AS zt
ON ( l~matnr = zt~matnr_c AND l~lgort = zt~lgort_c AND l~werks = zt~werks_c )
INTO CORRESPONDING FIELDS OF TABLE @gt_all
WHERE vk~tknum IN @p_tknum AND (
vk
~erdat IN @p_erdat AND
l
~matnr IN @p_matnr ).
CLEAR wa_all.
LOOP AT gt_all INTO wa_all.
SELECT SINGLE erdat INTO wa_all-erdat
FROM vttk
WHERE tknum EQ wa_all-tknum.
MODIFY gt_all FROM wa_all.
ENDLOOP.
SORT gt_all DESCENDING BY tknum tprfo.
"整理在ALV里不显示出被拆分的行项目开始
LOOP AT gt_all INTO wa_all.
IF wa_all-uecha IS NOT INITIAL .
APPEND wa_all TO lgt_all.
ENDIF.
ENDLOOP.
CLEAR lwa_all.
CLEAR wa_all.
LOOP AT lgt_all INTO lwa_all.
READ TABLE gt_all INTO wa_all WITH KEY vbeln = lwa_all-vbeln posnr = lwa_all-uecha .
IF lwa_all-lfimg IS NOT INITIAL AND wa_all-lfimg IS INITIAL .
DELETE gt_all WHERE vbeln EQ lwa_all-vbeln AND posnr EQ lwa_all-uecha.
ENDIF.
ENDLOOP.
SELECT * INTO CORRESPONDING FIELDS OF TABLE gt_dc
FROM ztbjdyzt
WHERE type EQ '003'.
CLEAR wa_dc.
CLEAR wa_all.
SORT gt_dc ASCENDING BY key1.
DELETE ADJACENT DUPLICATES FROM gt_dc COMPARING key1."去除重复的运单号
DATA:gt_tmp_all TYPE ty_all OCCURS 0.
LOOP AT gt_dc INTO wa_dc.
LOOP AT gt_all INTO wa_all.
IF wa_all-tknum EQ wa_dc-key1..
APPEND wa_all TO gt_tmp_all.
ENDIF.
ENDLOOP.
ENDLOOP.
CLEAR gt_all[].
gt_all[]
= gt_tmp_all[] .
CLEAR gt_tmp_all[].
"整理在ALV里不显示出被拆分的行项目开始结束
MOVE-CORRESPONDING gt_all TO gt_header_show.
LOOP AT gt_header_show INTO wa_header.
SELECT SINGLE bezei INTO wa_header-add01_txt
FROM vtadd01t WHERE add_info EQ wa_header-add01.
SELECT SINGLE bezei INTO wa_header-add02_txt
FROM vtadd02t WHERE add_info EQ wa_header-add02.
SELECT SINGLE bezei INTO wa_header-tplst_txt
FROM ttdst WHERE tplst EQ wa_header-tplst.
SELECT SINGLE name1 INTO wa_header-tdlnr_txt
FROM lfa1 WHERE lifnr EQ wa_header-tdlnr.
MODIFY gt_header_show FROM wa_header.
CLEAR wa_header.
ENDLOOP.
MOVE-CORRESPONDING gt_all TO gt_items_show.
DELETE ADJACENT DUPLICATES FROM gt_header_show COMPARING tknum .
DATA :sum_weight LIKE lips-brgew VALUE 0.
DATA:sum_lfimg LIKE lips-lfimg VALUE 0."单项目数量
LOOP AT gt_header_show INTO wa_header.
LOOP AT gt_items_show INTO wa_items.
IF wa_header-tknum EQ wa_items-tknum.
CALL FUNCTION 'UNIT_CONVERSION_SIMPLE'
EXPORTING
input = wa_items-brgew
round_sign
= 'X'
unit_in
= wa_items-gewei
unit_out
= 'TO'
IMPORTING
output = wa_items-brgew
EXCEPTIONS
conversion_not_found
= 01
division_by_zero
= 02
input_invalid
= 03.
sum_weight
= wa_items-brgew + sum_weight.
sum_lfimg
= wa_items-lfimg + sum_lfimg.
ENDIF.
ENDLOOP.
wa_header
-total_brgew = sum_weight."总重量
wa_header
-total_lfimg = sum_lfimg."总数量
MODIFY TABLE gt_header_show FROM wa_header.
CLEAR sum_weight.
CLEAR sum_lfimg.
ENDLOOP.
CLEAR wa_header.
CLEAR wa_items.
MOVE-CORRESPONDING gt_all TO itab.
ENDFORM.
"parameters: p_fname like RLGRAP-FILENAME default 'C:\Users\DONG\Desktop\model.xls'.
FORM frm_no_transfered_getdata.
CLEAR gt_all[].
DATA:lwa_all TYPE ty_all,
lgt_all
TYPE ty_all OCCURS 0.
CLEAR wa_all.
SELECT *
FROM vttk AS vk INNER
JOIN vttp AS vp
ON vk~tknum = vp~tknum INNER
JOIN lips AS l
ON l~vbeln = vp~vbeln INNER
JOIN likp AS lp
ON lp~vbeln = l~vbeln INNER
JOIN kna1 AS k
ON ( k~kunnr = lp~kunag OR k~kunnr = lp~kunnr )
LEFT
JOIN ztmm001 AS zt
ON ( l~matnr = zt~matnr_c AND l~lgort = zt~lgort_c AND l~werks = zt~werks_c )
INTO CORRESPONDING FIELDS OF TABLE @gt_all
WHERE vk~tknum IN @p_tknum AND (
vk
~erdat IN @p_erdat AND
l
~matnr IN @p_matnr ).
CLEAR wa_all.
LOOP AT gt_all INTO wa_all.
SELECT SINGLE erdat INTO wa_all-erdat
FROM vttk
WHERE tknum EQ wa_all-tknum.
MODIFY gt_all FROM wa_all.
ENDLOOP.
SORT gt_all DESCENDING BY tknum tprfo.
"整理在ALV里不显示出被拆分的行项目开始
LOOP AT gt_all INTO wa_all.
IF wa_all-uecha IS NOT INITIAL .
APPEND wa_all TO lgt_all.
ENDIF.
ENDLOOP.
CLEAR lwa_all.
CLEAR wa_all.
LOOP AT lgt_all INTO lwa_all.
READ TABLE gt_all INTO wa_all WITH KEY vbeln = lwa_all-vbeln posnr = lwa_all-uecha .
IF lwa_all-lfimg IS NOT INITIAL AND wa_all-lfimg IS INITIAL .
DELETE gt_all WHERE vbeln EQ lwa_all-vbeln AND posnr EQ lwa_all-uecha.
ENDIF.
ENDLOOP.
SELECT * INTO CORRESPONDING FIELDS OF TABLE gt_dc
FROM ztbjdyzt
WHERE type EQ '003'.
SORT gt_dc ASCENDING BY key1.
DELETE ADJACENT DUPLICATES FROM gt_dc COMPARING key1.
CLEAR wa_dc.
LOOP AT gt_dc INTO wa_dc.
DELETE gt_all WHERE tknum EQ wa_dc-key1.
ENDLOOP.
"整理在ALV里不显示出被拆分的行项目开始结束
MOVE-CORRESPONDING gt_all TO gt_header_show.
LOOP AT gt_header_show INTO wa_header.
SELECT SINGLE bezei INTO wa_header-add01_txt
FROM vtadd01t WHERE add_info EQ wa_header-add01.
SELECT SINGLE bezei INTO wa_header-add02_txt
FROM vtadd02t WHERE add_info EQ wa_header-add02.
SELECT SINGLE bezei INTO wa_header-tplst_txt
FROM ttdst WHERE tplst EQ wa_header-tplst.
SELECT SINGLE name1 INTO wa_header-tdlnr_txt
FROM lfa1 WHERE lifnr EQ wa_header-tdlnr.
MODIFY gt_header_show FROM wa_header.
CLEAR wa_header.
ENDLOOP.
MOVE-CORRESPONDING gt_all TO gt_items_show.
DELETE ADJACENT DUPLICATES FROM gt_header_show COMPARING tknum .
DATA :sum_weight LIKE lips-brgew VALUE 0.
DATA :sum_lfimg LIKE lips-lfimg VALUE 0.
LOOP AT gt_header_show INTO wa_header.
LOOP AT gt_items_show INTO wa_items.
IF wa_header-tknum EQ wa_items-tknum.
CALL FUNCTION 'UNIT_CONVERSION_SIMPLE'
EXPORTING
input = wa_items-brgew
round_sign
= 'X'
unit_in
= wa_items-gewei
unit_out
= 'TO'
IMPORTING
output = wa_items-brgew
EXCEPTIONS
conversion_not_found
= 01
division_by_zero
= 02
input_invalid
= 03.
sum_weight
= wa_items-brgew + sum_weight.
sum_lfimg
= wa_items-lfimg + sum_lfimg.
ENDIF.
ENDLOOP.
wa_header
-total_brgew = sum_weight."总重量
wa_header
-total_lfimg = sum_lfimg."总数量
MODIFY TABLE gt_header_show FROM wa_header.
CLEAR sum_weight.
CLEAR sum_lfimg.
ENDLOOP.
CLEAR wa_header.
CLEAR wa_items.
MOVE-CORRESPONDING gt_all TO itab.
ENDFORM. " GETDATA
*&---------------------------------------------------------------------*
*& Form FIXDATA
*&---------------------------------------------------------------------*
FORM fixdata USING tknum TYPE ty_header-tknum.
CLEAR hs_sum[].
CLEAR hs_col[].
CLEAR itab_col[].
CLEAR itab_sum[].
CLEAR itab_out[].
DATA: index LIKE sy-tabix .
DATA:wa_itab TYPE ty_sum.
LOOP AT itab INTO wa_itab.
*通过单位来转换数量 此函数为将单位不是KG的转换成单位为TO的重量
CALL FUNCTION 'UNIT_CONVERSION_SIMPLE'
EXPORTING
input = wa_itab-brgew
round_sign
= 'X'
unit_in
= wa_itab-gewei
unit_out
= 'TO'
IMPORTING
output = wa_itab-brgew
EXCEPTIONS
conversion_not_found
= 01
division_by_zero
= 02
input_invalid
= 03.
IF tknum EQ wa_itab-tknum.
hs_sum
= wa_itab.
COLLECT hs_sum.
hs_col
-name1 = wa_itab-name1 .
hs_col
-kunnr = wa_itab-kunnr.
COLLECT hs_col.
ENDIF.
ENDLOOP.
"SORT hs_col.
itab_col[]
= hs_col[].
itab_sum[]
= hs_sum[].
LOOP AT itab_sum.
itab_out
-tknum = itab_sum-tknum.
itab_out
-lgpla_c = itab_sum-lgpla_c.
itab_out
-matnr = itab_sum-matnr .
itab_out
-arktx = itab_sum-arktx .
itab_out
-brgew_sum = itab_sum-brgew.
"itab_out-vrkme = itab_sum-vrkme .
"itab_out-lgort = itab_sum-lgort.
READ TABLE itab_col WITH KEY name1 = itab_sum-name1 .
index = sy-tabix + 5."4这个数字代表itab_sum内表有几列是固定不变的
ASSIGN COMPONENT index OF STRUCTURE itab_out TO <f_fs1>.
<f_fs1>
= itab_sum-lfimg.
itab_out
-line_sum = itab_sum-lfimg.
itab_out
-brgew_sum = itab_sum-brgew.
COLLECT itab_out.
SORT itab_out ASCENDING BY lgpla_c matnr.
CLEAR itab_out.
ENDLOOP.
ENDFORM. " FIXDATA
FORM frm_catlg_set USING p_field p_text p_key p_edit p_no_out p_do_sum p_no_zero.
gs_fcat
-fieldname = p_field.
gs_fcat
-reptext = p_text.
gs_fcat
-key = p_key.
gs_fcat
-edit = p_edit.
gs_fcat
-no_out = p_no_out.
gs_fcat
-do_sum = p_do_sum.
gs_fcat
-no_zero = p_no_zero.
IF p_field = 'LINE_SUM'.
gs_fcat
-emphasize = 'C700'.
ENDIF.
APPEND gs_fcat TO gt_fcat .
CLEAR gs_fcat .
ENDFORM. "frm_catlg_set
FORM fieldcat_init USING grid_title TYPE lvc_s_layo-grid_title.
DATA: c(2) TYPE n,txt(20) TYPE c .
PERFORM frm_catlg_set USING:
'TKNUM' '理货单号' 'X' '' '' '' 'X' ,
'ERDAT' '发货日期' '' '' '' '' '' ,
'VBELN' '交货单号' 'X' '' '' '' 'X',
'POSNR' '行项目' '' '' '' '' '',
'UECHA' '被拆分的项目' '' '' '' '' '',
'MATNR' '物料编码' '' '' '' '' 'X' ,
'ARKTX' '物料描述' '' '' '' '' '',
'LFIMG' '数量' '' '' '' '' '',
'BEIZHU' '备注' '' 'X' 'X' '' ''.
gs_layo
-zebra = 'X'.
gs_layo
-cwidth_opt = 'X'.
ENDFORM. "fieldcat_init
FORM outdata USING grid_title TYPE lvc_s_layo-grid_title.
PERFORM fieldcat_init USING grid_title .
PERFORM show_alv TABLES gt_fcat gt_all[]
USING gs_layo.
ENDFORM. " outdata
FORM show_alv TABLES pt_fcat pt_tab USING ps_layout.
gs_grid
-edt_cll_cb = 'X'."当屏幕失去焦点时，自动刷新
gt_event
-name = 'CALLER_EXIT'. "slis_ev_caller_exit_at_start事件
gt_event
-form = 'FM_BUTTON'.
APPEND gt_event .
CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY_LVC'
EXPORTING
i_callback_program
= sy-repid
i_callback_user_command
= 'USER_COMMAND'
i_grid_settings
= gs_grid
i_callback_pf_status_set
= 'SET_PF_STATUS'
is_layout_lvc
= ps_layout
it_fieldcat_lvc
= pt_fcat[]
it_events
= gt_event[]
i_save
= 'X'
* it_event_exit = gt_event_exit[]
TABLES
t_outtab
= pt_tab
EXCEPTIONS
program_error
= 1
OTHERS = 2.
ENDFORM. "csalv_set_fcat
FORM user_command USING r_ucomm LIKE sy-ucomm rs_selfield TYPE slis_selfield.
CASE r_ucomm.
WHEN '&DATA_SAVE'.
WHEN '&SAVE' OR '&EXPORT' .
"PERFORM PRINT.
DATA: path LIKE rlgrap-filename..
DATA :file_path LIKE rlgrap-filename.
TYPES: BEGIN OF ty_tknum ,"存储运单号
tknum
LIKE vttk-tknum , "运单号
END OF ty_tknum.
"存储选中的运单号声明开始
DATA : hs_tknum TYPE HASHED TABLE OF ty_tknum WITH UNIQUE KEY tknum WITH HEADER LINE .
DATA: wa_tknum TYPE ty_tknum.
"存储选中的运单号声明结束
"设置文件存放路径调用函数开始
PERFORM set_file_path CHANGING path.
"设置文件存放路径调用函数结束
DATA:ls_row TYPE lvc_s_row,
lt_rows
TYPE lvc_t_row.
"获取ALV选中的行开始
CALL METHOD ref_grid->get_selected_rows
IMPORTING
et_index_rows
= lt_rows.
"获取ALV选中的行结束
LOOP AT lt_rows INTO ls_row.
READ TABLE gt_all INTO wa_all INDEX ls_row-index.
hs_tknum
= wa_all-tknum.
COLLECT hs_tknum.
ENDLOOP.
LOOP AT hs_tknum INTO wa_tknum.
PERFORM fixdata USING wa_tknum-tknum.
CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'
EXPORTING
input = wa_tknum-tknum
IMPORTING
output = wa_tknum-tknum.
file_path
= path && '\理货单' && wa_tknum-tknum && '.xls'.
PERFORM frm_download_template USING file_path."'C:\Users\DONG\Desktop\发货通知单.XLS'.
CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
EXPORTING
input = wa_tknum-tknum
IMPORTING
output = wa_tknum-tknum.
PERFORM data_to_excel USING file_path wa_tknum-tknum." 'C:\Users\DONG\Desktop\发货通知单.XLS'.
CLEAR wa_dc.
wa_dc
-to_date = sy-datum.
wa_dc
-to_time = sy-uzeit.
wa_dc
-type = '003'.
wa_dc
-tab_no = sy-index.
wa_dc
-name = sy-uname.
wa_dc
-key1 = wa_tknum-tknum.
wa_dc
-to_go = 'X'.
MODIFY ztbjdyzt FROM wa_dc.
IF r1 EQ 'X'.
DELETE gt_all WHERE tknum EQ wa_tknum-tknum.
ENDIF.
CLEAR itab_out[].
ENDLOOP.
IF sy-subrc EQ 0 .
MESSAGE s004(zmess) WITH '导出成功！'.
"rs_selfield-refresh = 'X'.
DATA stbl TYPE lvc_s_stbl.
stbl
-row = 'X'." 基于行的稳定刷新
stbl
-col = 'X'." 基于列稳定刷新
CALL METHOD ref_grid->refresh_table_display
EXPORTING
is_stable
= stbl.
ENDIF.
CLEAR path.
CLEAR file_path.
WHEN '&BACK'.
LEAVE TO SCREEN 0.
ENDCASE.
ENDFORM. "user_command
*&---------------------------------------------------------------------*
*& Form SET_PF_STATUS
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->RT_EXTAB text
*----------------------------------------------------------------------*
FORM set_pf_status USING rt_extab TYPE slis_t_extab..
SET PF-STATUS 'STANDARD'.
ENDFORM. "SET_PF_STATUS
*&---------------------------------------------------------------------*
*& Form fm_button
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->E_GRID text
*----------------------------------------------------------------------*
FORM fm_button USING e_grid TYPE slis_data_caller_exit.
CALL FUNCTION 'GET_GLOBALS_FROM_SLVC_FULLSCR'
* EXPORTING
* IR_SALV_FULLSCREEN_ADAPTER =
IMPORTING
* ET_EXCLUDING =
* E_REPID =
* E_CALLBACK_PROGRAM =
* E_CALLBACK_ROUTINE =
e_grid
= ref_grid
* ET_FIELDCAT_LVC =
* ER_TRACE =
* E_FLG_NO_HTML =
* ES_LAYOUT_KKBLO =
* ES_SEL_HIDE =
* ET_EVENT_EXIT =
* ER_FORM_TOL =
* ER_FORM_EOL =
.
* CALL METHOD ref_grid->check_changed_data.
* 设置enter事件
CALL METHOD ref_grid->register_edit_event
EXPORTING
i_event_id
= cl_gui_alv_grid=>mc_evt_enter
EXCEPTIONS
error
= 1
OTHERS = 2.
"CREATE OBJECT gt_event_receiver.
"SET HANDLER gt_event_receiver->handle_modify FOR ref_grid.
ENDFORM. "FM_BUTTON
FORM fill_cell USING i_row i_col p_value.
CALL METHOD OF excel 'CELLS' = cell
EXPORTING #1 = i_row #2 = i_col.
SET PROPERTY OF cell 'VALUE' = p_value.
ENDFORM. "fill_cell
FORM frm_insert_col USING position.
CALL METHOD OF excel 'COLUMNS' = column EXPORTING #1 = position .
CALL METHOD OF column 'INSERT'.
* CALL METHOD OF l_excel 'COLUMNS' = l_column
* EXPORTING
* #1 = 2.
ENDFORM.
FORM data_to_excel USING c_path tknum TYPE ty_header-tknum.
DATA:lt_tmp_header TYPE ty_header.
LOOP AT gt_header_show INTO lt_tmp_header .
IF lt_tmp_header-tknum EQ tknum.
wa_header
= lt_tmp_header.
EXIT.
ENDIF.
ENDLOOP.
* Create an Excel object and start Excel.
CREATE OBJECT excel 'EXCEL.APPLICATION'.
IF sy-subrc <> 0.
MESSAGE 'The excel object can''t be created' TYPE 'I'.
ENDIF.
* Create an Excel workbook Object.
CALL METHOD OF excel 'WORKBOOKS' = workbook .
* Transfer the header line to Excel.
CALL METHOD OF workbook 'OPEN' EXPORTING #1 = c_path.
"READ TABLE gt_header_show INTO wa_header INDEX 1.
CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'
EXPORTING
input = wa_header-tknum
IMPORTING
output = wa_header-tknum.
DATA:txt(30).
CONCATENATE '理货单号：' wa_header-tknum INTO txt.
PERFORM fill_cell USING 4 1 txt.
CLEAR txt.
CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
EXPORTING
input = wa_header-tknum
IMPORTING
output = wa_header-tknum.
CONCATENATE '车主/车号：' wa_header-add01 ' —' wa_header-add01_txt INTO txt.
PERFORM fill_cell USING 4 4 txt.
CLEAR txt.
DATA: l_erdat TYPE char10.
l_erdat
= wa_header-erdat(4) && '-' && wa_header-erdat+4(2) && '-' && wa_header-erdat+6(2).
CONCATENATE '发货日期：' l_erdat INTO txt.
PERFORM fill_cell USING 4 8 txt.
CLEAR txt.
CONCATENATE '物流公司：' wa_header-tdlnr_txt INTO txt.
PERFORM fill_cell USING 5 1 txt.
CLEAR txt.
CONCATENATE '司机：' wa_header-add02 ' —' wa_header-add02_txt INTO txt.
PERFORM fill_cell USING 5 4 txt.
CLEAR txt.
CONCATENATE '装运点：' wa_header-tplst_txt INTO txt.
PERFORM fill_cell USING 5 8 txt.
CLEAR txt.
PERFORM fill_cell USING 8 4 wa_header-total_lfimg.
CLEAR txt.
PERFORM fill_cell USING 8 5 wa_header-total_brgew.
CLEAR txt.
PERFORM frm_coverted_name USING sy-uname CHANGING txt.
PERFORM fill_cell USING 33 5 txt.
DATA:wa_sum TYPE itab_sum,
l_col
TYPE sy-index.
FIELD-SYMBOLS: <f>.
DATA : cust_num TYPE i .
DATA : cust_count TYPE i."客户数量
DESCRIBE TABLE itab_col LINES cust_num.
DESCRIBE TABLE hs_col LINES cust_count.
IF cust_count GT 5.
DO ( cust_count - 5 ) TIMES.
PERFORM frm_insert_col USING 11.
ENDDO.
ENDIF.
DATA :rows TYPE i VALUE 8."行数据从第十一行开始
LOOP AT itab_out INTO wa_sum.
CLEAR l_col.
IF wa_sum-tknum EQ wa_header-tknum.
rows = rows + 1.
"ADD 1 TO l_col.
DO.
ASSIGN COMPONENT sy-index OF STRUCTURE wa_sum TO <f>.
IF sy-index GT ( cust_num + cust_count + 5 ).
EXIT.
ENDIF.
ADD 1 TO l_col.
IF <f> IS INITIAL .
"<f> = ''.
PERFORM fill_cell USING rows l_col ''. "wa_sum-matnr.
ELSE.
PERFORM fill_cell USING rows l_col <f>. "wa_sum-matnr.
ENDIF.
ENDDO.
" CONCATENATE '' wa_sum-LGORT INTO TXT.
"PERFORM fill_cell USING 11 5 TXT.
CLEAR txt.
ENDIF.
ENDLOOP.
CLEAR wa_header.
*客户名称填充开始
DATA: c(2) .
DATA:cols TYPE i.
DATA:name_kunnr TYPE string.
LOOP AT hs_col .
CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'
EXPORTING
input = hs_col-kunnr
IMPORTING
output = hs_col-kunnr.
CLEAR name_kunnr.
CONCATENATE hs_col-name1 ':' hs_col-kunnr INTO name_kunnr.
c = c + 1.
cols
= c + 5.
CONCATENATE '' c INTO txt.
PERFORM fill_cell USING 6 cols name_kunnr ."填写用户名
PERFORM fill_cell USING 8 cols c."填写用户顺序
ENDLOOP.
*客户名称填充结束
GET PROPERTY OF excel 'ACTIVEWORKBOOK' = workbook.
* release and exit Excel.
CALL METHOD OF workbook 'SAVE'.
CALL METHOD OF excel 'QUIT'.
* Free all objects
FREE OBJECT cell.
FREE OBJECT workbook.
FREE OBJECT excel.
excel
-handle = -1.
FREE OBJECT row.
ENDFORM.
FORM frm_download_template USING c_path .
DATA: lv_objdata LIKE wwwdatatab,
lv_obj_name
LIKE wwwdatatab-objid,
lv_destination
LIKE rlgrap-filename,
lv_objid
LIKE sy-repid,
lv_subrc
LIKE sy-subrc.
DATA:l_ret TYPE abap_bool, lv_answer
.
DATA:lv_file TYPE string.
MOVE c_path TO lv_file.
CALL METHOD cl_gui_frontend_services=>file_exist
EXPORTING
file
= lv_file
RECEIVING
result
= l_ret
EXCEPTIONS
cntl_error
= 1
error_no_gui
= 2
wrong_parameter
= 3
not_supported_by_gui
= 4
OTHERS = 5.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
EXIT.
ENDIF.
IF l_ret EQ 'X'.
* 模版已存在，是否覆盖
CALL FUNCTION 'POPUP_TO_CONFIRM'
EXPORTING
text_question
= '模版已存在，是否覆盖？'
text_button_1
= '是'(001)
text_button_2
= '否'(002)
IMPORTING
answer
= lv_answer
EXCEPTIONS
text_not_found
= 1
OTHERS = 2.
IF sy-subrc <> 0.
ENDIF.
IF lv_answer EQ 'A'. "取消
EXIT.
ELSEIF lv_answer NE '1'. "否
"p_filepath = c_path.
ENDIF.
ELSE.
lv_answer
= '1'.
ENDIF.
CREATE OBJECT excel 'EXCEL.APPLICATION'.
GET PROPERTY OF excel 'Workbooks' = workbook .
CALL METHOD OF workbook
'Close'.
IF lv_answer EQ '1'.
MOVE 'ZMM_XLS_001' TO lv_obj_name."引用传入到服务器中xls模板
SELECT relid objid
FROM wwwdata
INTO CORRESPONDING FIELDS OF lv_objdata
UP TO 1 ROWS
WHERE srtf2 = 0 AND relid = 'MI'
AND objid = lv_obj_name.
ENDSELECT.
CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
EXPORTING
key = lv_objdata
destination
= c_path
IMPORTING
rc
= lv_subrc.
* IF lv_subrc = 0.
* p_filepath = c_path.
* ENDIF.
ENDIF.
* CALL METHOD OF
* g_workbook
* 'open'
*
* EXPORTING
* #1 = c_path.
*
* CALL METHOD OF
* g_excel
* 'worksheets' = g_sheet
* EXPORTING
* #1 = 1.
* CALL METHOD OF
* g_sheet
* 'activate'.
"SET PROPERTY OF g_excel 'visible' = 0.
FREE OBJECT sheet.
"FREE OBJECT g_applica.
FREE OBJECT workbook.
FREE OBJECT excel.
ENDFORM. "frm_download_template
FORM set_file_path CHANGING filepath LIKE rlgrap-filename.
DATA: gd_path TYPE string.
DATA: it_tab TYPE filetable,
gd_subrc
TYPE i.
DATA: ld_filename TYPE string,
ld_path
TYPE string,
ld_fullpath
TYPE string,
ld_result
TYPE i,
gd_file
TYPE c.
DATA:"filepath LIKE rlgrap-filename,
openfile
LIKE rlgrap-filename.
CALL METHOD cl_gui_frontend_services=>directory_browse
EXPORTING
window_title
= '文件路径选择'
initial_folder
= 'C:'
CHANGING
selected_folder
= gd_path.
CALL METHOD cl_gui_cfw=>flush.
CONCATENATE gd_path '' INTO filepath.
* CALL METHOD cl_gui_frontend_services=>file_open_dialog
* EXPORTING
* window_title = 'Select File'
* default_filename = '*.txt'
* multiselection = 'X'
* CHANGING
* file_table = it_tab
* rc = gd_subrc.
** LOOP AT it_tab INTO openfile-low.
** openfile-sign = 'I'.
** openfile-option = 'EQ'.
** APPEND openfile.
** ENDLOOP.
* CALL METHOD cl_gui_cfw=>flush.
*
*CALL METHOD cl_gui_frontend_services=>file_save_dialog
* EXPORTING
* default_extension = 'XLS'
* default_file_name = '产品出库单'
* initial_directory = 'c:/temp/'
* CHANGING
* filename = ld_filename
* path = filepath
* fullpath = ld_fullpath
* user_action = ld_result.
ENDFORM.
FORM frm_coverted_name USING usrid TYPE sy-uname
CHANGING name.
DATA: l_name_last TYPE adrp-name_last,
l_name_first
TYPE adrp-name_first,
l_persnumber
TYPE usr21-persnumber.
SELECT SINGLE persnumber INTO l_persnumber
FROM usr21
WHERE bname EQ usrid.
SELECT SINGLE name_last INTO l_name_last
FROM adrp
WHERE persnumber EQ l_persnumber.
SELECT SINGLE name_first INTO l_name_first
FROM adrp
WHERE persnumber EQ l_persnumber.
CONCATENATE l_name_last l_name_first INTO name.
ENDFORM.