我很好
分类: WINDOWS
2008-05-28 09:07:37
form XLS into intenal table:
Function: ALSM_EXCEL_TO_INTERNAL_TABLE
form internal table into intenal XLS: 往xls模板中填写数据
有时我们打印表单的时候,也可以通过excel模板来实现,把数据从程序内表中下载到模板,模板中对应字段或者行通过名字和程序对应起来。我们可以通过如下程序实现:
INCLUDE ole2incl.
DATA: excelobj TYPE ole2_object, " Excel object
wbookobj TYPE ole2_object, " Workbook object
wsheetobj TYPE ole2_object, " Worksheet object
rangeobj TYPE ole2_object, " Range object
cellobj TYPE ole2_object. " Cell object
DEFINE create_excel.
create object excelobj 'Excel.Application'. " Create excel object
if sy-subrc ne 0.
write: / 'Excel init failed, return code is ', sy-subrc.
exit.
else.
set property of excelobj 'Visible' = 1. " Set excel visible
call method of excelobj " Create wbook object
'Workbooks' = wbookobj.
call method of wbookobj 'Open' = wbookobj
exporting #1 = &1. "srcfile1
call method of wbookobj " Create wsheet object
'Worksheets' = wsheetobj exporting #1 = 'sheet1'.
call method of wsheetobj 'Activate'.
endif.
END-OF-DEFINITION.
PARAMETER: scrfile0(40) TYPE c DEFAULT 'c:\合同清单(继保软件).xls', "模板
savefil0(40) TYPE c DEFAULT 'c:\sapworkdir\合同清单(继保软件).xls'. "输出值
。。。。
。。。。
。。。。
create_excel scrfile0. "输出到Excel
PERFORM fill_range USING 'bstkd' head_bstkd. "输出表头
PERFORM fill_range USING 'vbeln' head_vbeln.
PERFORM fill_range USING 'netwr' head_tprice.
PERFORM fill_range USING 'name' head_name1.
LOOP AT orderlist. "输出行项目
CHECK orderlist-shprice NE 0. "产品单价为零
CHECK orderlist-tprice NE 0.
PERFORM fill_range USING 'matnr' orderlist-matnr.
PERFORM fill_range USING 'maktx' orderlist-maktx.
PERFORM fill_range USING 'maktx1' orderlist-maktx1.
PERFORM fill_range USING 'kwmeng' orderlist-kwmeng.
PERFORM fill_range USING 'sprice' orderlist-shprice.
PERFORM fill_range USING 'tprice' orderlist-tprice.
PERFORM insert_row USING 'rowno'.
ENDLOOP.
PERFORM fill_save USING savefil0.
*-----------------------------------------------------------------
* FORM fill_range
*-----------------------------------------------------------------
FORM fill_range USING value(f_range)
value(f_value).
CALL METHOD OF wsheetobj 'Range' = rangeobj
EXPORTING #1 = f_range.
SET PROPERTY OF rangeobj 'Value' = f_value.
ENDFORM.
*-----------------------------------------------------------------
* FORM fill_rowno
*-----------------------------------------------------------------
FORM fill_rowno.
CALL METHOD OF excelobj 'run'
EXPORTING #1 = 'ROWNO'.
ENDFORM.
*-----------------------------------------------------------------
* FORM fill_save
*-----------------------------------------------------------------
FORM fill_save USING value(f_save).
CALL METHOD OF wbookobj 'SaveAs' = wbookobj
EXPORTING #1 = f_save.
ENDFORM.
*-----------------------------------------------------------------
* FORM insert_row
*-----------------------------------------------------------------
FORM insert_row USING value(f_range).
CALL METHOD OF wsheetobj 'range' = rangeobj
EXPORTING #1 = f_range.
CALL METHOD OF rangeobj 'copy'.
CALL METHOD OF rangeobj 'select'.
CALL METHOD OF rangeobj 'insert'.
CALL METHOD OF rangeobj 'clearcontents'.
ENDFORM.
下载到Excel中有字段描述:
我们在做数据导出的时候经常用临时表把数据暂时存一下再输出,比如:
.......
at user-command.
case sy-ucomm.
when 'DOWN_ALL'.
perform all_data_excel_download.
when 'DOWN_DETA'.
perform detail_data_excel_download.
endcase.
......
form detail_data_excel_download.
data filename like rlgrap-filename.
refresh i_down_detail. clear i_down_detail.
move : 'P/O' to i_down_detail-ebeln,
'Item' to i_down_detail-ebelp,
'Mat_code' to i_down_detail-matnr,
'Group' to i_down_detail-matkl,
'Type' to i_down_detail-mtart,
'Netwr' to i_down_detail-netwr,
'Vendor' to i_down_detail-lifnr,
'Land' to i_down_detail-land1,
'Wkurs' to i_down_detail-wkurs,
'Amount' to i_down_detail-amt.
append i_down_detail. clear i_down_detail.
loop at iekpo.
move-corresponding iekpo to i_down_detail.
append i_down_detail. clear i_down_detail.
endloop.
call function 'WS_FILENAME_GET'
exporting
def_filename = ''
def_path = 'C:\'
mask = '*.XLS,*.XLS,*.XLS,*.TXT,*.TXT,*.*,*.*.'
mode = 'S'
importing
filename = filename
exceptions
inv_winsys = 1
no_batch = 2
selection_cancel = 3
selection_error = 4
others = 5.
call function 'WS_DOWNLOAD'
exporting
filename = filename
filetype = 'DAT'
tables
data_tab = i_down_detail
exceptions
file_open_error = 1
file_write_error = 2
invalid_filesize = 3
invalid_type = 4
no_batch = 5
unknown_error = 6
invalid_table_width = 7
gui_refuse_filetransfer = 8
customer_error = 9.
endform. " detail_data_excel_download
exporting
filename = 'C:\装置销售业绩清单.xls'
filetype = 'WK1'
tables
data_tab = tablename.
call function 'WS_EXCEL'
exporting
filename = 'C:\11.xls'
synchron = space
tables
data = it_ekpo. "单元格中最好要没有空格,否则可能串位