*&--------------------------begin--------------------------------------*
FORM DOWNLOAD_EXCEL_TEMPLATE USING FP_OBJID LIKE wwwdatatab-objid
FP_DEST LIKE sapb-sappfad
FP_DOWN.
DATA: L_PARA like WWWDATATAB.
DATA: ls_destination LIKE rlgrap-filename,
li_rc LIKE sy-subrc.
FP_DOWN = 'N'.
SELECT SINGLE WWWDATA~RELID WWWDATA~OBJID
INTO CORRESPONDING FIELDS OF L_PARA
FROM WWWDATA
WHERE WWWDATA~RELID = 'MI' AND
WWWDATA~OBJID = FP_OBJID AND
WWWDATA~SRTF2 = 0.
if sy-subrc <> 0.
MESSAGE TEXT-E12 TYPE 'S' DISPLAY LIKE 'E'. "The excel template no existed !
RETURN.
endif.
ls_destination = FP_DEST.
CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
EXPORTING
key = L_PARA
destination = ls_destination
IMPORTING
rc = li_rc.
if li_rc NE 0.
MESSAGE TEXT-E13 TYPE 'S' DISPLAY LIKE 'E'. "The excel template download unsuccessfully !
RETURN.
endif.
FP_DOWN = 'Y'.
ENDFORM. "--DOWNLOAD_EXCEL_TEMPLATE
FORM DOWNLOAD_EXCEL_DATA USING FP_DOWN_FILE LIKE sapb-sappfad.
data: l_down_save LIKE rlgrap-filename.
DATA: l_qty like vbap-kwmeng. "pri_pack-cqty.
data: l_date_file type c length 25.
data: l_name type c length 200,
l_shipaddr type c length 200,
l_address type c length 200,
l_lifnr type c length 50,
l_charg type c length 11,
l_mdesc type c length 200, "material desc(EN&ZH)
l_ntgew type p decimals 4, "like mara-ntgew, "net wgt
l_brgew type p decimals 4. "like mara-brgew. "gross wgt
data: l_line type I, l_init type I value 12. "12表示明細導出開始行
data: l_maxrow type I.
CLEAR: l_date_file.
WRITE SY-DATUM TO l_date_file YYMMDD.
CONDENSE l_date_file.
CONCATENATE 'D:/PACK_' l_date_file+0(2) l_date_file+2(2) l_date_file+4(2) INTO l_date_file.
loop at it_hpack.
clear: pri_pack, pri_pack[].
* append lines of it_dpack to pri_pack.
* delete pri_pack where vbeln <> it_hpack-vbeln.
loop at it_dpack where vbeln = it_hpack-vbeln and
check = 'X'.
move-corresponding it_dpack to pri_pack.
append pri_pack.
clear pri_pack.
endloop.
if pri_pack[] is not initial.
CREATE OBJECT appl 'EXCEL.APPLICATION'.
SET PROPERTY OF appl 'VISIBLE' = 0.
SET PROPERTY OF appl 'DisplayAlerts' = 0.
CALL METHOD OF appl 'WORKBOOKS' = book.
CALL METHOD OF book 'OPEN'
EXPORTING #1 = FP_DOWN_FILE.
CALL METHOD OF appl 'WORKSHEETS' = sheet EXPORTING #1 = 1.
GET PROPERTY OF appl 'ACTIVESHEET' = sheet.
GET PROPERTY OF appl 'ACTIVEWORKBOOK' = book.
SET PROPERTY OF sheet 'NAME' = it_hpack-vbeln.
" 導出表頭數據
CLEAR: l_name, l_address, l_lifnr, l_shipaddr.
CONCATENATE it_hpack-name1 it_hpack-name2 it_hpack-name3 it_hpack-name4
INTO l_name SEPARATED BY SPACE.
CONCATENATE it_hpack-str_suppl1 it_hpack-str_suppl2 it_hpack-str_suppl3 it_hpack-location
INTO l_address SEPARATED BY SPACE.
PERFORM SET_EXCEL_VALUE USING 2 8 it_hpack-zpdate. "date
PERFORM SET_EXCEL_VALUE USING 3 8 it_hpack-vbeln. "DN
PERFORM SET_EXCEL_VALUE USING 3 1 it_hpack-zname. "shipper's name
CONCATENATE it_hpack-zaddr it_hpack-zaddr2 INTO l_shipaddr SEPARATED BY SPACE.
PERFORM SET_EXCEL_VALUE USING 4 1 l_shipaddr. "shipper's address
IF it_hpack-lifnr is not initial.
l_lifnr = it_hpack-lifnr.
CONDENSE l_lifnr.
PERFORM DATA_ALPHA_CONVERSION_OUT USING l_lifnr.
CONCATENATE 'Vendor :' l_lifnr INTO l_lifnr SEPARATED BY SPACE.
ENDIF.
PERFORM SET_EXCEL_VALUE USING 5 1 l_lifnr. "vendor
PERFORM SET_EXCEL_VALUE USING 8 1 l_name. "Consignee's Name
PERFORM SET_EXCEL_VALUE USING 9 1 l_address. "Consignee's Address
" 導出明細數據
l_line = l_init.
DESCRIBE TABLE pri_pack LINES l_maxrow.
l_maxrow = l_maxrow + l_init.
l_ntgew = 0.
l_brgew = 0.
l_qty = 0.
loop at pri_pack.
PERFORM SET_EXCEL_VALUE USING l_line 1 pri_pack-crange. "Carton No.
PERFORM SET_EXCEL_VALUE USING l_line 2 pri_pack-groes. "Dimensions (cm)
if pri_pack-matnr = 'SAMPLE' or pri_pack-matnr = 'TOOLING'.
PERFORM SET_EXCEL_VALUE USING l_line 3 pri_pack-arktx. "Part Description
else.
CONCATENATE pri_pack-makte pri_pack-maktz INTO l_mdesc SEPARATED BY SPACE.
PERFORM SET_EXCEL_VALUE USING l_line 3 l_mdesc. "material desc(EN&ZH)
endif.
PERFORM SET_EXCEL_VALUE USING l_line 4 pri_pack-BSTKD. "PO#
PERFORM SET_EXCEL_VALUE USING l_line 5 pri_pack-kdmat. "Cust Code
PERFORM SET_EXCEL_VALUE USING l_line 6 pri_pack-cqty. "QUANTITY (PCS)
PERFORM SET_EXCEL_VALUE USING l_line 7 pri_pack-ntgew. "Net Weight (KG)
PERFORM SET_EXCEL_VALUE USING l_line 8 pri_pack-brgew. "Gross Weight (KG)
PERFORM SET_EXCEL_VALUE USING l_line 9 pri_pack-vgbel. "SO#
PERFORM SET_EXCEL_VALUE USING l_line 10 pri_pack-zbill. "Invoice
CONCATENATE '''' pri_pack-charg INTO l_charg.
PERFORM SET_EXCEL_VALUE USING l_line 11 l_charg. "Batch Number
l_ntgew = l_ntgew + pri_pack-ntgew.
l_brgew = l_brgew + pri_pack-brgew.
l_qty = l_qty + pri_pack-cqty.
l_line = l_line + 1.
if l_line = l_maxrow.
l_line = l_line + 2. "為了與明細空二行
PERFORM SET_EXCEL_VALUE USING l_line 3 ' TOTAL PACKAGE:'.
PERFORM SET_EXCEL_VALUE USING l_line 4 pri_pack-cnum.
PERFORM SET_EXCEL_VALUE USING l_line 5 'CTNS'.
l_line = l_line + 1.
PERFORM SET_EXCEL_VALUE USING l_line 3 ' TOTAL QTY:'.
PERFORM SET_EXCEL_VALUE USING l_line 4 l_qty. "it_hpack-zpall.
PERFORM SET_EXCEL_VALUE USING l_line 5 'PCS'. "'PALLETS'
l_line = l_line + 1.
PERFORM SET_EXCEL_VALUE USING l_line 3 'TOTAL NET WEIGHT:'.
PERFORM SET_EXCEL_VALUE USING l_line 4 l_ntgew.
PERFORM SET_EXCEL_VALUE USING l_line 5 'KGS'.
l_line = l_line + 1.
PERFORM SET_EXCEL_VALUE USING l_line 3 'TOTAL GR. WEIGHT:'.
PERFORM SET_EXCEL_VALUE USING l_line 4 l_brgew.
PERFORM SET_EXCEL_VALUE USING l_line 5 'KGS'.
l_line = l_line + 1.
PERFORM SET_EXCEL_VALUE USING l_line 3 ' TOTAL PCS:'.
PERFORM SET_EXCEL_VALUE USING l_line 4 it_hpack-zpall.
PERFORM SET_EXCEL_VALUE USING l_line 5 'PALLETS'.
l_line = l_line + 3.
PERFORM SET_EXCEL_VALUE USING l_line 6 ' 签 收 人：'.
l_line = l_line + 2.
PERFORM SET_EXCEL_VALUE USING l_line 6 '签收日期：'.
endif.
endloop.
GET PROPERTY OF appl 'ACTIVESHEET' = sheet.
GET PROPERTY OF appl 'ACTIVEWORKBOOK' = book.
CLEAR: l_down_save.
CONCATENATE l_date_file '_' it_hpack-vbeln '.xls' INTO l_down_save.
CALL METHOD OF book 'SAVEAS'
EXPORTING #1 = l_down_save "---'c:/test22.xls'
#2 = 1. "file format
SET PROPERTY OF appl 'VISIBLE' = 1.
* CALL METHOD OF sheet 'CLOSE'. "因為多Screen，以致Excel進程不能結束,
* CALL METHOD OF book 'CLOSE'. "所以先顯示導出結果，用戶退出程式方可結束進程
* CALL METHOD OF appl 'QUIT'.
FREE OBJECT sheet.
FREE OBJECT book.
FREE OBJECT appl.
endif.
endloop.
ENDFORM. "--DOWNLOAD_EXCEL_DATA