SAP EXCEL 上传和下载
前段时间做了一个关于EXCEL模板数据上传SAP，并将内表数据下载到EXCEL中的程序。由于网络原因，当数据量比较大的时候，使用OLE的逐条写数据的话，速度会比较慢，所以先将内表的数据复制到剪贴板上，然后再直接粘贴到EXCEL中，速度会比较快。下面说一下方法：
1.首先制作好EXCEL数据模板，用SMW0上传到服务器上。
EXCEL模板样式如下：
2.数据定义：
DATA: BEGIN OF header,
doc_date LIKE bapi_incinv_create_header-doc_date, "凭证日期
pstng_date LIKE bapi_incinv_create_header-pstng_date, "过账日期
gross_amount LIKE invfo-wrbtr,"bapi_incinv_create_header-gross_amount, "凭证货币的总发票金额
item_amount LIKE invfo-wmwst,"bapi_incinv_create_gl_account-item_amount,"税额
gl_account LIKE bapi_incinv_create_gl_account-gl_account, "税金科目
payee_payer LIKE bapi_incinv_create_header-payee_payer, "发票方
item_text LIKE bapi_incinv_create_header-item_text, "行项目文本
header_txt LIKE bapi_incinv_create_header-header_txt, "抬头文本
ref_doc_no LIKE bapi_incinv_create_header-ref_doc_no, "发票号
pmnttrms LIKE bapi_incinv_create_header-pmnttrms, "付款条件
currency LIKE bapi_incinv_create_header-currency, "货币码
del_costs_taxc LIKE bapi_incinv_create_header-del_costs_taxc, "税码
db_cr_ind LIKE bapi_incinv_create_gl_account-db_cr_ind, "税金借贷标识
blance TYPE rm08m-differenz, "余额
name12 TYPE zname12, "供应商描述
END OF header.
DATA: BEGIN OF itemOCCURS 0,
ebeln LIKE ekpo-ebeln,
ebelp LIKE ekpo-ebelp,
matnr LIKE ekpo-matnr,
maktx LIKE makt-maktx,
meins LIKE ekpo-meins,
menge LIKE ekbe-menge,
wrbtr LIKE ekbe-wrbtr, "金额
mwskz LIKE ekpo-mwskz, "税务代码
selct(1) TYPE c,
END OF item.
TYPE-POOLS: ole2.
DATA: gv_excel_obj TYPE ole2_object,
gv_workbook_obj TYPE ole2_object,
gv_sheet_obj TYPE ole2_object.
DATA: gv_fname LIKE rlgrap-filename,
gv_filename TYPE string,
gv_icount TYPE i,
gv_irow TYPE i.
DATA: gv_separator TYPE c.
FIELD-SYMBOLS:<fs_item> LIKE LINE OF item,
<fs_fld> TYPE ANY.
DATA: gt_excel(4096) OCCURS 0 WITH HEADER LINE.
TYPE-POOLS truxs.
DATA xltab TYPE truxs_t_text_data.
DATA: BEGIN OF gt_upload OCCURS 0,
cola(20),
colb(40),
colc(20),
cold(40),
cole(20),
colf(20),
colg(20),
colh(10).
DATA: END OF gt_upload.
DATA: gv_file LIKE rlgrap-filename.
3.DOWNLOAD_EXCEL子程序
*&---------------------------------------------------------------------*
*& Form DOWNLOAD_EXCEL
*&---------------------------------------------------------------------*
FORM download_excel .
****取得模板文件和路径
PERFORM temp_excel_get CHANGING gv_fname.
****以模板生成文件
PERFORM open_excel_file USING gv_excel_obj
gv_workbook_obj
gv_sheet_obj
gv_fname
'0'.
****填充模板
PERFORM write_header.
PERFORM write_item USING gv_excel_obj
gv_sheet_obj.
PERFORM save_close_excel USING gv_excel_obj
gv_workbook_obj
gv_sheet_obj.
MESSAGE ‘ 保存完毕!' TYPE 'I'.
ENDFORM. " DOWNLOAD_EXCEL
*&---------------------------------------------------------------------*
*& Form temp_excel_get
*&---------------------------------------------------------------------*
FORM temp_excel_get CHANGING p_fname.
DATA: l_objdata LIKE wwwdatatab,
l_destination LIKE rlgrap-filename,
l_rc LIKE sy-subrc,
l_errtxt TYPE string.
SELECT SINGLE relid objid
FROM wwwdata
INTO CORRESPONDING FIELDS OF l_objdata
WHERE srtf2 = 0
AND relid = 'MI'
AND objid = 'ZTEST_003'. "此处为EXCEL模板名称
l_destination = ’D:\模板.XLS'. "下载模板路径
* 检查表wwwdata中是否存在所指定的模板文件
IF sy-subrc NE 0 OR l_objdata-objid = space."如果不存在，则给出错误提示
CONCATENATE '模板' '不存在' INTO l_errtxt.
MESSAGE l_errtxt TYPE 'I'.
EXIT.
ENDIF.
* 如果存在，调用DOWNLOAD_WEB_OBJECT 函数下载模板到路径下
CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
EXPORTING
key = l_objdata
destination = l_destination
IMPORTING
rc = l_rc.
IF l_rc NE 0.
CONCATENATE '模板文件：' l_destination '下载失败!' INTO l_errtxt.
MESSAGE l_errtxt TYPE 'I'.
EXIT.
ENDIF.
p_fname = l_destination.
ENDFORM. " TEMP_EXCEL_GET
*&---------------------------------------------------------------------*
*& Form open_excel_file
*&---------------------------------------------------------------------*
FORM open_excel_file USING p_excel
p_workbook
p_sheet
p_fname
p_visible.
CREATE OBJECT p_excel 'Excel.Application'.
IF sy-subrc NE 0.
gv_flag = 'N'.
MESSAGE i796(f9) WITH '不能创建Excel对象'.
EXIT.
ENDIF.
CALL METHOD OF p_excel 'Workbooks' = p_workbook.
CALL METHOD OF p_workbook 'Open' = p_workbook
EXPORTING #1 = p_fname.
IF sy-subrc NE 0.
gv_flag = 'N'.
MESSAGE i796(f9) WITH '打开文件错误'.
EXIT.
ENDIF.
SET PROPERTY OF p_excel 'Visible' = p_visible.
CALL METHOD OF p_workbook 'Sheets' = p_sheet
EXPORTING #1 = 1.
* SET PROPERTY OF lcobj_sheet 'name' = sheetname.
ENDFORM. " OPEN_EXCEL_FILE
*&---------------------------------------------------------------------*
*& Form WRITE_HEADER
*&---------------------------------------------------------------------*
FORM write_header .
DATA: l_cell TYPE ole2_object.
*发票日期
CALL METHOD OF gv_excel_obj 'Cells' = l_cell
EXPORTING #1 = 1 "第1行
#2 = 2. "B列
SET PROPERTY OF l_cell 'Value' = header-doc_date.
*过账日期
CALL METHOD OF gv_excel_obj 'Cells' = l_cell
EXPORTING #1 = 2
#2 = 2.
SET PROPERTY OF l_cell 'Value' = header-pstng_date.
*发票号
CALL METHOD OF gv_excel_obj 'Cells' = l_cell
EXPORTING #1 = 3
#2 = 2.
SET PROPERTY OF l_cell 'Value' = header-ref_doc_no.
*付款条件
CALL METHOD OF gv_excel_obj 'Cells' = l_cell
EXPORTING #1 = 4
#2 = 2.
SET PROPERTY OF l_cell 'Value' = header-pmnttrms.
*货币
CALL METHOD OF gv_excel_obj 'Cells' = l_cell
EXPORTING #1 = 5
#2 = 2.
SET PROPERTY OF l_cell 'Value' = header-currency.
*抬头文本
CALL METHOD OF gv_excel_obj 'Cells' = l_cell
EXPORTING #1 = 6
#2 = 2.
SET PROPERTY OF l_cell 'Value' = header-header_txt.
*行项目文本
CALL METHOD OF gv_excel_obj 'Cells' = l_cell
EXPORTING #1 = 7
#2 = 2.
SET PROPERTY OF l_cell 'Value' = header-item_text.
*发票总金额
CALL METHOD OF gv_excel_obj 'Cells' = l_cell
EXPORTING #1 = 1
#2 = 'E'.
SET PROPERTY OF l_cell 'Value' = header-gross_amount.
*税额
CALL METHOD OF gv_excel_obj 'Cells' = l_cell
EXPORTING #1 = 2
#2 = 'E'.
SET PROPERTY OF l_cell 'Value' = header-item_amount.
*税码
CALL METHOD OF gv_excel_obj 'Cells' = l_cell
EXPORTING #1 = 3
#2 = 'E'.
SET PROPERTY OF l_cell 'Value' = header-del_costs_taxc.
*发票方
CALL METHOD OF gv_excel_obj 'Cells' = l_cell
EXPORTING #1 = 4
#2 = 'E'.
SET PROPERTY OF l_cell 'Value' = header-payee_payer.
ENDFORM. " WRITE_HEADER
*&---------------------------------------------------------------------*
*& Form WRITE_ITEM
*&---------------------------------------------------------------------*
FORM write_item USING p_excel
p_sheet.
* FIELD-SYMBOLS: <fld> TYPE ANY.
* DATA: lv_rows TYPE i,
* lv_cols TYPE i.
* DATA: l_cell TYPE ole2_object.
*
* lv_rows = 9. "从第10行开始
* LOOP AT item WHERE selct = 'X'.
* lv_cols = 1.
* DO 8 TIMES.
* ASSIGN COMPONENT sy-index OF STRUCTURE item TO <fld>.
* CALL METHOD OF gv_excel_obj 'Cells' = l_cell
* EXPORTING #1 = lv_rows
* #2 = lv_cols.
* SET PROPERTY OF l_cell 'Value' = <fld>.
* lv_cols = lv_cols + 1.
* ENDDO.
* lv_rows = lv_rows + 1.
* ENDLOOP.
DATA: lv_index TYPE i.
DATA: lv_str(512).
gv_separator = cl_abap_char_utilities=>horizontal_tab.
CLEAR gt_excel.
REFRESH gt_excel.
LOOP AT item
ASSIGNING <fs_item>.
lv_index = 0.
DO 8 TIMES.
lv_index = lv_index + 1.
ASSIGN COMPONENT lv_index OF STRUCTURE <fs_item> TO <fs_fld>.
IF sy-subrc <> 0.
EXIT.
ENDIF.
CLEAR lv_str.
WRITE <fs_fld> TO lv_str LEFT-JUSTIFIED.
CONDENSE lv_str.
IF lv_index = 1.
gt_excel = lv_str.
ELSE.
CONCATENATE gt_excel lv_str INTO gt_excel SEPARATED BY gv_separator.
ENDIF.
ENDDO.
APPEND gt_excel.
CLEAR gt_excel.
ENDLOOP.
*将数据一次性输入到剪切板并复制到EXCEL
*export data to clipboard
DATA: lv_ret TYPE i.
CALL METHOD cl_gui_frontend_services=>clipboard_export
IMPORTING
data = gt_excel[]
CHANGING
rc = lv_ret.
DATA: ls_cell TYPE ole2_object.
CALL METHOD OF p_excel 'RANGE' = ls_cell
EXPORTING
#1 = 'A9'.
* Paste data from clipboard
CALL METHOD OF ls_cell 'Select'.
CALL METHOD OF p_sheet 'Paste'.
ENDFORM. " WRITE_ITEM
*&---------------------------------------------------------------------*
*& Form SAVE_CLOSE_EXCEL
*&---------------------------------------------------------------------*
FORM save_close_excel USING p_excel
p_workbook
p_sheet.
SET PROPERTY OF p_excel 'DisplayAlerts' = 0.
CALL METHOD OF p_excel 'Save'.
CALL METHOD OF p_workbook 'CLOSE'.
CALL METHOD OF p_excel 'QUIT'.
ENDFORM. " SAVE_CLOSE_EXCEL
4.UPDATE_EXCEL子程序
PERFORM get_pc_fieldname.
PERFORM update_excel.
PERFORM excel_check.
*&---------------------------------------------------------------------*
*& Form GET_PC_FIELDNAME
*&---------------------------------------------------------------------*
FORM get_pc_fieldname .
CALL FUNCTION 'WS_FILENAME_GET'
EXPORTING
* mask = ',*.XLS ,*.XLS.'
mode = '0'
title = 'Get the file name'
IMPORTING
filename = gv_file
EXCEPTIONS
inv_winsys = 1
no_batch = 2
selection_cancel = 3
selection_error = 4
OTHERS = 5.
ENDFORM. " GET_PC_FIELDNAME
*&---------------------------------------------------------------------*
*& Form UPDATE_EXCEL
*&---------------------------------------------------------------------*
FORM update_excel .
REFRESH gt_upload.
CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
EXPORTING
* i_line_header = 'X'
i_tab_raw_data = xltab
i_filename = gv_file
TABLES
i_tab_converted_data = gt_upload
EXCEPTIONS
conversion_failed = 1
OTHERS = 2.
IF gt_upload[] IS INITIAL.
MESSAGE '上传EXCEL表没有数据' TYPE 'I'.
EXIT.
ENDIF.
ENDFORM. " UPDATE_EXCEL