TYPE-POOLS: slis,vrm, sbdst, soi.
TABLES: likp, kna1,lips,vbap,mara,konv.
DATA: gd_fieldcat TYPE slis_t_fieldcat_alv.
DATA: sla TYPE slis_layout_alv ,
ivariant LIKE disvariant,
i_repid LIKE sy-repid .
CONSTANTS document_name(30) VALUE 'SD01'.
CONSTANTS inplace VALUE 'X'.
DATA: flag .
DATA: g_def_active_sheetname(50) VALUE 'Sheet1'.
DATA: container TYPE REF TO cl_gui_custom_container,
control TYPE REF TO i_oi_container_control,
document TYPE REF TO i_oi_document_proxy,
spreadsheet TYPE REF TO i_oi_spreadsheet,
error TYPE REF TO i_oi_error,
errors TYPE REF TO i_oi_error OCCURS 0 WITH HEADER LINE.
CONTROLS: exceldata TYPE TABLEVIEW USING SCREEN 0100.
DATA: tablename(10), okcode(15),
row(4), column(4), data(39).
* spreadsheet interface structures for Excel data input
DATA: rangeitem TYPE soi_range_item.
DATA: ranges TYPE soi_range_list.
DATA: excel_input TYPE soi_generic_table.
DATA: excel_input_wa TYPE soi_generic_item.
DATA: initialized(1), retcode TYPE soi_ret_string.
DATA: item_url(256), already_done, newname(40).
DATA document_type(80).
DATA: app TYPE vrm_id, applist TYPE vrm_values.
DATA: excel(80) VALUE 'Excel.Sheet'.
DATA: line_count TYPE i,
column_count TYPE i.
DATA: BEGIN OF table_lines OCCURS 0.
INCLUDE STRUCTURE tline.
DATA: END OF table_lines.
DATA: g_prodh LIKE mvke-prodh. "产品层次
*---------------------------------*
*data define
*---------------------------------*
DATA: BEGIN OF it_itab OCCURS 0,
vkorg LIKE likp-vkorg,
kunnr LIKE likp-kunnr,
name1 LIKE kna1-name1,
wadat_ist LIKE likp-wadat_ist,
matnr LIKE lips-matnr,
arktx LIKE lips-arktx,
* lfimg LIKE lips-lfimg,
lfimg TYPE i ,
vbeln LIKE lips-vbeln,
posnr LIKE lips-posnr,
vgbel LIKE lips-vgbel,
* netpr LIKE ekpo-netpr,
netpr TYPE p DECIMALS 5,
peinh LIKE ekpo-peinh,
menge LIKE ekpo-menge,
bedat LIKE ekko-bedat,
vgpos LIKE lips-vgpos,
cob TYPE p DECIMALS 5,
smtz TYPE p DECIMALS 5,
cobz TYPE p DECIMALS 5,
meno(120) TYPE c,
flag TYPE c ,
END OF it_itab.
DATA: BEGIN OF it_chart OCCURS 0 ,
name1 LIKE kna1-name1 ,
smtz TYPE p DECIMALS 5 ,
END OF it_chart .
SELECTION-SCREEN BEGIN OF BLOCK blk01 WITH FRAME TITLE text-001.
SELECT-OPTIONS:
s_vkorg FOR likp-vkorg DEFAULT '1000'.
PARAMETERS:
p_lfart LIKE likp-lfart DEFAULT 'LF' OBLIGATORY.
SELECT-OPTIONS:
s_kunnr FOR kna1-kunnr,
s_wadat FOR likp-wadat_ist,
s_matnr FOR lips-matnr,
s_vbeln FOR likp-vbeln.
PARAMETERS : cob(10) TYPE c OBLIGATORY DEFAULT '2' .
SELECTION-SCREEN END OF BLOCK blk01.
PARAMETERS :
p_chart TYPE c AS CHECKBOX DEFAULT 'X' .
INITIALIZATION .
* CASE sy-tcode.
* WHEN 'ZSD14' .
* p_lfart = 'ZLCC'.
* WHEN 'ZSD15'.
* p_lfart = 'LF'.
* ENDCASE.
*
*AT SELECTION-SCREEN OUTPUT.
* IF sy-tcode <> 'SE38'.
* LOOP AT SCREEN.
* IF screen-name = 'P_LFART'.
* screen-input = '0'.
* MODIFY SCREEN.
* ENDIF.
* ENDLOOP.
* ENDIF.
*SELECTION-SCREEN BEGIN OF BLOCK b2 WITH FRAME TITLE text-002.
*PARAMETERS:
* p_n RADIOBUTTON GROUP rad1 DEFAULT 'X' ,
* p_w RADIOBUTTON GROUP rad1 .
*SELECTION-SCREEN END OF BLOCK b2.
*-------------------------------------*
*start of selection
*-------------------------------------*
START-OF-SELECTION.
IF p_lfart <> 'ZLCC' AND p_lfart <> 'LF'.
MESSAGE i000(oo) WITH '订单类型必须为LF或者ZLCC'.
EXIT.
ENDIF.
PERFORM frm_get_data.
PERFORM fixdata.
PERFORM get_chart_data.
IF p_chart = ''.
PERFORM outdata.
ELSE.
CALL SCREEN 100.
ENDIF.
*----------------------------------------*
*form frm-get-data
*----------------------------------------*
FORM frm_get_data.
SELECT
likp~vkorg
likp~kunnr
likp~wadat_ist
likp~vbeln
lips~posnr
lips~matnr
lips~arktx
lips~lfimg
lips~vgbel
lips~vgpos
kna1~name1
INTO CORRESPONDING FIELDS OF TABLE it_itab
FROM likp
INNER JOIN lips ON likp~vbeln = lips~vbeln
INNER JOIN kna1 ON likp~kunnr = kna1~kunnr
WHERE likp~vkorg IN s_vkorg AND
likp~lfart = p_lfart AND
likp~kunnr IN s_kunnr AND
likp~wadat_ist IN s_wadat AND
likp~vbeln IN s_vbeln.
ENDFORM. "frm_get_data
*&---------------------------------------------------------------------*
*& Form fixdata
*&---------------------------------------------------------------------*
FORM fixdata.
DATA: tmpvbeln LIKE vbap-vbeln ,
tmpposnr LIKE vbap-posnr ,
tmpknumv LIKE vbak-knumv .
LOOP AT it_itab.
CLEAR g_prodh.
CLEAR:tmpvbeln ,tmpposnr ,tmpknumv .
IF p_lfart = 'ZLCC'.
SELECT SINGLE netpr peinh menge
INTO (it_itab-netpr,it_itab-peinh,it_itab-menge)
FROM ekpo
WHERE ekpo~ebeln = it_itab-vgbel AND
ekpo~ebelp = it_itab-vgpos+1(5).
SELECT SINGLE bedat INTO it_itab-bedat
FROM ekko
WHERE ekko~ebeln = it_itab-vgbel.
ELSE.
SELECT SINGLE vbak~knumv vbap~vbeln vbap~posnr
INTO (tmpknumv,tmpvbeln,tmpposnr)
FROM vbak INNER JOIN vbap ON vbak~vbeln = vbap~vbeln
WHERE vbak~auart = 'ZOR1' AND
vbap~vbeln = it_itab-vgbel AND
vbap~posnr = it_itab-vgpos .
IF sy-subrc = 0 .
SELECT SINGLE * "kbetr kumza INTO (it_itab-netpr,it_itab-peinh)
FROM konv
WHERE knumv = tmpknumv AND
kschl = 'ZSMT'.
IF sy-subrc = 0 .
it_itab-netpr = konv-kbetr.
it_itab-peinh = konv-kumza.
ENDIF.
ELSE.
it_itab-flag = 'D'.
ENDIF.
ENDIF.
IF it_itab-peinh <> 0.
it_itab-netpr = it_itab-netpr / it_itab-peinh.
ENDIF.
*COB单价
it_itab-cob = cob.
*产品层次
SELECT SINGLE prodh INTO g_prodh
FROM mvke
WHERE matnr = it_itab-matnr AND
vkorg = it_itab-vkorg.
IF g_prodh+10(8) = '00000001'.
it_itab-netpr = it_itab-netpr - cob.
ELSE.
it_itab-cob = 0 .
ENDIF.
*COB总金额
it_itab-cobz = it_itab-cob * it_itab-lfimg.
*SMT总金额
it_itab-smtz = it_itab-netpr * it_itab-lfimg.
*读取文本(贴装点数)
PERFORM read_text USING it_itab-matnr
it_itab-meno.
MODIFY it_itab.
CLEAR it_itab.
ENDLOOP.
DELETE it_itab WHERE flag = 'D'.
ENDFORM. " fixdata
*&---------------------------------------------------------------------*
*& Form get_chart_data
*&---------------------------------------------------------------------*
FORM get_chart_data.
DATA: tmpcobz TYPE p DECIMALS 5 .
SORT it_itab BY name1.
LOOP AT it_itab.
MOVE-CORRESPONDING it_itab TO it_chart.
COLLECT it_chart.
tmpcobz = tmpcobz + it_itab-cobz.
ENDLOOP.
it_chart-name1 = 'COB'.
it_chart-smtz = tmpcobz .
APPEND it_chart.
ENDFORM. " get_chart_data
*&------------------------------------------------------*
*& Form read_text
*&------------------------------------------------------*
FORM read_text USING matnr LIKE it_itab-matnr
memo.
DATA: tdname LIKE thead-tdname.
CONCATENATE matnr '880300' INTO tdname.
CLEAR memo.
CLEAR table_lines.
REFRESH table_lines.
CALL FUNCTION 'READ_TEXT'
EXPORTING
id = '0001'
language = '1'
name = tdname
object = 'MVKE'
TABLES
lines = table_lines
EXCEPTIONS
id = 1
language = 2
name = 3
not_found = 4
object = 5
reference_check = 6
wrong_access_to_archive = 7
OTHERS = 8.
* LOOP AT table_lines .
* CONCATENATE memo table_lines-tdline INTO memo SEPARATED BY '|' .
* ENDLOOP .
* IF memo+0(1) = '|' . SHIFT memo . ENDIF .
* CLEAR table_lines .
* REFRESH table_lines .
READ TABLE table_lines INDEX 1.
IF sy-subrc = 0 .
memo = table_lines-tdline .
ELSE.
memo = ''.
ENDIF.
ENDFORM. "READ_TEXT
*&---------------------------------------------------------------------*
*& Form outdata
*&---------------------------------------------------------------------*
FORM outdata.
PERFORM fieldcat_init .
sla-colwidth_optimize = 'X'.
i_repid = sy-repid.
CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
EXPORTING
i_callback_program = i_repid
it_fieldcat = gd_fieldcat[]
i_save = 'A'
is_variant = ivariant
is_layout = sla
i_callback_user_command = 'USER_COMMAND'
TABLES
t_outtab = it_itab[].
ENDFORM. " outdata
*---------------------------------------------------------------------*
* FORM fieldcat_init *
*---------------------------------------------------------------------*
FORM fieldcat_init .
PERFORM frm_catlg_set USING: 'VBELN' '交货单号',
'POSNR' '交货单项目'.
IF p_lfart = 'ZLCC'.
PERFORM frm_catlg_set USING: 'VGBEL' '采购单号',
'VGPOS' '采购单项目',
'BEDAT' '采购订单日期',
'MENGE' '采购订单数量'.
ELSE.
PERFORM frm_catlg_set USING: 'VGBEL' '销售单号',
'VGPOS' '销售单项目'.
ENDIF.
PERFORM frm_catlg_set USING: 'KUNNR' '客户代码',
'NAME1' '客户名称',
'MATNR' '物料编号',
'ARKTX' '规格型号',
'WADAT_IST' '实际发货日期',
'LFIMG' '实际发货数量',
'MENO' '贴装点数',
'NETPR' 'SMT单价',
'SMTZ' 'SMT总金额',
'COB' 'COB单价',
'COBZ' 'COB总金额' .
ENDFORM. "fieldcat_init
*---------------------------------------------------------------------*
* FORM frm_catlg_set *
*---------------------------------------------------------------------*
FORM frm_catlg_set USING p_field
p_text.
DATA: ls_fieldcat TYPE slis_fieldcat_alv.
ls_fieldcat-fieldname = p_field.
ls_fieldcat-seltext_l = p_text.
APPEND ls_fieldcat TO gd_fieldcat .
CLEAR ls_fieldcat .
ENDFORM. "frm_catlg_set
*---------------------------------------------------------------------*
* FORM user_command *
*---------------------------------------------------------------------*
FORM user_command USING r_ucomm LIKE sy-ucomm
rs_selfield TYPE slis_selfield.
READ TABLE it_itab INDEX rs_selfield-tabindex.
CASE r_ucomm.
WHEN '&IC1'. "双击
IF rs_selfield-fieldname = 'VBELN'.
SET PARAMETER ID 'VL' FIELD it_itab-vbeln .
CALL TRANSACTION 'VL03N' AND SKIP FIRST SCREEN .
ELSEIF rs_selfield-fieldname = 'VGBEL' AND p_lfart = 'ZLCC' .
CALL FUNCTION 'ME_DISPLAY_PURCHASE_DOCUMENT'
EXPORTING
i_ebeln = it_itab-vgbel
EXCEPTIONS
not_found = 1
no_authority = 2
invalid_call = 3
preview_not_possible = 4
OTHERS = 5.
ELSEIF rs_selfield-fieldname = 'VGBEL' AND p_lfart <> 'ZLCC' .
SET PARAMETER ID 'AUN' FIELD it_itab-vgbel .
CALL TRANSACTION 'VA03' AND SKIP FIRST SCREEN .
ENDIF.
WHEN 'EXIT'.
LEAVE PROGRAM.
ENDCASE.
ENDFORM. "user_com
*&-------------------------------------------------------------------
*& Module OUTPUT_TO_EXCEL OUTPUT
*&-------------------------------------------------------------------
MODULE output_to_excel OUTPUT.
SET PF-STATUS '100'.
IF flag = space .
PERFORM create_basic_objects USING '' '' '' '' document_name.
PERFORM output_to_excel.
ENDIF.
ENDMODULE. " OUTPUT_TO_EXCEL OUTPUT
*&---------------------------------------------------------------------*
*& Module EXIT INPUT
*&---------------------------------------------------------------------*
MODULE exit INPUT.
flag = 'X'.
CASE okcode.
WHEN 'TESTMAC'.
CALL METHOD document->execute_macro
EXPORTING
macro_string = '模块1.TestMac'
IMPORTING
error = errors.
WHEN 'STOP' .
IF NOT document IS INITIAL.
CALL METHOD document->close_document.
FREE document.
ENDIF.
IF NOT control IS INITIAL.
CALL METHOD control->destroy_control.
FREE control.
ENDIF.
LEAVE PROGRAM.
WHEN 'BACK' .
IF NOT document IS INITIAL.
CALL METHOD document->close_document.
FREE document.
ENDIF.
IF NOT control IS INITIAL.
CALL METHOD control->destroy_control.
FREE control.
ENDIF.
" LEAVE TO SCREEN 1000.
SET SCREEN 0. " quit the program
"set screen 1000.
ENDCASE.
ENDMODULE. " EXIT INPUT
*---------------------------------------------------------------------*
* FORM create_basic_objects *
*---------------------------------------------------------------------*
FORM create_basic_objects USING p_app_name
p_classname
p_classtype
p_obj_key
p_docname.
CHECK initialized IS INITIAL.
* first get the SAP DOI i_oi_container_control interface
CALL METHOD
c_oi_container_control_creator=>get_container_control
IMPORTING
control = control
error = error.
* check no errors occured
CALL METHOD error->raise_message
EXPORTING
type = 'E'.
CREATE OBJECT container
EXPORTING
container_name = 'CONTAINER'.
DATA l_app_name(200).
IF p_app_name IS INITIAL.
l_app_name = 'R/3 Reporter'.
ELSE.
l_app_name = p_app_name.
ENDIF.
CALL METHOD control->init_control
EXPORTING
r3_application_name = l_app_name
inplace_enabled = inplace
inplace_scroll_documents = 'X'
parent = container
register_on_close_event = 'X'
register_on_custom_event = 'X'
no_flush = 'X'
IMPORTING
error = errors.
* save error object in collection
APPEND errors.
CLEAR item_url.
DATA: bds_instance TYPE REF TO cl_bds_document_set.
DATA: doc_signature TYPE sbdst_signature,
wa_doc_signature LIKE LINE OF doc_signature,
doc_components TYPE sbdst_components,
doc_uris TYPE sbdst_uri,
wa_doc_uris LIKE LINE OF doc_uris.
DATA: doc_classname TYPE sbdst_classname VALUE 'ZEXCEL',
doc_classtype TYPE sbdst_classtype VALUE 'OT',
doc_object_key TYPE sbdst_object_key VALUE 'ZEXCEL'.
IF NOT ( p_classname IS INITIAL OR p_classtype IS INITIAL
OR p_obj_key IS INITIAL ).
doc_classname = p_classname.
doc_classtype = p_classtype.
doc_object_key = p_obj_key.
ENDIF.
wa_doc_signature-prop_name = 'DESCRIPTION'.
app = 'excel'.
IF app = 'excel'. " default value.
document_type = excel.
wa_doc_signature-prop_value = p_docname.
ELSE.
* document_type = lotus.
* wa_doc_signature-prop_value = 'SPREAD123'.
ENDIF.
APPEND wa_doc_signature TO doc_signature.
CREATE OBJECT bds_instance.
CALL METHOD bds_instance->get_info
EXPORTING
classname = doc_classname
classtype = doc_classtype
object_key = doc_object_key
CHANGING
components = doc_components
signature = doc_signature.
CALL METHOD bds_instance->get_with_url
EXPORTING
classname = doc_classname
classtype = doc_classtype
object_key = doc_object_key
CHANGING
uris = doc_uris
signature = doc_signature.
FREE bds_instance.
READ TABLE doc_uris INTO wa_doc_uris INDEX 1.
item_url = wa_doc_uris-uri.
* ask the SAP DOI container for a i_oi_document_proxy for Excel
CALL METHOD control->get_document_proxy
EXPORTING
document_type = 'Excel.Sheet'
no_flush = 'X'
IMPORTING
document_proxy = document
error = errors.
APPEND errors.
* open a document saved in business document service.
CALL METHOD document->open_document
EXPORTING
open_inplace = inplace
document_url = item_url.
DATA: has TYPE i.
CALL METHOD document->has_spreadsheet_interface
EXPORTING
no_flush = ''
IMPORTING
is_available = has
error = errors.
APPEND errors.
CALL METHOD document->get_spreadsheet_interface
EXPORTING
no_flush = ' '
IMPORTING
sheet_interface = spreadsheet
error = errors.
APPEND errors.
* Activate sheet 1
CALL METHOD spreadsheet->select_sheet
EXPORTING
name = g_def_active_sheetname
no_flush = ''
IMPORTING
error = errors.
APPEND errors.
LOOP AT errors.
CALL METHOD errors->raise_message
EXPORTING
type = 'E'.
ENDLOOP.
FREE errors.
initialized = 'X'.
ENDFORM. " CREATE_BASIC_OBJECTS
*---------------------------------------------------------------------*
* FORM output_to_excel *
*---------------------------------------------------------------------*
FORM output_to_excel.
DATA: tmpdate(10) TYPE c.
column_count = 2.
it_chart-name1 = 'Test1'.
it_chart-smtz = 20.
APPEND it_chart.
it_chart-name1 = 'Test2'.
it_chart-smtz = 30.
APPEND it_chart.
it_chart-name1 = 'Test3'.
it_chart-smtz = 40.
APPEND it_chart.
LOOP AT it_chart.
line_count = 2.
PERFORM fill_cell USING line_count
column_count
it_chart-name1.
line_count = line_count + 1.
PERFORM fill_cell USING line_count
column_count
it_chart-smtz.
column_count = column_count + 1.
ENDLOOP.
ENDFORM. "output_to_excel
*---------------------------------------------------------------------*
* FORM fill_cell *
*---------------------------------------------------------------------*
FORM fill_cell USING i j val.
DATA: columns_number TYPE i,
rows_number TYPE i.
columns_number = 1.
rows_number = 1.
CALL METHOD spreadsheet->insert_range_dim
EXPORTING
name = 'cell'
no_flush = 'X'
top = i
left = j
rows = rows_number
columns = columns_number
IMPORTING
error = errors.
APPEND errors.
REFRESH: ranges, excel_input.
rangeitem-name = 'cell'.
rangeitem-columns = 1.
rangeitem-rows = 1.
APPEND rangeitem TO ranges.
excel_input_wa-column = 1.
excel_input_wa-row = 1.
excel_input_wa-value = val.
APPEND excel_input_wa TO excel_input.
* set data
CALL METHOD spreadsheet->set_ranges_data
EXPORTING
ranges = ranges
contents = excel_input
no_flush = 'X'
IMPORTING
error = errors.
APPEND errors.
CALL METHOD spreadsheet->fit_widest
EXPORTING
name = space
no_flush = 'X'.
REFRESH: ranges, excel_input.
ENDFORM. "fill_cell
阅读(1748) | 评论(0) | 转发(0) |